SQLPLAN Glitch

SQL Server, T-SQL
While I was doing some work for Andy Warren (more on this after Sept 1st), I came across an interesting little glitch when saving XML execution plans out as SQLPlan files. It's easy enough to replicate. Just get an XML execution plan from your query: SET STATISTITCS XML ON; SELECT... Click on the link to open the XML plan. Click on the "File" menu and then the "Save As" menu item. It opens the familiar file save window. Click on the "Save as type" drop down and switch to "All Files (*.*)" Save the file with an extension of ".sqlplan." Good. Now you've got an execution plan file that can be opened and viewed as a GUI execution plan. Without closing the XML, try to open this new plan. You…
Read More

Deadlocks vs. Blocks

SQL Server, T-SQL
It makes me crazy when I interview someone who has five or more years as a DBA, but they don't know the difference between a block and a deadlock. It's a complete showstopper for me. If you don't know this, you're an entry-level DBA, don't talk to me about your years of experience. Sorry, but there it is. Here's someone that's kinder than I am in every way. Not only have they cut people slack on this question, but he's provided a well done answer to the question. For those who may interview with me in the future, go and read this and understand it.
Read More

Serious Error

T-SQL
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…
Read More

Code from "Dissecting SQL Server Execution Plans"

T-SQL
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 words, caveat emptor, your mileage may vary, keep your hands and feet inside the vehicle…
Read More

Constant Scan in Execution Plans

SQL Server, T-SQL
I see a lot of searches from people apparently trying to find out what having a Constant Scan in their execution plan means. I can understand why. Here's the definition from the Books Online: The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used to add columns to a row produced by a Constant Scan operator. OK. Very precise and yet, unless you know what the sentence means, reading it can be pretty confusing. The key is to see what Compute Scalar means: The Compute Scalar operator evaluates an expression to produce a computed scalar value. This may then be returned to the user, referenced elsewhere in the query, or both. An example of both is in a filter predicate…
Read More

Breaking Down Complex Execution Plans

SQL Server, T-SQL
Peter Ward, the editor at SQL Server Performance, has published an article of mine on Breaking Down Complex Execution Plans. I go way beyond the blog entry below and show how the estimated costs in execution plans can mess you up, how to use the XML in execution plans to search through them for costly operations or operations that have mismatched estimated rows & actual rows and some other tips and tricks. Hopefully it's worth a read.
Read More

Why are Subqueries Dangerous?

T-SQL
If you go around to the various forums, you'll see postings, including some I've put up, that say using subqueries, especially correlated subqueries in the SELECT statements of queries is bad because it effectively acts as a cursor. I got called on it. So I had to do a bit of research. Books Online talks about it right off when describing subqueries. That's not proof though. I did some more looking around. Adam Machanic does a nice job of slicing up Functions, which are basically the same thing. But it's not exactly the same. I kept looking. This blog entry is just flat wrong, but the second comment points out the fallacy. Jeff Moden would also like this example since it shows the disparity between the actual cost of queries…
Read More

Operations Manager 2007 SQL Collection Article

T-SQL, Tools
It took me a while to find all the little pieces to put together a custom rule that used data collected through TSQL. In fact, because it took so much work to pull all the little pieces together, I wrote an article describing how I did it. This isn't a wonderful new invention. It's just the publication of a bunch of research. If you're an Operations Manager expert, this isn't for you. If you're like I am, trying to find your way through the forest, this little bit of map will hopefully provide some assistance. Available at SQL Server Central.
Read More