SA0047 : Consider indexing the column as it is used in a WHERE clause or JOIN condition
Introduction
Section titled “Introduction”This problem occurs when non-indexed columns are used in WHERE or JOIN clauses, potentially impacting query performance.
Description
Section titled “Description”Using columns without indexes in WHERE or JOIN clauses can slow down query execution. An index allows SQL Server to quickly locate and retrieve the required data without scanning the entire table, similar to how an index in a book helps find information quickly.
For example:
-- Example of problematic query with non-indexed columnsSELECT * FROM OrdersWHERE CustomerID = 'ALFKI';This query example fetches all orders for a specific customer. If the CustomerID column is not indexed, SQL Server must scan each row of the table to find matches, which is inefficient for large datasets.
-
Slower query performance due to non-indexed searches.
-
Increased CPU and I/O resource usage, leading to potential performance bottlenecks.
How to fix
Section titled “How to fix”Optimize query performance by indexing columns used in WHERE clauses or JOIN conditions.
Follow these steps to address the issue:
1.Identify the columns used in WHERE clauses or JOIN conditions that are not indexed.
2.Evaluate the existing indexes on the table to ensure that the necessary columns are not already indexed.
3.Create an index on the identified columns if one does not exist:
4.Use the CREATE INDEX statement to create the index, ensuring you specify the correct table and columns.
5.Test the query performance after indexing to ensure improvement and no adverse impact on other operations.
For example:
-- Create an index on the CustomerID columnCREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
-- Example of corrected query using the indexed columnSELECT * FROM OrdersWHERE CustomerID = 'ALFKI';The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| MinimumParentTableRowCount | The parameter specifies the minimum number of rows that the parent table of the indexed column must have for the rule to alert. This way small tables can be ignored. | 1000 |
| IgnoreColumnsFromTempTables | The parameter specifies if to ignore columns of temporary tables or table variables. | yes |
| IgnoreTableSourceWithAtLeastOneIndexedColumnInUse | The parameter specifies if to ignore table sources that already have one indexed column used in the query filter. | yes |
| ReportOnlyFirstNonIndexedColumnForTableSource | The parameter specifies whether to report only the first not indexed column that is found for a particular table source or report all not indexed columns. | yes |
Remarks
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Performance Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”CREATE PROCEDURE [dbo].[Procedure3WithWarnings]@param1 intASSELECT *FROM [Production].[BillOfMaterials] bWHERE ( b.[ComponentID] + 5 > @param1) or b.[BOMLevel] > 5
SELECT *FROM [Production].[BillOfMaterials] bWHERE ( b.[ComponentID] > (@param1 - 5)) or b.[BOMLevel] = @param1
SELECT *FROM [Production].[BillOfMaterials] bWHERE ( b.[ComponentID] > (@param1 - 5)) or b.[BOMLevel] /*IGNORE:SA0047*/ = @param1Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0047 : Consider indexing column [Production].[BillOfMaterials].[BOMLevel] as it is referenced in a WHERE clause or JOIN condition. | 6 | 43 |