I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx)

The avg_fragmentation_in_percent is a whopping 97% or more and the fragment count is in the thousands. Very interestingly, the avg_fragment_size_in_pages is just slightly more than 1. I can defrag it, which helps, but I'd like to prevent this from happening in the first place.

Why is the pages per fragment so low? I have FILEGROWTH = 128MB for the DB as a whole, but this particular table is the most active - so is there a way to tell SQL Server to allocate more growth to this table or index so that the pages-per-fragment is higher?

  • I should note as well, that this fragmented table gets a lot of inserts, but very rarely deletes.
    – Sugrue
    May 30 '13 at 9:08
  • 2
    What are you using for a clustered key for the table? Is it a monotonically increasing value (like an INDENTITY) or is it random (eg; GUID or character string). If it's the latter you may be getting page splits due to data having to be inserted to an existing page. May 30 '13 at 9:13
  • 2
    What is the fillfactor? What is the size of this table? And row size?
    – gbn
    May 30 '13 at 9:20
  • Stuart - That might be it. I have 3 indexes on this table. Index IX is basically sequential and 5% fragmented, 6 pages per fragment. Index PK is in time order and is 85% fragmented and Index SX is spatial and is 97% fragmented. Both PK and SX have 1 page per fragment. gbn - I don't seem to be setting the fillfactor; whats default? And what would be the best value for this?
    – Sugrue
    May 30 '13 at 9:50
  • 2
    You should fix the root cause of the fragmentation - not just apply a patch to it.... why is the table so fragmented? What is your clustering key (what columns, what datatypes)? Fix that first!
    – marc_s
    May 30 '13 at 11:20

First and foremost you should assess the impact of fragmentation. Much too often fragmentation is painted as the ultimate evil cause of all the server problems w/o any consideration of its actual impact. Fragmentation impacts several aspects:

  • Slow down scans due to impact on read-ahead efficiency and small IO size (page vs. extent)
  • Less efficient IO due to low rows per page because of frequent splits
  • Lower buffer pool cache hit ration (ie. lower page life expectancy) due to same as above
  • Higher cost impact of INSERTs due to frequent splits

All above are bad, but here is the real deal: you can have 97% fragmentation w/o any of the above symptoms being present. High fragmentation can indeed cause all these problems, but only if your specific workload actually interacts with fragmentation in a manner that causes these symptoms to appear.

I would recommend using Waits and Queues methodology to locate your actual performance bottlenecks and asses them appropriately. You may very well confirm that fragmentation has a serious impact and that would require schema and possibly application changes to address the problem, but you may discover that the problem lies somewhere else and the solution is completely different.

And lastly: you may also discover that $5k spent on more RAM and a few good SSDs alleviate completely the problems you have. Before you shrug in horror at such a remedy (what, no root cause being addressed? anathema!) consider the risk and costs associated with an application change...

  • Thanks Remus, I have measurable slow down issues with this index (timeouts is what drew my attention to it). Buying more ram isn't an option in this case as the database is created and used by customers on their machines.
    – Sugrue
    May 30 '13 at 11:53
  • 1
    Client timeouts, like the .Net SqlCommand default 30 seconds timeouts? May 30 '13 at 11:57
  • yes, "System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." It happens rarely, but much more often when fragmentation is high. Search is also much, much slower.
    – Sugrue
    May 30 '13 at 12:27
  • 3
    No way fragmentation causes that. 30 seconds would come from things like file growth or lock conflicts (likely caused by scans). I would focus on understanding what happens when these timeouts occur. May 30 '13 at 12:31
  • 4
    Any answer I'd venture would be speculation. Try Waits and Queues. Collect perf counters. Measure. May 30 '13 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.