The DBA's Essential Index Monitoring and Maintenance Tasks

Check for Index Fragmentation: USE master GO SELECT DB_NAME(database_id) AS 'DBName' ,OBJECT_SCHEMA_NAME(ips.OBJECT_ID, database_id) AS 'SchemaNm' ,OBJECT_NAME(ips.OBJECT_ID, database_id) AS 'TabName' ,OBJECT_ID AS 'ObjectID' ,ips.index_id AS 'IndexID' ,CAST (avg_fragmentation_in_percent AS decimal(4,2)) AS 'AvgFrag%' ,page_count AS 'PageCount' FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'Limited') ips WHERE page_count > 1000 AND index_type_desc <> 'HEAP' AND avg_fragmentation_in_percent > 2.5 ORDER BY DBName , SchemaNm , OBJECT_NAME(ips.OBJECT_ID, database_id) , IndexID, [AvgFrag%] desc;
 * source: https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1761816