Just in case you’ve just joined me, I decided on April 1 to post a different DMV query every day for the month of April. People seem to be enjoying the series, so that gives me added motivation to keep going. Fellow SQL MVP Paul Randal (blog | twitter) is doing a similar series where he exposes a different SQL myth every day through the month of April.

The DMV for Day 10 is sys.dm_exec_procedure_stats, which was added in SQL Server 2008, is described by BOL as:

Returns aggregate performance statistics for cached stored procedures. The view contains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

You can discover a wealth of information from sys.dm_exec_procedure_stats, but I will just show one query today. This particular DMV only works with SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Top Cached SPs By Total Physical Reads (SQL 2008 only) 
-- Physical reads relate to disk I/O pressure
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time 
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads DESC;

This query will help detect the most expensive, cached stored procedures from a physical reads perspective, which relates to read, disk I/O pressure.  There are a couple of caveats here though. First, you need to look at the cached_time column to make sure that it is similar for the top offenders. One way to ensure that the cached_time is nearly the same for most of your stored procedures is to periodically run DBCC FREEPROCCACHE on your instance with a SQL Agent job. Doing that flushes every plan out of the cache for the entire instance (which is actually not as scary as it sounds). You will probably see a very brief spike of CPU activity as the plans are recompiled, lasting a few seconds. Most SQL instances have a decent amount of CPU headroom, since CPUs have gotten so much more powerful over the last several years. The second caveat is that only cached stored procedures will show up in the query. If you are using WITH RECOMPILE or OPTION(RECOMPILE), (which is usually not a good idea anyway) those plans won’t be cached.

If you see lots of stored procedures with high total physical reads or high average physical reads, it could mean that you are under severe memory pressure, and SQL Server is having to go to the disk I/O subsystem for data too often. It could also mean that you have lots of missing indexes or that you have “bad” queries (with no WHERE clauses for example) that are causing lots of clustered index or table scans on large tables.

Now, you have another tool available to help find the stored procedures that are causing the most read I/O pressure on your instance. Btw, this query is filtered by the current database. You can remove the WHERE clause to look at the entire instance.