EX0015 : Find best clustered index
Introduction
Section titled “Introduction”The correct choice between clustered and non-clustered indexes can significantly impact query performance. Non-clustered indexes become more efficient than existing clustered indexes when they handle more user seeks than the clustered index incurs lookups. Ignoring this can lead to suboptimal performance in data retrieval.
Description
Section titled “Description”The rule considers a non-clustered indexes to be better compared to the existing clustered index if the number of user seeks on non-clustered index is greater than the number of lookups on the related to the table clustered index.
For example, suppose this non-clustered index experiences more user seeks than the following clustered index’s lookups:
CREATE NONCLUSTERED INDEX idx_nc ON TableName(ColumnA);
CREATE CLUSTERED INDEX idx_c ON TableName(ColumnB);In this scenario, the non-clustered index idx_nc is potentially a better candidate for clustering. The failure to adjust indexing strategies may result in inefficient data access patterns.
-
Leads to slower query performance due to unnecessary lookups.
-
Missed opportunities for optimizing data access and server resources.
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”Rule has no parameters.
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”Design Rules, Explicit Rules, Code Smells
Additional Information
Section titled “Additional Information”SQL Server Dynamic Management Views