You’ve got a stored procedure that calls other stored procedures that build dynamic SQL that call other functions, and there’s so much calling going on that you would like to phone a friend.
Your query plans look like piles of spaghetti, and when you zoom in, you just see more tiny piles of tiny spaghetti.
Finding the slow parts is really easy thanks to sp_HumanEvents by Erik Darling (Blog – @ErikDarlingData.) Download it, install it in your master database, and you’ll be ready to go.
In one window, get ready to run the nasty stored procedure. Make a note of which session you’re in – it shows up at the bottom of your SSMS window like this:
See the “SQL2019\Brent (60)”? That number in parenthesis (60) is my session ID.
In another window, fire up sp_HumanEvents:
sp_HumanEvents
@event_type = 'query'
, @session_id = 60 /* change this to match the session you're interested in */
, @seconds_sample = 30
, @query_sort_order = 'duration'
, @query_duration_ms = 50 /* only grab queries that ran at least this long */
The parameter names & values are pretty self-explanatory there. After 30 seconds, you’ll get a nice rundown of what queries ran, how long they took, their execution plans, and much more.
You could do this same thing with Profiler, but it’s more painful to set up, has more overhead, and doesn’t give you the nice, clean, filtered result set. You could also do this same thing by rolling your own Extended Events session, but seriously, who’s got time for that? Erik’s done all the hard work for you.
If you like this, you’ll love how easy it is to find queries using option recompile.
