A Fine Slice Of SQL Server
Have you ever wondered where the
.xelfile is saved when you create a new Extended Event session and don’t specify the full path (just the file name)?Like so: [image removed because you should go to Tom’s site and see it, ed.]
Well, so did I and here’s what I’ve found out.
Click through to learn where these files end up if you don’t specify anything.
Comments closedMitchell Sternke looks at some new extended events:
Running Extended Event (XEvent) sessions on SQL Server has a cost. Since XEvents was designed for high-performance, this is usually unnoticeable. However, it can become an issue depending on which events, actions, and other XEvent features are in use. New metrics available in SQL Server 2022, in Azure SQL Database, and in Azure SQL Managed Instance can help you understand the performance impact of using XEvents in your database.
The sys.dm_xe_session_events DMV (sys.dm_xe_ database_session_events on Azure SQL Database) provides one row for each event found in an active session definition, allowing you to see all events that are currently publishing on your SQL Server instance. Four new columns have been added to help with troubleshooting performance:
Read on to learn more about these columns and what they can do for you.
Comments closedErik Darling notes a problem with time-based filters on operations:
There are six waitfor commands that each pause for 1 second. In between them are queries that finish in milliseconds.
If I watch the event output, eventually, I’ll see this:
Okay, so the stored procedure took more than 5 seconds, but… no individual query took more than 5 seconds.
This happens a lot and I’m not sure there’s a good answer for it.
Comments closedOn one of the servers that we monitor, it was reported that periodically a specific job would seem to hang and take forever to complete. I was asked to set up something to help find exactly what statement in the job that was the root cause of the hang.
Read on to see how Lori was able to collect the info necessary to resolve this issue.
Comments closedChris Webb looks in on things:
Something I do all the time when performance tuning Power BI is use SQL Server Profiler to monitor query and refresh activity. There’s a handy external tool that lets you open up Profiler with a connection to Power BI Desktop; if you’re using Power BI Premium you can also connect Profiler up to a published dataset using the XMLA Endpoint. Profiler is a bit old-school though and likely to lead to snarky comments from SQL Server DBAs (it’s not deprecated for Analysis Services though!) who’ll say you should be using Extended Events (aka xEvents) instead. And guess what – did you know you can use Analysis Services xEvents to monitor activity in Power BI Desktop?
It’s the power of Extended Events.
Comments closedGrant Fritchey wants to see if you’re bulking up:
Wouldn’t it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don’t know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event.
Click through for an overview of what this event provides, as well as what it doesn’t provide.
Comments closedKenneth Fisher performs some diagnostics:
Dev: We are getting an error: SELECT permission was denied on the object MyObject.
Me: Ok, what instance, database, and account is getting the error?
Dev: The instance is InstanceA and we think the database is DatabaseA but we have no idea what the service account is.Now, watch this.
Watch and learn.
Comments closedBut one thing that caught my eye in all the ruckus is a new Extended Event called
query_antipattern, which is a lot more up my personal alley. You may have come across my Bad Habits series, or might have seen me rant about some of these in person. I think this is a promising compromise between manually parsing every single query and just not realizing how many little things are collectively bringing SQL Server to its knees.This event is not yet documented, but it seems like it could be some combination of runtime code analysis and execution plan inspection. Right now, all we have to go on is a set of cryptic values in
sys.dm_xe_map_values:
Read on to see those values and Aaron’s speculation.
Comments closedGrant Fritchey digs into a useful Extended Event:
One of my favorite little bits of information about Extended Events is the fact that everyone running a full instance of SQL Server has deadlock information available to them, even if they never enabled Trace Flag 1222 through the system_health session. That captures the xml_deadlock_report which has the full deadlock graph. However, what if you want to capture deadlock info, but, you’re dealing the GDPR, and transmitting query values could be problematic? Enter xml_deadlock_report_filtered.
Read on to see how it works, though note Grant’s warning that this is a non-documented event.
Comments closedGrant Fritchey points out another use for extended events:
Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here’s a little one that came up, auditing unique constraint violations.
It can also handle most other types of errors, making this a robust way of tracking issues. Back in the 2008 days, I built a little WPF program to watch for all of the errors on the couple of production SQL Server instances I managed. At one point, I saw one of the devs trying to write a query and getting an error. I IM’d the dev and said “You forgot the GROUP BY clause” (or whatever the problem was—it was something minor like that). He came over with a bit of panicked excitement, trying to figure out how exactly I was able to see what he wrote given that I was nowhere near his cube. Good times.
Comments closed