6

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

3
  • While the process is still running, perhaps DBCC INPUTBUFFER(<spid from sp_whoisactive>); Apr 28, 2014 at 17:24
  • Thanks Aaron. This shows me that there were 0 parameters, which makes me wonder if anything has been passed at all. Apr 28, 2014 at 17:31
  • DBCC INPUTBUFFER doesn't show me what parameters were used and I know there were some. How can there not be a way to do this?
    – influent
    Sep 16, 2014 at 18:41

1 Answer 1

3

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.

3
  • This is what we ended up doing. Jan 12, 2015 at 21:01
  • It is not always an option to run a trace on a production machine.
    – user56996
    Jan 20, 2015 at 17:14
  • If you always want to capture parameters for a specific stored proc, Extended Events are probably the lowest impact solution. A little more a of a learning curve than profiler, though ;)
    – epic_fil
    Jan 20, 2015 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.