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
0 comments:
Post a Comment