Aaron Bertrand

Bad cardinality estimates coming from SSMS execution plans

SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentals and query tuning.

Itzik’s Posts

I have a few people to thank for a recent update to SQL Sentry Plan Explorer. Brooke Philpott (@Macromullet) and Greg Gonzalez (blog | @SQLsensei), of course, for R & D and for digging into the code and sorting it out. But also to Paul White (blog | @SQL_kiwi) for being persistent in helping us validate the fixes.

The problem Paul discovered is that SQL Server 2008+ messes up cardinality estimates on certain queries when key or RID lookups are involved. I'll leave the deeper explanation to Paul's blog post and the bug he filed on Connect, but long story short, we were taking these misrepresented estimates, believing them, and extrapolating them to show you "better" information. Unfortunately, as Paul explains, we were duped.

Paul shows the following query against a copy of AdventureWorks 2005.

SELECT
    th.ProductID,
    th.TransactionID,
    th.TransactionDate
FROM Production.TransactionHistory AS th 
WHERE 
    th.ProductID = 1 
    AND th.TransactionDate BETWEEN '20030901' AND '20031231';

Management Studio yields the following plan, just as Paul described it:

SSMS plan

In Plan Explorer, we tried to be helpful by multiplying the estimated number of rows (rounded to 17) by the number of executions (45), and came up with 765:

Original Plan Explorer plan

For most operators, this approach yields the right data, but due to this bug in SQL Server, it is not correct for key/RID lookups. We've adjusted for that, and released the appropriate fix in 7.2.42.0 (download it now!). The graphical plan now properly shows correct row counts for both estimated:

New estimated plan in Plan Explorer

And actual:

New actual plan in Plan Explorer

I'll repeat Paul's warning: Watch out for poor cardinality estimates when a predicate is applied as part of a lookup.

There were some more complex problems caused by these misleading estimates, which we have also addressed. I will blog about a few of those in a follow-up post – for this post I just wanted to demonstrate that we quickly resolved the specific issue Paul highlighted in his post.