Really Search Cache for Execution Plans

Posted by & filed under , , , , , .

Really Finding Those Driods?!

Today’s stored procedure for the DBADiagnostics database is a re-hash of a post I did a couple months ago.  The reason for re-hashing the procedure is that when I used it last week I found out there were some serious issues with its results.  Some plans were being duplicated and the performance statistics weren’t accurate because of this.  And why not fix the issues and make a DBADiagnostics post out of it.

In the previous version, I was using the execution count from sys.dm_exec_query_stats to determine how often a plan was being executed.  In the DMV though the SQL statements in the may execute in varying counts depending on conditional logic.

Instead of using the execution count from sys.dm_exec_query_stats the procedure now uses usecount from sys.dm_exec_cached_plans; which represents how often the plan was used.  The execution count represented how often the statements in the plan were executed.

The stored procedure accepts the following parameters:

  • @Database: The database to search for plans within.  This value can be NULL.
  • @ObjectName: The name of the procedure to search for plans for.  This value can be NULL.

The procedure can use either or none of the variables.  Obviously with both variables empty the results will be for all cached plans on the server.

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Utility')
EXEC('CREATE SCHEMA [Utility] AUTHORIZATION [dbo]')
GO

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('Utility.CachedPlanSearch'))
DROP PROCEDURE [Utility].[CachedPlanSearch]
GO

/*============================================================
Procedure: [Utility].[CachedPlanSearch]
Author: Jason Strate
Date: June 1, 2009

Synopsis:
Searches cache for all occurances of a plan based on an
procedure and or database name. Results assumes that average
execution time for returned results are less than 24 hours.

Exec Utility.CachedPlanSearch
@DatabaseName = 'msdb'
,@ObjectName = 'sp_sqlagent_get_startup_info'

============================================================
Revision History:
Date: By Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE Utility.CachedPlanSearch
(
@DatabaseName sysname = NULL
,@ObjectName sysname = NULL
)
AS

;WITH cteExecInfo
AS (
SELECT DB_NAME(st.dbid) AS database_name
,OBJECT_NAME(st.objectid, st.dbid) AS object_name
,cp.usecounts -- Use in place of qs.execution_count for whole plan count
,CAST(SUM(qs.total_worker_time)/(cp.usecounts*1.) as decimal(12,2)) AS avg_cpu_time
,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(cp.usecounts*1.) as decimal(12,2)) AS avg_io
,SUM(qs.total_elapsed_time)/(cp.usecounts)/1000 as avg_elapsed_time_ms
,st.text AS sql_text
,qs.plan_handle
FROM sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE (DB_NAME(st.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)
AND (OBJECT_NAME(st.objectid, st.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)
GROUP BY st.dbid, st.objectid, cp.usecounts, st.text, qs.plan_handle
)
SELECT cte.database_name
,cte.object_name
,cte.usecounts
,cte.avg_cpu_time
,cte.avg_io
,CONVERT(varchar, DATEADD(ms, cte.avg_elapsed_time_ms, 0), 114) AS avg_elapsed_time
,qp.query_plan
,cte.sql_text
FROM cteExecInfo cte
OUTER APPLY sys.dm_exec_query_plan(cte.plan_handle) qp
ORDER BY cte.usecounts DESC

I’ve often found this procedure useful when people complain about the execution of a procedure. Instead of pulling out performance tools and running test versions of the procedure this will provide the last plan that was used for the procedure.

  • Pingback: Searching for Plans | Strate SQL

  • Pingback: Re-blog – June 3-June 9 | My Blog

  • sql_handle

    Great stuff Jason! Couple words of caution about usecount – which is still an improvement over “execution count”:
    “Not incremented when parameterized queries find a plan in the cache. Can be incremented multiple times when using showplan.”
    http://msdn.microsoft.com/en-us/library/ms187404.aspx?ppud=4
    Not incrementing for parameterized queries is probably a bigger issue than the increase due to showplan. Regardless, because I only stumble around with xquery and the plan cache without help from folks like you, this post is another for which I am in your debt. :-)