A Fine Slice Of SQL Server
Erik 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 closedErik Darling is less than enthused:
The results from my Extended Event session, and… I’ll talk through my disappointment in a moment, was not terribly spectacular.
There are some promising concepts in there but as a whole, it does seem like we’ll need fingers crossed that this improves for RTM.
Comments closedTom Zika checks out some extended events:
It has been announced today (2022-05-24) during the MS Build event. The blog post includes a download link. Unfortunately, the Docker container is not quite ready yet.
Anyway, because I’m a #TeamXE, I had to check out if there are any new goodies there. So, I took an XE event list from Microsoft SQL Server 2019 (RTM-CU16) and the new one from Microsoft SQL Server 2022 (CTP2.0) and compared them.
There are a lot of new events—click through to see how many.
Comments closed