Finding the slow query: sp_WhoIsActive, Trace Flag 7412, and Live Query Plans
Which query is slow? One of the more convenient ways to figure this out is to sample activity while the procedure is running with the free procedure sp_WhoIsActive, written by Adam Machanic.
This demo shows how you can get more information from sp_WhoIsActive and live query profiles by enabling trace flag 7412. To get information on the estimated overhead of TF 7412 by SQL Server version, check out this post by Pedro Lopes.