EX0018 : Analyze execution plan and check for high cost operations
Introduction
Section titled “Introduction”The absence of necessary indexes in SQL Server execution plans may cause performance issues, leading to inefficient query execution strategies.
Description
Section titled “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:
SELECT * 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 scansorindex scanscan 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
Section titled “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:
CREATE INDEX IX_CustomerID ON Orders (CustomerID);The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “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
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”Explicit Rules, 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”SELECT e.[EmployeeID] , c.[Title] , c.[FirstName] , c.[LastName] , a.[AddressLine1] , c.[AdditionalContactInfo]FROM [HumanResources].[Employee] AS eINNER JOIN [Person].[Contact] AS cON c.[ContactID] = e.[ContactID]INNER JOIN [HumanResources].[EmployeeAddress] AS eaON e.[EmployeeID] = ea.[EmployeeID]INNER JOIN [Person].[Address] AS aON ea.[AddressID] = a.[AddressID]INNER JOIN [Person].[StateProvince] AS spON sp.[StateProvinceID] = a.[StateProvinceID]INNER JOIN [Person].[CountryRegion] AS crON cr.[CountryRegionCode] = sp.[CountryRegionCode];Analysis Results
Section titled “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 |