Joe Fleming wants to know how I solve weird problems. I'm not sure I'm actually qualified to answer this question since I, myself, am a weird problem, but I'll give it a shot. One aspect of my job is to provide support to our clients. No, I'm not on call (thank the gods), but I'll get roped in for, well, the weirder ones. No, no, not the weirder clients (although...), the weirder problems. Recently we had one, not exactly weird, but it did take a few odd turns along the way. The Problem A client asked us to tell them when a query ran long. Simple. We have a long running query alert, all built in to Redgate Monitor, so, done. No, see, we like getting alerted when queries run…
Yeah, yeah, second AI post in a row. I promise not to make a habit of it. But I saw someone else mention that you can feed them XML and the AI will read the execution plan. I had to test it out and then overshare my results with all of you. We Need A Query Here's a query: SELECT c.CustomerID, a.City, s.Name, st.Name FROM Sales.Customer AS c JOIN Sales.Store AS s ON c.StoreID = s.BusinessEntityID JOIN Sales.SalesTerritory AS st ON c.TerritoryID = st.TerritoryID JOIN Person.BusinessEntityAddress AS bea ON c.CustomerID = bea.BusinessEntityID JOIN Person.Address AS a ON bea.AddressID = a.AddressID JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE st.Name = 'Northeast' AND sp.Name = 'New York'; This query results in this execution plan: There are some tuning opportunities here.…
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…
Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work FevziKartal. The rest of this post is just me replicating work already done by others. I just want to see it in action. Columns in Live Data Explorer Back when I wrote the 2017 version of my query performance tuning book (no link, a) get the 2022 version for reasons I'm about to explain, b) this post is just about testing FevziKartal's work, not self-promotion), I was on board with #TeamXE. Jonathan Kehayias had taken me under his wing and explained the virtues of Extended Events and I was sold. I was also grossly ignorant. I thought that the way you…
Yeah, stupid long title. It's a question I put to several different AI engines. I'm curious what the aggregated knowledge of the internet has to say on the topic of blockers to Extended Events adoption. I'll leave it to you to do the search on your favorite engine and get the wordy, long-winded, answers. However, I want to bring up some of the points that were raised in order to talk about them. First, we'll talk about some of the odd answers. Then, we'll talk about the consensus. Also, one engine gave me a blatantly poor example Session, so I want to point that out. Note: I'm not dinging on AIs. I'm growing to like the little monsters. We're going to address valid points that they bring up. However, I…
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…
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…
The invitation this month for #PGSqlPhriday comes from Dian Fay. The topic is pretty simple, database change management. Now, I may have, once or twice, spoken about database change management, database DevOps, automating deployments, and all that sort of thing. Maybe. Once or twice. OK. This is my topic. I've got some great examples on taking changes from the schema on your PostgreSQL databases and then deploying them. All the technical stuff you could want. However, I don't want to talk about that today. Instead, I want to talk about something really important, the concept of rollbacks when it comes to database deployments. Why Are Rollbacks Difficult? The entire purpose of your PostgreSQL database is to persist, that is to store, the data. Then, of course, offer up a way…
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…