DBCC SHOWCONTIG and DBCC INDEXDEFRAG in SQL Server 2005

Index is most important part of the SQL Server though it is not a concept of RDBMS. Index is really important to boost up the speed of data retrieval but just creating index will not do, there must be a policy of maintaining an index also, because whenever you perform any insert, update or delete operation on the table data-distribution states will get out of date.

DBCC INDEXDEFRAG command is used to maintain the index and its stat whereas DBCC SHOWCONTIG used to see fragmentation details and density for a given index and/or table.

Let us see it practically.

–create one table for test

create table DBCCTest

(

ID uniqueidentifier not null CONSTRAINT pkID PRIMARY KEY CLUSTERED DEFAULT NEWID(),

name varchar(20),

DateEntered datetime DEFAULT GETDATE()

)

–to check the index density and performance, you have to have couple of hundred thousand rows.

–let us create one loop to enter data

–I will run below batch five times to enter

–quarter milion of data row

SET NOCOUNT ON

DECLARE @I INT

SET @I =0

WHILE @I<50000

BEGIN

–we won’t insert ID and DateEntered as we have default value there

INSERT INTO DBCCTest (Name) VALUES(‘Ritesh’)

SET @I=@I+1

END

–now let us check states of indexes by DBCC ShowCOntig

DBCC SHOWCONTIG(DBCCTest) WITH ALL_INDEXES

–DBCC SHOWCONTIG scanning ‘DBCCTest’ table…

–Table: ‘DBCCTest’ (1028198713); index ID: 1, database ID: 7

–TABLE level scan performed.

— Pages Scanned…………………………..: 1974

— Extents Scanned…………………………: 249

— Extent Switches…………………………: 1973

— Avg. Pages per Extent……………………: 7.9

— Scan Density [Best Count:Actual Count]…….: 12.51% [247:1974]

— Logical Scan Fragmentation ………………: 99.19%

— Extent Scan Fragmentation ……………….: 0.40%

— Avg. Bytes Free per Page…………………: 2650.2

— Avg. Page Density (full)…………………: 67.26%

–DBCC execution completed. If DBCC printed error messages, contact your system administrator.

–above is the results of SHOWCONTIG command.

–now let us use INDEXDEFRAG

DBCC INDEXDEFRAG (‘ADVENTUREWORKS’,‘DBCCTest’,‘pkID’)

–here is the results of index defragmentation

–pages scanned pages moved pages removed

–1949 1332 638

–now again check SHOWCONFIG

DBCC SHOWCONTIG(DBCCTest) WITH ALL_INDEXES

–SEE the result difference in both SHOWCONTIG run.

–DBCC SHOWCONTIG scanning ‘DBCCTest’ table…

–Table: ‘DBCCTest’ (1028198713); index ID: 1, database ID: 7

–TABLE level scan performed.

— Pages Scanned…………………………..: 1336

— Extents Scanned…………………………: 171

— Extent Switches…………………………: 176

— Avg. Pages per Extent……………………: 7.8

— Scan Density [Best Count:Actual Count]…….: 94.35% [167:177]

— Logical Scan Fragmentation ………………: 1.12%

— Extent Scan Fragmentation ……………….: 7.02%

— Avg. Bytes Free per Page…………………: 49.6

— Avg. Page Density (full)…………………: 99.39%

–DBCC execution completed. If DBCC printed error messages, contact your system administrator.

–let us not keep big garbage after checking

DROP TABLE DBCCTest

Happy SQLing with SQLHub!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah