Category: Uncategorized

I've watched several people recently go straight to XML when reading execution plans because they didn't know about the execution plan properties in the first operator. Now, don't get me wrong. If going straight to the XML is working for you, that's fine. Keep doing it. I'm absolutely not questioning how anyone does things. I just want people to know that "hidden" information isn't so much hidden as much as it's not too obvious. First Operator I've written before about the first operator in an execution plan (here, as well as here, and here, and even a problem with them here). I don't have a whole lot to add to those posts. If you look around at other blog posts I've done on execution plans, I use the properties of…

A common issue in performance is the lookup, key or RID, but the question frequently asked is, which columns are looked up? You need to know in order to try to address the issue. The answer is easy enough to find. Let's take a quick look. Which Columns Are Looked Up? Let's take an example query: SELECT p.NAME, AVG(sod.LineTotal) FROM Sales.SalesOrderDetail AS sod JOIN Production.Product AS p ON sod.ProductID = p.ProductID WHERE sod.ProductID = 776 GROUP BY sod.CarrierTrackingNumber, p.NAME HAVING MAX(sod.OrderQty) > 1 ORDER BY MIN(sod.LineTotal); There is a nonclustered index on the ProductID column. Depending on the value, it's going to reduce the rows returned extremely well. So, the optimizer is likely to pick that index. Here's the index definition: CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail (ProductID ASC) In…

I saw a question the other day, questioning why they're creation of temporary tables was so slow. What they were doing was (a much more complicated version of) this: SELECT soh.SalesOrderID, sod.SalesOrderDetailID, soh.SalesOrderNumber INTO #MyTempTable FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE 1 = 0; Now, my immediate response, and no, I didn't type it, was, "Hey, you're not "creating" temporary tables. You're using SELECT...INTO." Let's be fair. That is a method to create temporary tables. Also, that method has some advantages. Biggest one being, you don't have to know, or define, the data structure. You get it for free. It does come down to one thing though. Why is "WHERE 1=0" slow? WHERE 1=0 Math may be weird these days, but in good…

While I have not yet signed the contract, I have submitted an outline and proposal for a new version of my book on query performance tuning. Most of the information in the existing book is still very valid and immediately applicable. However, some of the information is out of date. Other pieces can be tweaked to tell a better story. A little bit of it is just wrong or has aged out of applicability. Because of all this, I'm not simply going to update the existing book. Instead, this time, it's a complete, from scratch, rewrite. All the way. I'm planning to drop entirely the chapters on hardware. I'm doing this for a bunch of reasons. One, hardware has changed radically over the years. Of all the information in the…

While we are clearly beginning to see in-person events on the calendar, the vast majority of presentations, events, talks, etc., are virtual. There are a lot of positives to all these virtual presentations. People who can't travel, for whatever reason, can get access to presentations they might otherwise have never seen. A lot of the virtual presentations are recorded, so you can watch, or re-watch, at your leisure. Also, with the recordings, you can repeat sections, speed things up, slow things down, do more, to get more, with the presentation. Further, because so many of the virtual events are free, or radically reduced in cost, we have seen something of a democratization of presentations. In short, there are a lot of positive features for this currently prevalent approach. But you…

Hello all! I wanted to apologize to you. I haven't been keeping up with the blog well at all over the last couple of months. I have simply been struggling with motivation. I have enough for my work commitments, but extra-curricular stuff has just been lagging. So, please allow me to say I'm sorry for not getting more sharing out there. Next, I'm making a commitment to you that I will be posting regularly again from this point forward. As a part of that, I'd sure love to hear from you on what kind of content you'd like to see. Do we need more on execution plans, or on SQL Server fundamentals? Would a bunch of stuff on Extended Events or Query Store be more interesting? How much DevOps stuff…

Quite a few years ago, I wrote a post about SELECT * and performance. That post had a bit of a click-bait title (freely admitted). I wrote the post because there was a really bad checklist of performance tips making the rounds (pretty sure it's still making the rounds). The checklist recommended a whole bunch of silly stuff. One silly thing it recommended was to simply substitute ALL columns (let me emphasize that again, name each and every column) instead of SELECT * because "it was faster". My post, linked above, showed that this statement was nonsense. Let's be clear, I'm not a fan of SELECT *. Yes, it has some legitimate functionality. However, by and large, using SELECT * causes performance problems. SELECT * Hurts The most fundamental place…

The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing data from being removed in the parent table if it’s in the child table. The rules for these relationships are not terribly complex: The columns in the two tables must be the same data type, although, if SQL Server can automatically, and correctly, convert the data you can get away with different data types. But don’t do that. It’s begging for an issue. Keep them the same and you won’t have any problems.The child values can be nullable, which means that any child data is unknown.The child data can also be required, meaning that you have to have the relationship, no…

The moment you decide you want to monitor your SQL Server instance for query behavior, you're going to be forced to make a bunch of choices. Further, all these choices lead to problems that you're going to have to deal with. Some of the problems are obvious. For example, you decided to capture all the statements run against the system. That's a lot of data you'll have to be prepared to manage. However, other problems are extremely subtle and can really be a pain. For example, how do you differentiate between a query run by a person in SQL Server Management Studio and the queries that SSMS itself runs against the system? Queries From SSMS Let's say I'm interested in capturing both the batch completed event and the statement completed…

I was recently asked a question on a forum by a person who was frustrated with all the tool choices we have for measuring performance. Moreover, they were frustrated that a simple and clear combination of the tools to achieve synergy was extremely challenging. In fact, they said that, just using the query_hash as an example, they never saw a single match between the DMVs, Query Store and Extended Events. Now, that's pretty unlikely and I'm sure we could talk about why that might be the case. However, this idea of combining the tools, I shared a bunch of thoughts on it. I decided, maybe it's worth sharing here too. Achieving Synergy Honestly, this is tough. I work for a company that makes a monitoring tool. We are trying to…