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

From SQL Server Wiki
Jump to: navigation, search
(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...")
 
 
(One intermediate revision by the same user not shown)
Line 4: Line 4:
 
GO
 
GO
 
SELECT DB_NAME(database_id) AS 'DBName'  
 
SELECT DB_NAME(database_id) AS 'DBName'  
,OBJECT_SCHEMA_NAME(ips.OBJECT_ID, database_id) AS 'SchemaNm'
+
,OBJECT_SCHEMA_NAME(ips.OBJECT_ID, database_id) AS 'SchemaName'
,OBJECT_NAME(ips.OBJECT_ID, database_id) AS 'TabName'
+
,OBJECT_NAME(ips.OBJECT_ID, database_id) AS 'TableName'
 
,OBJECT_ID AS 'ObjectID'
 
,OBJECT_ID AS 'ObjectID'
 
,ips.index_id AS 'IndexID'
 
,ips.index_id AS 'IndexID'
Line 11: Line 11:
 
,page_count AS 'PageCount'
 
,page_count AS 'PageCount'
 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'Limited') ips
 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'Limited') ips
WHERE page_count > 1000
+
 
 +
-- Enter the number of pages here:
 +
WHERE page_count > 1000  
 
AND index_type_desc <> 'HEAP'
 
AND index_type_desc <> 'HEAP'
 
AND avg_fragmentation_in_percent > 2.5
 
AND avg_fragmentation_in_percent > 2.5
 
ORDER BY DBName
 
ORDER BY DBName
, SchemaNm
+
, SchemaName
 
, OBJECT_NAME(ips.OBJECT_ID, database_id)
 
, OBJECT_NAME(ips.OBJECT_ID, database_id)
 
, IndexID, [AvgFrag%] desc;  
 
, IndexID, [AvgFrag%] desc;  
 
</pre>
 
</pre>
 
*source: https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1761816
 
*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
 +
 +
===What Links Here===
 +
{{Special:WhatLinksHere/{{PAGENAME}}}}
 +
 +
[[category:today's topic]]

Latest revision as of 14:29, 23 March 2018

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

What Links Here[edit]