Execution Plan Estimated Operator Cost
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.
Dissecting SQL Server Execution Plans Chapter 3
The third chapter has been published over at Simple Talk. So if you’re interesting the book, you can go to Red Gate to get a free e-book copy of it, or you can see chapters one and two and now three over at Simple-Talk. And for those who just feel the need to kill a tree, I’m told we should have a print version real soon now and available from Amazon.
Dissecting SQL Server Execution Plans at PDC
I just got word that Red Gate has printed more copies of the book that they’ll be distributing at the Microsoft Professional Developers Conference that’s taking place in LA. I just wish we had a 2008 version of the book now because, while most of it is still applicable, there’s more that can be done with execution plans now.
Have I mentioned I think the missing index information that’s displayed with the statement text in the graphical execution plan in SQL Server 2008 is pretty slick? Well it is.
I hope they’re going to distribute it at the PASS Summit this year too. I think they are, but I don’t know that for a fact.
Video Lessons
A while back, I wrote a book, Dissecting SQL Server Execution Plans. Because of it, I had some conversations with Steve Jones & Andy Warren. For a SQL Server geek, heady company. Anyway, they asked me what my plans are for the book. Plans? I wrote it. I thought that was the plan. But they meant lessons, licensing and all that kind of stuff. I didn’t have a clue, but they did. A few weeks ago I flew down to Florida and recorded a bunch of short video lesson plans derived from the book and from discussions with Andy Warren. It was a blast.
They’ve now been published over at JumpstartTV. I hope you find them useful. I had a blast doing them and learned a lot from Andy and his crew(including Brian Knight) while I did it.
Here’s a picture of me in their high-tech batcave/recording studio… OK. Kidding. It was a supply closet, but they’re running a seriously high-end operation down there, they’re just a bit pressed for space.
Serious Error
When I wrote the book “Dissecting SQL Server Execution Plans” I knew I was going to get things wrong. Several people have pointed out things over the last couple of months. They’ve all been in the details. None of them were serious errors of fact. Andy Warren just found a huge one.
In the section on Table Hints I detail how to apply an INDEX() hint. It’s on page 123 in the electronic version or 124 of the first print version. I state that index number starts at 0 with the clustered index. That’s just flat wrong. A clustered index is always 1. A 0 indicates a heap. Other indexes will have values greater than 1. If you were to supply a 0 to the INDEX() hint, as shown in the book, it forces either a clustered index scan or a table scan. I go on to suggest that you use index names in order to be sure of the index used by your query and show that in the example. Good thing too. Hopefully people will listen more to that than the statement preceding it.
I’m not sure if I’ll get a chance to do edits prior to more print runs, but that will definately be included. Thanks Andy.
UPDATE:
I no more than let Tony Davis, my editor, know about the issue than he gets the electronic version updated. Plus, he’s at home sick. Tony, you’ve earned TWO beverages on me at the PASS Summit this year.
Code from “Dissecting SQL Server Execution Plans”
This is the complete code listing from the book “Dissecting SQL Server Execution Plans.” You need a copy of AdventureWorks. Please note, AdventureWorks changes. It changed three times while I wrote the book. These changes can be very subtle causing variations in statistics which will make some of the queries generate execution plans in a different manner than what was published in the book. Some of these changes can be pretty radical causing the queries to not work at all. Also, the book went through quite a few edits including rearranging the order in which sections appear. This listing is the order in which things were written and it might vary from the book. In other works, caveat emptor, your mileage may vary, keep your hands and feet inside the vehicle at all times… You get the idea.
Home of the Scary DBA 