SQL Server – Identify Ad-hoc queries ripe for Forced Parameterization through Plan Guide

Background

Wanted to see whether I can put together a way to identify Ad-hoc queries that are ripe for Forced Parameterization through Plan Guide.

The script is specifically targeted at

  1. Identifying true Ad-hoc queries
    • Ability to Skip Shell Queries
      • As the name suggests, this entry just saves the exact adhoc batch text in cache and points to the parameterized query plan (the prepared compiled plan). In other words the shell query has a dummy compiled plan which just points to the parameterized compiled plan. The shell query is relatively small in size compared to the parameterized (or prepared query)” – Link
    • Option to skip Trivial Query Plans
      • Compared to Fully Optimized Query Plans, Trivial queries plans are usually parameterized as the engine feels it is safe to do so
  2. Counting Number of SQL Statements within each batch
    • Plan Guides can only be targeted at Single Statement Batches
  3.  Summarized
    • Silly summarization through dbo.udf_StripArgumentValues

 

Code

dbo.udf_StripArgumentValues


use master
go

if not exists 
(
	select * 
	from   dbo.sysobjects 
	where  id = object_id(N'[dbo].[udf_StripArgumentValues]') 
	and    xtype in (N'FN', N'IF', N'TF')
)
begin

	exec('
			create function [dbo].[udf_StripArgumentValues]
			(
				@input nvarchar(4000)
			)
			returns nvarchar(4000)
			begin
				return 1/0
			end
		')

end
go

ALTER function [dbo].[udf_StripArgumentValues]
(
	@InputString nvarchar (4000)
)
returns nvarchar(4000)

begin

	declare @OutputString nvarchar (4000)
	declare @CharNum	  integer
	declare @TestChar		nvarchar(1)
	declare @TestCharSaved  nvarchar(1)
	declare @skipChar     bit

	declare @skipDigit	  bit

	declare @skipping		  bit
	declare @skipPosStart	  int

	declare @skipPosNull	   int
	declare @CHAR_PlaceHolder  char(1)
	declare @CHAR_SINGLEQUOTES char(1)
	declare @CHAR_SPACE		   char(1)
	declare @CHAR_COMMA		   char(1)
	declare @CHAR_POUND		   char(1)
	declare @CHAR_PlaceHolder_SKIPEND char(1)

	--Set Variables ( Constant )
	set @skipPosNull = -1
	set @CHAR_PlaceHolder = ''


	set @CHAR_SINGLEQUOTES = ''''
	set @CHAR_SPACE = ' '
	set @CHAR_COMMA = ','
	set @CHAR_POUND = '#'
	set @CHAR_PlaceHolder_SKIPEND = '*'

	--Reset Variables
	set @CharNum = 1
	set @OutputString = ''

	set @skipChar	   = 0
	set @skipPosStart  = @skipPosNull

	set @skipDigit     = 0

	while @CharNum <= len(@InputString)
	begin

		--Get Char for current position
		set @TestChar = substring(@InputString, @CharNum, 1)

		--Skip Digits
		/*
		if (@TestChar between '0' and '9') 
		begin
			set @skipChar = 1
		end
		*/

		--If Single Quotes
		if (@TestChar = @CHAR_SINGLEQUOTES) 
		begin

			--skip current character
			set @skipChar = 1

			--set mode to skipping
			set @skipping = 1

			--capture skip pos, if not currently skipping
			if (@skipPosStart = @skipPosNull)
			begin

				set @skipPosStart = @CharNum

			end

		end

		--If Skip Digit
		else if (@TestChar between '0' and '9') 
		begin

			--skip current digit
			set @skipDigit = 1

			--set mode to skipping
			set @skipping = 1

			--capture skip pos, if not currently skipping
			if (@skipPosStart = @skipPosNull)
			begin

				set @skipPosStart = @CharNum

			end

		end

		/*
			If we are not in the middle of skipping
		*/
		else if (@skipping != 1)
		begin

			set @skipChar = 0
			set @skipDigit = 0

		end

		--Keep Character
		--if @skipChar = 0
		if (
				    ( @skipChar = 0 )
				and ( @skipDigit = 0 )
			)
		begin

			set @OutputString = @OutputString + @TestChar
	
		end

		--If Single Quotes / End
		if (
				(
					   ( @TestChar = @CHAR_SINGLEQUOTES ) 

				)
				and (@skipping = 1)
				and (@skipPosStart != @CharNum)
			)
		begin

			set @skipping = 0

			set @skipPosStart  = @skipPosNull

			set @OutputString = @OutputString
									 + @CHAR_SINGLEQUOTES 
									 + @CHAR_SINGLEQUOTES

		end

		/*
			If character is space, or comma
				and previous character ( @TestCharSaved ) is between 0 and 9
		*/
		else if 
			(
				(
					   ( @TestChar = @CHAR_SPACE) 
					or ( @TestChar = @CHAR_COMMA )
				)
				and ( @skipping = 1)
				and ( @skipPosStart != @CharNum)
				and ( @TestCharSaved between '0' and '9') 
			)
		begin

			set @skipping = 0

			set @skipPosStart  = @skipPosNull

			set @OutputString = @OutputString + @CHAR_POUND

			--skip current digit
			set @skipDigit = 0

		end

		-- Save @TestChar
		set @TestCharSaved = @TestChar

		--Move Pointer
		set @CharNum = @CharNum + 1

	end


	/*
		If at end of Loop, we are still skipping
			see if we need to add last replacement marker
	*/
	if 
		(
			( @skipping = 1)

		)
	begin

		/*
			If character is space, or comma
				and previous character ( @TestCharSaved ) is between 0 and 9
		*/
		if ( @TestCharSaved between '0' and '9') 
		begin

			set @OutputString = @OutputString + @CHAR_POUND

		end

	end

	return @OutputString

end

GO

grant execute on [dbo].[udf_StripArgumentValues] to [public]
go


 

dbo.sp_ListQueryPlanAdhocQueries

Ouline

Parameters

  1. includeAdhocShellQuery
    • Options
      • 0 ( means to exclude Adhoc Shell Queries )
      • 1 ( means to include Adhoc Shell Queries)
    • Usually want to keep this as 0
      • Obviously, this means to skip Shell Queries
  2. StatementOptmLevel
    • Options
      • FULL
      • TRIVIAL
    • Usually want to keep at FULL, as plans marked as TRIVIAL are usually parameterized since the Engine deems it safe to do so
  3. maxNumberofRecords
    • Maximum Number of records to return
    • Only effectual for detailed report
  4. summarized
    • Options
      • 0 ( means to display detailed view)
      • 1 ( means to display aggregated view )
  5. Database
    • Database Name
  6.  sql
    • sql pattern
      • Can be
        • sql snippet
        • object name

 

Actual Code



use [master]
go

set quoted_identifier on;
go
 
if object_id('[dbo].[sp_ListQueryPlanAdhocQueries]') is null
begin

	exec('create procedure [dbo].[sp_ListQueryPlanAdhocQueries] as begin select 1/0 as [shell] end' )
	
end
go

alter procedure [dbo].[sp_ListQueryPlanAdhocQueries]
(
	  @includeAdhocShellQuery bit		    = 0
	, @StatementOptmLevel	  sysname	    = 'FULL'
	, @maxNumberofRecords	  int			= 1E3
	, @summarized			  bit			= 1
	, @database				  sysname       = null
	, @sql					  varchar(600)  = null	
)
as
begin

	set nocount on;
	set XACT_ABORT on;
	set transaction isolation level read uncommitted;

	declare @sqlWildcard		varchar(800)
 
	declare @queryText			nvarchar(4000)

	declare @id					int
	declare @idMax				int

	declare @databaseID			int

	declare @queryPlan TABLE
	(
		  [id]							    int	identity(1,1)
		, [refcounts]						int
		, [usecounts]						int
		, [plan_handle]					    varbinary(64)
		, [ParameterizedPlanHandle]			varbinary(64)
		, [StatementOptmLevel]				sysname
		, [StatementSubTreeCost]			float
		, [NumberofSQLStatementsInBatch]	smallint
		, [size_in_bytes]					int
		, [cacheobjtype]					sysname
		, [objtype]							sysname

		, [text]							nvarchar(max)
		, [databaseID]						int				    null
		, [database]
			as case
					when ([databaseID] = 32767) then 'Resource DB'
					else db_name([databaseID])
				end
		, [strippedArgumentValues]		    nvarchar(max)		null
		 
		, [XMLDocument]						xml
		, [XMLFragment-QueryPlan]			xml
		, [QueryPlan-StmtSimple-Exist]		bit
		, [QueryPlan-QueryPlan-Exist]		bit
 
		, [count]							int	default (1)

	)
		

 
	if (@sql is not null)
	begin
 
		set @sqlWildcard = '%' + @sql + '%'
 
	end
 
	if (@database is not null)
	begin
 
		set @databaseID = db_id(@database)
 
	end
 

	; WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	 ,cte
	(
 
		  [refcounts]
		, [usecounts]
		, [plan_handle]
		, [size_in_bytes]
		, [cacheobjtype]
		, [objtype]
		, [text]
		, [queryPlanAsXML]
		, [databaseID]
	)
	as
	(
	   select
				  tblDECP.[refcounts]
				, tblDECP.[usecounts]
				, tblDECP.[plan_handle]
				, tblDECP.[size_in_bytes]
				, tblDECP.[cacheobjtype]
				, tblDECP.[objtype]
				, tblDest.[text]
				, [queryPlanAsXML]
					= tblDEQP.query_plan
				, [databaseID]
					= cast(tblDEPA.value as int)
 
		from   sys.dm_exec_cached_plans tblDECP
 
		CROSS APPLY sys.dm_exec_query_plan(tblDECP.plan_handle) tblDEQP
 
		CROSS APPLY sys.dm_exec_sql_text(tblDECP.plan_handle) AS tblDEST

		CROSS APPLY sys.dm_exec_plan_attributes(tblDECP.plan_handle) as tblDEPA

		WHERE tblDEPA.[attribute] = 'dbid'

		AND   (
					   ( @databaseID is null )
					or ( tblDEPA.value = @databaseID )
			  )	

		and  (
  
					--Query does not target sys schema
					( tblDEST.[text]  not like '%sys.%' )
  
			   )
 
 
		and  (
 
				(
 
						( objType = 'Adhoc' )
 
					and (
 
								( tblDEST.[text]  like @sqlWildcard )
							or ( @sqlWildcard is null )
 
						)
 
				)
 
 
			)


	)
	,cteQP
	(
		  [refcounts]
		, [usecounts]
		, [plan_handle]
		, [ParameterizedPlanHandle]
		, [StatementOptmLevel]
		, [StatementSubTreeCost]
		, [NumberofSQLStatementsInBatch]
		, [size_in_bytes]
		, [cacheobjtype]
		, [objtype]
		, [text]
		, [databaseID] 
		, [XMLDocument]
		, [XMLFragment-QueryPlan]
		, [QueryPlan-StmtSimple-Exist]
		, [QueryPlan-QueryPlan-Exist]
 

	)
	as
	(
		select
			  cteXML.[refcounts]
			, cteXML.[usecounts]
			, cteXML.[plan_handle]
			, [ParameterizedPlanHandle]
				= stmt.value
					(
						  '(@ParameterizedPlanHandle)'
						, 'varchar(64)'
					) 

			, [StatementOptmLevel]
				= stmt.value
				( 
					  '(@StatementOptmLevel)'
					, 'varchar(30)' 
				) 

			, [StatementSubTreeCost]
				= stmt.value
					(
						  '(@StatementSubTreeCost)[1]'
						, 'VARCHAR(128)'
					)

			, [NumberofSQLStatementsInBatch]
				= cteXML.[queryPlanAsXML].value
				(
					'declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
					count(//spsql:StmtSimple)'
					, 'int'
				)

			, cteXML.[size_in_bytes]
			, cteXML.[cacheobjtype]
			, cteXML.[objtype]
			, cteXML.[text]
 			, cteXML.[databaseID]	
 
			, [XMLDocument]
				= cteXML.[queryPlanAsXML].query('.')
 
			, [XMLFragment-QueryPlan]
				= cteXML.[queryPlanAsXML].query('//QueryPlan')
 
			, [QueryPlan-StmtSimple-Exist]
				= cteXML.[queryPlanAsXML].exist('//StmtSimple')
 
			, [QueryPlan-QueryPlan-Exist]
				= cteXML.[queryPlanAsXML].exist('//QueryPlan')
 
	from   cte cteXML
 
	CROSS APPLY cteXML.[queryPlanAsXML].nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
												 AS batch(stmt)

	)

	insert into @queryPlan
	(
		  [refcounts]
		, [usecounts]
		, [plan_handle]
		--, [ParameterizedPlanHandle]
		, [StatementOptmLevel]		
		, [StatementSubTreeCost]	
		, [NumberofSQLStatementsInBatch]
		, [size_in_bytes]				
		, [cacheobjtype]				
		, [objtype]						
		, [text]	
		, [databaseID]
 
		, [XMLDocument]					
		, [XMLFragment-QueryPlan]		
		, [QueryPlan-StmtSimple-Exist]
		, [QueryPlan-QueryPlan-Exist]
 
	)


	select 
			top (@maxNumberofRecords)
		  [refcounts]
		, [usecounts]
		, [plan_handle]
		--, [ParameterizedPlanHandle]
		, [StatementOptmLevel]		
		, [StatementSubTreeCost]	
		, [NumberofSQLStatementsInBatch]
		, [size_in_bytes]				
		, [cacheobjtype]				
		, [objtype]						
		, [text]						
 		, [databaseID]
		, [XMLDocument]					
		, [XMLFragment-QueryPlan]		
		, [QueryPlan-StmtSimple-Exist]
		, [QueryPlan-QueryPlan-Exist]


	from   cteQP 

	where  (

				--@includeAdhocShellQuery
				(
					(
							( @includeAdhocShellQuery = 0 )
						and ( [QueryPlan-QueryPlan-Exist] = 1 )
					)
					or
					(
							( @includeAdhocShellQuery = 1 )
					)

				)

				--@StatementOptmLevel
				and
				(
					(
							( @StatementOptmLevel is null )
						or  ( 
								[StatementOptmLevel] 
									= @StatementOptmLevel
							)
					)
				)


		   )
	

	update tblQP
	set    [strippedArgumentValues]	= [master].[dbo].[udf_StripArgumentValues]
										(
											[text]
										)
	from   @queryPlan tblQP

	/*
		Get Number of Matching queries
	*/
	update tblQP

	set    [count]	= 
						(

							select count(*)

							from  @queryPlan tblQP_Inner

							where tblQP.[strippedArgumentValues] 
									= tblQP_Inner.[strippedArgumentValues]

							and   tblQP.[id] != tblQP_Inner.[id]
						)

	from   @queryPlan tblQP



	if (@summarized = 1)
	begin

		select 
			  [id]
				= min([id])

			, [refcounts]
				= sum([refcounts])

			, [usecounts]
				= sum([usecounts])

			, [plan_handle]
				= min ([plan_handle])

			, [ParameterizedPlanHandle]
				= null

			, [StatementOptmLevel]		
				= max([StatementOptmLevel])

			, [StatementSubTreeCost]	
				= avg([StatementSubTreeCost])

			, [NumberofSQLStatementsInBatch]
				= avg([NumberofSQLStatementsInBatch])	

			, [size_in_bytes]	
				= avg([size_in_bytes])				

			, [cacheobjtype]					
				= min([cacheobjtype])

			, [objtype]		
				= min([objtype])					

			, [database]
				= min([database])

			, [text]							
				= min([text])

			, [XMLDocument]						
				= cast
					(
						min
						(
							cast([XMLDocument] as nvarchar(max))
						)
						as xml
					)
						
			, [XMLFragment-QueryPlan]			
				= cast
					(
						min
						(
							cast([XMLFragment-QueryPlan] as nvarchar(max))
						)
						as xml
					)

			, [QueryPlan-StmtSimple-Exist]		
				= min(
						cast([QueryPlan-StmtSimple-Exist] as smallint)
					 )

			, [QueryPlan-QueryPlan-Exist]	
				= min(
						cast ( [QueryPlan-QueryPlan-Exist] as smallint)
					 )

			, [count]	
				= count(tblQP.[id])

		from   @queryPlan tblQP

		group by 
				tblQP.[strippedArgumentValues]

		order by
				(
					avg(tblQP.[StatementSubTreeCost]) 
						* count(tblQP.[id])
						* sum(tblQP.[usecounts])
				) desc


	end
	else if (@summarized = 0)
	begin

		select 
			  [id]
			, [refcounts]
			, [usecounts]
			, [plan_handle]
			, [ParameterizedPlanHandle]
			, [StatementOptmLevel]		
			, [StatementSubTreeCost]	
			, [NumberofSQLStatementsInBatch]	
			, [size_in_bytes]					
			, [cacheobjtype]					
			, [objtype]							
			, [database]
			, [text]							
			, [XMLDocument]						
			, [XMLFragment-QueryPlan]			
			, [QueryPlan-StmtSimple-Exist]		
			, [QueryPlan-QueryPlan-Exist]		
			, [count]	

		from   @queryPlan tblQP

		order by

				(
					(tblQP.[StatementSubTreeCost]) 
						* tblQP.[usecounts]
				) desc

			   , tblQP.[usecounts] desc
			   , tblQP.[size_in_bytes] desc


	end



end
go


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

Sample Invocations

All Databases


declare @includeAdhocShellQuery		bit
declare @StatementOptmLevel			sysname
declare @maxNumberofRecords			int
declare @summarized					bit
declare @sql					    varchar(600)

set @includeAdhocShellQuery = 0
set @StatementOptmLevel = 'FULL'
--set @StatementOptmLevel = 'TRIVIAL'
set @maxNumberofRecords	= 1E6
set @summarized = 1

exec [dbo].[sp_ListQueryPlanAdhocQueries]
		   @includeAdhocShellQuery      = @includeAdhocShellQuery
		 , @StatementOptmLevel		= @StatementOptmLevel
		 , @maxNumberofRecords		= @maxNumberofRecords
		 , @summarized			= @summarized
		 , @sql				= @sql

Output

QueryPlan-Adhoc-20160525-0624PM

 

Database – csAnalytics


declare @includeAdhocShellQuery		bit
declare @StatementOptmLevel			sysname
declare @maxNumberofRecords			int
declare @summarized					bit
declare @database				    sysname 
declare @sql					    varchar(600)

set @includeAdhocShellQuery = 0

--set @StatementOptmLevel = 'TRIVIAL'
set @StatementOptmLevel = 'FULL'

set @maxNumberofRecords	= 1E6

set @summarized = 1

set @database = 'csAnalytics'

exec [dbo].[sp_ListQueryPlanAdhocQueries]
		   @includeAdhocShellQuery  = @includeAdhocShellQuery
		 , @StatementOptmLevel		= @StatementOptmLevel
		 , @maxNumberofRecords		= @maxNumberofRecords
		 , @summarized				= @summarized
		 , @database				= @database
		 , @sql						= @sql
Output

QueryPlan-Adhoc-20160525-0647PM-csAnalytics

 

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