SQL Server – v2005 – Single Use Plans – Plan Guide

Background

In a very recent post we spoke to how we determined that we have a lot of Single Use Plans in our database.

This can be problematically as Plan Storage alone was using up 3 GB of our storage.

And, of course than can put a squeeze in our ability to sufficiently cache data.

Diagnostic

It has been a couple of days since we applied a fix.

And, that fix is to set parameterization to Forced on our MSDB database.

Let us go back and run our diagnostics queries and investigate to what extent the patch helped.

Plans Aggregated by Cache Type

Code


/*
	Kimbery L. Tripp
	You are here: Home >> Plan cache and optimizing for adhoc workloads
	Plan cache and optimizing for adhoc workloads
	http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
*/
SELECT 
		  [CacheType]
			= objtype

        , [Total Plans]
			= COUNT_BIG(*)

        , [Total MBs]
			= SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024

        , [AVG USE COUNT]
			= AVG(usecounts)

        , [Total MBs - USE COUNT 1]
			= SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 

        , [Total Plans - USE COUNT 1]
			= SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE COUNT 1] DESC

GO 

Output

CachedPlanStatistics-20160513-0948AM

 

Plans Aggregated by Cache Type

Code


select
 
         [Database]
            = case
                    when st.dbid = 32767 then 'Resource DB'
                    else DB_NAME(st.dbid)
              end
 
        , [DatabaseID]
            = st.[dbid]
 
        , [Number of Plans]
            = COUNT(*)
 
        , [SizeInMB]
            = cast
                (
                    SUM( cast ( cp.size_in_bytes as bigint) ) 
                    / ( 1024 * 1024)
                    as bigint
                )
 
from    sys.dm_exec_cached_plans cp
 
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
 
WHERE cp.usecounts < 3
 
GROUP BY
           st.[dbid]
         , DB_NAME(st.[dbid])
 
order by
		[SizeInMB] desc 

Output

SingleUsePlansByDatabase-20160513-1005AM


Explanation

  1. We have close to 100 thousand plans taking up 3 GB of RAM

 

Compare Single Use Plans Vs All Plans

Code


select
 
         [Database]
            = case
                    when st.dbid = 32767 then 'Resource DB'
                    else DB_NAME(st.dbid)
              end
 
        , [DatabaseID]
            = st.[dbid]
 
        --, cp.objtype
 
        , [Number of Plans - Single]
            = sum
                (
                    case
                        when (cp.usecounts < 3) then 1
                        else 0
                    end
                )
 
        , [Number of Plans]
            = COUNT(*)
 
        , [%Number of Single Plans]
            = cast
                (
                    sum
                    (
                        case
                            when (cp.usecounts < 3) then 1
                            else 0
                        end
                    ) * 100.00
                    / COUNT(*)
                    as decimal(10,2)
                )
 
        , [SizeInMB - Single]
            = 
                sum
                (
                    case
                        when (cp.usecounts < 3) 
                            then cast(cp.size_in_bytes as bigint)
                        else 0
                    end                        
                )
                / ( 1024 * 1024)
 
        , [SizeInMB]
            =
                sum
                (
                    cast(cp.size_in_bytes as bigint)
                )
                / ( 1024 * 1024)
 
        , [%Number Single Plans Storage]
            =
                cast
                    (
                        sum
                        (
                            case
                                when (cp.usecounts < 3) 
                                    then cast(cp.size_in_bytes as bigint)
                                else 0
                            end                        
                        ) * 100.00
                        /
                        sum
                        (
                            cast(cp.size_in_bytes as bigint)
                        )
                    as decimal(10,2)
                )
 
from    sys.dm_exec_cached_plans cp
 
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
 
GROUP BY
      st.[dbid]
       , DB_NAME(st.[dbid])
--  , cp.objtype
 
order by
        [SizeInMB] desc
      , DB_NAME(st.[dbid]) asc

Output

Compare Single Use Plans Vs All Plans - 20160513 - 1018AM


Explanation

  1. We have over 110 thousand plans taking up over 3GB of RAM
  2. The Database ID is NULL, as they are not database centric SQL, but Ad-hoc queries

 

