Question :
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?
Answer :
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…