THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

July, the 31 Days of SQL Server DMO’s – Day 7 (sys.dm_exec_procedure_stats)

 

The sys.dm_exec_procedure_stats is one of my favorite DMV’s that I will use when I am looking into the performance of a stored procedure. This DMV returns information about the performance of a cached stored procedures. The information within this DMV is aggregated since the last time that the DMV statistics were reset. To better illustrate the benefits of this DMV, let run a quick query against my AdventureWorks2012 database.

SELECT * FROM sys.dm_exec_procedure_stats
WHERE database_id = db_id('AdventureWorks2012')

image

image

image

By looking at the results returned by this DMV, we can gather a wealth of knowledge about how a stored procedure may be performing. We can easily obtain the name of the stored procedure by using the OBJECT_NAME function. This query also returns the sql_handle and plan_handle and if you have been keeping up with this month long series on Dynamic Management Objects, then you know how we can use these two columns to obtain the actual sql text and the execution plan for these stored procedures. We are also able to see when the stored procedure was added to the cache and the last time it executed as well as how many times it has executed.

From a performance perspective, we are able to view statistics about the min, max, last, and total worker time a stored procedure used, which represents the amount of CPU time in microseconds that this stored procedure consumed.

We are able to see min, max, last, and total statistics for logical reads/writes and physical reads/writes. The duration that the stored procedure took to execute is also presented in the result set of this DMV represented as the min, max, last, and total elapsed times.

When looking for stored procedures that may need optimizations, this is an excellent place to start. Especially when you are needing a historical view and not necessarily a look at currently executing stored procedures. This is definitely one of my favorites!!

For more information of this Dynamic Management View, please see the below Books Online link:

http://msdn.microsoft.com/en-us/library/cc280701.aspx

Published Sunday, July 07, 2013 11:09 AM by Tamarick Hill

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Daniel Adeniji (http://danieladeniji.wordpress.com) said:

Nice and well presented blog.

Stumbled upon Michael K. Campbell piece on finding un-used Views (http://sqlmag.com/blog/finding-potentially-unused-views-your-databases).

And, that lead me to Glen Berry's piece (http://sqlserverperformance.wordpress.com/2009/07/08/looking-for-unused-stored-procedures-in-sql-server-2008/#comment-7825).

And, that lead me to Goggling for sys.dm_exec_procedure_stats; which  in turn lead me to yours.

November 9, 2013 10:30 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement