Tuesday, April 28, 2009

View Possible Bad indexes on SQL Database

Execute the following code to view possible bad indexes in SQL Database;

SELECT object_name(s.object_id) AS 'Table', i.name AS 'Index ', i.index_id,
user_updates AS 'Total Number of Writes', user_seeks + user_scans + user_lookups AS 'Total Number of Reads',
user_updates - (user_seeks + user_scans + user_lookups) AS 'Difference'
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = db_id()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY 'Difference' DESC, 'Total Number of Writes' DESC, 'Total Number of Reads' ASC;

No comments:

Post a Comment

 
span.fullpost {display:inline;}