SA0047 : Consider indexing the column as it is used in a WHERE clause or JOIN condition

The topic describes the SA0047 analysis rule.

Message

Consider indexing the column as it is used in a WHERE clause or JOIN condition

Description

The rules checks WHERE or JOIN clauses for non-indexed columns staying on the one side of comparison expression. Indexing those columns may improve the query performance.

How to fix

Review the query and consider indexing the reported column as it is used in a WHERE clause or JOIN condition.

Scope

The rule has a Batch scope and is applied only on the SQL script.

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

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1CREATE PROCEDURE [dbo].[Procedure3WithWarnings]
 2@param1 int
 3AS
 4SELECT   *
 5FROM [Production].[BillOfMaterials] b
 6WHERE ( b.[ComponentID] + 5 > @param1) or  b.[BOMLevel] > 5
 7
 8SELECT  *
 9FROM [Production].[BillOfMaterials] b
10WHERE ( b.[ComponentID] > (@param1 - 5)) or  b.[BOMLevel] = @param1
11
12SELECT  *
13FROM [Production].[BillOfMaterials] b
14WHERE ( b.[ComponentID] > (@param1 - 5)) or  b.[BOMLevel] /*IGNORE:SA0047*/ = @param1

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
See Also

Other Resources