2

I have a long running query in SQL Server 2014. It's a stored procedure invocation. In Activity Monitor, it shows up. The details view provides the currently running SQL, which is the EXEC statement. The command column says it's an UPDATE.

The stored procedure in question has several UPDATE statements. How do I know which one is currently running? Also, is there a way to know the stored procedure parameters?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Seva Alekseyev
  • 59,826
  • 25
  • 160
  • 281
  • 2
    First part is a duplicate of http://stackoverflow.com/q/9293429/73226. Second part not whilst it's already running except if the plan in plan cache was compiled for this execution and then you can get them from the XML. – Martin Smith Sep 21 '16 at 18:57
  • sp_who2, sp_whoIsActive – Channa Jun 10 '19 at 17:05

1 Answers1

1

Too long to comment and thought it doesn't answer you question, it may give you some places to start.

  1. Kill the proc, then use SQL Profiler to trace the proc while you execute it. This is resource intensive though and option 2 is better IMHO.
  2. Use Adam Machanic's sp_whoisactive to trouble shoot. There are lots of tutorials out there with different options and use cases for this. A popular one is:

exec dbo.sp_WhoIsActive @get_plans =1, @get_additional_info = 1

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
S3S
  • 24,809
  • 5
  • 26
  • 45