Click or drag to resize

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.

Scope

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

Parameters
NameDescriptionDefault 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.

Categories

Explicit Rules, Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
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

 MessageLineColumn
1EX0018 : 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 ).154
2EX0018 : 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).480
3EX0018 : 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).480
4EX0018 : 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 ).780
5EX0018 : 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).840
6EX0018 : 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 ).880
See Also

Other Resources