This article describes how to identify tables that are fragmented internally (i.e., fragmentation inside data pages) or externally (i.e., extents are fragmented).
The process of doing a DBCC re-index of a large database can be a time-consuming task. In most cases the fragmentation occurs in just a part of the database and is concentrated in just a few tables. Re-indexing just those tables should be sufficient and thus precious time and computer resources can be saved.
Here I describe the way to identify the fragmented tables. I use the DBCC SHOWCONTIG T-SQL statement as a basis to gather information about all user tables in the database that have a CLUSTERED index. I assume here that fragmentation in the clustered index would lead to fragmentation in all other indexes since the cluster index is used as the "cluster key" with all other non-clustered indexes that are built upon it.
I use DBCC SHOWCONTIG with the TABLESRESULT clause in order to query the resulting results table to list all the table names that have either internal fragmentation that falls below a given acceptable scan density percentage or external fragmentation that is greater than a given extent fragmentation percentage. The resulting tables should be defragmented using re-index or index defrag.
Here is the procedure code for identifying fragmented tables. The procedure should be compiled inside the database to be re-indexed.
Create procedure sp_list_fragmented_tables ( @internalFragmentationPctAllowed int, @externalFragmentationPctAllowed int ) as BEGIN Declare @showContigStmt varchar(100) Declare @CurrTable varchar(50) /* Cursor declared on USER tables that have A clustered index */ DECLARE c_examined_tables cursor for select a.table_name from information_schema.tables a,sysindexes b where a.table_type = 'BASE TABLE' and a.table_name = object_name (b.id) and b.indid = 1 set noCount on /* create Result table as a temporary table */ Create table #showContigResults (ObjectName sysname, Objectid bigint, IndexName sysname, indexid int, [level] int, pages int , [rows] bigint, minRecsize int, maxRecsize int, avgRecSize real , ForwardRecs int, Extents int, ExtentSwitches int, AvgFreeBytes real, AvgPageDensity real, ScanDensity decimal(5,2), BestCount int, ActCount int, LogicalFrag decimal (5,2), ExtentFragmentation decimal (5,2)) /* loop over all tables and exec DBCC SHOWCONTIG with TABLERESULTS format */ OPEN c_examined_tables FETCH NEXT FROM c_examined_tables INTO @CurrTable WHILE @@FETCH_STATUS = 0 BEGIN set @showContigStmt = 'DBCC SHOWCONTIG ([' + @currTable + '])' + ' with tableresults' Insert #showContigResults exec (@showContigStmt) FETCH NEXT FROM c_examined_tables INTO @CurrTable END close c_examined_tables deallocate c_examined_tables /* output fragmented objects that fall within the criteria */ select ObjectName , ScanDensity , ExtentFragmentation from #showContigResults where ScanDensity < @internalFragmentationPctAllowed or ExtentFragmentation > @externalFragmentationPctAllowed END go
Here is an example of a call to the function:
/* list only tables that have less than 50% SCAN density (internal fragmentation) and more than 10% Extent fragmentation (external fragmentation) */ exec sp_list_fragmented_tables 50,10
The process I showed here can be used as a tool for identifying fragmented tables and focusing de-fragmentation or re-indexing maintenance tasks just to those objects and thus reduce the time- and resource-intensive job of re-indexing all the tables in a large database.
About the author
Eli Leiba works at the Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. His email is firstname.lastname@example.org.