You Should Use sp_HumanEvents To Make Extended Events Easy #tsqltuesday

Another One

---

This month, Grant has chosen the form of his destructor T-SQL Tuesday topic as Extended Events.

While my relationship with Extended Events is complicated for many reasons:

• Awful documentation
• Hardly any guidance on usage
• Almost nothing useful about what type of target to use when
• Everything stored in XML
• Slow, unfriendly GUI in SSMS

My need to use them while consulting outweighs my gripes and grievances about how Microsoft has chosen to write about, use, and present the data to you.

That’s where my stored procedure sp_HumanEvents comes in handy. It’s totally free, and open source. I built it because I needed to be able to get things going quickly without a lot of fuss and clicking around.

It will set up and pull data to help you track down issues in the following areas:

• Blocking
• Query Performance
• Compiles
• Recompiles
• Wait stats

I chose to leave deadlocks out of it, because the system health extended event session captures a good deal of those by default. It has a very limited amount of data from a toned-down blocked process report in it too, but it’s missing a lot of good information.

You can find the full documentation for it here, and also by using the @help parameter in the procedure itself.

My Favorite Martian

---

While it can do many things, the way I use it most often is to capture the long-running queries and plans from a stored procedure I have running in SSMS, so I can filter out the little query plans that don’t need my attention, and focus on the really bad parts.

To do that, just run this code:

EXEC sp_HumanEvents
    @event_type = 'query',                   
    @query_duration_ms = 5000,               
    @session_id = N'58',                    
    @keep_alive = 1;

That will set up an extended event that captures the following events, focused in on a single session id, and only collect details about queries that run for more than 5 seconds.

Don’t forget to replace the @session_id with whatever your session ID is running locally.

• sqlserver.module_end    
• sqlserver.rpc_completed  
• sqlserver.sp_statement_completed
• sqlserver.sql_statement_completed
• sqlserver.query_post_execution_showplan   

Some of them are a bit duplicative, but this was really designed to be flexible for a lot of different situations.

Once that runs, look for a session called keeper_HumanEvents_query in your Extended Event sessions in SSMS.

From there, you can right click to Watch Live Data, and then start running whatever is in your query window.

Every time a query takes more than 5 seconds, you’ll see the statement and query plan entries show up in the data viewer.

Thanks for reading!

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.