ÿþ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

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

WITH PlanSearch

AS (

      SELECT qp.dbid

            ,qp.objectid

            ,DB_NAME(qp.dbid) as DatabaseName

            ,OBJECT_NAME(qp.objectid, qp.dbid) as ObjectName

            ,cp.usecounts

            ,cp.plan_handle

      FROM sys.dm_exec_cached_plans cp

            CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT) qp

    WHERE cp.cacheobjtype = 'Compiled Plan'

      AND (DB_NAME(qp.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)

    AND (OBJECT_NAME(qp.objectid, qp.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)

)

,PlansAndStats

AS (

      SELECT ps.DatabaseName

        ,ps.ObjectName 

        ,ps.usecounts -- Use in place of qs.execution_count for whole plan count

        ,CAST(SUM(qs.total_worker_time)/(ps.usecounts*1.) as decimal(12,2)) AS avg_cpu_time 

        ,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(ps.usecounts*1.) as decimal(12,2)) AS avg_io 

        ,SUM(qs.total_elapsed_time)/(ps.usecounts)/1000 as avg_elapsed_time_ms

        ,ps.plan_handle 

    FROM PlanSearch ps

            LEFT OUTER JOIN sys.dm_exec_query_stats qs ON ps.plan_handle = qs.plan_handle

    GROUP BY ps.DatabaseName

        ,ps.ObjectName 

        ,ps.usecounts 

            ,ps.plan_handle

)

SELECT ps.DatabaseName

    ,ps.ObjectName

      ,ps.usecounts

      ,ps.avg_cpu_time

      ,ps.avg_io

      ,ps.avg_elapsed_time_ms

      ,qp.query_plan

      ,ps.plan_handle

FROM PlansAndStats ps

      CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp 

GO

 

Exec Utility.CachedPlanSearch

    @DatabaseName = 'msdb'

    ,@ObjectName = NULL