The DBA's Essential Index Monitoring and Maintenance Tasks

From SQL Server Wiki
Revision as of 15:23, 9 March 2018 by RobertSter (talk | contribs) (Created page with "Check for Index Fragmentation: <pre> USE master GO SELECT DB_NAME(database_id) AS 'DBName' ,OBJECT_SCHEMA_NAME(ips.OBJECT_ID, database_id) AS 'SchemaNm' ,OBJECT_NAME(ips.OBJE...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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;