Execution Plan Estimated Operator Cost
March 19, 2009 at 1:09 pm (SQL Server 2008, SQLServerPedia Syndication) (dissecting sql server execution plans, execution plans, performance, performance tuning, SQL Server, SQL Server 2008, TSQL)
I’ve said it over and over again, the costs on operators in execution plans, even in actual execution plans are estimates. You need to understand that when looking at your execution plans. It’s vital because you need to be able to distinguish between the truly costly parts of a plan and the less costly parts of a plan. Don’t believe me? Take a look at this picture and see if you can spot the discrepancy:
Spot it yet?
Add up the costs for the operators visible in the part of the plan…
Yep 125%. And there’s more to the plan that I’m not showing. I think this one must total near 200%. The statistics are up to date and there’s no consistency errors in the database. These estimates are just off sometimes.
This is a rather costly query being run against Microsoft Dynamics CRM. There were two missing queries identified by the optimizer and about four key lookup operations that I fixed with two index changes. This jumped out and I wanted to share. The fact is, right now, the query is working well. That particular table has one scan and some reads, identified from looking at the STATISTICS I/O, and it’s not even close to 100% of the cost of the query, but I would sure be in a panic if I believed the estimated operation cost.
Gail said,
March 19, 2009 at 4:05 pm
Erland (one of the SQL MVPs) often mentions the query that he had once where the estimated costs totalled over 10 000% I think in his case it had to do with a recursive CTE.
It was not a once-off occurrence.
Christopher said,
March 20, 2009 at 6:33 am
Nice post , I think that most people starting out with performance and query plans tend to trust the figures 100%. I hope lots of people read this and realise you need to know which operators are “bad” vs “good”
scarydba said,
March 20, 2009 at 6:34 am
This one is very consistent too. It’s absolutely not the first time I’ve seen the estimated costs be completely off. It is the first time I’ve seen the estimated costs add up to more than 100%. 10,000% would be cool.
scarydba said,
March 20, 2009 at 6:36 am
Thanks Christopher. The thing is, the information is out there from lots of sources, but I don’t think people believe it. But surely, a picture is worth a thousand words, seeing is believing, are you going to believe me or your lieing eyes… you know the drill.
Gail said,
March 20, 2009 at 10:11 am
The problem with ‘bad operators’ (and it’s a topic I have on my blog to-do list) is that an operator’s classification of bad or good is very dependent on the conditions and the query.
A table scan is not a problem on a table with 5 rows.
A clustered index scan is the best way to evaluate a query that returns 95% of the table
A hash join is the ideal operator to join two 5 million row, unsorted result sets.
A nested loop join is terrible when the two result sets that it’s joining are very large.
Yet people often consider table scans, clustered index scans and hash joins to be bad and nested loop joins to be good.
Patrick said,
March 20, 2009 at 4:26 pm
Grant – Love your articles. A quick question related to execution plans but not specific to this post…
I’m reading ‘Dissecting SQL Server Execution Plans and there’s some confusion relating to Nested Loop Joins (page 55). In figure 16, the description of the nested loop states that the top input is the outer and the bottom input is the inner. However in your description to you say that, “… the outer data set [is] the bottom operator in a graphical execution plan.”
Could you clarify which is correct?
Thanks!
scarydba said,
March 22, 2009 at 2:47 pm
Hey,
Thank you for the compliments. I usually write on stuff that I’m interested in or excited about it. It’s good to know I’m being useful to others as well.
Both say the same thing, sort of. It’s just which of the two is “outer” and “inner” varies. The first data stream, on top, is scanned a row at a time against the set of data on the bottom. But, my text should have matched the picture, since that’s straight out of SQL Server. I would always take what’s there as correct (unless completely and utterly proved otherwise, and even then… question it.).
Thanks for pointing it out.
Gail said,
March 23, 2009 at 8:19 am
If I recall, the bottom table (in the exec plan) is the ‘inner table’ of the nested loop. This can be seen from the number of executions of the seek/scan on the bottom table.
Since a nested loop is just that, the inner table will get read once per row of the outer resultset. Knowing that, a check of the execution count of the seeks/scans will show which table is inner or outer.
for (i-0; i < table1.rowcount; i++) {
// read table2 and return matching rows
}
Execution count is shown in the tooltip when viewing exec plans in 2008’s management studio. 2005 doesn’t show the exec count, but it is in the XML for anyone feeling adventurous.
Sankar Reddy said,
April 10, 2009 at 11:32 am
I just ran across a query on a server I acquired few weeks ago that shows the cost % is 427095307%. I wish I could post the screenshot here!
Its huge table and the statistics sampled ratio is 0.11%. Time to update stats with fullscan.
scarydba said,
April 10, 2009 at 12:45 pm
That’s a big number. It’s funny because, I’ve never seen this until last month, now I’m seeing it all over the place and the numbers are getting huge.
Sankar Reddy said,
April 10, 2009 at 3:18 pm
Grant, I posted the screen shot here (http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!347.entry).
Ather Murtuzapurwala said,
July 13, 2009 at 4:04 pm
Something which I never thought……
Sam Trenchard said,
March 9, 2010 at 5:32 am
Grant,
In your book on understanding the optimizer, you mention in relation to Indexed views that the output may not be syncronous with the query against the underlying tables. How can this be possible ? I understood that the schemabinding and unique clustered index ensured that the aggregated data in the view was in step with the base table data.
Hope you have time to respond.
Sam Trenchard
weblinkdb said,
February 18, 2015 at 11:52 am
[…] The costs on operators in execution plans, even in actual execution plans are estimates. This link shows over 125% estimated cost in execution plan […]