SA0131 : High number of estimated rows found in execution plan

High estimated row counts in execution plans may indicate incorrect join conditions, outdated statistics, or missing/unused indexes, leading to performance issues.

Description

This problem pertains to queries within stored procedures, views, and functions that show a high number of estimated rows in their execution plans. Such estimates can lead to inefficient execution strategies in SQL Server, potentially degrading performance.

For example:

SQL
Copy
1-- Example of a query with high estimated rows
2SELECT * FROM Orders WHERE OrderDate > '2023-01-01';

In this example, if the OrderDate column is not indexed properly, SQL Server might estimate a high number of rows, which can lead to suboptimal query execution plans, such as choosing a table scan over an index seek.

  • Leads to inefficient use of resources by potentially triggering full table scans.

  • Could cause increased IO operations, leading to slower query performance.

How to fix

Optimize SQL queries with high estimated row counts in execution plans to improve performance and resource usage.

Follow these steps to address the issue:

  1. Review the query’s filtering conditions, particularly in the WHERE clause, to ensure they reflect accurate and optimal conditions for the dataset. For instance, verify the join conditions are correct.

  2. Update the statistics for the tables involved in the query to ensure that SQL Server has the most recent data distribution information. Use the UPDATE STATISTICS command for this purpose:

  3. Check if appropriate indexes are present on columns used in filtering conditions like WHERE clauses or join conditions. If missing, consider adding indexes to enhance execution plans, such as using CREATE INDEX.

  4. Evaluate the execution plan of the query to determine if any operators, like table scans, are suboptimal. Use tools like SQL Server Management Studio to inspect the execution plan’s graphical representation.

For example:

SQL
Copy
1-- Example of corrected query with an index
2CREATE INDEX idx_OrderDate ON Orders(OrderDate);
3
4-- Updated statistics
5UPDATE STATISTICS Orders;
6
7-- Optimized query
8SELECT * FROM Orders WHERE OrderDate > '2023-01-01';

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 4
See Also

Other Resources

Analysis Rules