Long running queries - stored procedures

SQL Server performance monitoring and alerting

Moderators: Colin Millerchip, priyasinha, Adam, chriskelly

Long running queries - stored procedures

Postby DBdave » Tue Jul 29, 2014 12:11 pm

If a sproc is captured as a long running query the parameters used to execute the stored procedure are not captured which makes it harder to analyse the issue and makes the alert a little pointless.

Is there a way to find out what was passed into the sproc?
DBdave
 
Posts: 8
Joined: Thu Oct 03, 2013 11:21 am

Postby chriskelly » Wed Jul 30, 2014 2:00 pm

Thank you for your post.

SQL Monitor uses a SQL Server table valued function called sys.dm_exec_sql_text (based upon a sql_handle found using the view sys.dm_exec_query_stats) to gather information about queries which are run. These are used in a number of places including the long-running query alert.

sys.dm_exec_sql_text (Transact-SQL)
http://msdn.microsoft.com/en-gb/library/ms181929.aspx

sys.dm_exec_query_stats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189741.aspx

The tool uses a complex query to get this information, and unfortunately neither of these hold the parameters specified when running stored procedures. To the best of my knowledge there is no way to retrospectively find these out either.

I'm sorry that I don't have a better answer for you. But feel free to add any additional questions.
Chris Kelly
Technical Support Engineer
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK


Return to SQL Monitor 4

Who is online

Users browsing this forum: No registered users and 0 guests