Delivering a SQL Server Health Check - SQL Server 2012 Internals and Troubleshooting - page 431

qs.total_worker_time AS [TotalWorkerTime],qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload
This query returns the top cached stored procedures ordered by execution count. It can help you
determine which stored procedures are called most often, and how often they are called. Knowing
this is very helpful for baseline purposes. For example, if you know that your most frequently
executed stored procedure is normally called 50 times/second, but you later see it being called
300 times/second, you would want to start investigating. Perhaps there was a change to your
application(s) that is causing them to call that stored procedure much more often. It might be by
design, or it might be a defect in the application, introduced in a recent update. It is also possible
that your applications are suddenly seeing more load, either from legitimate users or from a
denial-of-service attack.
One thing to keep in mind when examining all these queries that are looking at cached
stored procedures is that the stored procedures may have gone into the cache at different
times, which skews the numbers for these queries. You should always look at the cached_time
in the query results (see Listing 15-39) to see how long the stored procedure has been in
the cache.
LISTING 15-39: Top cached stored procedures by average elapsed time
-- Top Cached SPs By Avg Elapsed Time (SQL Server 2012)
SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.total_elapsed_time, qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time,
GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);
-- This helps you find long-running cached stored procedures that
-- may be easy to optimize with standard query tuning techniques
This query captures the top cached stored procedures by average elapsed time. This is useful because
it can highlight long-running (on average) stored procedures that might be very good tuning can-
didates. If you can make some changes to a stored procedure that previously took 90 seconds that