whatstakingsolong.sql

--determine which queries are running
SELECT  *
FROM    sys.dm_exec_requests requests
WHERE   requests.session_id > 50
ORDER BY total_elapsed_time DESC

SELECT  requests.session_id, 
        requests.status, 
        requests.command, 
        requests.statement_start_offset,
        requests.statement_end_offset,
        requests.total_elapsed_time,
        details.text
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) details
WHERE   requests.session_id > 50
ORDER BY total_elapsed_time DESC
--with the session id of the stored procedure query you'd like to debug, determine what line is running
SELECT  SUBSTRING(detail.text, 
                  requests.statement_start_offset / 2, 
                  (requests.statement_end_offset - requests.statement_start_offset) / 2)
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) detail
WHERE   requests.session_id = 82