In my last article (http://www.sql-server-performance.com/articles/per/tsql_statement_performance_p1.aspx) I showed how to use the sys.dm_exec_query_stats dynamic management view (DMV). This article showed how to use the information from this DMV to determine what TSQL statements where the worst performing statements on an instance of SQL Server 2005. In this article I will continue on with my performance monitoring discussion and will show how to use the sys.dm_exec_cached_plans DMW to monitor the performance of your stored procedures.
Understanding DMV statistics
Before I start discussing how to gather stored procedure performance statistics I want to review some basic information about DMV statistics. If you have already read my prior articles on DMVs then you should already understand how SQL Server collects and manages DMV information so you can skip to the next section, if not read on.
With SQL Server 2005, Microsoft introduced a number of system views called DMVs. These views allow you to probe SQL Server to determine the health, diagnosis problems, or review operational information of a SQL Server instance.
DMV statistics are collected while SQL Server is running, and are reset every time SQL Server starts. Statistics for certain DMV can also be reset when you drop and re-create their components. This is true for objects like stored procedures, and tables. For other DMV’s information can be reset by running DBCC commands.
When you use a DMV you need to keep in mind how long SQL Server has been collecting DMV information to determine how useful the data returned from the DMV might be. If SQL Server has only been up for a short period of time then you might not want to use some DMV statistics because they don’t represent a relative sampling of the true work load that an instance might encounter. Also SQL Server can only maintain so much information, so some information can be lost while SQL Server performs management activities. So if SQL Server has been up for a long period of time there is a potential that some statistical information has been overwritten.
Therefore any time you use a DMV keep these points in mind while you review the information returned via SQL Server 2005 DMVs. Only make database or application code changes when you are confident the information obtained from the DMVs is accurate and complete.
Use Count
Would you like to know which stored procedure is executed the most? You can get this information by joining the sys.dm_exec_cached_plans DMV information with the date returned from the sys.dm_exec_sql_text table-valued dynamic management function (DMF). Although it is not as straight forward as you might think. So let me walk you through some different queries to help you understand how to use this DMF and DMV to get the use count for stored procedures.
In order to understand how sys.dm_exec_cached_plans and sys.dm_exec_sql_text work run the following code on your server, and review the output:
SELECT usecounts, text, dbid, objectid FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = ‘Proc’;
Here I used the CROSS APPLY operator to join the output of the sys.dm_exec_sql_text DMF with cached plan information in the sys.dm_exec_cached_plans DMV. The CROSS_APPLY operator will join this information anytime the DMF returns a value using the plan_handle from the DMV. When you run the above TSQL on your machine and review the output you should notice that there are multiple rows returned with the same value for the text column. Why does this occur? This happens because there are sometime multiple plans in the procedure cache for the same stored procedure. You might also notice that function are displayed, as well as extended stored procedures and CLR compiled stored procedures. For some reason Microsoft has decided to classify functions, extended stored procedures, and CLR stored procedures as a “Proc” objtype. It makes sense to me that extended stored procedures and CLR stored procedures should be classified as a “Proc” objtype, but it doesn’t make sense that a function should also be a “Proc” objtype. So to get an accurate use count for only the user defined stored procedures I modified the code above to look like this:
SELECT DB_NAME(dbid) AS [DB_NAME],
OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME],
OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME],
SUM(usecounts) AS [Use_Count],
dbid,
objectid
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = ‘Proc’
AND UPPER(
— remove white space first
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(text,’ ‘,’ ‘),
‘ ‘,’ ‘),
‘ ‘,’ ‘),
‘ ‘, ‘ ‘),
‘ ‘,’ ‘),
‘ ‘,’ ‘),
‘ ‘,’ ‘)
)
LIKE ‘%CREATE PROC%’
GROUP BY dbid, objectid;
To help you better understand this code let me explain what I am doing. In order to remove the functions, extended stored procedures and the CLR store procedures I am searching the “text” column for the phrase “CREATE PROC”. Since you might code your create procedure statement with multiple spaces between the “CREATE” and “PROC” keywords I used a series of REPLACE statements to remove all the extra spaces. Also because you might code your CREATE PROCEDURE statement with mixed case, so I use the UPPER function to convert the text to UPPER case.
When you run the above code you might notice that a few of the DB_NAME columns are null. This happens when the dbid value is 32767. This database id number is associated with a system database that is commonly called the Resource database. This Resource database is not that well known, but it is an actual database that does exist on your system but you can’t see it in SQL Server Management Studio. It can be found by browsing your DATA directory and looking for mdf and ldf files that start with mssqlsystemreource. The Resource database contains all the compiled system stored procedures and functions. So to completely identify all the possible database names I’ve modified the above code to stuff in the word Resource in the database name column when the dbid is equal to 32767. So here is my code to identify the use counts by database for all user defined stored procedures:
SELECT CASE when dbid = 32767
then ‘Resource’
else DB_NAME(dbid) end [DB_NAME],
OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME],
OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME],
SUM(usecounts) AS [Use_Count],
dbid,
objectid
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = ‘Proc’
AND UPPER(
— remove white space first
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(text,’ ‘,’ ‘),
‘ ‘,’ ‘),
‘ ‘,’ ‘),
‘ ‘, ‘ ‘),
‘ ‘,’ ‘),
‘ ‘,’ ‘),
‘ ‘,’ ‘)
)
LIKE ‘%CREATE PROC%’
GROUP BY dbid, objectid;