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