December 28, 2007 at 10:52 am
On some tables when I run DBCC ShowContig followed by DBReindex followed by ShowContig I notice Extent Scan Fragmentation actually increases. Why does this happen? Below are the SHOWCONTIG results after running DBReindex three times.
After First DBReindex
- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 47.58%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%
After Second DBReindex
- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 20.16%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%
After Third DBReindex
- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 67.74%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%
Thanks, Dave
December 31, 2007 at 10:59 am
it's nothing to worry about
http://www.sqlservercentral.com/articles/Administering/fragmentation101/1346/
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 1, 2008 at 7:36 pm
How much free space do you have in the database?? Make sufficient free space in the database to contain the rebuilt object (or better 3 or 4 times the size of the object) and try again. If there is contiguous space free the odds are higher that the physical pages and extents will be laid down sequentially. This is better for scan performance as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 2, 2008 at 7:49 am
My indexes are not spread over multiple files so the link does not apply in my case. That's what I find so confusing.
In terms of disk space I have enough free space to accomodate the rebuild of the indexes for the table in my example.
Thanks, Dave
January 2, 2008 at 8:15 am
I think theSQLGuru has touched on the right area. Worth looking at the following which may explain this:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/22/956.aspx
Also useful to note the effect of using SHRINKFILE/SHRINKDATABASE and how they affect fragmentation. Paul Randall also discussed on his blog the problems of Auto_Shrink on fragmentation too:
http://www.sqlskills.com/blogs/paul/default,month,2007-11.aspx#ad9f25699-346a-454b-a56f-656f9ce193bb
Also worth looking at 'Microsoft SQL Server 2000 Index Defragmentation Best Practices' by Mike Ruthruff, if you haven't got it already. Some very useful info and having worked (to some extent) off of his findings, including testing of our own on small to large DBs working in a high-performance 24/7 environment with virtually non-existent maintenance windows, found this to be good guidance. Hopefully the link still works (MS have a habit of changing them :crazy ):
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx?pf=true
Ultimately, though, don't think there is too much to worry about as you've only a little under 1,000 pages.
HTH
January 2, 2008 at 9:05 am
DBADave, you state "In terms of disk space I have enough free space to accomodate the rebuild of the indexes for the table in my example.". Free DISK space is not what I was speaking about. I was speaking about free, contiguous space inside your sql server data file. Unless you have been managing your files yourself and not letting them 'autogrow' (which I have YET to find a client that didn't do), you do not have sufficent free space available (or at least not enough such blocks since you unfortunately cannot specify intra-file placement).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 2, 2008 at 9:19 am
Thanks for the link about the shrinkfile impact on fragmentation. I did not know that.
I'll keep researching the Extent Scan Fragmentation issue to see what else I can find.
Dave
January 2, 2008 at 9:22 am
I understand now. Since I am using AutoGrow is it then nearly impossible to control extent fragmentation? And if so, should I ignore extent fragmentation and concentrate on logical fragmentation, scan density and average page density, which is what I concentrated on in the past?
Dave
January 2, 2008 at 10:32 am
With autogrow maintaining your database size you have virtually no chance of having non-fragmented data and indexes. Double or triple the size of the database and then try some defrags. Note that very large growths will prevent data access for potentially long periods of time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply