Quickest way to identify most used Stored Procedure variation in SQL Server 2005

Question

I am trying to figure out if there's a way to identify a "version" of a SP that gets called the most. I have an SP that gets called with a bunch of different parameters. I know that the SP is causing some issues and trying to pin point the problem. Besides capturing calls to the SP and manually sifting through the results, is it possible to use the profiler to group SP calls by supplied parameters?

I'm not a DB(A/E), just a web dev, so any hints/points in the right direction will be helpful. Thanks!

EDIT: Recompiling the SP does not help much.

Answer 1

This will give you the top 50 most used procs and the statements in the procs, from here: Display the 50 most used stored procedures in SQL Server

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
  execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
and exists (SELECT 1 FROM sys.procedures s
WHERE s.is_ms_shipped = 0
and s.name = x.ProcName )
ORDER BY execution_count DESC

Visit that link to grab the query for the proc name only, but I think this is a better query since it gives you the statements in the procs also

Answer 2

It sounds like you only need to be able to capture this information for a short period of time. The sproc may be called a large number of times during that period, but it's a finite period.

If that is the case, perhaps you could log the sproc calls somewhere? If you have control over the sproc code, you could perform the logging there. One approach would be to create a special table for this purpose, add an INSERT to that table at the beginning or end of the existing sproc, and wait for some records to accumulate in the table.

Depending on the specifics, you could create a column in the custom logging table for each sproc parameter.

Then you'd have ample information about the usage of the sproc, for the period of time you perform the logging.

Given the data accumulated in the table, you could query to find the most frequent parameter values, which users or applications or web pages, etc. are entailed, the datetimes for the beginning and ending of the sproc call, and whatever else you log.

This would not involve any changes to the application code, and it could be completely eliminated after you have completed your troubleshooting. So, aside from the inevitable performance hit of all that logging, the price of this approach is not high.

Edit: This approach would be an alternative for users who lack the special permissions required to run DMV queries on tables such as sys.dm_exec_query_stats. In many shops, getting such permissions -- particularly on production databases -- is not feasible for developers.