How to monitor a stored procedure to capture execution times

Question

Post reply

How to monitor a stored procedure to capture execution times

IMary

Old Hand

Points: 361
More actions
October 18, 2019 at 9:17 pm

#3690323

Hi,
I want to monitor a procedure, Sp_search, to capture what different parameters are passed and how much time it is taking for each run.
Is there a way can trace once single stored procedure?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80342 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6d51f6e-c01b-4880-abb2-4f0cfd1f4531/extended-event-trace-on-event-rpccompleted-not-capturing-sqltext-action-unable-to-retrieve-sql?forum=sqldatabaseengine

this may help however if you want to do this for a short period you could use profiler

***The first step is always the hardest *******

Lowell SSC Guru Points: 323527 More actions · Answer 3

you can check teh procedure cache.

if you run this, you might see the same procedure multiple times, because each has a specific execution plan due to different parameters:

SELECT Db_name(st.dbid) dbname,
       OBJECT_SCHEMA_NAME(st.objectid, dbid) schemaname,
       Object_name(st.objectid, dbid) storedprocedure,
       Max(cp.usecounts) execution_count
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE  Db_name(st.dbid) IS NOT NULL
       AND Db_name(st.dbid) = Db_name()
       AND cp.objtype = 'proc'
    -- AND (st.objectid = OBJECT_ID('usp_ExtractPatientRefundTransaction') )
GROUP  BY cp.plan_handle,
          Db_name(st.dbid),
          OBJECT_SCHEMA_NAME(objectid, st.dbid),
          Object_name(objectid, st.dbid)
ORDER  BY Max(cp.usecounts)

then you can remove the group by info, and start digging into the actual plans, execution times, etc.

  DECLARE @ServerRestartedDate varchar(30)
  SELECT @ServerRestartedDate = CONVERT(varchar(30),dbz.create_date,120)  from sys.databases dbz where name='tempdb'
;WITH MyCTE
AS
(


--DECLARE @seconds INT = 15
      SELECT TOP 100
      @ServerRestartedDate As ServerRestartedDate,
           st.last_execution_time As LastExecutionTime,
           CONVERT(DECIMAL(9,3),(st.total_worker_time/ 1000000.0) / NULLIF(st.execution_count,0)) AS AverageSeconds,
           st.execution_count As TotalExecutions,
           (st.last_elapsed_time / 1000000 ) As LastElapsedSeconds,
             (st.max_elapsed_time / 1000000 ) As MaxElapsedSeconds,
             Db_name(fn.dbid) AS dbname,
             Object_schema_name(fn.objectid, fn.dbid) +'.' + Object_name(fn.objectid, fn.dbid) AS QualifiedObjectName,
             Object_schema_name(fn.objectid, fn.dbid) AS schemaname,
             Object_name(fn.objectid, fn.dbid) AS objectname,
             fn.*,
             st.*
      FROM   sys.dm_exec_procedure_stats st
             CROSS APPLY sys.dm_exec_sql_text(st.[sql_handle]) fn
      WHERE  st.last_elapsed_time >= ( 1000000  * 5 ) --15 seconds default
--    AND (Db_name(fn.dbid)  = @dbname OR @dbname IS NULL)
      ORDER BY st.execution_count DESC
      )

      SELECT  * FROM MyCTE ORDER BY AverageSeconds desc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

How to monitor a stored procedure to capture execution times

Cookies on SQLServerCentral