31Mar 2025 by Grant Fritchey 4 Comments

How Do You Pick Events in Extended Events?

SQL Server
A while back I wrote about using AI to explore why people are not using Extended Events. You can read all about it here, but a short summary of the biggest blockers would be: Familiarity (more comfort in Trace or DMVs) Lack of Knowledge (just don't know how it works) XML (I agree) Event Overload (there are just too many) For this blog post I want to focus on the last one, Event Overload. There really are a lot of events in Extended Events. I don't just think that's a good thing. I think it's a GREAT thing. However, I get it. I hit the same problem, regularly. Which events do I use to do thing that I'm trying to do? Except for blogs like this one, there's not always…
Read More
17Mar 2025 by Grant Fritchey No Comments

Can You See Table Valued Parameters in Extended Events?

SQL Server, You Can't Do That In Profiler
I live for questions and this was an interesting one. Can you see Table Valued Parameters that have been passed in to Extended Events? I literally have no idea. I'm sure we'll see something, I just don't know what. Time to find out. Table Valued Parameters I don't want to get in to whether or not table valued parameters are a good or bad thing. Like anything else, I'm sure they can be used for good or for evil. However, just like knowing what value was passed to an integer, I can see why you may want to know what was passed in to a table valued parameter. To get started, let's create a table type: CREATE TYPE ErrorList AS TABLE ( ErrorTime DATETIME, UserName sysname, ErrorNumber INT, ErrorMessage NVARCHAR(4000)…
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
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
20Feb 2023 by Grant Fritchey No Comments

What Happens On AWS RDS?

AWS, RDS
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…
Read More
11Nov 2022 by Grant Fritchey 2 Comments

SQL Server 2022 Query Performance Tuning

SQL Server
If you're interested in getting a digital copy, my brand spanking new book is now available here. It's in the intro, but let me tell you a little bit about the new book. It's really new. Some of the older versions of the book were simply updated, a bunch of changes to most chapters, a couple of new chapters, fixes for old mistakes, ta-da, new book. Not this time. This time, I rewrote it all. From scratch. Now, some of the chapter titles are the same. Quite a few of the examples are the same (if code illustrates something successfully, I'm reusing it). However, overall, it's a brand new book. There's a lot of new material too. The last update was for SQL Server 2017. There has (almost) been two…
Read More
03Oct 2022 by Grant Fritchey 2 Comments

Function Vs. Performance

SQL Server
Recently I was looking through DBA.StackExchange when I saw a pretty simple question that I decided to answer. I went off, set up a test database and some test tables and quickly wrote a query to answer the question. I got it all formatted pretty and was on my way to post it when I saw that another answer was already there. Yeah. Identical to mine. Almost line for line. Well, nuts. I know. I'll write a blog post. The Setup The original poster had two tables that, frankly, are badly designed. However, they share enough data that they are "related" if not relational. Here's the code: CREATE DATABASE Testing; GO USE Testing; GO CREATE TABLE Table_A ( ID INT IDENTITY(1, 1), Score INT ); CREATE TABLE Table_B ( FromPoint…
Read More