EX0018 : Analyze execution plan and check for high cost operations |
![]() |
The topic describes the EX0018 analysis rule.

Analyze execution plan and check for high cost operations

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.

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.

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

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 |

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


Explicit Rules, Performance Rules, Bugs

There is no additional info for this rule.

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; |

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 |
