byrmol Blog


From the code library - Checking Index health

Analysing index health is a vital part of database maintenance.

Wading through DBCC SHOWCONTIG results is not fun at all.

You probably have something similar but I thought I would share.

–Stub to execute DBCC ShowContig
create proc zzShowContig
set nocount on
dbcc showcontig  with tableresults , all_indexes
–The table to hold the results
create table zzShowContigResults (ObjectName sysname, ObjectID int, IndexName sysname primary key
    , IndexID int, Level int, Pages int, Rows int, MiniumumRecordSize int, MaximummRecordSize int
    , AverageRecordSize money, ForwardedRecords int, Extents int, ExtentSwitches int
    , AverageFreeBytes money, AveragePageDenisty money, ScanDensity money
    , BestCount int, ActualCount int, LogicalFragmentation money, ExtentFragmentation money)
goProcedure to populate the table
create proc zzPopulateContigData
set nocount onDestroy existing results
truncate table zzShowContigResults
insert zzShowContigResults
    EXEC zzShowContig
–remove sys tables
delete zzShowContigResults where left(ObjectName,3) in ('sys', 'dtp')
–Run it!
exec zzPopulateContigData
–Play with it!
select *
from zzShowContigResults
where LogicalFragmentation > 0 or ExtentFragmentation > 0
order by [Extents] desc