SA0131 : High number of estimated rows found in execution plan

The topic describes the SA0131 analysis rule.

Message

High number of estimated rows found in execution plan

Description

The rule examines stored procedures, views and functions for the queries which have a high number of estimated rows in their estimated execution plans.

How to fix

Consider reviewing this kind of queries as a high number of estimated rows could mean that a filtering join condition is not correct, statistics are not up to date, index is not used or missing.

Scope

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

Parameters
Name Description Default Value
RowLimit

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.

100000
Remarks

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

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE [dbo].[testsp_SA0131]
 2    @ManagerID [int]
 3AS 
 4BEGIN   
 5    SET NOCOUNT ON;
 6
 7SELECT 
 8    e.[BusinessEntityID]
 9    ,p.[Title]
10    ,p.[FirstName]
11    ,p.[MiddleName]
12    ,p.[LastName]
13    ,p.[Suffix]
14    ,e.[JobTitle]  
15    ,p.[EmailPromotion]
16    ,a.[AddressLine1]
17    ,a.[AddressLine2]
18    ,a.[City] 
19    ,a.[PostalCode]
20    --,cr.[Name] AS [CountryRegionName] 
21    ,p.[AdditionalContactInfo]
22FROM [HumanResources].[Employee] e
23        INNER JOIN [Person].[Person] p
24        ON p.BusinessEntityID = e.[BusinessEntityID]
25    INNER JOIN [Person].[BusinessEntityAddress] bea 
26    ON bea.ModifiedDate < GETDATE()
27    INNER JOIN [Person].[Address] a 
28    ON a.[AddressID] = bea.[AddressID]
29END;
Analysis Results
  Message Line Column
1 SA0131 : High number of estimated rows found in the statement execution plan. Estimated Rows:1700690, Operator:Hash Match 5 0
See Also

Other Resources

Analysis Rules