02Sep 2024 by Grant Fritchey No Comments

Missing Columns in Extended Events Live Data Explorer

SQL Server
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…
Read More
19Aug 2024 by Grant Fritchey 4 Comments

What Are the Most Common Blockers to Adopting SQL Server Extended Events?

SQL Server
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…
Read More
12Aug 2024 by Grant Fritchey 1 Comment

Observing Extended Events

SQL Server, You Can't Do That In Profiler
Sure, you can right click on a running session for Extended Events and open the Live Data window, but are there other ways of observing what Extended Events is capturing? Yes. Extended Events Output Let me just get out of the way immediately, there's probably a way to do this programmatically with DBATools. I need to dig in there, and I'll post what I find. But what can we do right now? SQL Server Management Studio (SSMS). Here are all the sessions currently on my test/demo machine: Some of these are built in, system_health for example. Most of the rest are mine. So, how can we see what they do? Well, notice that every one has a little plus sign next to it: If we click on the plus sign,…
Read More
01Jul 2024 by Grant Fritchey No Comments

Only Capture Extended Events For a Given Time

SQL Server
It's a great question. Let's say you want to capture stored procedure completions. But, you only want to capture them between 3AM and 4AM. Can you do it? Output of rpc_completed Let's create a really simple event: CREATE EVENT SESSION [RPCTimeBoxed] ON SERVER ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1) WHERE ([sqlserver].[database_name]=N'AdventureWorks')); If we start this event, run some code, the output within the Data Explorer window looks like this: Just a couple of points here. Notice the fields in the event. None of them are dates or times. However, up above, we get the timestamp column. Done, right? Let's use that. But first, what does AI, through CoPilot tell me? CoPilot To The Rescue? I asked CoPilot. It took a couple of refinements to get it on board with the idea that…
Read More
27Jun 2024 by Grant Fritchey No Comments

Data Technology Learning Resources

AWS, Azure, Database Fundamentals, PostgreSQL, SQL Server
Hey all! Quick one here. Tracy Boggiano, awesome person that she is, has put together an interesting collection of data over on GitHub. It's titled DBA Resources, but it goes beyond DBAs. It's much more about the different data platforms on display. And, it's in Github. Create a pull request and you can add to it. Get your own blog on there, whatever. Anyhoo, worth a look. I'm going to do some contributions. Oh, AND, it's multi-platform, cause, isn't everything these days.
Read More
24Jun 2024 by Grant Fritchey 4 Comments

Exploring Window Functions Execution Plans

SQL Server
There are quite a few different ways that you're likely to see window functions evidence themselves within your execution plan. Let's take a look at one example. Window Functions For our example, I've got a pretty simple query: SELECT soh.CustomerID, soh.SubTotal, ROW_NUMBER() OVER (PARTITION BY soh.CustomerID ORDER BY soh.OrderDate ASC) AS RowNum, Soh.OrderDate FROM Sales.SalesOrderHeader AS soh WHERE soh.OrderDate BETWEEN '1/1/2013' AND '7/1/2013' ORDER BY RowNum DESC, soh.OrderDate; Nothing to it really. What kind of execution plan does this generate? Here's the plan with runtime metrics (aka, an actual plan): I showed the missing index suggestion (and let's remember, they're just suggestions) just for completion and to show I'm not hiding anything. Potentially, adding an index could speed up the query. However, that doesn't affect what we're going to talk…
Read More
13Nov 2023 by Grant Fritchey No Comments

Query Store Reports Time Intervals

SQL Server
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…
Read More
15Sep 2023 by Grant Fritchey 2 Comments

T-SQL Tuesday #166: Wrap-up

SQL Server
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…
Read More
14Sep 2023 by Grant Fritchey 3 Comments

T-SQL Tuesday #166: Why Not Extended Events?

SQL Server
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…
Read More
26Jun 2023 by Grant Fritchey 2 Comments

Battle of the Query Hints in Query Store

SQL Server
I recently presented a session on the Query Store at Data Saturday Rhineland and the question came up: If there's already a query hint on a query, what happens when you try to force a similar query hint? Yeah, OK, that is a weird one. I don't know the answer, but I'm about to find out. Setting up the Battle I've got this simple procedure I use a lot to illustrate bad parameter sniffing. In AdventureWorks, this query can produce up to five different plans, depending on the values called. Most of the time, it's one of two plans, which I'll get to in a minute. Here's the query: CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference (@ReferenceOrderID INT) AS BEGIN SELECT p.Name, p.ProductNumber, th.ReferenceOrderID FROM Production.Product AS p JOIN Production.TransactionHistory AS th…
Read More