SQL Server 2022 Query Performance Tuning

SQL Server
If you're interested in getting a digital copy, my brand spanking new book is now available here. It's in the intro, but let me tell you a little bit about the new book. It's really new. Some of the older versions of the book were simply updated, a bunch of changes to most chapters, a couple of new chapters, fixes for old mistakes, ta-da, new book. Not this time. This time, I rewrote it all. From scratch. Now, some of the chapter titles are the same. Quite a few of the examples are the same (if code illustrates something successfully, I'm reusing it). However, overall, it's a brand new book. There's a lot of new material too. The last update was for SQL Server 2017. There has (almost) been two…
Read More

Query Store at PASS Data Community Summit

SQL Server, T-SQL
While Query Store has been out for quite some time now, released in 2016, there's still quite a lot of missing understanding of what Query Store can do for you, and, how it does it. I've put together a new presentation on the Query Store, "Using Query Store to Understand and Control Query Performance", incorporating the latest stuff from 2022, but still showing you all the goods from 2016, for the PASS Data Community Summit. Why Query Store I've been in love with Query Store since it was released. If you look through my blog, I've been talking about Query Store a lot. I provided a little help to Tracy Boggiano on her book (yes, emphasis on hers, because it is, I just helped), Query Store for SQL Server 2019.…
Read More

Check Every Metric

SQL Server
Recently, a person asked about the costs differences in an execution plan, referencing them as if they were performance measures. The key to understanding performance is to check every metric. When it comes to execution plans, I'm sure I've said this before, so please allow me to repeat myself. The cost numbers shown in an execution plan, which, barring a recompile, will be the same for an execution plan or an execution plan with runtime metrics (aka, estimated and actual plans), are not measures of performance. They do not represent actual metrics. Instead, they are calculations of a theoretical actual performance measurement. So, you can't look at two plans, with two costs, and say, "this plan will perform better." Instead, you can say, "this plan has a lower estimated cost."…
Read More

Function Vs. Performance

SQL Server
Recently I was looking through DBA.StackExchange when I saw a pretty simple question that I decided to answer. I went off, set up a test database and some test tables and quickly wrote a query to answer the question. I got it all formatted pretty and was on my way to post it when I saw that another answer was already there. Yeah. Identical to mine. Almost line for line. Well, nuts. I know. I'll write a blog post. The Setup The original poster had two tables that, frankly, are badly designed. However, they share enough data that they are "related" if not relational. Here's the code: CREATE DATABASE Testing; GO USE Testing; GO CREATE TABLE Table_A ( ID INT IDENTITY(1, 1), Score INT ); CREATE TABLE Table_B ( FromPoint…
Read More

Query Tuning At PASS Data Community Summit

PASS
The all new, in-person, PASS Data Community Summit is in just a few weeks. Since I'm shortly going to be publishing a 100% rewritten book on query tuning, I decided to take a look at what kind of sessions on the topic are going to be available at the Summit. Query Tuning With The Best You can search through the published sessions here. I didn't even bother typing in "query tuning". Far too lazy. I just typed "query" and already saw some amazing stuff. In no particular order... One of the people I've ALWAYS learned from, Kimberly Trip is delivering a precon, Indexing for Performance. That's going to be a great day of good learning. Kim is insanely knowledgeable and a fantastic presenter. Well worth your time. Erin Stellato, another…
Read More

Query Tuning and Easy Solutions

SQL Server
TLDR: There ain't one. I was privileged last week to be able to present a couple of sessions at the SQL Server and Azure SQL Conference (great event, I recommend it). One of my sessions was an intro to query tuning. Basically, I went through a bunch of common code smells and suggested different possible solutions. The one thing that came up, both from my own stories and the questions from the (engaged) audience, is just how much everyone wants a magic, run fast, switch. Query Tuning The Easy Way Here you go. The single easiest way to make your queries run faster: Just throw money at the problem. Buy more hardware. Buy bigger hardware. Get more disks and disk controllers (not just more disks). Go to the next highest…
Read More

Two Clustered Indexes?

SQL Server
Everyone knows that you only get a single clustered index, right? Wouldn't it be great though if you could have two clustered indexes? Well, you can. Sort of. Let's talk about it. Two Clustered Indexes First I'm going to create a table: DROP TABLE IF EXISTS dbo.od; GO SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID, pod.DueDate, pod.OrderQty, pod.ProductID, pod.UnitPrice, pod.LineTotal, pod.ReceivedQty, pod.RejectedQty, pod.StockedQty, pod.ModifiedDate INTO dbo.od FROM Purchasing.PurchaseOrderDetail AS pod; With that in place, let's start with a clustered index: CREATE CLUSTERED INDEX TestCIndex ON od (ProductID); And, a query to test with: SELECT od.PurchaseOrderID, od.PurchaseOrderDetailID, od.DueDate, od.OrderQty, od.ProductID, od.UnitPrice, od.LineTotal, od.ReceivedQty, od.RejectedQty, od.StockedQty, od.ModifiedDate FROM dbo.od WHERE od.ProductID BETWEEN 500 AND 510 ORDER BY od.ProductID; This results in the following execution plan: OK. Well done, Grant. That's how a clustered index works. The…
Read More

Identifying Recompile Causes

SQL Server
Strictly speaking, a recompile isn't really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here's one way to get it done. Extended Events for Recompile Causes You knew I was going there. Seriously though, we know that, since SQL Server 2005, all recompiles are at the statement level (unless you call for a recompile of a procedure or create that proc with a RECOMPILE hint). So, capturing the recompiles and getting grotty on the details means capturing statements. Understand that this can be expensive, especially in terms…
Read More

Query Store as an Upgrade Tool

SQL Server, Tools
There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That's because of the new cardinality estimation engine introduced in 2014. Most queries won't notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool. The Steps We're going to…
Read More

Which Query Used the Most CPU? Implementing Extended Events

SQL Server, T-SQL, Tools
A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it's not readily apparent how to get it. While you can look at what's in cache through the DMVs to see the queries there, you don't get any real history and you don't get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data. A…
Read More