-
Introduction
Section titled “Introduction”The topic describes the EX0016 analysis rule.
EX0016: Generate DROP INDEX statements for inefficient indexes using dynamic management views information.
Section titled “EX0016: Generate DROP INDEX statements for inefficient indexes using dynamic management views information.”Message
Section titled “Message”Generate DROP INDEX statements for inefficient indexes using dynamic management views information.
Description
Section titled “Description”The rule generates DROP INDEX statements for all indexes that haven’t been used recently and are possible candidates to be dropped by using the SQL Server Dynamic Management Views.
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.
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 |
Categories
Section titled “Categories”Explicit Rules
Additional Information
Section titled “Additional Information”There is no additional info for this rule.