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:
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:
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:
And actual:
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.
