SSMS – Scripting – Job – Error – “msdb.dbo.sp_add_jobserver” – “is already targeted at server”

 

Background

Using SSMS, Scripted SQL Server Job so that I can apply it against other SQL Servers, but ran into error.

Scripting

Script SQL Server Agent Job

scriptJob_20180612_0128PM.PNG

 

Error

Error Message


Msg 14269, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 101 [Batch Start Line 8]
Job 'SharePublicly' is already targeted at server 'LAB'.

Troubleshooting

When a job is scripted, the code generated includes a portion that invokes sp_add_jobserver.

The sp_add_jobserver procedure binds the job to the targeted server.


EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
         @job_id = @jobId
       , @server_name = N'(local)'

IF (@@ERROR  0 OR @ReturnCode  0)
      GOTO QuitWithRollback

Remediation

Outline

  1. Check msdb system tables
    • dbo.sysjobservers
    • dbo.systargetservers
  2. If matching records
    • Here are the tables that needs to be considered
      • dbo.sysjobservers.jobName ( @jobID )
      • dbo.systargetservers.serverName ( @servername)
    • Conditions satisfied
      • Local variable @skipWhenJobExist is enabled ( set to 1 )
  3. Action
    • Skip invoking msdb.dbo.sp_add_jobserver

Code Snippet


DECLARE @jobId   BINARY(16)
DECLARE @jobName sysname
DECLARE @serverName sysname
DECLARE @serverNameLocal sysname
declare @commit bit
declare @skipWhenJobExist bit

set @jobName = 'SharePublicly';
set @servername = N'(local)'
set @serverNameLocal = N'(local)'

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

set @skipWhenJobExist = 1
--set @skipWhenJobExist = 0

/*
	Check if Job is already attached to Target Server
*/
if not exists
(

	select
			  [src] = 'dbo.sysjobservers'
			, tblSJS.* 

	from   dbo.sysjobservers tblSJS

	left outer join dbo.systargetservers tblSTS
			on tblSJS.server_id = tblSTS.server_id

	where  tblSJS.job_id = @jobId

	and  

		(

			(
					 ( tblSJS.server_id = 0)
				 and ( @servername = @serverNameLocal )

			)

			or
			(
					 ( tblSJS.server_id = tblSTS.server_id)
				 and ( tblSTS.server_name = @serverName )
			)

		)
	and (@skipWhenJobExist = 1)
)
begin

	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
			  @job_id = @jobId
			, @server_name = @serverName

	IF (@@ERROR  0 OR @ReturnCode  0)
	begin
		GOTO QuitWithRollback
	end

end

Source Control

GitHub

DanielAdeniji/SQLServerScriptingAgentErrorJobAlreadyTargeted
Link

 

SQL Server–Error–“Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space”

Background

Upon starting SQL Server, we received the error posted below.

Error

Image

msdb110_upgrade_20171003_1115PM (cropped up)

Textual

Msg 945

Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details

Troubleshooting & Remediation

Outline

  1. Preparation
    • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
    • Identify Folder’s location and filenames for msdb data and log files
    • Identify location of msdb install files
      • instmsdb.sql
  2. Inspect
    • Review Accounts’ access
      • Review Accounts NTFS Permissions on msdb data and log files
  3. Get out the way
    • Stop MS SQL Server Engine and Agent services
  4. Get Set
    • Safe keep msdb files
      • Copy msdb data and log files to a neutral location
  5. Go!
    • Start MS SQL Server using trace files
      • Trace Flags
        • -T3608
    • Detach msdb database
      • Using SQLCMD or Management Studio, issue sp_detach_db ‘msdb’
    • Rename existing msdb data and log files
    • Create msdb database
  6. Rollout
    • Stop SQL Server
    • Remove SQL Server Trace Flags
    • Retrace Trace Flags
      • -T3608
  7. Restart SQL Server Engine
  8. Validate
    • Access msdb database

Actual Steps

Preparation

  • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
  • Identify Folder’s location and filenames for msdb data and log files
  • Identify location of msdb install files
    • instmsdb.sql

Make Note of SQL Service Accounts

SQL Server Configuration Manager
Image

Explanation
  1. Services
    • SQL Server
      • Local System
    • SQL Server Agent
      • [domain]\svc_sql

 

Identify Folder’s location and filenames for msdb data and log files

Identify the folder where system databases ( master, model, and msdb ) are stored.

Dynamic Management Views

If SQL Server is running in normal mode or within trace flags ( -T 3605 ), please get folders and names of msdb files.

SQL
select 

          [dbid] 
            = tblSMF.database_id
            
        , [database] 
            = db_name(tblSMF.database_id)
            
        , [fileID] 
            = tblSMF.file_id            
            
        , [fileSymbolicName]
            = tblSMF.[name]
        
        , [filePhysicalName]
            = tblSMF.[physical_name]
            
        --, tblSMF.*            
        
from   sys.master_files tblSMF

where  tblSMF.[database_id] <= 4

order by
        tblSMF.database_id asc
        

Image

Explanation
  1. Database
    • msdb
      • MSDBData & MSDBLog noted

 

SQL Server Configuration Manager
Image

Explanation
  1. Startup Parameter
    • -d
      • -dE:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

 

Identify location of msdb install files

Syntax

[InstallDriveLetter]:\Program Files\Microsoft SQL Server[Version].[InstanceName]\MSSQL\Install

Sample

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install

 

Inspect

Review Accounts’ access

Review Accounts NTFS Permissions on msdb data and log files
Outline

Here are the steps to take to read out the “Effective” permissions

  1. To view effective permissions on files and folders
    1. Open Windows Explorer, and then locate the file or folder for which you want to view effective permissions.
    2. Right-click the file or folder, click Properties, and then click the Security tab.
    3. Click Advanced, click the Effective Permissions tab, and then click Select button.
      • In the Select Window
        • Enter the name of a user or group, and then click OK.
    4. The selected check boxes indicate the effective permissions of the user or group for that file or folder.
Image

Get Set…

Safe keep msdb files

Copy msdb data and log files to a neutral location

Create a safe location and make copies of files to place in Safe Location.

Image

Go!

Start MS SQL Server using trace files

Trace Flags
Trace Flags Outline
  • Trace Flags
    • -T3608

 

Trace Flag ID What does it do Applicable Effective
3608 It will not start and recover any database except the MASTER system database. So none of the system databases; msdb, tempdb, model, etc will start or recover if this trace flag is used while starting SQL Server. Yes Yes
 902  Trace Flag 902 will bypass the internal upgrade script on startup No  N/A
Trace Flags Steps
SQL Server Configuration Manager
Add(ing) Trace Flag :- -T3608

Add(ed) Trace Flag :- -T3608

 

Start MS SQL Server
SQL Server Configuration Manager

 

Detach msdb
SQL

exec sp_detach_db 'msdb'

 

Rename MSDB Data & Log Files
Image

Create msdb
Command Line

sqlcmd -S . -i instmsdb.sql

SSMS
SSMS – Output

Rollout

Outline

  1. Stop SQL Server
  2. Remove SQL Server Trace Flags
  3. Retrace Trace Flags
    • -T3608

Stop MS SQL Server

Image

Trace Flags
Trace Flags Remove

remove Trace Flags

Trace Flags Remove – removed

Summary

There is a lot of very empty spaces here.

So much was left out to make this post concise and address them during more specific subject materials.

 

References

  1. SQLServerCentral.com
    • mssqlfun
      • How to rebuild MSDB database?
        Link
  2. Microsoft
    • Microsoft \ TechNet \ Windows Server
      • Access Control > Managing Permissions > Set, View, Change, or Remove Permissions on an Object
        Link
    • msdn
      • Sql Server 2012 – Cannot recover the master database
        Link
  3. Trace Flags
    • 3608
      • Paul Randal
        • How to recreate the msdb database in SQL Server 2005?
          Link
    •  902
      • Patrick Keisler, MSFT – Premier Field Engineer
        • Use Trace Flag 902 to Recover from a Cumulative Update Failure
          Posted on :- 2017-July-7th
          Link

 

SQL Server Agent Roles

Background

We are evaluating using BMC’s Control-M, our corporate IT Job Management tool.

And, wanted to see what we will have to do to get it to work against SQL Server Instances.

 

SQL Server Agent Roles

In SQL Server, jobs are managed through SQL Server Agent.

PreDefined Roles

SQL Server Agent has predefined security roles.

SQL Server Management Studio ( SSMS )

Jobs are saved in the system database, msdb.

To view the roles, please do the following:

  1. Launch SQL Server Management Studio (SSMS)
    • Connect to the SQL Server Instance
    • Choose System Databases
    • From the list of System Databases, choose msdb
    • Within the msdb database, transverse to Security \ Roles \ Database Roles
    • The SQL Server Agent roles are noted having names that start with SQLAgent

       

 

Permission Set

Let us dig deeper into these roles and see what they afford us, where they are different, and what is the minimum we can get away with.

Concentric

The roles are listed in increasing order of privileges assigned.

More precisely as Microsoft would say it, they are concentric.

Looked up the term concentric and here is how it is defined:

They are of or denoting circles, arcs, or other shapes that share the same center, the larger often completely surrounding the smaller.

And, so we can see that MSFT’s documentation is very useful, and here it is in verbatim:

Link
The SQL Server Agent database role permissions are concentric in relation to one another — more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). 

Tabulated

Role Definition Details
SQLAgentUserRole Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.  a) Have permission on owned jobs
SQLAgentReaderRole SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. a) List all jobs – their properties, schedules, and executionhistory
SQLAgentOperatorRole SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. a) Manage ( enable or disable jobs, edit job steps )

b) They can execute, stop, or start jobs

c) Delete job execution history

 

Which Role?

For system jobs we do not want an external job manager as SQL Server Agent is able to do so sufficiently.

We only want an external job manager for specific jobs.

Let us review the predefined system roles and judge their appropriateness for what we have in mind:

  1. SQLAgentOperatorRole
    • Affords all roles to all jobs
    • Too much for us
  2. SQLAgentReaderRole
    • (+)
      • Able to create and and manage own job
      • Read privileges on all jobs; their steps, schedule, and run history
    • (-)
      • Job Management does not need to view job data nor review job run history
        • A bit much for our targeted need
  3. SQLAgentUserRole
    • Requirements
      • (+)
        • Create own jobs
        • Run owned jobs
          • Existing jobs ownership can be re-assigned
      • (-)
        • Job has be owned
          • We have to review what is the ramification of changing job ownership for each specific job

 

Follow Up

Our follow-up task is to review the impact of changing job ownership for specific jobs.

 

References

  1. Microsoft
    • SQL Server Agent Fixed Database Roles
      Link
    • Implement SQL Server Agent Security
      Link

 

SQL Server – Recreate System Database – msdb

Background

One of my brothers called me and said that that he is unable to use his beloved SQL Server Management Studio  ( SSMS ) to edit records in a table.

And, that SQL Server says his msdb database his corrupted.

 

Remediate

Restore from Backup

To remediate I asked if he takes backup of the msdb database and he says No.

And, so I know that is not quite a good look.

 

Recreate MSDB from Scratch

Overview

In the sample script below, we will recreate the MSDB Database.

SQL Server comes with a script called instmsdb.sql.  The file creates the msdb database.

We need to make sure that the database and its files do not exist and so we will do some housekeeping and rid self of the existing files.

It is a long winded code and so let us talk about it a bit.

 

Steps

  1. Preparation
    • Base Values
      • Set Instance Name
      • Set Folder Location of the instmsdb.sql file
      • Set Default Folder Location of system database files
      • Set File names for msdb data and log files
  2. Computed Values
    • Set full file name for MSDB Data and Log files
  3. Get Current Timestamp
  4. Compute Backup folder where we will keep the current data and log files for the msdb database
  5. If Backup folder does not exist, create it
  6. Stop MS SQL Server Engine and other SQL Server Services
  7. Start MS SQL Server Engine in minimal mode
    • Trace Flags
      • 3608
  8. Using sqlcmd.exe, detach msdb database
  9. Using sqlcmd.exe, recreate msdb database using instmsdb.sql
  10. Stop MS SQL Server
  11. Restart MS SQL Server in regular mode

 

Code


@echo on
setlocal

Rem set instance Name to Default
set "_instname=."

Rem Set Folder for location of instmsdb.sql
set _installFLD=E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install

Rem Set Folder for Location of System Database files such as ( master, model, tempdb, msdb)
set _dbSystemFLD=E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

Rem Set filename for MSDB Database & Log file
set _dbMSDBData=MSDBData.mdf
set _dbMSDBLog=MSDBLog.ldf

Rem Set Full File Name for Data and Log files
set _datafile=%_dbSystemFLD%\%_dbMSDBData%
set _logfile=%_dbSystemFLD%\%_dbMSDBLog%


REM serverfault
REM Need leading zero for batch script using %time% variable
REM Answered by David
REM http://serverfault.com/questions/147515/need-leading-zero-for-batch-script-using-time-variable/529445
:prepare time stamp 
set year=%date:~10,4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
:replace leading space with 0 for hours < 10
if "%hour:~0,1%" == " "  set hour=0%hour:~1,1%
set minute=%time:~3,2%
set second=%time:~6,2%
set _timeStamp=%year%%month%%day%_%hour%%minute%%second%

set _BACKUPFLD=%_dbSystemFLD%\BACKUP\%_timeStamp%

REM If Backup Folder does not exist, mkdir Backup Folder 
if not exist "%_BACKUPFLD%" mkdir "%_BACKUPFLD%"

REM Stop MSSQLServer
net stop mssqlserver /y

REM Start MSSQLServer
NET START MSSQLSERVER /T3608


REM Using SQLCMD Detach msdb database
sqlcmd  -E -S %_instname% -d master -Q "set quoted_identifier off; if db_id('msdb') is not null begin exec sp_detach_db 'msdb'; end "


REM If MSDB Data & Log file already exist at target folder, pleaes move to backup folder 
if exist "%_datafile%" move "%_datafile%" "%_BACKUPFLD%"
if exist "%_logfile%"  move "%_logfile%"  "%_BACKUPFLD%"


REM Using SQLCMD run instmsdb.sql
SQLCMD -E -S %_instname% -i "%_installFLD%\instmsdb.sql" -o instmsdb_%_timeStamp%.log


REM Stop MSSQLServer
net stop mssqlserver /y


REM Start MSSQLServer
NET START MSSQLSERVER


endlocal


Source Code Control

GitHub

Here is the URL for our repository

 

References

  1. Database Features > Databases (Database Engine) > System Databases > Rebuild System Databases
    Link

Sql Server – SQL Server Agent – Optimizing DB Traffic

Background

In our last post, we spoke about how MSDB is responsible for some of our Single use queries.

Let us dig more in by identifying some of those queries and see if we can change, modify, or reduce their frequency.

 

MSDB – Queries

We have identified a couple of queries and here they are:

  1. msdb.dbo.sp_sqlagent_get_perf_counters
  2. msdb.dbo.sysmail_help_profile_*

 

SQL – msdb.dbo.sp_sqlagent_get_perf_counters

SQL Server Profiler

SQL Server Profiler – Captured

Image

sqlserverprofiler-20160908-0334pm

 

Textual

  1. Text Data :- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
  2. Application Name: – SQLAgent – Alert Engine

 

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters”
    • Every 20 seconds

 

Remediation

Steps

  1. Script out all current SQL Server Agent Alerts
  2. Remove all the alerts
    • Please keep in mind that it is not enough to disable them
Before

sqlserveragentalerts-20160908-0254pm

 

SQL Server Agent – Mail

SQL Server Profiler

Image

sqlserverprofiler-20160908-0426pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N'Local Relay Server'
exec msdb.dbo.sysmail_help_profileaccount_sp @profile_id=1
exec msdb.dbo.sysmail_help_admin_account_sp @account_id=1

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help*” commands
    • Every 5 minutes

Remediation

Steps

  1. Access SQL Server Agent Properties
  2. Access the “Alert System” Tab
  3. Disable “Enable Email profile”
Before

alertsystem-before

 

After
alertsystem-after

 

SQL Server Profiler ( After)

Image

sqlserverprofiler-20160908-0437pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N''

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help_profile_sp” commands
    • Every 5 minutes
    • Captured Output
      • Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid 

Summary

If you are not using SQL Server Agent for alerts and email notification services, you might be able to get away with removing alerts and disabling the email profile setup for SQL Server Agent.

Again, please help in mind we are specifically targeting a v2005 SQL instance.

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.

 

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/