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