Aaron Bertrand

Bad cardinality estimates coming from SSMS execution plans

SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’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.