SQL Unused Indexes Test
One of the balancing acts of SQL Server is the use of indexes. Too few indexes can cause scans to occur which hurts performance and too many indexes causes overhead for index maintenance during data updates and also a bloated database. Administrators hence need to continuously track which indexes are used and how they are used. Unused indexes in particular can have a negative impact on performance. This is because when the underlying table data is modified, the index may need to be updated also. This takes additional time and can even increase the probability of blocking. To avoid this, administrators need to rapidly isolate the unused indexes and drop them. The Unused Indexes test facilitates this. Using this test, administrators can quickly determine the number of unused indexes in each database, and also understand how badly that index impacts database performance.
This test is disabled by default. To enable the test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Microsoft SQL as the Component type, Performance as the Test type, choose this test from the disabled tests list, and click on the << button to move the test to the ENABLED TESTS list. Finally, click the Update button.
Target of the test : A Microsoft SQL server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for every database on the monitored Microsoft SQL server
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Number of Unused Indexes: |
Indicates the total number of unused indexes currently found in this database. |
Number |
Use the detailed diagnosis of this measure to know which indexes in a database are unused. The detailed diagnosis also reveals how often each such index has been updated, so that you can assess the unnecessary overheads that the database may have incurred in the process. |