SQL Statements

SQL Profiler

Here are statements that have msdb referenced in their textdata

Statements

Event Class TextData Application Name
SQL:Batch Completed EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters SQLAgent – Alert Engine
RPC:Completed exec msdb.dbo.sysmail_help_profile_sp @profile_name=N’Local Relay Server’ DatabaseMail – SQLAGENT90 -Id<3280>
RPC:Completed exec msdb.dbo.sysmail_help_profileaccount_sp @profile_id=1 DatabaseMail – SQLAGENT90 -Id<3280>
RPC:Completed exec msdb.dbo.sysmail_help_admin_account_sp @account_id=1 DatabaseMail – SQLAGENT90 -Id<3280>
SQL:Batch Completed UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = 1, queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = 0x0F11A965B531294D9040BFED4205424E and session_id = 121 SQLAgent – Job Invocation Engine
SQL:Batch Completed  DECLARE @startExecutionDate DATETIME SET @startExecutionDate = msdb.dbo.agent_datetime(20160513, 102000) UPDATE msdb.dbo.sysjobactivity SET start_execution_date = @startExecutionDate WHERE job_id = 0x2FDE0D6E24B0F74D9BE214711F075C3A and session_id = 121 SQLAgent – Job Manager
SQL:Batch Completed  EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N’sa’ SQL Agent – Job Manager
SQL:Batch Completed  EXECUTE msdb.dbo.sp_help_jobstep @job_id = 0x0F11A965B531294D9040BFED4205424E SQL Agent – Job Manager
SQL:Batch Completed  EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0x7D9DCC5B69FC264EB8A4CEF1EA032A3C, @step_id = 1, @sql_message_id = 20554, @sql_severity = 0, @run_status = 1, @run_date = 20160513, @run_time = 102000, @run_duration = 0, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @session_id = 121, @message = N’. The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. [SQLSTATE 01000] (Message 20554).  The step succeeded.’ SQL Agent – Job Manager
SQL:Batch Completed  BEGIN TRAN UPDATE msdb.dbo.sysjobsteps SET last_run_outcome = 1, last_run_duration = 0, last_run_retries = 0, last_run_date = 20160513, last_run_time = 102000 WHERE (job_id = 0x0F11A965B531294D9040BFED4205424E) AND (step_id = 1) DECLARE @lastExecuteStepDate DATETIME set @lastExecuteStepDate = msdb.dbo.agent_datetime(20160513, 102000) UPDATE sysjobactivity SET last_executed_step_date = @lastExecuteStepDate, last_executed_step_id = 1 WHERE job_id = 0x0F11A965B531294D9040BFED4205424E AND session_id = 121  COMMIT TRAN SQL Agent – Job Manager
SQL:Batch Completed  DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(20160513, 103000) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = 121 AND job_id = 0x0F11A965B531294D9040BFED4205424E SQL Agent – Update Job Activity
SQL:Batch Completed  UPDATE msdb.dbo.sysjobservers SET last_run_date = 20160513, last_run_time = 102000, last_run_outcome = 1, last_outcome_message = N’The job succeeded.  The Job was invoked by Schedule 136 (Replication agent schedule.).  The last step to run was step 1 (Run agent.).’, last_run_duration = 0 WHERE (job_id = 0x7D9DCC5B69FC264EB8A4CEF1EA032A3C) AND (server_id = 0) SQL Agent – Job Manager
 SQL:Batch Completed EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0x7D9DCC5B69FC264EB8A4CEF1EA032A3C, @step_id = 0, @sql_message_id = 0, @sql_severity = 0, @run_status = 1, @run_date = 20160513, @run_time = 102000, @run_duration = 0, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @session_id = 121, @message = N’The job succeeded.  The Job was invoked by Schedule 136 (Replication agent schedule.).  The last step to run was step 1 (Run agent.).’ SQL Agent – Job Manager

 

 

