byrmol Blog

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
as
set nocount on
dbcc showcontig  with tableresults , all_indexes
go
--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)
go
--Procedure to populate the table
create proc zzPopulateContigData
as
set nocount on
--Destroy existing results
truncate table zzShowContigResults
--Populate
insert zzShowContigResults
    EXEC zzShowContig
--remove sys tables
delete zzShowContigResults where left(ObjectName,3) in ('sys', 'dtp')
go
--Run it!
exec zzPopulateContigData
--Play with it!
select *
from zzShowContigResults
where LogicalFragmentation > 0 or ExtentFragmentation > 0
order by [Extents] desc