How to get parameter values for dm_exec_sql_text

Question

I'm running the following statement to see what queries are executing in sql server:

select *
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.database_id = DB_ID('<dbname>')

The sql text that comes back is parameterized:

(@Parm0 int) select * from foo where foo_id = @Parm0

Is there any way to get the values for the parameters that the statement is using? Say by joining to another table perhaps?

Answer 1

Edit : Remus is correct, this will only bring out the compiled versions on the first time that the query plan hit the cache, not subsequent runs.

You should be able to get the parameters from the query plan, since it contains the last parameters used. Altering your code:

select * 
from sys.dm_exec_requests r 
cross apply sys.dm_exec_query_plan(plan_handle) as qp
cross apply sys.dm_exec_sql_text(r.sql_handle) 
where r.database_id = DB_ID('<dbname>') 

You will find the final column of the query plan is query_plan, an xml version of the query plan which you can manually inspect, at the bottom of the XML are the parameters, or if you fancy the challenge use XML parsing and XQuery to pull out the ParameterList tags