The DBA's Essential Index Monitoring and Maintenance Tasks

From SQL Server Wiki
Revision as of 18:58, 9 March 2018 by RobertSter (talk | contribs)
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 '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; 


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