December 14, 2009 at 8:48 am
I'm working on a script to rebuild all indexes with a fragmentation > 30%. I have the script working but it looks like the rebuilding isn't working. When i looked closer the problem seems to be the online option.
The first thing i do is looking for fragmented indexes with the dm_db_index_physical_stats DMF. The fragmented indexes are rebuild:
ALTER INDEX <index> ON <table> REBUILD WITH (ONLINE=ON)
But when i look at the dm_db_index_physical_stats DMF after the rebuild the fragmentation isn't lower and in some cases even higher!!! So after this i investigated further with the DBCC SHOWCONTIG function. This resulted into the next results for a fragmented index.
Before any rebuild:
- Pages Scanned................................: 177478
- Extents Scanned..............................: 22272
- Extent Switches..............................: 76445
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 29.02% [22185:76446]
- Logical Scan Fragmentation ..................: 77.97%
- Extent Scan Fragmentation ...................: 20.44%
- Avg. Bytes Free per Page.....................: 1615.8
- Avg. Page Density (full).....................: 80.04%
After rebuilding with the "WITH (ONLINE = ON)" option, as you can see the fragmentation even increased!
- Pages Scanned................................: 177491
- Extents Scanned..............................: 22288
- Extent Switches..............................: 85009
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 26.10% [22187:85010]
- Logical Scan Fragmentation ..................: 87.84%
- Extent Scan Fragmentation ...................: 19.80%
- Avg. Bytes Free per Page.....................: 1616.2
- Avg. Page Density (full).....................: 80.03%
After rebuilding without the "WITH (ONLINE = ON)" option:
- Pages Scanned................................: 168640
- Extents Scanned..............................: 21082
- Extent Switches..............................: 21081
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.99% [21080:21082]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 13.28%
- Avg. Bytes Free per Page.....................: 1617.1
- Avg. Page Density (full).....................: 80.02%
Can anybody explain this behavior??
December 14, 2009 at 9:11 am
Do you have autoshrink on? Are you doing a manual database shrink after the index rebuild?
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
December 14, 2009 at 10:05 am
GilaMonster (12/14/2009)
Do you have autoshrink on? Are you doing a manual database shrink after the index rebuild?
I thought autoshrink was off, but it seems to be on :ermm:
Edit: After turning it off everything works as it should be. Now i only have to investigate what the consequences are of turning this thing off...
December 14, 2009 at 11:52 am
Consequences of turning auto-shrink off - improved performance. Read the following, and then read the two blog posts that it links to (at the bottom)
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply