29Nov 2021 by Grant Fritchey No Comments

Get SQL Server 2022 Now: It’s Called Azure

Azure, SQL Server
If you're like me, your palms went into a cold sweat to get your grubby fingers on the bits for SQL Server 2022 a few nanoseconds after you heard the announcement. Well, you might be waiting a while. As will I. However, I really do want to start experimenting with some of the functionality right now. If you do to, then join me at Azure SQL Database. Azure SQL Database as a Preview for SQL Server 2022 Yeah, I know it's not the same. I get it. You have a need to run this stuff on big iron, not virtual, and certainly not in the cloud. Still, you want to learn, right? Tons of the new functionality is either already in Azure or incorporates Azure functionality. You can get started…
Read More
19Jul 2021 by Grant Fritchey No Comments

Filtering Extended Events Using Actions

SQL Server, You Can't Do That In Profiler
Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don't have to be collected in order to put them to work filtering your Extend Events. Using Actions To Filter Extended Events Actions, also called Global Fields, are additional bits of data that you can add to a given Event when you're setting up an Extended Events Session. They are programmatic additions to the Event, as described here. Think of them sort of like triggers. In practice, adding an Action, database_name, to an Event, like the rpc_completed…
Read More
06Jul 2021 by Grant Fritchey No Comments

Permissions Needed To Force Plans in Query Store

SQL Server
I was recently asked what permissions were needed to force plans in query store. I'm sure I knew at one point, but at the moment I was asked, I couldn't remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here's what I found. Permissions in Query Store Look through the blog, you'll find I'm pretty enamored with Query Store. I even contributed to a book on the topic (a little, it was almost all Tracy's work on that book, I just helped out). I haven't addressed security and Query Store. You do need to think about security in Query Store. For example, should you give read access to Query Store to your dev…
Read More
14Jun 2021 by Grant Fritchey 2 Comments

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
10May 2021 by Grant Fritchey No Comments

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
03May 2021 by Grant Fritchey No Comments

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
26Apr 2021 by Grant Fritchey 2 Comments

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
19Apr 2021 by Grant Fritchey No Comments

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
05Apr 2021 by Grant Fritchey 1 Comment

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
29Mar 2021 by Grant Fritchey 8 Comments

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