Difference between revisions of "The DBA's Essential Index Monitoring and Maintenance Tasks"

From SQL Server Wiki
Jump to: navigation, search
Line 29: Line 29:
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC
ORDER BY avg_fragmentation_in_percent DESC
===What Links Here===
[[category:today's topic]]

Latest revision as of 14:29, 23 March 2018

Check for Index Fragmentation:

USE master
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'
,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
, 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

What Links Here[edit]