Tools From Erland Sommarskog: sp_sqltrace and beta_lockinfoPosted: December 10, 2012
You can simply to run this to get a resultset from sp_sqltrace:
EXEC sp_sqltrace ‘EXEC yourSP pVal’
Download link for these Tools from Erland Sommarskog, SQL Server MVP:
beta_lockinfo – http://www.sommarskog.se/sqlutil/beta_lockinfo.html
this will give you a snapshot of what is going on right, including current stored procedure and statement. You need to have at least VIEW SERVER STATE to run it.
sp_sqltrace – http://www.sommarskog.se/sqlutil/sqltrace.html
This tool permits you to run a trace filtered for a certain process for a while (default 10 seconds) and then summarises the data. You need the same permissions as to run Profiler, but everything is executed server-side.