August 6, 2009 at 3:43 pm
I would like some clarification on what I should be looking at to find out how fragmented and index is. I have been using the following code to find the most fragmented index on a particular DB and Table.
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(@Databasename),
OBJECT_ID('@Tablename), NULL, NULL, NULL)
ORDER BY avg_fragmentation_in_percent DESC
I then have run the following command to rebuild a particular index.
Alter Index [@Index] On [@Tablename] Rebuild With (Online = On)
After the rebuild when I check the avg_fragmentation_in_percent column I would expect to see that percentage at zero but I do not. Is this not the correct place to look, or am I missing something with my Alter Index query?
August 7, 2009 at 3:13 am
How big is the index in question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 4:30 am
The first eight pages of a table are allocated from mixed extents. These pages will therefore almost certainly always be technically logically fragmented. If the table is relatively small, the effect of this unavoidable fragmentation may dominate the overall figure reported.
A mixed extent contains the usual eight pages (8KB each) but the individual pages may be shared by one or many different objects. Since pages allocated from mixed extents will almost certainly not be logically contiguous (the page number order will not match the logical order) these count as fragmented. There's nothing to be done about this. SQL Server never consolidates pages from mixed extents into uniform extents even if a clustered index is rebuilt.
SQL Server only starts allocating complete (uniform) extents to an object once it has grown beyond the first eight pages. Note that SQL Server will never go back to allocating from mixed extents once it has started allocating uniform extents, even if the object falls back below eight pages in size - or even if it is emptied completely.
On a table with hundreds of pages (all but the first eight from uniform extents) the effect of the first eight pages being probably logically out-of-order therefore has a minimal impact on the overall fragmentation figure.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 7, 2009 at 6:54 am
The index that I am testing with is only 63 KB.
August 7, 2009 at 7:00 am
Way too small to worry about fragmentation. Start getting concerned about fragmentation when there are around 1000 pages in the index. Fragmentation just isn't a problem on very small indexes because it's very likely that they'll be in memory all the time. Fragmentation's only a problem when doing large range scans from disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 7:10 am
rod.means (8/7/2009)
The index that I am testing with is only 63 KB.
That will just fit in the first (mixed) extent. My previous comments apply to indexes as well as tables, in case that wasn't clear.
I should just add that in fact one can get rid of the single page allocations by rebuilding the clustered index once the structure (table or index) has reached 3 extents (24 pages) in size. Apparently the allocation algorithm changed at some point in SQL Server 2005.
(You can still get single-page allocations at non-leaf levels)
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply