Getting performance statistics for cached stored procedures

Getting performance statistics for cached stored procedures

It is always extremely important to maintain your SQL server to the utmost efficiency.    DBAs review the database structure and whether certain pieces such as stored procedures, triggers, etc. can be tuned even further.  One of the areas that will be discussed are the stored procedure which can be used during any time as well as day.

 Dynamic Management Views and Function (DMVs and DMFs) to identify resources used by stored procedures

DMVs and DMFs were introduced in SQL Server 2005 and are able to obtain figures only for those stored procedures that have execution plans in the procedure cache.  The SQL Server engine starts gathering information about the performance of a stored procedure the first time that  the plan is placed in the procedure cache.  As long as the stored procedure’s execution plan stays in the cache, SQL Server keeps updating the performance figures every time the stored procedure is executed. If the execution plan for a stored procedure is dropped from the procedure cache then the metrics for the stored procedure are also removed.  Therefore you will only be able to obtain metrics for those stored procedures that have cached plans, and those metrics will contain a summarization of the amount of resources used since the stored procedure was compiled, and its’ execution plan was placed in the procedure cache.  One thing to keep in mind is whether you are using the SP_RECOMPILE procedure for any or all stored procedures, that will remove the statistical information.

sys.dm_exec_procedure_stats

This DMV was introduced with SQL Server 2008.   By using this DMV, you can returns metrics for stored procedures, but it does not directly identify the stored procedure by name.  Instead, the DMV only identifies the object_id, and a database_id for each stored procedure:  Therefore to identify the actual stored procedure name, that the performance figures belong to, you should either join the output of this DMV with one of the system views within the appropriate database, or use a few metadata functions.

SELECT TOP 10
       fld.object_id,
      fld.database_id,
      OBJECT_NAME(object_id, database_id) ‘SP name’,  
     fld.cached_time,
     fld.last_execution_time, 
     fld.total_elapsed_time, 
     fld.total_elapsed_time/fld.execution_count AS [avg_elapsed_time], 
     fld.last_elapsed_time, fld.execution_count 
  FROM sys.dm_exec_procedure_stats AS fld
  where database_id <> 32767
  ORDER BY [total_worker_time] DESC;

This SQL example demonstrates the information pertaining to the stored procedures.  This allows you to review what if possible is performing poorly or long.

• cached_time: when the procedure was cached or last started.
• last_execution_time: when the procedure was last executed.
• execution_count: this tells you the amount of times the procedure was executed

I would suggest to not to display the resource database which always has a ID of 32767 and you can view that in the where clause.

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. (from BOL).  In SQL Server the maximum databases per instances that can be created are 32,767. This last number has been reserved by Resource Database itself.

sys.dm_exec_query_stats

This DMV can be used to obtain statement level metrics.  These metrics are only available if the statement comes from a cached plan.  By using this DMV and summarizing the statement metrics up to the plan level, you are able to identify metrics for stored procedures.

SELECT TOP 10 
       DB_NAME(dbid) AS DatabaseName,
      OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME],  
      OBJECT_NAME(objectid,dbid)AS [Process_Name],
      MAX(qs.creation_time) AS 'cache_time',
      MAX(last_execution_time) AS 'last_execution_time',
      MAX(usecounts) AS [execution_count],
      SUM(total_worker_time) / SUM(usecounts) AS AVG_CPU,
      SUM(total_elapsed_time) / SUM(usecounts) AS AVG_ELAPSED,
      SUM(total_logical_reads) / SUM(usecounts) AS AVG_LOGICAL_READS,
      SUM(total_logical_writes) / SUM(usecounts) AS AVG_LOGICAL_WRITES,
      SUM(total_physical_reads) / SUM(usecounts)AS AVG_PHYSICAL_READS      
  FROM sys.dm_exec_query_stats qs  
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle 
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) 
  WHERE objtype = 'Proc' AND text NOT LIKE '%CREATE FUNC%' and dbid <> 32767
  GROUP BY execution_count, cp.plan_handle, DBID, objectid 
  ORDER BY execution_count DESC

Final Wrap up

As demonstrated these queries will provide you with a simple method to view the stored procedures performances.  You can tune the stored procedures that need it by watching the various reporting fields displayed.    You have the ability of storing this information or exporting it to Excel for maintaining a log over a time period.