Implications

  1. Event Class
    • RPC Completed
      • Direct Stored procedure calls
    • SQL Batch completed
      • Some are SP calls
        • They will have “execute or exec” prefixes
          • Sample
            • EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
            • EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N’sa’
            • EXECUTE msdb.dbo.sp_help_jobstep @job_id = 0x0F11A965B531294D9040BFED4205424E
      • Others will be single-statement SQL Statements
        •  UPDATE msdb.dbo.sysjobservers SET last_run_date = 20160513, last_run_time = 102000, last_run_outcome = 1, last_outcome_message = N’The job succeeded.  The Job was invoked by Schedule 136 (Replication agent schedule.).  The last step to run was step 1 (Run agent.).’, last_run_duration = 0 WHERE (job_id = 0x7D9DCC5B69FC264EB8A4CEF1EA032A3C) AND (server_id = 0)
      • And, yet others will be multi-line SQL Statements
        • Sample
          • DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(20160513, 103000) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = 121 AND job_id = 0x0F11A965B531294D9040BFED4205424E

 

Remediation

Guide Plans

Forward

The “Event Class”, in our case, the manner in which the SQL Statements are invoked matter when considering Guide Plans

  1. Guide plans will likely not help with explicit nor implicit Stored Procedure calls
  2. They will likely help with Single line “SQL Statements”
  3. And, we will run into an obstacle when we try to apply them against “Multi Statement” SQL

 

Use Case – Single Line SQL Statement

Let us create plan guides and see if the engine will use them.

Create Plan Guide

use [msdb]
go

set nocount on;
set XACT_ABORT on;

declare @sql nvarchar(400)
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @planGuideName nvarchar(128)
DECLARE @commit bit

set @commit = 0
--set @commit = 1

set @sql = 'UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = 1, queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = 0x0F11A965B531294D9040BFED4205424E and session_id = 121'
set @planGuideName = 'msdb.dbo.sysjobactivity set data for specific Job & Session ID'
set @planGuideName = 'msdb dbo sysjobactivity set data for specific Job & Session ID'

if not exists 
(
	SELECT *
	FROM   sys.plan_guides
	WHERE  [name] = @planGuideName
)
begin

	begin tran


		EXEC sp_get_query_template 
				  @sql
				, @stmt OUTPUT
				, @params OUTPUT
		;

		select 
				  [@stmt] = @stmt
				, [@params] = @params


		if (
				( @stmt is not null )
			)
		begin

			print 'Invoking sp_create_plan_guide ... '

			EXEC sp_create_plan_guide 
					  @planGuideName
					, @stmt
					, N'TEMPLATE'
					, NULL
					, @params
					, N'OPTION(PARAMETERIZATION FORCED)'
					;

			print 'Invoked sp_create_plan_guide'

		end


	if (@commit = 0)
	begin

		print 'Rolling Back...'

		rollback tran

		print 'Rolled back'

	end
	else
	begin

		print 'Committing Tran'

		commit tran;

		print 'Commit Tran'

	end

end
go


 

Drop Plan Guide


use [msdb]
go


set nocount on;
set XACT_ABORT on;

declare @sql nvarchar(400)
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @planGuideName nvarchar(128)
DECLARE @commit bit

declare @planGuideNameLength int

set @planGuideName = 'msdb.dbo.sysjobactivity set data for specific Job & Session ID'
set @planGuideName = 'msdb dbo sysjobactivity set data for specific Job & Session ID'

set @commit = 0
--set @commit = 1

begin tran


	SELECT 
			  @planGuideName = [name]
			, @planGuideNameLength = len([name])
	FROM   sys.plan_guides
	WHERE  [name] = @planGuideName

	select 
			  [@planGuideName] = @planGuideName
			, [@planGuideNameLength] = @planGuideNameLength 

	if exists 
	(
		SELECT [name]
		FROM   sys.plan_guides
		WHERE  [name] = @planGuideName
	)
	begin

		print 'Invoking sp_control_plan_guide with drop '
				+ '( ' + @planGuideName + ')'
				+ ' ... '

		EXEC sp_control_plan_guide 
					  N'DROP'
					, @planGuideName
				  

		print 'Invoked sp_control_plan_guide with drop '
				+ '( ' + @planGuideName + ')'


	end


if (@commit = 0)
begin

	print 'Rolling Back...'

	rollback tran

	print 'Rolled back'

end
else
begin

	print 'Committing Tran'

	commit tran;

	print 'Commit Tran'

end
go


 

 

Validate Plan Guide Usage

Once you have the plan guide in place, you will like want to find out if it is been used.

Here is what SQL god, Jonathan Kehayias, placed in the Public Domain for us stupid people

Code

/*
	The Rambling DBA: Jonathan Kehayias
	The random ramblings and rantings of frazzled SQL Server DBA
	How many times has that Plan Guide been used?
	http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/10/26/how-many-times-has-that-plan-guide-been-used.aspx
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
, ctePlanGuide
(
	  [dbName]
	, PlanGuideName
	, refcounts
	, usecounts 
)
as
(
		SELECT  

			  dbName
				= query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@TemplatePlanGuideDB)[1]', 'varchar(128)') 

			, PlanGuideName
				= query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@TemplatePlanGuideName)[1]', 'varchar(128)') 

			, refcounts
		 
			, usecounts 

	   FROM sys.dm_exec_cached_plans 

	   CROSS APPLY sys.dm_exec_query_plan(plan_handle) 

	   WHERE query_plan.exist('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@TemplatePlanGuideName])[1]')=1 
)

, ctePlanGuideAggregated
(
	  dbName
	, PlanGuideName
	, [TotalRefCounts]
	, [TotalUseCounts]
)
as
(
	SELECT  

			  dbName
			, PlanGuideName
			, [TotalRefCounts]
				= SUM(refcounts)
			, [TotalUseCounts]
				= SUM(usecounts)

	FROM ctePlanGuide

	GROUP BY 
			  dbName
			, PlanGuideName
) 

select 
		  dbName
		, PlanGuideName
		, [TotalRefCounts]
		, [TotalUseCounts]

from   ctePlanGuideAggregated
Output

planGuideUsageStats

 

Microsoft Connect

  1. Title : EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters hogs CPU although no alerts enabled – by SQLKnitter
    • Opened By: SQLKnitter
    • ID: 536354
    • Date Opened : 2/25/2010 3:55:33 AM
    • Status : Closed (as Fixed )

 

Summary

I have noted in the Reference section some postings about Forced Parameterization per SQL Parameter Sniffing.

Again, this is a long road.

At best, MSDB queries that are single statement queries are relatively small compared to the whole.

A lot of what we saw are “execute SP…“; which conceals the fact that they are SP/RPC calls.

And, then we noticed the multi-line queries such as “DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(20160513, 103000) … “.

 

References

Plan Guide Management – Create

  1. Specify Query Parameterization Behavior by Using Plan Guides
    https://msdn.microsoft.com/en-us/library/ms191275.aspx
  2. Using Plan Guides and Plan Freezing
    Turgay Sahtiyan – February 28, 2013
    https://blogs.msdn.microsoft.com/turgays/2013/02/28/using-plan-guides-and-plan-freezing-2/


Plan Guide Management – Delete

  1. Delete a Plan Guide
    https://msdn.microsoft.com/en-us/library/hh510231.aspx

 

Plan Guide Management – Measuring

  1. Database Engine Features and Tasks Database Features Plan Guides
    Use SQL Server Profiler to Create and Test Plan Guides
    https://msdn.microsoft.com/en-us/library/ms188255.aspx

 

Forced Parameterization

  1. Forced Parameterization Can Lead to Poor Performance
    Thomas Stringer – SQL Server Premier Field Engineer
    Twitter: @SQLife
    https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/09/03/forced-parameterization-can-lead-to-poor-performance/
  2. Back to Basics: SQL Parameter Sniffing due to Data Skews
    Lisa Gardner – Premier Field Engineer
    @SQLGardner
    https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/08/27/back-to-basics-sql-parameter-sniffing-due-to-data-skews/

 

One thought on “SQL Server – v2005 – Single Use Plans – Plan Guide

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s