rowan.collis

sqlserver custom checkpoint for fragmentation

Discussion created by rowan.collis on Apr 20, 2011
Latest reply on Jul 12, 2017 by RowanCollis

SQLSERVER probe av_fragmentation.

 

The av_fragmentation checkpoint in the standard template picks up all indexes in all databases in the instance.

Its way too much information so here is the query to limit the fragmentation data to the indexes that you are interested in.

You can use normal sql pattern matching to pick up the indexes you want and create a custom checkpoint for which puts the data into qos and therefore UMP.

In this query I'm only interested in the RN tables.

 

select ix.name as IndexName, IPS.avg_fragmentation_in_percent as av_frag
   FROM sys.dm_db_index_physical_stats(DB_ID('NimsoftSLM'), NULL, NULL, NULL , 'LIMITED') AS IPS join NimsoftSLM.sys.objects o on IPS.object_id=o.object_id join NimsoftSLM.sys.indexes ix on IPS.object_id=ix.object_id and IPS.index_id=ix.index_id where o.name like 'RN%' order by o.name

 

Setup av_frag as your checked value and IndexName as the Row Indentifier (and Object) in the checkpoint and you'll get a single target for each index.

Outcomes