EX0018 : Analyze execution plan and check for high cost operations

The absence of necessary indexes in SQL Server execution plans may cause performance issues, leading to inefficient query execution strategies.

Description

When a query lacks appropriate indexes, SQL Server might resort to inefficient execution plans. One major consequence is a reliance on expensive operations, such as bookmark lookups, table scans, or index scans, which can significantly degrade performance.

Example of potential inefficiency:

SQL
Copy
1SELECT * FROM Orders WHERE CustomerID = 123;

The above query could trigger a table scan if there’s no index on the CustomerID column. As the table grows, the impact on performance increases, making queries slower and more resource-intensive.

  • Table scans or index scans can consume significant I/O resources, causing slow query performance.

  • Missing indexes identified during query plan analysis may highlight optimization opportunities, allowing for faster data retrieval.

How to fix

To resolve the inefficiencies caused by missing indexes in SQL Server execution plans, it’s crucial to optimize queries through the strategic creation of indexes.

Follow these steps to address the issue:

  1. Analyze the query plan of the reported statement using SQL Server Management Studio (SSMS) to identify any missing indexes and high-cost operations. In SSMS, execute the query with the Include Actual Execution Plan option enabled to view the plan.

  2. Identify operations like table scans and bookmark lookups, which indicate missing indexes, within the execution plan. Look for suggested indexes provided in the execution plan’s missing indexes section, if available.

  3. Create the necessary indexes on the columns used in WHERE clauses or joins that are causing the inefficiencies. Ensure the created indexes cover the columns effectively and consider using composite indexes where appropriate.

Example of creating an index to improve query performance:

SQL
Copy
1CREATE INDEX IX_CustomerID ON Orders (CustomerID);

Scope

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

Parameters
Name Description Default Value
CheckForTableScanOperatorsCost

The parameter specifies the minimum cost in percent of the ‘Table Scan’ operator in order it to be included in the results. Value of equal or greater of 100 will exclude the operator from the results.

10
CheckForIndexScanOperatorsCost

The parameter specifies the minimum cost in percent of the ‘Index Scan’ operator in order it to be included in the results. Value of equal or greater of 100 will exclude the operator from the results.

10
CheckForBookmarkLookupOperatorsCost

The parameter specifies the minimum cost in percent of the Bookmark Lookup (Key Lookup, RID Lookup) operators in order to be included in the results. Value of equal or greater of 100 will exclude the operator from the results.

10
CheckForSortOperatorsCost

The parameter specifies the minimum cost in percent of the ‘Sort’ operator in order it to be included in the results. Value of equal or greater of 100 will exclude the operator from the results.

10
CheckForHashMatchOperatorsCost

The parameter specifies the minimum cost in percent of the ‘Hash Match’ operator in order it to be included in the results.Value of equal or greater of 100 will exclude the operator from the results.

10
CheckForOperationsWithCost

The parameter specifies the minimum cost in percent of any of the available operator in order to be included in the results.Value of equal or greater of 100 will exclude the operator from the results.

100
ExpandMode

The parameter specifies if stored procedures,function and view create statements to be expanded during analysis.

ExpandAll
ShowInvalidObjectErrors

The parameter specifies if execution plan generation errors due to invalid objects to be displayed.

yes
CheckForMissingIndexes

The parameter specifies if missing indexes found in the plan to be reported.

yes
CheckForImplicitConversionCausingIndexScan

The parameter specifies if to report index scans caused by implicit conversion.

yes
Remarks

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

Effort To Fix
3 hours per issue.
Categories

Explicit Rules, Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT  e.[EmployeeID]
 2      , c.[Title]
 3      , c.[FirstName]
 4      , c.[LastName]
 5      , a.[AddressLine1]
 6      , c.[AdditionalContactInfo]
 7FROM  [HumanResources].[Employee] AS e
 8INNER JOIN [Person].[Contact] AS c
 9ON  c.[ContactID] = e.[ContactID]
10INNER JOIN [HumanResources].[EmployeeAddress] AS ea
11ON  e.[EmployeeID] = ea.[EmployeeID]
12INNER JOIN [Person].[Address] AS a
13ON  ea.[AddressID] = a.[AddressID]
14INNER JOIN [Person].[StateProvince] AS sp
15ON  sp.[StateProvinceID] = a.[StateProvinceID]
16INNER JOIN [Person].[CountryRegion] AS cr
17ON  cr.[CountryRegionCode] = sp.[CountryRegionCode];
Analysis Results
  Message Line Column
1 EX0018 : The operator Hash Match has a relative cost of 28% and may need revising (Node Id: 2, Estimated Operator Cost:28% | 0.11162100000000002, Estimated Rows:282.216, Logical Operator: Inner Join). 1 0
2 EX0018 : The operator Index Scan has a relative cost of 45% and may need revising (Node Id: 7, Estimated Operator Cost:45% | 0.180413, Estimated Rows:19614, Logical Operator: Index Scan). 1 0
See Also

Other Resources

Analysis Rules