Find Queries Using a Key Lookup Operator

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

Query Compile Time

SQL Server, You Can't Do That In Profiler
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…
Read More

Query Store on Azure SQL Database

SQL Server
Under the covers, Azure SQL Database is just good old fashioned SQL Server and this includes Query Store on Azure. While many things can be different when working with Azure, Query Store just isn't one of them. Let's talk about it a bit. Query Store on Azure Unlike your databases created on a SQL Server instance (big iron, VM, hosted VM, wherever), the databases you create on Azure SQL Database have Query Store enabled by default. Managed Instance and Synapse are different. In their case, they operate the same as an instance of SQL Server, off by default. Further, in the single database of Azure SQL Database, you can't, as in can not, disable Query Store. It's on by default and it's staying that way. This leads to a simple…
Read More

Using Extended Events Live Data With Azure

Azure, SQL Server, You Can't Do That In Profiler
In my last post I showed some shortcomings of Extended Events, however, it is possible to use Live Data with Azure. Let's explore exactly how that works. To get started, you'll need to follow the directions here to get set up with Azure Storage as the output target of your Extended Events session within your Azure SQL Database. There is a little bit of prep work, but it's all laid out in Microsoft's document. I found the Powershell to be a bit sketchy, but it shows you what's needed. The T-SQL just works. Live Data With Azure Once you've created an Extended Events Session that is output to Azure Storage, you've done most of the work. The trick is really simple. Get the Azure Storage account set up with a…
Read More

Extended Events and Azure SQL Database

SQL Server, You Can't Do That In Profiler
Knowledge of how your system behaves is vital to better control, maintain, and grow the system. While Azure provides all sorts of wonderful assistance within Azure SQL Database, you're still going to need that same knowledge. When it comes to getting detailed information about Azure SQL Database, the tools are a little more limited than with an on-premises instance of SQL Server, or any virtual instance of SQL Server. There are no trace events. To see individual query calls, recompile events, query store behaviors, and so much more, you're going to have to use Extended Events. I'm going to write a series of posts on using Extended Events with Azure SQL Database. Tradition would call for this first post to be an initial how-to. Instead, I want to take a…
Read More

Extended Events: Embrace the XML

SQL Server
While XML is, without a doubt, a giant pain in the bottom, sometimes, the best way to deal with Extended Events is to simply embrace the XML. Now, I know, just last week, I suggested ways to avoid the XML. I will freely admit, that is my default position. If I can avoid the XML, I will certainly do it. However, there are times where just embracing the XML works out nicely. Let's talk about it a little. Copy This Query I have a theory. It goes like this: There has only, ever, been a single XML query written from scratch. All other XML queries are just copied from that one and then edited to do what is necessary OK. Maybe that's not entirely true. In fact, I know it's…
Read More

Extended Events: Avoid the XML

SQL Server, Tools
One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped. Look, I get it. I don't like XML either. It's a pain to work with. It's actively difficult to write queries against it. If there weren't a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let's see how. Live Data Window I have a video that goes into this in detail right here. But the core concept is simple.…
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

Getting Started Reading Execution Plans: Highest Cost Operator

SQL Server, T-SQL
Reading execution plans in SQL Server is just hard. There's a lot to learn and understand. I previously outlined the basics I use to get started when I'm looking at an execution plan for the first time. However, just those pointers are not enough. I want to explain a little further why and how those basic steps are how you get started reading execution plans. To begin with, instead of talking about the first operator, which I've detailed before, we'll talk about the highest cost operators. Highest Cost Operator Every execution plan within SQL Server includes what the optimizer has determined to be the estimated cost of each operation. All these estimated operator costs are tallied up, and that makes up the estimated cost of the whole execution plan. You…
Read More

Most Costly Statement in a Stored Procedure

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