Which parameters was a stored procedure or command executed with?

Question

We are using nHibernate which generates queries from .NET code. Occasionally, some queries get stuck in the sp_whoisactive list and we cannot find out why. I have a feeling that it has to do with a missing or broken parameter. When I run sp_whoisactive, however, I only see the parameter name (in example: @p0), not the actual value. Is there a way to retrieve this information while the process is still running?

Example:

SELECT 
    this_.Id as Id6_2_, this_.HRNumber as HRNumber6_2_, 
    this_.FirstName as FirstName6_2_, this_.LastName as LastName6_2_, 
    this_.StatusTypeID as StatusTy5_6_2_, 
    this_.PropertyId as PropertyId6_2_, this_.DepartmentGroupId as Departme7_6_2_, 
    property2_.PropertyID as PropertyID10_0_, 
    property2_.PropertyCode as Property2_10_0_, 
    property2_.LMSCode as LMSCode10_0_, property2_.PropertyName as Property4_10_0_, 
    property2_.Report as Report10_0_, department3_.Id as Id4_1_, 
    department3_.Name as Name4_1_, department3_.DisplayName as DisplayN3_4_1_ 
FROM 
    [dbo].[EmployeeDepartmentGroupView] this_ 
LEFT OUTER JOIN
    [dbo].[Property] property2_ ON this_.PropertyId=property2_.PropertyID 
LEFT OUTER JOIN
    [dbo].[DepartmentGroup] department3_ ON this_.DepartmentGroupId=department3_.Id 
WHERE 
    this_.HRNumber LIKE @p0;

Please note, this is automatically generated SQL from nHibernate.

Specs: SQL Server 2008r2

score 3 · Accepted Answer · answered Dec 23 '14 at 11:04

If you have a suspicion about which processes/procedures, etc cause the issue, could you not use SQL Profiler / Trace / extended events to get the data? - it is then fully recorded including the Parameters that were passed. - of course this would need to be running ahead of time - not any use once it's already running. However a well filtered Trace can have relatively low performance impact, so you could leave the Trace running all the time.

Here's basic how to setup a Trace/Profiler: How To Create a Trace with SQL Server Profiler

You can find better sources of info on this once you have your head round the concepts.

Which parameters was a stored procedure or command executed with?

1 Answers1