18,272 Metrics install automatically if you have Redgate Monitor installed.
If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.
Use this metric if you want to monitor the number of indexes per database that haven’t been used for the last month. Indexes that aren’t in use should be removed because they can degrade INSERT, UPDATE and DELETE performance, and they use storage space.
For more information, see http://blogs.msdn.com/b/sqlcat/archive/2006/02/13/531339.aspx.
If you want to see which specific indexes haven’t been used for the last month, run this query:
[sql]
SELECT db_name() as dbname,
o.name as tablename,
i.name as indexname,
i.index_id,
user_seeks + user_scans + user_lookups as total_reads,
user_updates as total_writes,
(SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.index_id = s.index_id
AND s.object_id = p.object_id) as number_of_rows,
s.last_user_lookup,
s.last_user_scan,
s.last_user_seek
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
ON i.index_id = s.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(o.object_id, ‘IsUserTable’) = 1
AND ISNULL(s.database_id, DB_ID()) = DB_ID()
AND (
isnull(s.last_user_seek, ‘19000101’) < datediff(month, -1, getdate()) AND
isnull(s.last_user_scan, ‘19000101’) < datediff(month, -1, getdate()) AND
isnull(s.last_user_lookup, ‘19000101’) < datediff(month, -1, getdate())
)
ORDER BY total_reads DESC;[/sql]
Note: If you identify an index that hasn’t been used in the last month, check whether it really is unused or whether an application is not using the index on hints. The DMV counter:[sql]sys.dm_db_index_usage_stats[/sql] can help you with this, but also bear in mind that these DMV counters are initialized to empty whenever the SQL Server MSSQLSERVER service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
Metric definition
Name
Number of unused indexes
Description
Use this metric if you want to monitor the number of indexes per database that haven't been used for the last month. Indexes that aren't in use should be removed because they can degrade INSERT, UPDATE and DELETE performance, and they use storage space.
For more information, see http://blogs.msdn.com/b/sqlcat/archive/2006/02/13/531339.aspx.
If you want to see which specific indexes haven't been used for the last month, run this query:
SELECT db_name() as dbname,
o.name as tablename,
i.name as indexname,
i.index_id,
user_seeks + user_scans + user_lookups as total_reads,
user_updates as total_writes,
(SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.index_id = s.index_id
AND s.object_id = p.object_id) as number_of_rows,
s.last_user_lookup,
s.last_user_scan,
s.last_user_seek
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
ON i.index_id = s.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
AND ISNULL(s.database_id, DB_ID()) = DB_ID()
AND (
isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND
isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND
isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())
)
ORDER BY total_reads DESC;
Note: If you identify an index that hasn't been used in the last month, check whether it really is unused or whether an application is not using the index on hints. The DMV counter:[sql]sys.dm_db_index_usage_stats[/sql] can help you with this, but also bear in mind that these DMV counters are initialized to empty whenever the SQL Server MSSQLSERVER service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
The T-SQL query that will collect data
Instances to collect from
Select all
Databases to collect from
1
Collection frequency
86400
Use collected or calculated values
Leave the Use a calculated rate of change between collections check box unchecked
Metric collection
Enabled
Alert definition
Alert name
Number of unused indexes is too high
Description
This alert is raised if the number of indexes that haven't been used for the last month exceeds the set thresholds. If you want to see which specific indexes haven't been used, run this query:
SELECT db_name() as dbname,
o.name as tablename,
i.name as indexname,
i.index_id,
user_seeks + user_scans + user_lookups as total_reads,
user_updates as total_writes,
(SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.index_id = s.index_id
AND s.object_id = p.object_id) as number_of_rows,
s.last_user_lookup,
s.last_user_scan,
s.last_user_seek
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
ON i.index_id = s.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
AND ISNULL(s.database_id, DB_ID()) = DB_ID()
AND (
isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND
isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND
isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())
)
ORDER BY total_reads DESC;
Note: If you identify an index that hasn't been used in the last month, check whether it really is unused or whether an application is not using the index on hints. The DMV counter:[sql]sys.dm_db_index_usage_stats[/sql] can help you with this, but also bear in mind that these DMV counters are initialized to empty whenever the SQL Server MSSQLSERVER service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
Raise an alert when the metric value goes
Above the defined threshholds
Default threshold values
| High: | 50 |
| Medium: | 10 |
| Low: | 0 |
Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.
Raise an alert when the threshold is passed for
1 collection
Alert is
Enabled
