Please follow the below step for creating a custom checkpoint:-
- Open the gui of the sqlserver probe
- Navigated to the template tab and create a custom checkpoint with the below query:
sp_MSforeachdb 'SELECT distinct ''?'' db_name, OBJECT_NAME(i.object_id,db_id(''?'')) AS tablename, isNull(i.name,'''') AS indexname, phystat.avg_fragmentation_in_percent as fragmentation,alloc_unit_type_desc as alloc_unit FROM sys.dm_db_index_physical_stats(db_id(''?''), NULL, NULL, NULL, ''LIMITED'') phystat JOIN [?].sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id where OBJECT_NAME(i.object_id) is not null'
- Deactivate the default checkpoint 'av_fragmentation'
- Activate the new custom checkpoint for monitoring.
The above query may return multiple rows. If it does, the query would need to be modified by your DBA to include a column with a unique key for each row so that a row identifier can be setup in the probe.
Without a unique row identifier, the probe can not check each row and send alarms as needed.