As a SQL Server DBA I have maintenance jobs to regularly rebuild/reorganize indexes. Recently, I ask myself about table fragmentation in SQL Server. Then I have read article regarding heap tables and their fragmentation. We constantly check the status of SQL servers in various way, and I am wondering if adding a check on table fragmentation will be useful.
I’m thinking to develop something like: "Is their a cluster index ?" yes -> do nothing, no -> so check :
percentage of fragmentation > x%
numbers of forward pointing > x
numbers of rows > x
if x > some values then raise an alert.
What do you think of setting up that kind of check, useful or not?
Are Defragmentation of heap tables going to reduce physical size of database's files on disk ?
EDIT :
I finally use this piece of code to detect heap tables which need to be rebuild :
SELECT Database_id, Object_name([object_id]) as TableName, Index_type_desc,
Avg_fragmentation_in_percent, rowmodctr, forwarded_record_count
From sys.dm_db_index_physical_stats(db_id(),object_id(''),null,null,'detailed') AS SDDIPS
Inner join sys.sysindexes AS SI on SDDIPS.[object_id] = SI.id
AND SDDIPS.index_id = SI.indid
--Inner join sysobjects AS SO on SI.id = SO.id
Where index_level = 0 AND index_type_desc = 'HEAP' AND Avg_fragmentation_in_percent > 40
AND rowmodctr > 100 AND forwarded_record_count > 1000
However it runs for a long time period and performance like PLE collapse