Click here to monitor SSC
  • Av rating:
  • Total votes: 74
  • Total comments: 16
Greg Larsen

Which of Your Stored Procedures are Using the Most Resources?

11 January 2011

Dynamic Management Views and Functions aren't always easy to understand. However, they are the easiest way of finding out which of your stored procedures are using up the most resources. Greg takes the time to explain how and why these DMVs and DMFs get their information. Suddenly, it all gets clearer.

When you start to look at tuning up your stored procedures where do you start?  If your database has only a few stored procedures, it might be fairly simple to figure this out by doing a quick test to determine which stored procedure is using the most resources.  But what if you have hundreds of them? How do you identify those that are using the bulk of the resources? In this article I will show you how to quickly identify the worst performing stored procedures on your server.

Getting Metrics Related to Stored Procedure Executions

How do you set about getting figures for the time taken by stored procedures, or the amount of resources used in their execution? There is more than one way to discover these execution metrics.  I am not going to mention all the different methods: Heck, I probably don't even know all the different ways! Instead, I will focus in on how to use 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 then enhanced with the introduction SQL Server 2008.   I am sorry to say that, if you are running on a SQL Server version prior to 2005, then this article will not help you in identifying your worst performing stored procedures.

I’m going to show you two different DMV methods to get stored procedure execution metrics.  One method works in both SQL Server 2005 and SQL Server 2008, while the other method is only available in SQL Server 2008.  Let me explain both of these methods.

Before I show you these two methods, first let me explain a little about how SQL Server collects the DMV metrics for stored procedure executions.    These two different DMVs are able to obtain these 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.

sys.dm_exec_procedure_stats

The first DMV that I’d like to discuss is 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.  Here is an example of  how you might do that:

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName

      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] 

      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME] 

      ,*  FROM sys.dm_exec_procedure_stats 

You can see, by looking at this query, that I used the “database_id” and the “object_id” columns from the sys.dm_exec_procedure_stats and then used a couple of metadata functions to identify the name of the database, the schema_name, and the object name for each record returned from this DMV.  Run this query on one of your SQL Server instances and see what comes back.  Let me explore a couple of key columns values returned by this view, and explain what they mean and how you can use them. 

First, let’s look at the “cached_time” column.  This column contains the time in which the stored procedure was compiled and placed in the procedure cache.  You can use this column to identify how long the database engine has been collecting metrics for the identified stored procedure.  This is very useful when comparing the amount of resources that one stored procedure has consumed over another.   By knowing how long SQL Server has been collecting metrics will help you determine how skewed the resource usage metrics might be when comparing two different stored procedures.

The next column you might be interested in looking at is the “execution_count” .  This column identifies the number of times the stored procedure has been executed since it has been placed in the procedure cache.  When you are looking for tuning opportunities you need to consider tuning those store procedures that have been executed many times.  Making small performance gains in a stored procedure that has been executed 100,000 times might be much more beneficial than speeding up a stored procedure two fold if it has only been executed a few timea.   To understand what each column value represents in this DMV I would recommend you refer to Books Online

When I look at stored procedure metrics, I like to identify the average amount of resources used for a single execution of a stored procedure.   Calculating the average resources usage per execution allows you to more easily compare one stored procedure to another. To calculate these average resource usage numbers, I divide the total used number by the “execution_count” column.   Here is some code  where I used this technique to calculate average resources usage numbers for stored procedures that have metrics on my system:

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName

      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] 

      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]

      ,cached_time

      ,last_execution_time

      ,execution_count

      ,total_worker_time / execution_count AS AVG_CPU

      ,total_elapsed_time / execution_count AS AVG_ELAPSED

      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS

      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES

      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS

FROM sys.dm_exec_procedure_stats 

ORDER BY AVG_LOGICAL_READS DESC

Here I have ordered the results by the average logical reads per execution of a stored procedure.  By doing this I can tell which SP has performed the most logical I/O’s per execution.   When you run this query on your system, remember to keep an eye on the “execution_count” column.  This will help you identify those stored procedures that are executed frequently. By modifying the query above and ordering the results based on a different column you can compare each stored procedure based on different average resource usage.   Keep in mind when you are looking out the output from different queries, it might not be as beneficial to tune a procedure that is only executed once, over a procedure that is executed many time. 

If you don’t have SQL Server 2008, that doesn’t mean you are out of luck at finding stored procedure metrics.  In the next section I will show you how to get the similar results as the above query using DMVs and DMFs that are available in both SQL Server 2008 and SQL Server 2005.

sys.dm_exec_query_stats

The sys.dm_exec_query_stats DMV can be used to obtain statement level metrics.  Once again, 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.   The code below can be used in SQL Server 2005 and SQL Server 2008 to produce similar results as I did above using the sys.dm_exec_procedure_stats DMV method:

SELECT CASE WHEN dbid = 32767 then 'Resource' ELSE DB_NAME(dbid)END AS DBName

      ,OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME] 

      ,OBJECT_NAME(objectid,dbid)AS [OBJECT_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%'

       GROUP BY cp.plan_handle,DBID,objectid 

One thing to note about this code, the “last_execution_time”,  “AVG_CPU” and “AVG_ELAPSED” will have a slightly different values then using the sys.dm_exec_procedure_stats method.  It makes sense that these numbers will not be exactly the same since these values are calculated by rolling up the individual statement metrics to calculate the totals at the stored procedure level.  If you look closely at the different values produced by these two methods you will find a lower CPU and elapsed time value is reported using this method, and the last execution date will be a little close to the current time.   

Getting Stored Procedure Metrics

When doing performance analysis of your system it is good to be able to find those stored procedures that are using the most resources.  By know this information you can focus in and tune those stored procedures that provide you the biggest performance improvement for your effort.  Next time you are wondering which stored procedures are using the most resource you will now have a way to identify that regardless of whether you are  monitoring SQL Server 2005 or SQL Server 2008.

Greg Larsen

Author profile:

Greg started working in the computer industry in 1982. In 1985, he got his first DBA job, and since then he has held five different DBA jobs and managed a number of different database management systems. Currently works as a DBA for Department of Health in Washington State managing SQL Server databases, and also does part-time consulting. He has published numerous articles in SQL Server Magazine, and many online web sites dedicated to SQL Server. He also is a SQL Server MVP and holds a number of Microsoft Certification. Greg can be reached at gregalarsen@msn.com.

Search for other articles by Greg Larsen

Rate this article:   Avg rating: from a total of 74 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Can't locate DMV .
Posted by: abeku66 (view profile)
Posted on: Monday, January 17, 2011 at 10:04 AM
Message: I suppose i'm not doing anything wrong but i couldn't locate "sys.dm_exec_query_stats" in the master db. SQL 2008 !

Subject: ignore above.
Posted by: abeku66 (view profile)
Posted on: Monday, January 17, 2011 at 10:05 AM
Message: sorry.. sys.dm_exec_procedure_stats

Subject: Could this be the little critter?
Posted by: Phil Factor (view profile)
Posted on: Tuesday, January 18, 2011 at 6:26 AM
Message:

Subject: Thanks.
Posted by: abeku66 (view profile)
Posted on: Tuesday, January 18, 2011 at 12:24 PM
Message: well , its possible that i can't see it because i have a SQL 2008 that is currently running in 2005 (90) compatibility mode. I will change the mode and try and i'm sure i will find it. Thanks for your help.

Subject: DMV book
Posted by: Ian Stirk (not signed in)
Posted on: Monday, January 24, 2011 at 2:03 AM
Message: Hi,

Nice article.

You can discover a lot more about improving SQL performance via DMVs in this forthcoming book www.manning.com/stirk. It contains more than 100 scripts to identify problems, and offers a wide range of solutions.

Chapter 1 can be downloaded for free and includes scripts for:

A simple monitor
Finding your slowest queries
Find your missing indexes
Identifying what SQL is running now
Quickly find a cached plan

Thanks
Ian

Subject: Re: DMV Book
Posted by: Andrew Clarke (view profile)
Posted on: Monday, January 24, 2011 at 1:21 PM
Message: Well, we're ahead of you Ian, because Louis and Tim's book Performance Tuning with SQL Server Dynamic Views has been available for months...
Performance Tuning with SQL Server Dynamic Views
... and Louis is responsible for Simple-Talk's most popular Database article. We've already run free chapters from this book on Simple-Talk.



Subject: Differences in Results
Posted by: Lee Linares (not signed in)
Posted on: Tuesday, January 25, 2011 at 2:01 PM
Message: I ran both queries against a SQL Server 2008 Standard 64 bit instance and found a huge difference in the reported results. In the 2008 version the execution count was 36 but in the 2005 version the execution count for the same stored procedure was 180. I ran the queries one right after the other. I tried changing a few things and what I noticed was that if I included this line in the SELECT statement of the 2005 version:
SUM(qs.execution_count) as EC
then I would get 144 for that value. If I subtract the 144 from the 180 I get 36. I have repeated this test several times with the same results. Any ideas on why such a huge difference in the execution counts using the SQL 2005/2008 version and the SQl 2008 only version?

Lee

Subject: Reply to Differences in Results
Posted by: Greg Larsen (view profile)
Posted on: Wednesday, January 26, 2011 at 1:43 PM
Message: Oops. Sorry to say this but you found a bug in the code. As it turns out since I did a CROSS APPLY between sys.dm_exec_cached_plans and sys.dm_exec_sql_text and this was getting multiple execute count numbers. I should not have summed, instead I should have gotten the max. Thank you for pointing this out. Below is the code to fixed the problem you identified:

SELECT CASE WHEN dbid = 32767 THEN 'Resource' ELSE DB_NAME(dbid)END AS DBName
      
,OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME]  
      
