EX0018 : Analyze execution plan and check for high cost operations

The topic describes the EX0018 analysis rule.

Message

Analyze execution plan and check for high cost operations

Description

The rule generates execution plan of the statements and check the plan for missing indexes and operations that have high relative to the statement cost.

The supported operations are: Bookmark Lookups (RID Lookup and Key Lookup), Table Scan, Index Scan, Hash Match and Sort.

These should be considered for reviewing as the reasons for each of these operations often may have negative impact on the specific query.

The analysis rule also checks for missing indexes that have high impact on a specific query and are found during query plan generation.

The impact for each of the supported operations can be configured using the rule parameters.

How to fix

Review the query plan of the reported statement and consider any p missing indexes and high cost operations that may affect negatively the query performance.

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
 1CREATE PROCEDURE [dbo].[uspTest]
 2    @ManagerID [int],
 3    @ManagerID1 [int] = 1,
 4    @ManagerID2 [int] = 12,
 5        @ManagerID3 [nvarchar](50) = NULL,
 6        @ManagerID4 nvarchar(50) = 'NULL1',
 7        @ManagerID5 nvarchar(50) = 'NULL'
 8
 9AS 
10BEGIN   
11    SET NOCOUNT ON;
12
13    -- Use recursive query to list out all Employees required for a particular Manager
14    WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
15    AS (
16        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 -- Get the initial list of Employees for Manager n
17        FROM [HumanResources].[Employee] e 
18            INNER JOIN [Person].[Contact] c 
19            ON e.[ContactID] = c.[ContactID]
20        WHERE [ManagerID] = '@ManagerID'
21        UNION ALL
22        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
23        FROM [HumanResources].[Employee] e 
24            INNER JOIN [EMP_cte]
25            ON e.[ManagerID] = [EMP_cte].[EmployeeID]
26            INNER JOIN [Person].[Contact] c 
27            ON e.[ContactID] = c.[ContactID]
28        )
29    -- Join back to Employee to return the manager name 
30    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName',
31        [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
32    FROM [EMP_cte] 
33        INNER JOIN [HumanResources].[Employee] e 
34        ON [EMP_cte].[ManagerID] = e.[EmployeeID]
35        INNER JOIN [Person].[Contact] c 
36        ON e.[ContactID] = c.[ContactID]
37    ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
38    OPTION (MAXRECURSION 25) 
39
40
41
42SELECT  *
43FROM  Sales.SalesOrderHeader AS SOH
44JOIN Sales.SalesOrderDetail AS SOD
45ON  SOH.SalesOrderID = SOD.SalesOrderID
46
47SELECT 
48    e.[EmployeeID]
49    ,c.[Title]
50    ,c.[FirstName]
51    ,c.[MiddleName]
52    ,c.[LastName]
53    ,c.[Suffix]
54    ,e.[Title] AS [JobTitle] 
55    ,c.[Phone]
56    ,c.[EmailAddress]
57    ,c.[EmailPromotion]
58    ,a.[AddressLine1]
59    ,a.[AddressLine2]
60    ,a.[City]
61    ,sp.[Name] AS [StateProvinceName] 
62    ,a.[PostalCode]
63    ,cr.[Name] AS [CountryRegionName] 
64    ,c.[AdditionalContactInfo]
65FROM [HumanResources].[Employee] e
66    INNER JOIN [Person].[Contact] c 
67    ON c.[ContactID] = e.[ContactID]
68    INNER JOIN [HumanResources].[EmployeeAddress] ea 
69    ON e.[EmployeeID] = ea.[EmployeeID] 
70    INNER JOIN [Person].[Address] a 
71    ON ea.[AddressID] = a.[AddressID]
72    INNER JOIN [Person].[StateProvince] sp 
73    ON sp.[StateProvinceID] = a.[StateProvinceID]
74    INNER JOIN [Person].[CountryRegion] cr 
75    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
76
77SELECT ContactID,
78LastName,
79Phone
80FROM Person.Contact
81WHERE EmailAddress LIKE 'sab%'
82
83SELECT *
84FROM [dbo].[DatabaseLog]
85WHERE DatabaseLogID = 1
86
87SELECT  * FROM Person.AddressType WHERE Name = 1
88
89END;

Analysis Results
  Message Line Column
1 EX0018 : Implicit conversion ([AdventureWorks_Test].[HumanResources].[Employee].[ManagerID] as [e].[ManagerID]=CONVERT_IMPLICIT(int,’@ManagerID’,0)) causes index scan may need revising (Node Id: 8, Estimated Operator Cost:7% | 0.00804544, Estimated Rows:6.14894, Logical Operator: Clustered Index Scan ). 14 3
2 EX0018 : The operator Hash Match has a relative cost of 23% and may need revising (Node Id: 6, Estimated Operator Cost:23% | 0.11107600000000003, Estimated Rows:282.216, Logical Operator: Inner Join). 46 1
3 EX0018 : The operator Index Scan has a relative cost of 37% and may need revising (Node Id: 8, Estimated Operator Cost:37% | 0.180413, Estimated Rows:19614, Logical Operator: Index Scan). 46 1
4 EX0018 : The operator Key Lookup has a relative cost of 95% and may need revising (Node Id: 3, Estimated Operator Cost:95% | 0.0610102, Estimated Rows:1, Logical Operator: Key Lookup ). 76 1
5 EX0018 : The operator RID Lookup has a relative cost of 50% and may need revising (Node Id: 3, Estimated Operator Cost:50% | 0.0032831, Estimated Rows:1, Logical Operator: RID Lookup). 82 1
6 EX0018 : Implicit conversion (CONVERT_IMPLICIT(int,[AdventureWorks_Test].[Person].[AddressType].[Name],0)=CONVERT_IMPLICIT(int,[@1],0)) causes index scan may need revising (Node Id: 0, Estimated Operator Cost:100% | 0.0032886, Estimated Rows:1, Logical Operator: Clustered Index Scan ). 86 1
See Also

Other Resources