Once more, my apologies for being late on getting the T-SQL Tuesday announcement out. I have no excuse. However, our extended event on Extended Events (yes, I'm the third person to make this joke, yes, I'm blatantly stealing) still has several entries, so let's talk about them. Let's get mine out of the way. I was simply curious what the search engines revealed when I asked a pretty common question: how do you identify slow queries? What I found was, the answers on most search engines to this question are old, very old. Not to say wrong, but since many of them were created before a working version of Extended Events (let alone Query Store) was released, how could they tell you. On to actually good posts. One of my…
With 165 T-SQL Tuesday events, two, just two, this one, T-SQL Tuesday #166, and another one back in 2018 or 2019 (I forget and I'm far too lazy to go look) have been on Extended Events. At conferences I'm frequently the only one doing sessions on Extended Events (although, sometimes, Erin Stellato is there, presenting a better session than mine). I did a session at SQL Konferenz in Germany earlier this week on Extended Events. Hanging out in the hallway at the event (which was great by the way), I was talking with some consultants. Here's their paraphrased (probably badly) story: "I was working with an organization just a few weeks back. They found that Trace was truncating the text on some queries they were trying to track. I asked…
I've had people come up to me and say "PostgreSQL is open source and therefore license free. Why on earth would I put PostgreSQL in Azure?" Honestly, I think that's a very fair question. The shortest possible answer is, of course, you don't have to. You can host your own PostgreSQL instances on local hardware, or build out VMs in Azure and put PostgreSQL out there, some other VM host, or maybe in Kubernetes containers, I mean, yeah, you have tons of options. So why PostgreSQL in Azure, and specifically, I mean the Platform as a Service offering? Let's talk about it. PostgreSQL in Azure Let's assume for a moment that you're PostgreSQL expert (I'm not, but I do play one on TV). You know exactly how to set up…
I've been seeing more and more noise about how ChatGPT can help with technical stuff, writing code, all that. Well, I wanted to know how best to set up Query Store in SQL Server 2022, so I asked it. I'm not going to say this any other way, ChatGPT lied to me. Here's the conversation. I'm in italics. The Conversation With ChatGPT What are the best settings for Query Store in SQL Server 2022 Query Store is a powerful feature in SQL Server 2022 that helps in monitoring query performance and identifying query regressions over time. However, setting up Query Store requires careful consideration of several factors, including the size of the database, the frequency of queries, and the query workload. Here are some best practices for configuring Query Store…
Last week I posted the results from using Extended Events to snoop on what happens inside an AWS RDS database. This week, I'm taking a look at what happens on Azure SQL Database. I'm using the same toolset again, if for no other reason that I'm consistent in my approach. So it's basically just rpc_completed & sql_batch_completed on the database in question. Let's check out the results. What Happens on Azure SQL Database I would be doing the same thing as before, breaking apart the batch commands from the stored procs and/or prepared statements. However, after 48 hours, I only have 116 of both, so I'm just going to combine them this time. The batch called most frequently, for a whopping total of 8 times over 48 hours, isn't even…
It's possible for you to see new technology at work if you use Extended Events to monitor cardinality feedback. To put it simply, cardinality, the number of rows being returned, is estimated by SQL Server. Sometimes, it gets these estimates right. Sometimes, it gets them wrong. New functionality within SQL Server 2022 uses Query Store to see how well those estimates are working. If they're off, the optimizer can actually change plans to get you different behaviors based on this feedback. There's even more than one way to monitor cardinality feedback. Let's talk about it. Extended Events First up, to really see the full set of behaviors in action, we can use Extended Events: CREATE EVENT SESSION [CardinalityFeedback] ON SERVER ADD EVENT sqlserver.query_ce_feedback_telemetry, ADD EVENT sqlserver.query_feedback_analysis, ADD EVENT sqlserver.query_feedback_validation, ADD…
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…
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…
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…
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…