Fragmentation Query?

[bowwow]

DBCC SHOWCONTIG returned the following info on the table Im concerne3d about. The “Extent Scan Fragmentation” is the value I’m concerned about as it indicates fragmentation.

DBCC SHOWCONTIG scanning 'GroomHistory' table...
Table: 'GroomHistory' (1253579504); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 43547
- Extents Scanned..............................: 13265
- Extent Switches..............................: 13264
- Avg. Pages per Extent........................: 3.3
- Scan Density [Best Count:Actual Count].......: 41.04% [5444:13265]
- Extent Scan Fragmentation ...................: 95.44%
- Avg. Bytes Free per Page.....................: 424.1
- Avg. Page Density (full).....................: 94.76%

After running the query below to try and resolve the fragmentation issue, the result of the DBCC SHOWCONTIG is almost identical. There is no indication that the DBCC query below has failed, am I missing something here? Is it that I will only see the positive change over a period of time?

DBCC DBREINDEX (‘dbo.groomhistory’, ‘’, 80)

Any help much appreciated.
Kind Regards
Bowwow

 

[icemel]

I would look into rebuilding the index, if you can afford the "downtime". I forget what the ramifications are of rebuilding... there are options you can use to minimize impact on the other indexes on the table.

You can also try DBCC INDEXDEFRAG first, and see if that makes any difference.

 

[mrdenny]

defragmenting your idnexes won't help with this. You aren't looking at the fragmention of an index. You are looking at the fragmention of the table it self (identified by the index id: 0 at the top).

I know of a couple of ways to clean up a table fragmentation problem.

1. Export the table, drop the table and recreate it, then reload the data.

2. Create a new table, copy the data into this new table, drop the old table, and rename the new table to the same name as the old table.

Do you have a clustered index on this table? If not creating a clustered index may help with this. Make sure that your padding is setup correctly for your data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

Good point about index: 0 -

however, you can still defrag the table by adding a clustered index, then defrag/reindex/rebuild the clustered index