Click or drag to resize

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.

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0131

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.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

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

 MessageLineColumn
1SA0131 : High number of estimated rows found in the statement execution plan. Estimated Rows:1700690, Operator:Hash Match6-1
See Also

Other Resources