Running a bit late this month for T-SQL Tuesday (it’s not exactly Tuesday is it!) but that’s Grant Fritchey’s (blog|twitter) fault! Yes, he’s the host this month and he ran a bit late with the invitation. I probably should have gotten this done earlier but I’m blaming Grant and you can’t stop me.
Grant would like us to talk about Extended Events. I’ve posted a few things in the past about Extended Events such as featuring a video of Grant’s that helped me understand the Extended Events Viewer quite a bit better, and an Extended Events session that catches errors that occur. That said, I still find it rather complicated and tend to avoid it unless I truly need it. So this time I want to mention a script of Erik Darling’s (blog|twitter) called sp_HumanEvents. Yes, this is the second time in a row my T-SQL Tuesday post has been about Erik. It’s a coincidence, I promise!
Regardless, this script is designed to be a super easy way to create temporary, or even more permanent extended event sessions. For example here are a couple of the scripts he mentions in his post (link from the SP name).
Perhaps you think queries recompiling are the cause of your problems! Heck, they might be. Have you tried removing recompile hints? ?
EXEC dbo.sp_HumanEvents @event_type = 'recompilations',
@seconds_sample = 30;
To capture all types of “completed” queries that have run for at least one second, for 20 seconds, from a specific database
EXEC dbo.sp_HumanEvents @event_type = 'query', @query_duration_ms = 1000,
@seconds_sample = 20, @database_name = 'YourMom';
Pretty simple right? And there is a parameter called @keep_alive that makes them permanent sessions. And then once you have some sessions created you can run this command to load the data into tables:
EXEC sp_HumanEvents @output_database_name = N'YourDatabase',
@output_schema_name = N'dbo';
Note: You’ll want to put the output code into a scheduled job.
Now, Erik’s SP isn’t perfect and doesn’t cover every possible use for Extended Events so you really should get more comfortable with it (and by you I mean I). That said, it does cover a lot of the more common uses so it couldn’t hurt to take a look.