I had an excellent group of people in Gothenburg Sweden when I taught there and I was asked: Can You See Who Forced a Plan? I didn't know the answer for certain, so I said what I always say: I don't know, but I'll see if I can find out. Query Store System Views One of the first places I'd look to see who forced a plan is the system views in Query Store. No, I don't think it'll be there, but it's worth a look. The obvious place it could be is sys.query_store_plan. After all, that's where a plan will be marked as is_forced. But you look through that and there's nothing about who forced a plan. And looking through the other views, there's nothing showing that. So, this…
I'm still learning PostgreSQL and one of the things I've been looking at a lot lately is instrumentation. What do I mean? Well, if you're in SQL Server, think, Dynamic Management Views (DMV), Extended Events, Query Store, <hack, spit> Trace <spit>. How do we know how long a query took to run? PostgreSQL can tell you, but, and this is one of those wild, cool, but, honestly, slightly frustrating things about PostgreSQL, not natively. Let's talk about it. Instrumentation If you connect up to a PostgreSQL database, you actually do have the equivalent of DMVs. It's called the Cumulative Statistics System. And yeah, it's a bunch of views on a bunch of functions. It displays all sorts of data about IO, tables, indexes, etc.. It will even show you acive…
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…
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."…