Home / server / Questions / 230100
Accepted
Martin Smith
Asked: 2011-02-03 03:38:41 +0800 CST

How can Windows defragmentation tools cause internal fragmentation in SQL Server?

  • 772

I was just reading this article where the author talks about the file system fragmentation that can be caused by growing database files. There was one bit that I didn't quite follow.

What about Windows defragmentation tools?

Although you can use a Windows defragmentation tool to defragment your database files, these tools simply move chunks of files around to get them contiguous. This moving of chunks of files can cause internal fragmentation that you might not be able to resolve easily.

Is the author saying here that the disc defragmenter makes no attempt to put the chunks of files in the correct sequence or have I misunderstood? If he is saying that then is this a limitation of all disc defragmenter utilities - even commercial ones?

sql-server fragmentation

2 Answers

  1. Any external fragmentation tool (doesn't matter commercial or free) does not restructure the internal contents of the file, unlike DBCC REINDEX.

    After defragmentation, the defragmented file will be a bit for bit duplicate of the original. Therefore, any holes within the database are still present and you will still need, from time to time, to rebuild your indexes to combat internal fragmentation.

    • 1
  2. Best Answer

    Luckily, you can resolve both internal table extent fragmentation and page fragmentation by rebuilding your indexes or by building and dropping a clustered index on a heap table.

    1. You should not have heaps
    2. You should be rebuilding your indexes regularly anyway.

    To resolve table fragmentation, you often have to rebuild the table or clustered index in a different filegroup.

    Not everyone has a spare filegroup.

    [cynical]

    Seems to me a pitch to use "Kronos Workforce" as a magic way to do stuff your DBAs should be doing anyway. I've had the same issues with vendors pitching some whizzy (expensive) product to my management to do standard DBA tasks

    [/cynical]

    Edit: It appears I've misunderstood. Kronos is not a DBA tool.

    However, the FUD in the article could be explained by these "tweaks"

    • Changing the max degree of parallellism (MAXDOP) to 1
    • Set the default Index Fill Factor to 75 percent
    • Disable the Auto Update Statistics Setting
    • 0