In my previous blog entry, I talked about index fragmentation and some of the tools that could be used to monitor and remove the fragmentation. The general rule to follow, according to Microsoft best practices, is to rebuild an index with more than 30% fragmentation. In a recent class I was teaching, we used the SSMS built-in reports to find out which indexes to rebuild. Using SQL Server Management Studio 2008, right-click on your database, select Reports then Standard Reports then choose Index Physical Statistics. This report will show fragmentation details for all indexes in a database and will recommend rebuilding where that 30% threshold has been passed. Using AdventureWorks, we homed in on a particular table, Production.Product where all the indexes needed rebuilding. Okay, the clustered index only had 23% fragmentation so the recommendation was to reorganize rather than rebuild the index which executes a light-weight defrag process. Trouble was, when we ran the rebuild and reorganize processes, the fragmentation figures were unchanged ranging from 23% to an alarming 67%. So what's the deal?
At first, I thought it was because of the FILLFACTOR option. This allows you to leave a portion of free space on each leaf-level data page in an index for new rows to be added therefore increasing insert performance by reducing expensive page-splits. For some reason, SSMS does not show you the original FILLFACTOR for an index but SQL Server does store this information in its system tables as it needs that information during rebuild time, because it reapplies the original FILLFACTOR at that time. I looked in the system catalog view sys.indexes and found the fill_factor column. Getting the value out for a particular index is tricky since you need to use the OBJECT_ID function for the index first. But that value came up as zero in each case which is the default and actually translates to 100% fillfactor, filling each data page completely. OK, that was a red herring. Nice try.
Analyzing the data, I soon realized the real reason was the LACK of data. This table had only 504 rows and only consumed 13 data pages in the clustered index. The non-clustered indexes used only 2 or 3 data pages. In these cases, the fragmentation algorithm gets easily fooled. It cannot tell between normal fragmentation and a half-full final page. In a large table, the final page being partially full does not scew the entire calculation of fragmentation since there are so many data pages. However, if there are only a handful of pages, the calculation is seriously flawed. In further testing, I discovered that it was not the report's fault. All the techniques for monitoring fragmentation including DBCC SHOWCONTIG and sys.dm_db_index_physical_stats were fooled by small tables. Surely the algorithm should be modified to ignore the last data page? Or maybe seasoned DBAs know not to worry about small table indexes. Touchez...
Cheers
Brian
Recent posts
Index Fragmentation…resources getting better?
More on the SSWUG.org Virtual Conference
SSWUG.org Virtual Conference first impressions
Advertisement: |
The Leader in Network Knowledge
No concern with less than 1000 pages
According to Microsoft you don't need to concern yourself with fragmentation if there are less than 1000 pages.
Post new comment