July 1, 2002 at 5:30 am
I have been asked to check for fragmentation on tables in databases, and to fix it up if necessary.
My problem is this.
I have run a dbcc showcontig on the tables, and get my report on the fragmentation.
I then run a dbcc indexdefrag, run the showcontig command again and the before and after figures of fragmentation are the same. It makes no difference.
I then run a dbcc dbreindex on the tables. Same story, the before and after fragmentation figures are the same. Why?
July 1, 2002 at 9:09 am
Can you post the numbers sans the actual table name?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 1, 2002 at 9:39 am
Table has a clustered index?
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 9, 2002 at 12:13 am
Actual Figures and Table Name are as follows:
DBCC SHOWCONTIG scanning 'ApSupplierNarHdr' table...
Table: 'ApSupplierNarHdr' (692249571); index ID: 1, database ID: 12
TABLE level scan performed
It does have a clustered index
- Pages Scanned................................: 10
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 3.3
- Scan Density [Best Count:Actual Count].......: 66.67% [2:3]
- Logical Scan Fragmentation ..................: 10.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 745.5
- Avg. Page Density (full).....................: 90.79%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
July 9, 2002 at 10:03 pm
Looking at the particular statistics, this is an extremely small table, is it not?
You've got 3 extents. You had 2 extent switches. This is the minimum number. An extent switch occurs whenever the DBCC statement has to move from one extent to another as it goes through the pages of the table or index. So when you go from extent A to extent B, that's an extent switch. When you go from extent B to extent C, that's another. If you were bouncing back and forth between extents, that would be a bad sign.
You want scan density as close to 100% as possible. However, with the small table size, this value is easily skewed. It's the same problem any small sample size (such as a survey of voters or flipping a coin).
You want your logical and extent scan fragmentation as close to 0% as possible. Again, the small size of the DB may be causing the 33% value.
One thing that might help is setting the fillfactor on the DBCC DBREINDEX. What fillfactor setting are you using on that command?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 10, 2002 at 9:51 am
For 10 extents, this cannot be a big deal. I wouldn't really be concerned unless I had over 100 extents.
Brian has good advice.
Steve Jones
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply