EX0011 : Identify inefficient indexes using dynamic management views information
Introduction
Section titled “Introduction”Identifying and managing unused indexes in SQL Server is essential to optimize database performance and resource utilization.
Description
Section titled “Description”In SQL Server, unused indexes can consume significant resources, including storage space and processing power, without contributing to query performance enhancements. Regular maintenance and tuning of indexes is a critical process for database administrators to ensure optimal performance and efficiency. Understanding which indexes are truly unused helps in decluttering the database system and improving its performance.
Example of an index potentially identified as unused:
-- Usage of the index might be minimal or non-existentCREATE INDEX idx_sample ON SampleTable (Column1);Unused indexes, like the one above, are often retained indefinitely due to oversight, adding unnecessary overhead during data modification operations. It’s crucial to periodically review index usage statistics to identify candidates for removal or alternative optimization strategies.
-
Unused indexes can slow down data modification operations, such as
INSERT,UPDATE, andDELETE, because SQL Server maintains all defined indexes. -
Retaining unused indexes leads to increased storage costs and longer backup times due to the larger database size.
-
Occasionally used indexes, such as those for monthly reports or specific queries, need careful evaluation before removal.
The rule uses the SQL Server Dynamic Management Views to get index usage information.
The unused indexes information is based on statistics gathered since the last time SQL Server instance was started.
⚡ ** note: ** Be careful when dropping unused or rarely used indexes. Some indexes can be used rarely and be created for specific purpose; for example, to optimize monthly report.
How to fix
Section titled “How to fix”The rule has a ContextOnly scope and is applied only on current server and database schema.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| IndexEfficiencyMininumPercent | The parameter specifies the efficiency of the index since the last server restart. The index efficiency is calculated based on the reads per writes. | 100 |
| IndexMinimumNumberOfRows | Indexes covering less rows than specified by this parameter will be ignored. | 10000 |
Remarks
Section titled “Remarks”The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”3 hours per issue.
Categories
Section titled “Categories”Explicit Rules, Performance Rules, Maintenance Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.