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."…
Honestly, sincerely, no kidding, I love Distributed Replay. Yes, I get it. Proof positive I'm an idiot. As we needed proof. To be a little fair to me, I love what Distributed Replay could have been, with a little more love. However, fact is, it's on the deprecation list for 2022. Which means, what minimal amount of love, if any, that Microsoft was giving to it, it's all gone, forever. Unlike the Little Engine That Could, turns out that Distributed Replay was the Little Engine That Almost Could, But Didn't. Really Didn't. Let's discuss it a bit. Distributed Replay The concept is wonderful. Capture a bunch of queries from your production system. Replay them on a non-production system for testing. Add in the idea of being able to chain together…
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…
I have long been a fan of Azure Data Studio, but one shortcoming has kept me from truly adopting it: Query Plans in Azure Data Studio. Sure, there was a plug-in you could install. Also, you could use a somewhat truncated version of Plan Explorer, but all I wanted was for SQL Server Management Studio plans to be query plans in Azure Data Studio. Go and get version 1.35 of the tool. Right now. DUDE! You have 1.35 of Azure Data Studio? Cool. Now, go to the menu bar. Click on "File." Click on "Preferences". Click on "Settings". Now, type the following into the search box: workbench editor enable preview. You should see this: Check the box below where it says "Workbench > Editor: Enable Preview" just like I have…
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…
I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn't see anywhere else, let me share it with you. Extended Events You must have known I was going to bring up Extended Events. Surely. Well, I am. Now, if you search up "timeout" in the events, you find lock timeouts, execution plan timeouts (ooh), and stuff like that. It's not related to the login timeout. So, look up "connection" or "log in". You get a lot of information, but again, none of it is related to timeouts. In fact, the best info is in process_login_finish. It does include login…
While teaching about Extended Events and Execution Plans last week, Jason, one of the people in the class, asked: Is there a way in Extended Events to find queries using a Key Lookup operation? Sadly, the answer is no. However, you can query the Execution Plans in cache or in the Query Store to find this. Thanks for the question Jason. Here's your answer. Finding Key Lookups Since we can't have Extended Events just feed us the information, we have to query the plans. That means pulling out data from the XML. So, to find queries that are using the Key Lookup operator, we can do this: SELECT DB_NAME(detqp.dbid), SUBSTRING( dest.text, (deqs.statement_start_offset / 2) + 1, (CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text) ELSE deqs.statement_end_offset END - deqs.statement_start_offset ) / 2…
A question that came up recently was how to track the query compile time. It's actually a pretty interesting question because, there aren't that many ways to tell how long it took to compile the query, and they don't necessarily agree. For most of us, most of the time, compile time for a given query doesn't matter. However, I love telling the story of the query I had on an old system that could run in 90ms, but took 5 minutes to compile. In short, sometimes compile time matters. How To See Query Compile Time If you want to see how long it takes a query to compile, you have, to my knowledge, three options. The first, and possibly easiest, is to look at the plan properties on an execution…
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…
A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don't have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital. Happily, Extended Events are here to help. Finding a Costly Statement Query tuning is initially an act of discovery. Which queries, batches, procedures are inflicting the most pain on us. That pain could be measured a bunch of ways. The three most common, in particular order, are: Frequency with with a given query/batch/procedure is called.Resources used by that query.Length of time that it takes…