I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge. Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are.…
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.…
Blog post #7 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Sooner or later when you're working with SQL Server, someone is going to complain that the server is slow. I already pointed out the first place you should look when this comes up. But what if they're more precise? What if, you know, or at least suspect, you have a problem with a query? How do you get information about how queries are behaving in SQL Server? Choices For Query Metrics It's not enough to know that you have a slow query or queries. You need to know exactly how slow they are. You must measure. You need to know how long they take to run and you need to know how many resources are…
Oh good gosh. Six months without a single blog post. Most important information: I'm not dead. Apologies. The issue is pretty simple. I'm getting old. Ha! Seriously though, I am suffering a bit from a lack of energy to do ALL the things and still maintain this blog. However, to hell with that. Time for a refresh. Watch this space. I'm going to be cleaning it up. Changing a few things. Also, getting back to posting. I don't think as many of the posts will be technical though. I'm going to start posting some leadership ideas and suggestions. I've been involved in community for a long time, and I want to see it keep going. I have received so much from the communities I'm involved with, I want to give…
A great question came up over at DBA.StackExchange regarding the query store reports time intervals: How can SQL Server's Query Store Reports show data for minute-length intervals, when "Statistics Collection Interval" is set to 1 hour? I was intrigued because it's not something I had thought about at all. How Does the Report Decided on Time? The first thing you need to know is that all performance information inside Query Store is aggregated. By default, the aggregation interval is 60 minutes. You can adjust that up or down (although, I wouldn't recommend making it too granular, you'll see a massive increase in storage size). It's aggregated because trying to capture every execution of every query, as anyone who has done it using Extended Events knows, is expensive and has a…
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…
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…
I was talking with some developers from my team about monitoring, and I said, "We all use the same tools," referring to other monitoring software. Then, it hit me. How is AWS collecting monitoring data on it's RDS servers, specifically, the SQL Server instances. So, I set out to determine what happens on AWS RDS when it comes to the native monitoring. The Setup This part should be as obvious as it is easy. I'm going to use Extended Events. I've written before about how AWS RDS supports Extended Events, so I won't repeat all that here. I'll just leave you with the session I'm running to see what happens on AWS RDS: CREATE EVENT SESSION [ExEventTesting] ON SERVER ADD EVENT sqlserver.rpc_completed, ADD EVENT sqlserver.sql_batch_completed ADD TARGET package0.event_file (SET filename…
The question came up that someone wanted to monitor the queries impacting tempdb using Extended Events. Initially, I was pretty sure that there was no real way to do this. The standard query events like rpc_completed includes spill information, if any occurred, but that's it. There are also no actions that you can add to an event that would capture tempdb behaviors. So, I want to my senior DBA, YaDuBiGle. I found this ancient (2009) article on SQLTips, that surprised me with a pretty slick solution using, are you ready, waits. Clever. How well does it work? Let's try it out. Testing Capturing Queries Impacting TempDB I'm not stealing their code, so you can follow the link to see the setup. I'm much more interested in the results. How well…
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…