SQL Server - Cost estimation issues even with up-to-date statistics

The only way to know if statistics are update-to-date or outdated is look at Actual Records count and Estimated Records count in an execution plan. A mismatch indicates that statistics need to be updated. But there are exceptions to this rule. There are situations when even if statistics is up-to-date still there is a difference in estimated and actual count.


First let's check the result with fixed parameter query. In my example I have bloated the data in AdventureWorksDW2008R2.FactInternetSales by duplicating the data multiple times (of couse, ensuring that the Primary key is unique).

T-SQL with fixed query (OK)

T-SQL with local variables (Issue)

Stored procedure with local variables (Issue)

 Here are the scenarios when the same query with different implementation style would give a matching estimated and actuals count.

Possible Solutions...

Here are the possible solutions to correct the issue.

Stored procedure without local variables (OK)

 

 

Dynamic sql with sp_executesql (OK)

Dynamic sql with EXEC (OK)

_