Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Fragmentation Fear

By Tony Davis,

It's always bothered me slightly that the main rationale behind the design of clustered indexes in SQL Server seems to be to proactively prevent…fragmentation, rather than organize the data based on the needs of the most critical queries, by storing together those values that we are likely to query together. It was refreshing to see Brent Ozar's recent take on the index fragmentation issue, and his advice to "stop worrying" so much about it, and instead try to tackle the real underlying problems.

As Brent points out, index defragmentation is easy to do. We can configure SQL Server Maintenance Plans to rebuild all our indexes, on a schedule, regardless of whether they need it or not. Unfortunately, most of the rebuilds will be a waste of time, if not actively harmful. Ola Hallengren's scripts, or any script that interrogates the DMVs to check the fragmentation level, before rebuilding, offers a much better option. However, if the rebuild threshold is some arbitrary value across all indexes, then you'll still do a lot of needless rebuilding.

Index fragmentation can, of course, cause performance problems, but is actually not a huge issue for many indexes. Logical fragmentation (index pages in the wrong order) thwarts SQL Server's read-ahead mechanism and makes it less IO-efficient at reading contiguous pages on disk. However, this only really affects large range scans from disk, with the emphasis on large and disk. Reduced page density (many gaps causes by page splits and deletes) is a knottier form of fragmentation. If pages are "half full", they will take up twice the space on disk, and in memory, and twice the IO bandwidth to transfer the data. Again, though, this won't affect infrequently modified indexes.

If our data is in memory anyway, as Brent suggests, or we're able to take hard look at our queries and reduce the number of large range scans, then we can stop worrying so much about fragmentation for those indexes. Once we do, maybe we can return to considering index design primarily in terms of establishing the "natural order" of the data, rather than fragmentation fear.

What do you think? What might make it easier to get over fragmentation fear? Would more/better indexing options help (I noticed Oracle has at least four index clustering structures to SQL Server's one)?


Total article views: 314 | Views in the last 30 days: 2
Related Articles

Index Rebuilds Still Leave Fragmentation

Index Rebuilds Still Leave Fragmentation


Index rebuild and fragmentation level

Why did the fragmentation level increase after rebuild ?


Rebuilding indexes

Rebuilding indexes


Rebuild Indexes of database with fragmentation more than 30%

Rebuild of indexes is the prime job of a DBA as heavily fragmented indexes can degrade query perform...


index fragmentation

index fragmentation

database weekly    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones