SQL Server heap tables fragmentation monitoring

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

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/

StackExchange Database Administrators Q#148202, answer score: 6