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 'SchemaName' ,OBJECT_NAME(ips.OBJECT_ID, database_id) AS 'TableName' ,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

-- Enter the number of pages here: WHERE page_count > 1000 AND index_type_desc <> 'HEAP' AND avg_fragmentation_in_percent > 2.5 ORDER BY DBName , SchemaName , OBJECT_NAME(ips.OBJECT_ID, database_id) , IndexID, [AvgFrag%] desc;
 * source: https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1761816

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL, 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC