SQL Server – Plan Guide – Creation / Generalizing

Background

Wanted to generalize the code we put together for creating template plan guides.

Btw, here is that post.

Code

DDL

[dbo].[planguide] – DDL




use [DBUtility]
go

set noexec off
go

if object_id('[dbo].[planguide]') is not null
begin

	set noexec on

end

/*

	drop table [dbo].[planguide]

	exec sp_help '[dbo].[planguide]'

	exec sp_helpindex '[dbo].[planguide]'

*/
create table [dbo].[planguide]
(

	  [id]					int not null identity(1,1)
	, [name]				nvarchar(128) not null 	
	, [database]			sysname not null
	, [queryText]			nvarchar(4000) not null

	, [planType]			sysname not null

	, [module_or_batch]		sysname null
							
	, [hint]				sysname not null

	, [addedBy]				sysname null
		constraint [planGuideAddedBy] default SYSTEM_USER

	, [addedOn]				smalldatetime null
		constraint [planGuideAddedOn] default getdate()

	, constraint [PK_PlanGuide]
		primary key
			(
				  [name]
				, [database]
			)
)
go

set noexec off
go


if not exists
	(
		select *
		from   sys.indexes tblSI
		where  object_id = object_id('[dbo].[planguide]')
		and    tblSI.[name] = 'INDX_ID'
	)
	begin

		create index [INDX_ID]
		on   [dbo].[planguide]
		(
			[ID]
		)
		with
		(
			FILLFACTOR=100
		)

	end


go

if not exists
	(
		select *
		from   sys.columns tblSC
		where  object_id = object_id('[dbo].[planguide]')
		and    tblSC.[name] = 'active'
	)
	begin

		alter table [dbo].[planguide]
			add [active] bit 
				constraint [constraintPlanGuideActive] 
					default (0)
	end


go

[dbo].[planguide] – DML



use [DBUtility]
go

set quoted_identifier off;
set nocount on;
go


truncate table [dbo].[planguide];
go

/*
	Database - msdb
*/
insert into [dbo].[planguide]
(
	  [name]
	, [database]
	, [queryText]
	, [planType]	
	, [module_or_batch]
	, [hint]	
	, [active]		
)
select
		  'msdb dbo sysjobactivity set data for specific Job & Session ID' as [name]
		, 'msdb' as [database]
		, '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' as [queryText]
		, N'TEMPLATE' as [type]	
		, null as [module_or_batch]
		, N'OPTION(PARAMETERIZATION FORCED)' as [hint]			
		, 1 as [active]		

union
select
		  'msdb dbo sysjobservers set data for last run on specific Job & Server ID' as [name]
		, 'msdb' as [database]
		, 'UPDATE msdb.dbo.sysjobservers SET last_run_date = 20160513, last_run_time = 192500, last_run_outcome = 1, last_outcome_message = N''The job succeeded.  The Job was invoked by Schedule 4 (Replication agent schedule.).  The last step to run was step 1 (Run agent.).'', last_run_duration = 1 WHERE (job_id = 0xFCE1BD072720F44690D33A807A62D377) AND (server_id = 0)'
			 as [queryText]
		, N'TEMPLATE' as [type]	
		, null as [module_or_batch]
		, N'OPTION(PARAMETERIZATION FORCED)' as [hint]			
		, 1 as [active]		

go

 

 

[dbo].[sp_planGuide_process]


use [master]
go

if object_id('dbo.sp_planGuide_process') is null
begin

	exec('create procedure dbo.sp_planGuide_process as select [shell] = 1/0 ')

end
go

alter procedure [dbo].[sp_planGuide_process]
(
	  @action	        tinyint = 1
	, @validateBeforeAdding bit = 0
)
as
begin

	set nocount on;
	set XACT_ABORT on;

	declare
		  @id				int --not null identity(1,1)
		, @planGuideName				nvarchar(128) --not null 	
		, @database			sysname --not null
		, @queryText		nvarchar(4000) --not null
		, @planType			sysname --not null
		, @module_or_batch	sysname --null
		, @hint				sysname --not null


	declare @databaseContext		 sysname
	declare @idLast					 int
	declare @idMax					 int
	declare @count					 int
	declare @numberofRecordsAffected int

	DECLARE @templatetext			nvarchar(max);
	DECLARE @parameters				nvarchar(max);
	DECLARE @planExist				bit

	declare @iNumberofMatchesFoundInCachedPlan  int
	declare @bSkipAdding bit

	declare @errBuffer			   nvarchar(400)
	DECLARE @ErrMsg				   nvarchar(4000)
	DECLARE @ErrSeverity		   int
	DECLARE @ErrState			   int

	DECLARE @CHAR_TAB			   varchar(10)		
	DECLARE @CHAR_CRLF			   varchar(10)	


	--declare @commit					bit

	set @CHAR_TAB = CHAR(9)
	set @CHAR_CRLF = CHAR(13) + char(10)
	set @idLast = -1
	set @id = @idLast

	set @databaseContext = db_name()

	set @idMax = ( 
					select max([id]) 
					from   [DBUtility].[dbo].[planguide] 
				)
	set @count = 0
	set @planExist = 0


	while (
					( @id is not null )
				and ( @count <= @idMax )

			)	
	begin

		set @bSkipAdding = 0

		select top 1
					@id =	[id]
				, @planGuideName	= [name]
				, @database	= [database]
				, @queryText = [queryText]
				, @planType = [planType]
				, @module_or_batch = [module_or_batch]
				, @hint	= [hint]

		from   [DBUtility].[dbo].[planguide]

		where  [database] = @databaseContext

		and    [id] > @idLast

		and    [active] = 1

		order by [id] asc

		/*
			Get Number of Records
		*/
		set @numberofRecordsAffected = @@ROWCOUNT


		if (@numberofRecordsAffected > 0)
		begin

			select
						[@idLast] = @idLast
					, [@id] = @id
					, planGuideName
						= @planGuideName
					, [database]
						= @database
					, queryText
						= @queryText
					, planType
						= @planType
					, module_or_batch
						= @module_or_batch
					, hint
						= @hint
					, [@numberofRecordsAffected]
						= @numberofRecordsAffected

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

				set @planExist = 1

			end
			else
			begin

				set @planExist = 0

			end

			/* If Adding Plan */
			if (@action = 1)
			begin

				/* 
					If plan does not exist 
				*/
				if (@planExist = 0)
				begin


					if (@validateBeforeAdding = 1)
					begin

						select @iNumberofMatchesFoundInCachedPlan 
									= count(*)

						FROM sys.dm_exec_cached_plans AS cp 

						CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

						CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

						where 
								(

									( st.[text] not like '%sys%' )

								)

						AND
								(

										( @queryText is null )
									or ( @queryText = '' )
									or ( st.[text] = @queryText)

								)


						/*
							If Number of Matches found in cache is zero
								skip adding
						*/
						if (@iNumberofMatchesFoundInCachedPlan =0)
						begin

							set @bSkipAdding = 1

							print 'Skipping sp_create_plan_guide '
									+ ' as query '
									+ '( '
									+ @queryText
									+ ') '
									+ ' not found in sys.dm_exec_cached_plans!'



						end


					end

					if (@bSkipAdding = 0)
					begin

						BEGIN TRY

							/*
								Get Query Template
							*/
							exec sp_get_query_template 
									  @querytext = @querytext
									, @templatetext = @templatetext output
									, @parameters = @parameters     output

							/*
								Display Query Template
							*/
							select 
									  [@querytext] = @querytext
									, [@templatetext] = @templatetext
									, [@parameters] = @parameters

							if (
									( @templatetext is not null )
								)
							begin

								print 'Invoking sp_create_plan_guide '
									+ ' for '
									+ @planGuideName
									+ ' ... '


								EXEC sp_create_plan_guide 
											@planGuideName = @planGuideName
										,   @stmt = @templatetext
										,   @planType = @planType --N'TEMPLATE'
										,   @module_or_batch = NULL
										,   @params = @parameters
										,   @hints  = @hint -- N'OPTION(PARAMETERIZATION FORCED)'
										;

								print 'Invoked sp_create_plan_guide '
										+ ' for '
										+ @planGuideName

							end -- ( @templatetext is not null )

							END TRY
							BEGIN CATCH

								SELECT 
									  @ErrMsg = ERROR_MESSAGE()
									, @ErrSeverity = ERROR_SEVERITY()
									, @ErrState = ERROR_STATE()

								set @errBuffer = ''

								print @CHAR_TAB + 'Error  :'
								print @CHAR_TAB + '====== :' 
								print @CHAR_TAB + '@querytext :' + @querytext

								print @CHAR_TAB + '@ErrMsg :' + @ErrMsg

								print @CHAR_TAB + '@ErrSeverity :' + cast( @ErrSeverity as varchar(10))

								print @CHAR_TAB + '@ErrState :' + cast( @ErrState as varchar(10))

								 -- Test XACT_STATE for 0, 1, or -1.
								 -- If 1, the transaction is committable.
								 -- If -1, the transaction is uncommittable and should 
								 --     be rolled back.
								 -- XACT_STATE = 0 means there is no transaction and
								 --     a commit or rollback operation would generate an error.

								  -- Test whether the transaction is uncommittable.
								  IF (XACT_STATE() = -1)
								  BEGIN

									PRINT @CHAR_TAB 
											+ 'The transaction is in an uncommittable state.'

									raiserror 
										(
											  @ErrMsg 
											, @ErrSeverity
											, @ErrState
										)

									return
        
								  END;

							END CATCH



					end -- skip adding

				end -- if not exists 

			end --if (@action = 1)

			/* If Remove Plan */
			else if (@action = 2)
			begin

				/* 
					If plan does not exist 
				*/
				if (@planExist = 1)
				begin

						print 'Invoking sp_control_plan_guide/Drop '
								+ ' for '
								+ @planGuideName
								+ ' ... '

						EXEC sp_control_plan_guide 
									N'DROP'
								, @planGuideName

						print 'Invoked sp_control_plan_guide/Drop '
								+ ' for '
								+ @planGuideName
				  

				end


			end

		end --if (@numberofRecordsAffected > 0)

		/*
			Save @id that was read
		*/
		set @idLast = @id

		/*
			Increment Count
		*/
		set @count = @count + 1

	end -- while

end

go

exec sys.sp_MS_marksystemobject '[dbo].[sp_planGuide_process]'
go



Invoke [dbo].[sp_planGuide_process]

Code


	use msdb
	go

	begin tran

		exec [dbo].[sp_planGuide_process]

	rollback tran

Review Plan Guides

Code


SELECT *
FROM   sys.plan_guides

Output

sys-plan-guides

Instrumentation

Code

Check Cached Plans for TemplatePlanGuide

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

 


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

cached-plans-templatePlanGuide

 

Listening

It is Friday night and my Venezuelan family is killing me, with this joint.

 

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