Friday, June 29, 2012

Stored procedure Usage


How you might want to monitor execution of stored procedures. I am using Dynamic Managemant views (DMV) to accomplish the monitoring of SP executions.

This will be my script to identify the most frequently run SP’s of an instance of SQL Server, as well as those SPs that use the most CPU, I/O or run the longest.

SELECT @@SERVERNAME                                       AS [Instance_Name]                     -- col. 1
            , DB_NAME(f1.dbid)                                         AS [DB_Name]                             -- col. 2
            , OBJECT_SCHEMA_NAME(f1.objectid,dbid) AS [Schema_Name]                      -- col. 3
            , OBJECT_NAME(f1.objectid,dbid)                   AS [Stored_Procedure]                  -- col. 4
            , MAX(v1.usecounts)                                          AS [Execution_count]                     -- col 5
            , SUM(v2.total_worker_time)                              AS [Total_cpu_Time_ms]               -- col. 6
            , SUM(v2.total_worker_time) / 1000                  AS [Total_cpu_Time_sec]               -- col. 7
            , SUM(v2.total_worker_time) / 1000 / 60
                                                                                       AS [Total_cpu_Time_min]              -- col. 8
            , SUM(v2.total_worker_time) / (MAX(v1.usecounts) * 1.0)
                                                                                       AS [AVG_cpu_Time]                     -- col. 9
           , SUM(v2.total_physical_reads + v2.total_logical_reads + v2.total_logical_writes)
                                                                                       AS [Total_IO]                                -- col. 10
          , SUM(v2.total_physical_reads + v2.total_logical_reads + v2.total_logical_writes) /
           (MAX(v1.usecounts))                                           AS [AVG_Total_IO]                     -- col. 11
          , SUM(v2.total_physical_reads)                             AS [Total_Physical_Reads]            -- col. 12
          , SUM(v2.total_physical_reads) / (MAX(v1.usecounts) * 1.0)
                                                                                       AS [AVG_Physical_Read]              -- col. 13
          , SUM(v2.total_logical_reads)                                AS [Total_Logical_Reads]              -- col. 14
          , SUM(v2.total_logical_reads) / (MAX(v1.usecounts) * 1.0)
                                                                                       AS [AVG_Logical_Read]               -- col. 15
          , SUM(v2.total_logical_writes)                               AS [Total_Logical_Writes]              -- col. 16
          , SUM(v2.total_logical_writes) / (MAX(v1.usecounts) * 1.0)
                                                                                       AS [AVG_logical_writes]                -- col. 17
          , SUM(v2.total_elapsed_time)                               AS [Total_Elapsed_Time_ms]         -- col. 18
          , SUM(v2.total_elapsed_time) / 1000                    AS [Total_Elapsed_Time_sec]        -- col. 19
          , SUM(v2.total_elapsed_time) / 1000 / 60             AS [Total_Elapsed_Time_min]        -- col 20
          , SUM(v2.total_elapsed_time) / MAX(v1.usecounts)
                                                                                       AS [AVG_Elapsed_Time_ms]         -- col. 21
          , SUM(v2.total_elapsed_time) / MAX(v1.usecounts) / 1000
                                                                                       AS [AVG_Elapsed_Time_s]            -- col. 22
          , SUM(v2.total_elapsed_time) / MAX(v1.usecounts) / 1000 /60
                                                                                       AS [AVG_Elapsed_Time_min]         -- col. 23
 FROM sys.dm_exec_cached_plans v1
INNER JOIN sys.dm_exec_query_stats v2 ON v1.plan_handle = v2.plan_handle
CROSS APPLY sys.dm_exec_sql_text(v1.plan_handle) f1
WHERE DB_NAME(f1.dbid) IS NOT NULL
     AND v1.objtype = 'proc'
     AND DB_NAME(f1.dbid) NOT IN ('master','model','msdb')
GROUP BY v1.plan_handle
                 , DB_NAME(f1.dbid)
                , OBJECT_SCHEMA_NAME(objectid,f1.dbid)
                , OBJECT_NAME(objectid,f1.dbid)
  ORDER BY 21 desc


By changing the ORDER BY clause I can order them in different ways, depending on whar you want to see.

On column 5 means I want to know which stored procedure is used the most.

On column 6 means I want to know which stored procedure used the most total cpu time.

On column 21 means I want to know which stored procedure is using the most average elapsed time.


No comments:

Post a Comment