sqlrx logo
SQLRx Tip of the Month
 

March 2013
______________________________________________________________________________
SQL 2008    SQL Server Administration (SQL 2008 - 2012)
 

Use the new dynamic management view sys.dm_exec_procedure_stats to view performance statistics about cached stored procedures. This dmv will only show information on the stored procedures that are still in the cache. If a stored procedure is aged out or removed from the cache, then performance info will not be in the view.

 
For more information check out Books Online or http://msdn.microsoft.com/en-us/library/cc280701(v=sql.110).aspx

 

  

SELECT TOP 10 d.object_id, d.database_id,

OBJECT_NAME(object_id, database_id) 'proc name',

d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],

d.last_elapsed_time, d.execution_count

FROM sys.dm_exec_procedure_stats AS d

ORDER BY [total_worker_time] DESC;

 

TOM Mar2013 

  ______________________________________________________________________________
As always, don't forget to regularly monitor the 11 VitalSigns  used by SQLRx to debug performance bottlenecks.

If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month, email it to us!  If you missed any of our previous tips, you can view them here.

For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].


Sincerely,

 

Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430  |  Dallas, TX  75204
Phone: 214.526.7680 x 113 | [email protected] 

 

 

Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit

 

25 Years of Credibility & Trust

 

Join our email list          Check out our events          SQLRx Downloads