Boredom and XML
I recently found myself in the midst of a client issue that lead me to need a bunch of corroborating data from the system health extended event session.
There’s hardly any good documentation on it, and even fewer well-written queries or resources for parsing data out of it.
So now I’m making it easy for you, because I care about you more that Microsoft does.
If you need further proof of that, just look at the Query Store or Extended Events GUI.
Now look at sp_QuickieStore and sp_HumanEvents.
Who loves you, baby? I do.
Activated Development
Since this is currently in beta, it’s missing a lot of the bells and whistles that my other stored procedures have.
Right now, it just pulls all of the useful performance data out that I can get at:
- Queries with significant waits
- Top waits by count
- Top waits by duration
- Potential IO issues
- CPU usage details
- Memory usage details
- Critical system health issues
- CPU intensive queries
- An incredibly nerfed blocked process report
- Query plans for blocked queries
I know that there’s gobs of data around errors and security and all that jazz, but that stuff is often irrelevant to what I’m trying to coax out of a SQL Server.
In the future, I’ll be doing what I can to make sure I’m pulling all of the performance-related event data that I can, and trying to add some analysis and additional filtering to each section.
If you have any feedback, please open issues on GitHub.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Bumps of geese, Comrade Erik.
(Russian guy I used to work with, but his name wasn’t Erik.)
You’ll make centerfold of Beergut Magazine any time now!
Fingers crossed, legs… Well…
You sure love is, thank you, again!
Something like that 😂
Hi Erik
Thank you for this procedure, I have been using your script all the time for two years and it saves me a lot time.
I’ve tried the script but on my server with one particular db (10 GB) it took 4 minutes to retrieve the data, is it normal behavior or can we somehow to speed up the data retrieval?
Thank you again and please keep blogging…..
If the time was spent reading the extended event file, there’s not a lot I can do. That is a bit of a mess on Microsoft’s end. You could try reading data in smaller chunks, like just a single day.
I’m working on improvements to other bits of the XML parsing, but it’s not always easy and there are a lot of things that can’t be generally applied because of differences in SQL Server version and server hardware.
Another cool DBA toy for our war-chest from Ozar/Darling DB gods.
Making our DBA lives easier.
Salute,
Jeff S.
🥳🥳🥳