Transact SQL – Msg 102, Level 15, State 1 – Handling SQL Quotes

Background

Trying to create a plan guide, but unable to because of the single quotes around SQL argument values.

Memory Pressure

We are having memory pressure and wanted to aggregate our plan cache based on plan types.


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

Output:

CacheType

Explanation:

  1. We have 90 thousand Ad-hoc entries
  2. A total of 2 GB
  3. 2 GB about 50 thousand entries are single use plans

 

One and Done Query Plans

As I like College Hoops, I will use the term used when talking about guys who only attend college for one year and thus satisfy NBA pre-requisites…

So here are the One and Done Queries.

Code


SELECT top 10
	       [planHandle] = cp.plan_handle
	     , [queryPlan] = deq.query_plan
	     , [sqlText] = st.[text]
	     , [objectType] = cp.objtype
	     , [sizeInBytes] = cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS deq
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.cacheobjtype = N'Compiled Plan'
AND   cp.objtype IN(N'Adhoc', N'Prepared')
AND   st.[text] not like '%sys.dm%'
AND   cp.usecounts = 1

order by cp.size_in_bytes desc

Output:

SingleQuotes

 

Plan Guide

Create Plan Guide

So we capture the SQL text and try to create a plan guide from it.

Code


declare @planName sysname
declare @sqlText nvarchar(4000)

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);

set @planName = 'StopSendEmails'

set @sqlText= 'select stop_send_emails from Student where studentID='joesmith@hotmail.com''

--set @sqlText= "select stop_send_emails from Student where studentID='joesmith@hotmail.com'"

 

Error:

Textual

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'joesmith@hotmail'.

Image

Msg102-Level-15

 

Corrected Code


set quoted_identifier off;
go

declare @planName sysname
declare @sqlText nvarchar(4000)

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);

set @planName = 'StopSendEmails'

--set @sqlText= 'select stop_send_emails from Student where studentID='joesmith@hotmail.com''
set @sqlText= "select stop_send_emails from Student where studentID='joesmith@hotmail.com'"

EXEC sp_get_query_template
	      @sqlText
		, @stmt OUTPUT
		, @params OUTPUT

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

begin tran

	EXEC sp_create_plan_guide
			  @name = @planName
			, @stmt = @stmt
			, @type = N'TEMPLATE'
			, @module_or_batch = NULL
			, @params = @params
			, @hints = N'OPTION(PARAMETERIZATION FORCED)'
		;

	select
			  planGuideID = tblPG.plan_guide_id
			, [planName] = tblPG.[name]
			, [modifydate] = tblPG.[modify_date]
			, [queryText] = [query_text]
			, [objectId] = [scope_object_id]
			, [parameters] = [parameters]
			, [hints] = [hints]
	from   sys.plan_guides tblPG
	; 

rollback tran

 

Explanation
  1. Issue the “set quoted_identifier off;”
    • This setting indicates that entries in Double-Quotes should not be taken as Identifiers
  2. The original SQL text is also escaped in double-quotes; as a revision from single-quotes

 

Image

PlanGuideCreationSuccessful

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