How to identify running proc parameters using DMVs only?

Question

Hi Folk,

When I use below query to identify running procs, it just gives me proc definition or its name but not the parameters with which proc is running. Using profiler or extended events it is easy to identify but can't parameters be identified using DMVs only. Thanks

select db_name(sp.dbid) as DBNAME,sp.spid,sp.ecid,sp.kpid,sp.lastwaittype,sp.blocked,sp.status,sp.program_name,sp.loginame,sp.nt_domain,sp.nt_username,sp.hostname,sp.cmd,sp.open_tran, sp.login_time,
sp.last_batch,sp.cpu,sp.physical_io,sp.memusage, dest.text
from sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS dest
where sp.spid>50 and sp.status not in ('sleeping') order by sp.last_batch desc

Regards
Chirag

Answer 1

Hi,

>How to identify running proc parameters using DMVs only?

It seems that this cannot be achieved using DMVs. Only the compiled parameter values can be obtained from the DMVs, not the runtime values.
Using profiler or extended events are options.

I can see a good post on this topic.
https://dba.stackexchange.com/questions/142488/in-sql-server-is-there-a-way-to-determine-the-values-of-the-parameters-passed-t

Answer 2

You can use sys.dm_excec_buffer, but you will not see parameter values for RPC calls; only when calls are made as EXEC statements. And it does not apply to nested procedures.