RSS Feed (xml)
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;
Other Performance Related Queries for Store Procedures
As with anything it would be nice to measure the performance of your stored procedures. Having the use count is interesting but it doesn’t tell how much resources are being used or how long the stored procedure takes to run. So let me show you a couple of performance monitoring TSQL statements for measuring the performance of your stored procedures.
I like to measure the performance of a stored procedure by counting the number of logical I/Os it performs. Using logical I/Os seems to be a good way to measure how efficient a stored procedure is since I/O normally takes the longest of any operation. If you can reduce the number of I/Os you normally improve the performance of your SP. Here is a script that displays the number of logical I/Os required for each stored procedure:
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],
SUM(total_logical_reads) AS [total_logical_reads],
SUM(total_logical_reads) / SUM(usecounts) * 1.0 AS [avg_logical_reads],
dbid,
objectid
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
JOIN
(SELECT SUM(total_logical_reads) AS [total_logical_reads],
plan_handle
FROM sys.dm_exec_query_stats
GROUP BY plan_handle) qs
ON cp.plan_handle = qs.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
ORDER BY SUM(total_logical_reads) / SUM(usecounts) * 1.0 DESC;
Here I took my query that calculated the use count and then joined it to a summarized rollup of the sys.dm_exec_query_stats DMV to get the logical read code for each stored procedure. By looking at the avg_logical_reads column you can get a sense for which stored procedures are less efficient than others.
Another interesting thing to measure is how long a stored procedure takes to run. By how long they run I mean the amount of time in seconds from the start of the query till the query finishes. What some might call the elapsed time, or the amount of time a user might wait while the store procedure executes. Below is a query that calculates the average elapsed time for each stored procedure that SQL Server 2005 tracks:
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],
SUM(total_elapsed_time) AS [total_elapsed_time],
SUM(total_elapsed_time) / SUM(usecounts) * 1.0 AS [avg_elapsed_time],
substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23) total_elapsed_time_ms,
dbid,
objectid
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
JOIN
(SELECT SUM(total_elapsed_time) AS [total_elapsed_time],
plan_handle
FROM sys.dm_exec_query_stats
GROUP BY plan_handle) qs
ON cp.plan_handle = qs.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
ORDER BY SUM(total_elapsed_time) / SUM(usecounts) * 1.0 DESC;
Conclusion
You should be monitoring all the code that is developed for your server. By using the code in this article you can identify those stored procedures that use lots of resources. It is best to perform this monitoring in a development environment while applications are being built. You can use the techniques presented here to provide feedback to your programmers on how efficient their Store Procedure are. By giving your programmers some performance statistics you can help them better understand how well their code is running, and promote performance design practices that produce efficient code.
No comments:
Post a Comment