Databases Reference
In-Depth Information
result in it returning in 5 seconds, you will look like a magician to your boss. Conversely, if a long-
running query is executed only once a day, optimizing it will not really help your overall workload
as much as you might expect. This query is somewhat less sensitive to the
cached_time
issue, as you
are sorting by average elapsed time.
Next, you are going to look at the most expensive stored procedures from an overall CPU perspec-
tive, with the query shown in Listing 15-40.
LISTING 15-40:
Top cached stored procedures by total worker time
-- Top Cached SPs By Total Worker time (SQL Server 2012).
-- Worker time relates to CPU cost
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 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 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.total_worker_time DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached
-- stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure
This query returns the top cached stored procedures ordered by total worker time. Worker time
relates to the CPU cost of a query or stored procedure. Especially if you see signs of CPU pressure
from looking at your top cumulative wait types or your CPU utilization history, you should look
very closely at the results of this query to i gure out which stored procedures were the most expen-
sive from a CPU perspective. The reason you sort by total worker time is because this takes into
account the total CPU cost of the stored procedure since it has been cached. You might have a stored
procedure that is not particularly expensive for a single execution that is called very frequently,
resulting in a very high overall CPU cost.
With this query, you do need to pay particular attention to the
cached_time
column for each stored
procedure. The length of time that a stored procedure has been in the cache can have a huge effect
on its cumulative cost i gures. That's why I like to periodically clear out the procedure cache so that
the stored procedures that are part of my regular workload will be recompiled and go back into the
cache at nearly the same time. This makes it much easier to accurately interpret the results of all
these stored procedure cost queries. It also has the benei t of clearing out single-use, ad hoc query
plans that may be wasting a lot of space in your cache.
Next, the query shown in Listing 15-41 will provide some information about your cached stored
procedures from a logical reads perspective.
Search WWH ::
Custom Search