,OBJECT_NAME(objectid,dbid)AS [OBJECT_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%'
      
GROUP BY cp.plan_handle,DBID,objectid



Subject: Re:Reply to differences in results
Posted by: Andrew Clarke (view profile)
Posted on: Thursday, January 27, 2011 at 2:45 AM
Message: The source of the article has now been updated.

Subject: To be clear
Posted by: Anonymous (not signed in)
Posted on: Friday, January 28, 2011 at 6:27 AM
Message: What does really mean "these metrics are only available if the statement comes from a cached plan." ?

Subject: Unable to use DMV's because of "VIEW SERVER STATE" permissions
Posted by: JMoreno (view profile)
Posted on: Monday, January 31, 2011 at 12:48 PM
Message: I'm unable to use the DMV's because I don't have VIEW SERVER STATE permission. I understand that the DMV's were introduced in part so that people could use them without being sysadmin's. But I haven't found a clear explanation as to who SHOULD have VIEW SERVE STATE permission and who SHOULD NOT. Any suggestions?

Subject: re: To be clear
Posted by: Greg Larsen (view profile)
Posted on: Thursday, February 03, 2011 at 6:14 AM
Message: What I mean is statitics are only available for query plans that are in the procedure cache. When a plan is removed from the procedure cache the statistics are also removed.

Subject: re: Unable to use DMV's because of "VIEW SERVER STATE" permissions.
Posted by: Greg Larsen (view profile)
Posted on: Thursday, February 03, 2011 at 6:18 AM
Message: I suppose there are many opinions abouut who should have "VIEW SERVER STATE" permissions. My point of view is there is no risk in giving people how want to view DMV information "VIEW SERVER STATE" permissions. There isn't any thing people can do with these rights and there is no data that they can return that would compromise you system that I know of. Providing these rights are like providing SELECT right to a table.

Subject: Reduced Resource Numbers due to SUM
Posted by: Greg Larsen (view profile)
Posted on: Sunday, February 06, 2011 at 4:09 PM
Message: I found another bug in my SQL Server 2005 code for identifying the stored procedure statistics. Here is the fixed code:
USE TweedledeeTweedledum
GO
SELECT CASE WHEN dbid = 32767 then 'Resource' ELSE DB_NAME(st.dbid)END AS DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS [SCHEMA_NAME]
,OBJECT_NAME(st.objectid,dbid)AS [OBJECT_NAME]
,MAX(qs.creation_time) AS 'cache_time'
,MAX(qs.last_execution_time) AS 'last_execution_time'
,MAX(cp.usecounts) AS [execution_count]
,SUM(total_worker_time) / MAX(cp.usecounts) AS AVG_CPU
,SUM(total_elapsed_time) / MAX(cp.usecounts) AS AVG_ELAPSED
,SUM(total_logical_reads) / MAX(cp.usecounts) AS AVG_LOGICAL_READS
,SUM(total_logical_writes) / MAX(cp.usecounts) AS AVG_LOGICAL_WRITES
,SUM(total_physical_reads) / MAX(cp.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) st
WHERE objtype = 'Proc'
AND text
NOT LIKE '%CREATE FUNC%'
GROUP BY cp.plan_handle,DBID,objectid;

Subject: Fix the article?
Posted by: Jeff Moden (view profile)
Posted on: Tuesday, February 12, 2013 at 10:01 AM
Message: @Andrew Clarke,

A whole lot of people don't read the discussion that follows. Any chance of applying the fixes that have been discussed to Greg's code in the article?

Subject: Fix the article?
Posted by: Jeff Moden (view profile)
Posted on: Tuesday, February 12, 2013 at 11:21 PM
Message: @Andrew Clarke,

A whole lot of people don't read the discussion that follows. Any chance of applying the fixes that have been discussed to Greg's code in the article?

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.