April 16, 2009 at 2:35 pm
I have a SQL2K (8.00.2055) server.
I ran a DBCC "SHOWCONTIG" on my "Accounts" table (it has 795065 records and one Clustered Index), trying to determine if it's fragmented and if it needs defragging. I've read that if the "Logical Scan Framentation" value is above 30%, then the table could benefit from defragging.
True? If so, do I use "DBCC INDEXDEFRAG" do perform the defrag?
Can I use the same methodology on SQK2005?
Any help is appreciated.
DBCC SHOWCONTIG scanning 'Accounts' table...
Table: 'Accounts' (1246627484); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 38095
- Extents Scanned..............................: 4795
- Extent Switches..............................: 30740
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 15.49% [4762:30741]
- Logical Scan Fragmentation ..................: 45.99%
- Extent Scan Fragmentation ...................: 10.16%
- Avg. Bytes Free per Page.....................: 3047.0
- Avg. Page Density (full).....................: 62.35%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
April 16, 2009 at 11:20 pm
rew (4/16/2009)
DBCC SHOWCONTIG scanning 'Accounts' table...Table: 'Accounts' (1246627484); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 38095
- Extents Scanned..............................: 4795
- Extent Switches..............................: 30740
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 15.49% [4762:30741]
- Logical Scan Fragmentation ..................: 45.99%
- Extent Scan Fragmentation ...................: 10.16%
- Avg. Bytes Free per Page.....................: 3047.0
- Avg. Page Density (full).....................: 62.35%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This output shows you that not only shows how fragmented the pages are; you also have wasted space. As the Avg Page Density is stating only 62% of the page on average is full; so about 113MB/297MB of your table space is not being used right now. So I would us DBCC DBREINDEX to rebuild the table with fill factor of 80-90% depending on how dynamic is the data in the table.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply