SQL Server heap tables fragmentation monitoring

Question

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

Answer 1

Are Defragmentation of heap tables going to reduce physical size of database's files on disk ?

No they are not going to reduce physical size of files. The fragmentation we are talking about is logical fragmentation not physical. As per BOL the fragmentation for heap is defined as

This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

Read more about heap fragmentation in This SE thread. I have explained what does heap fragmentation means.

The concept of fragmentation does not actually fits to heap they are bunch of un-ordered pages. The best advise to deal with heap is create a clustered index on it. Also note that when you rebuild a heap you also rebuild all non clustered indexes so be careful because this is going to generate lot of I/O and transaction log. There might be case where you need to rebuild heap to get rid of forwarding pointers in that case you ofcourse can but keep an eye on NCI that are getting rebuilt.

I dont think getting an alert when fragmentation crosses certain limit for an index is good idea. Just define a plan to rebuild index weekly or twice in a week that should suffice. You already have Ola Hallengren script at your disposal.

Answer 2

I would suggest that, rather than 'roll-your-own', you save yourself a whole bunch of time and effort and implement Ola Hallengren's Index Maintenance script.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

And, check out Brent Ozar's tweaks for Ola's script

https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/