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
     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.

Tuesday, June 26, 2012

SQL Server 2005 Performance Dashboard in SQL Server 2008

Many of you DBA’s out there would have heard about the SQL Server 2005 Performance Dashboard Reports and used it before. I find the reports highly useful and time saving, it allows me to get a picture of how my SQL Server is performing.

For those who don't know about it, you can download it from here and add it as a custom report to Management Studio (SSMS). It is a free download! Read on below to find out how to install it and use it on your SQL Server. Also there are included custom modified files that allow you to use this for a SQL Server 2008 instance. This is provided "as is" with no guarantees.


The report are mainly intended to quickly identify performance problems and help to resolve them. These reports use the DMV's that came out with SQL 2005. so they are very fast & reliable information when troubleshooting a performance problem. Some of the common problems that can be detected using the Dashboard Reports are:

  1. CPU bottlenecks (which queries are consuming the most CPU and Plan for this query)
  2. I/O bottlenecks (wich queries are performing the most IO and plan for this query)
  3. Index recommondations generated by the query optimizer (missing index recommendations)
  4. Blocking
  5. Latch contention and other Wait Types

  1. You need to have Management Studio installed on the machine where you intend to use the Performance Dashboard Reports (could be either client or server machine).
  2. Your SQL Server 2005 needs to have at a minimum SP2 applied (build 9.0.3042 or greater)

  1. You can install the Performance Dashboard Reports from here.
  2. Once you install the above, go to %\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard and run the setup.sql script against the SQL instance you want to monitor the performance for.