Transact SQL – Error – “Arithmetic overflow error converting numeric to data type numeric”

Background

Revising a SQL Code snippet for finding Bookmark Lookups and ran into an error stating:

Msg 8115, Level 16, State 1
Arithmetic overflow error converting numeric to data type numeric

Code

Original Code

Here is the original code …


set NUMERIC_ROUNDABORT ON;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as decimal(30, 0)
										--as float
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as decimal(30, 0)
												--as numeric(30, 1)
												--as float
										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc


 

Error Message


Msg 8115, Level 16, State 1, Line 13
Arithmetic overflow error converting numeric to data type numeric.

Remediation

To remediate please try one of the options stated below:

  1. Set “Numeric Abort off”
    • set NUMERIC_ROUNDABORT OFF
  2. Cast as float

Set Numeric Abort Off

Code


--set NUMERIC_ROUNDABORT ON;
set NUMERIC_ROUNDABORT OFF;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as decimal(30, 0)
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as decimal(30, 0)

										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc




 

Output

SetNumericRunAbortOff

 

Cast as float

Code



set NUMERIC_ROUNDABORT ON;
--set NUMERIC_ROUNDABORT OFF;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as float
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as float
										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc



 

Output

CastAsFloat

 

 

Summary

Of the two corrective choices, casting to float is the path that localizes the problem to the specific arithmetic that is failing and so I will suggest you choose it over the “set option” that works more at module level.

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

 

Sermons & Stories – 2016/June

Sermons

  1. Mattie Montgomery
    • Liberty University Convocation ( Published on Feb 6, 2015 )
      On February 4, 2015, at Convocation, North America’s largest weekly gathering of Christian students, Mattie Montgomery addressed the student body at Liberty University. Mattie talks about using platforms. He reads from a passage in Acts. This ties into his message about platforms and reaching the world for Jesus regardless of the platform and just walking along with God instead of just working for God.
      Video
    • Little Faith ( Published on Nov 19, 2015 )
      Closing Segment
      Full Video
    • Mattie Montgomery Tells His Story ( Published on Mar 17, 2012 )
      Video
    • Momentum  ( Published on April 1, 2015 )
      Full ( Link )
    • Todd White, Mattie Montgomery, Ben Fitzgerald and Daniel Hagen Q&A ( Published on Nov 8, 2015 )
      Mattie Montgomery – Gospel – Hell & Eternity Message ( Link )
      Full ( Link )
  2. Lester Sumrall
    • Lester Sumrall Biography 1913-1996 ( Published on Feb 10, 2013 )
      I won’t be satisfied unless I win a million souls for jesus everyday

  3. Neil T. Anderson
    • Discipleship Counseling ( Published on Apr 13, 2013, Added on 2016-05-26 )
  4. Damon Thompson
    • Damon Thompson–Daphne, Alabama (2016)

In Depth

Mattie Montgomery

Liberty University Convocation

  1. Almighty Yahweh has  a message
    • Thank God for the opportunity
    • Unique Message for each individual
    • Instructed Tongue that can sustain the weary
  2. If we do not seek God’s face, we are going to miss the purpose he has for our live
    • If you are supposed to be a teacher and you go out and became a great revivalist, you would have missed God’s calling on your Life
  3. Come back to the simplicity of your first love
  4. You don’t have to seek influence, just seek the father.  And, if you can rest in Him, you will find the greatness you know
  5. In your rush to get to the next big thing, do not miss the eunuch the father is trying to reach
  6. No one can be great for God, who has not been great with him
  7. If you can not touch the father’s heart, it does not matter if you can move the father’s hand
  8. In my own journal, I wrote that I have tried to move God’s hand, without touching God’s heart
  9. A call to repentance
  10. The father is already pleased
    • When Yeshua was first Baptized, God said this is my beloved son with whom I am well pleased
    • He had not done anything yet
  11. Greatness
    • We don’t reach for greatness to make God happy, we do it because he loves us
  12. The mandate of the Christian Faith is fellowship with God
    • Not serving the poor
    • Not reaching the Lost
    • Everything else is a consequence of Fellowship with God
  13. Job as Christian is to reach the world through Christ?
    • Our job is to obey Holy Spirit, and Holy Spirit job is to reach the World for Christ
      • Do not whore yourself to reach the world
      • Your Job is to follow Christ and Holy Spirit and have them reach the world
  14. When David wrote that your word is light to my feet
    • His word to me to light to his path
    • Every step of the way, David walked with God
    • David was a man after God’s heart
  15. Pursue Holiness
  16. Prayer for Mattie
    • Because you are great with him privately, allow him to be great for you publicly

 

Little Faith

  1. Follow Me
    • They thought it was follow Jesus out of sin, and not also into destiny
  2. Faith & Commandment
    • Why did Peter wait to receive the commandment that God gave?
      • Faith is dependant upon invitation
      • If you have not been commanded by God to do what you are doing, you do not have faith, just delusional
    • If we don’t have the Word of God on a situation, then we don’t have nothing to build our situation on
    • Only do it, because God has set your mind to do it
    • God will speak to you, if you listen to him
    • Oh for a people, who believe in God’s word
      • even when they lack faith
      • Current account of Bank Account
    • Let us make it personal
      • When I first met my wife
        • Knew marriage was in the works, after 2 weeks of dating
        • Homeless
        • First got paid, the amount was $400
        • Went to Mall and ended up at Zales Jewelry
        • Saw a ring for $880
          • Might as well be 130 million dollars
          • 10% discount
          • Employee said he will give me his 60% employee discount
          • Was going to charge me $405
          • Taxes and Protector Plan ended up at $405
          • Started taking money out and had the exact money to the dime
    • Teen got pregnant
      • In response to your sin, God will bless you with the honor with becoming a parent
      • Repay
        • Rebellion with Kindness
        • Dirt with Love
        • Dishonor with Honor
      • Love that does not follow the guideline of any love you have ever experienced on the earth
      • I promise you it is Love you have not exhausted
      • Darkness is not too Dark
      • Jesus is still sitting at the right side of the Father and still making intercession for you
      • You may be afraid of what comes next, but is Perfect Love cast out fear
      • His strength is made perfect in your weakness

 

Mattie Montgomery Tells His Story

  1. If I want to leave people better than how I find them, I can not be taking from them, I have to give to them
  2. God is always in the background, working on me,
  3. I did not have anything to give to anyone, and so I had to leave everything I had behind

 

Todd White, Mattie Montgomery, Ben Fitzgerald and Daniel Hagen Q&A

Todd White

    1. .. May give you a spirit of wisdom and revelation in your knowledge of Him. I ask that the eyes of your heart may be enlightened, so that you may know the hope of His calling, the riches of His glorious inheritance in the saints, and the surpassing greatness of His power to us who believe. He displayed this power in the working of His mighty strength … ( Ephesians 1:18 )
    2. Continue in Him
      • I have written these things to you about those who are trying to deceive you.   And as for you, the anointing you received from Him remains in you, and you do not need anyone to teach you. But just as His true and genuine anointing teaches you about all things, so remain in Him as you have been taught. ( 1 John 2:26-28 )
    3. Studying the Bible without the Holy Spirit teaches you only Head Knowledge
    4. I not only want to read the Bible, I want your word to read me
      • So that my sins and lies can be illuminated
    5. All of the gifts are a byproduct of being a son

Mattie Montgomery

  1. If and when is it right to talk about Eternity and Hell, when Evangelizing
    • We evangelize because we are conscious of Hell and Eternity
    • Growing our Church will not sustain us
    • If one loses focus of hell, we will lose our Passion
    • Conscious of Eternal blows wind in our sail\soul
    • They may never get another opportunity to hear the Gospel
    • Met 3 young men
    • One said he did not need prayer, as he was his own God
    • There was an accident and the lost man lost his Life
    • The reality of Eternity and the Closeness of Eternity has to be central to the Evangelizing Lifestyle

 

Momentum

  1. Encounter
    • Living God
  2. Faith
    • It does not take Faith to believe God exist
    • It takes Faith to to put your Life in his hands and not know what he will do with it
    • Direction without Encounter is Religion
  3. Altar in front of the throne of God, because the place of encounter is the place of Worship
  4. There is a purpose for encounter and that purpose is Transformation
  5. The Servant’s Obedience
    • The Lord has given me an Instructed tongue ( Isaiah 50:4 )
      • The Lord GOD has given Me the tongue of disciples, That I may know how to sustain the weary one with a word.
        He awakens Me morning by morning, He awakens My ear to listen as a disciple.
        The Lord GOD has opened My ear; And I was not disobedient Nor did I turn back.…

 

Neil T. Anderson

Discipleship Counseling

  1. You repent
  2. Truth sets us free
  3. Power and authority over deamon
  4. Forgiveness
    • Major Problem that troubles people is unforgiveness
    • Bitterness is like swallowing poison hoping someone dies
    • Holding someone captive, and then finding out it is you
    • And in wrath his master turned him over to the tormentors till he should pay all that he owed. So also My heavenly Father will deal with every one of you if
      you do not freely forgive your brother from your heart. ( Link )
    • Letting the Devil set the Agenda
  5. In Bible
    • If you find a promise, claim it
    • If you find a commandment, obey it
    • If you find a fact, believe it
  6. Whatever you have learned or received or heard from me, or seen in me–put it into practice. And the God of peace will be with you (  Philippians 4:9 )
  7. Sanctification
    • Positional
    • Progressive

SQL Server – Delete And Table Spool

Background

Quick follow-up to our last post.  Btw, that post’s title is “SQL Server – Delete and Worktable“.  And, it dealt with a worktable reference we saw in the Statistics I/O.

Objective

In this post we will talk about a table spool operator we saw when removing data from another table in the same Stored Procedure.

Code

Delete Record

Actual Code

declare @ID varchar(100)


set @ID = N'daniel@gmail.com'


begin tran			

	print ''
	print 'Step :- Delete ( Condition :- Overlap Index Exist & Enabled )'
	print '============================================================='

	delete 
	from   [UserAcct].[UserActivity]
	where  [userid] = @ID

rollback tran

 

Query Plan

QueryPlan

 

Explanation

  1. We can see that an Index Seek is performed to identify the records that will be pruned
  2. A Clustered Index Delete is performed to actually remove the records
  3. Two Table Spool operators are performed to guard against Haloween Protection

 

Table Spool

TableSpool-cropped

 

Explanation

Here the properties of the “Table Spool” Operator

  • Physical Operation = Table Spool
  • Logical Operation = Eager Spool
  • Actual Execution Mode = Row
  • Actual Number of Rows = 0
  • Estimated Number of Rows = 616
  • Actual Rebinds = 1
  • Actual Rewinds = 0

 

Statistics I/O

StatisticsIO

 

Explanation

  1. Table :- UserActivity
    • Scan count 1, Logical reads 6
  2. Table :- worktable
    • Scan count 2, Logical reads 0

 

What is causing Table Spools?

Indexes?

sp_helpindex

Code

exec sp_helpindex '[UserAcct].[UserActivity]'

 

Output

sp_helpIndex

 

sp_helpindexinfo

Code

exec sp_helpindexinfo 'UserAcct', 'UserActivity'

Output

sp_helpIndexinfo

 

Explanation
  1. We can see that there are two indexes that are similar ( INDX_UserActivity_CoverIndex1INDX_UserActivity_CoverIndex2 )
    • INDX_UserActivity_CoverIndex1
      • userID, recent_activity, schoolID, courseID, eventCD
    • INDX_UserActivity_CoverIndex2
      • userID, recent_activity, schoolID, courseID, moduleID, eventCD
    • The differences is that INDX_UserActivity_CoverIndex2 has the moduleID column

 

 

Delete with & without Index

Outline

  1. Delete Record
  2. Disable Secondary Index
  3. Delete Record
  4. Rebuild Disabled Index
  5. Delete Record

Actual Code


/*
	set statistics io on;
*/

set nocount on;
go

declare @ID varchar(100)


set @ID = N'daniel@gmail.com'


begin tran			

	print ''
	print 'Step :- Delete ( Condition :- Overlap Index Exist & Enabled )'
	print '============================================================='

	delete 
	from   [UserAcct].[UserActivity]
	where  [userid] = @ID

	print ''
	print 'Step :- Index Disable'
	print '====================='
	ALTER INDEX [INDX_UserActivity_CoverIndex1]
		on [UserAcct].[UserActivity]
		DISABLE


	print ''
	print 'Step :- Delete ( Condition :- Overlap Index Exist & Disabled )'
	print '=============================================================='

	delete 
	from   [UserAcct].[UserActivity]
	where  [userid] = @ID

	print ''
	print 'Step :- Index Rebuilding'
	print '=================================='

	ALTER INDEX [INDX_UserActivity_CoverIndex1]
		on [UserAcct].[UserActivity]
		REBUILD


	print ''
	print 'Step :- Delete ( Condition :- Overlap Index Exist & Enabled [ Post Rebuild ] )'
	print '====================================='

	delete 
	from   [UserAcct].[UserActivity]
	where  [userid] = @ID

rollback tran
go

Query Plan

 

QueyPlan-Cropped

 

Explanation

  1. Delete ( Condition :- Clustered & 2 Non-Clustered Indexes exist)
    • Operators
      • Index :- Look for record
      • Top
      • Clustered Index Delete
      • Table Spool :- Two Index Spools
      • Non-Clustered Index Delete
      • Sequence
  2. Index Disable
  3. Delete ( Condition :- Clustered & 2 Non-Clustered Indexes exist – 1 of them disabled)
    • Operators
      • Index :- Look for record
      • Top
      • Clustered Index Delete
      • Non-Clustered Index Delete
  4. Index Rebuild
    • Operators
      • Index Scan
      • Index Insert
  5. Delete ( Condition :- Clustered & 2 Non-Clustered Indexes exist [ Back to original condition] )
    • Operators
      • Index :- Look for record
      • Top
      • Clustered Index Delete
      • Table Spool :- Two Index Spools
      • Non-Clustered Index Delete
      • Sequence

 

Statistics I/O

 

checkIndex-Cropped

 

Explanation

  1. Delete ( Condition :- Clustered & 2 Non-Clustered Indexes exist)
    • UserActivity ( Scan Count 1, logical reads 6 )
    • Worktable ( Scan Count 2, logical reads 0 )
  2. Index Disable
  3. Delete ( Condition :- Clustered & 2 Non-Clustered Indexes exist – 1 of them disabled)
    • UserActivity ( Scan Count 1, logical reads 6 )
    • No worktable reference
  4. Index Rebuild
    • UserActivity ( Scan Count 1, Logical reads 851236 )
  5. Delete ( Condition :- Clustered & 2 Non-Clustered Indexes exist)
    • UserActivity ( Scan Count 1, logical reads 6 )
    • Worktable ( Scan Count 2, logical reads 0 )

 

Investigate if one of our Non-Clustered Indexes can be disabled

Let us go back and look at the indexes…

sp_helpindexinfo

Code

exec sp_helpindexinfo 'UserAcct', 'UserActivity'

Output

sp_helpIndexinfo

 

Review Index Usage

Outline

Let us measure impact of indexes …

  1. sys.dm_db_index_usage_stats
    • Seek/Scans/User Lookups
  2. sys.dm_os_buffer_descriptors
    • Memory uptake

 

Code


declare @databaseID int
declare @objectName sysname
declare @objectID   sysname

set @databaseID = db_id()
set @objectName = '[UserAcct].[UserActivity]'
set @objectID = object_id(@objectName)

; with cteAllocationUnit
as
(

	SELECT 
			  [object_id]
			, index_id 
			, allocation_unit_id
			, tblAU.[type]

	FROM sys.allocation_units AS tblAU

	INNER JOIN sys.partitions AS tblP

			ON tblAU.container_id = tblP.hobt_id 
			AND 
			(
				   tblAU.[type] = 1 
				OR tblAU.[type] = 3
			)

	where  tblP.[object_id] = @objectID


	UNION ALL

	SELECT 
			  object_id
			, index_id
			, allocation_unit_id
			, tblAU.[type]

	FROM sys.allocation_units AS tblAU

	INNER JOIN sys.partitions AS tblP 

		ON tblAU.container_id = tblP.[partition_id]
		AND tblAU.[type] = 2

	where  tblP.[object_id] = @objectID

)
, cteOSBufferDescriptors
as
(

	SELECT 
			  tblOSBD.allocation_unit_id
			--, tblOSBD.page_type
			, [NumberofPages]
				= count(*)
			, [bufferMB]
				= CONVERT
					(
						  DECIMAL(12,2)
						, CAST(
									COUNT(*) as bigint
							  )
							* CAST(8 as float)/1024
					) 

	FROM sys.dm_os_buffer_descriptors tblOSBD

	group by
			  tblOSBD.allocation_unit_id
			--, tblOSBD.page_type

)

, cteUsageTotal
(
	  [object_id]
	, [userUsage]
)
as
(

	SELECT 
			  [object_id]
			, [userUsage]
				= sum(
						      tblSIUS.[user_seeks]
							+ tblSIUS.[user_scans]
							+ tblSIUS.[user_lookups]
					)

	from   sys.dm_db_index_usage_stats tblSIUS

	where  tblSIUS.[object_id] = @objectID

	group by
			[object_id]


)

select

			  tblI.[name]

			, tblI.index_id

			, [userSeeks]
				= tblSIUS.[user_seeks]

			, [userScans]
				= tblSIUS.[user_scans]

			, userLookups
				= tblSIUS.[user_lookups]

			, [%ofReadToOthersInObject]
				= cast
					(
						(
							  tblSIUS.[user_seeks]
							+ tblSIUS.[user_scans]
							+ tblSIUS.[user_lookups]
						)	
						* 100.00
						/ 
						[userUsage]

						as decimal(10,2 )
					)

			, userUpdates
				= tblSIUS.[user_updates]

			, [readToUpdateRatio]
				= 
					cast
						(
							(
								(tblSIUS.[user_seeks] + tblSIUS.[user_scans] + tblSIUS.[user_lookups]) 
								   * 1.00
								/ NULLIF(tblSIUS.[user_updates], 0)
							)
							as decimal(10, 2)
						)

			--, tblAU.[allocation_unit_id]

			--, tblOSBD.[page_type]

			, tblOSBD.[NumberofPages]

			, tblOSBD.[bufferMB]

from  sys.indexes tblI

left outer join sys.dm_db_index_usage_stats tblSIUS

	on   tblSIUS.object_id = tblI.object_id
	and  tblSIUS.index_id = tblI.index_id

left outer join cteUsageTotal cteUT
		on tblSIUS.[object_id] = tblSIUS.[object_id]

left outer join  sys.partitions tblSP

	on   tblSIUS.object_id = tblSP.object_id
	and  tblSIUS.index_id = tblSP.index_id

left outer join cteAllocationUnit tblAU

	on   tblSIUS.object_id = tblAU.object_id
	and  tblSIUS.index_id = tblAU.index_id


left outer join  cteOSBufferDescriptors tblOSBD

	on   tblAU.[allocation_unit_id]	= tblOSBD.[allocation_unit_id]

where tblSIUS.[database_id] = @databaseID 
and   tblSIUS.[object_id] = @objectID

order by
			 (
					tblSIUS.[user_seeks]
				+ tblSIUS.[user_scans]
				+ tblSIUS.[user_lookups]
			 ) desc	

			, tblOSBD.[bufferMB] desc

 

Output

IndexInMemoryStats

Explanation

  1. %ReadToOthers
    • Index
      • INDX_UserActivity_CoverIndex1 :- 53.48%
      • PK_UserActivity :- 43.24%
      • INDX_UserActivity_CoverIndex2 :- 3.30%
    • INDX_UserActivity_CoverIndex2
      • It looks like we can disable INDX_UserActivity_CoverIndex2 as it is only been used by 3.3% of overall
      • We will free up 139 MB of current memory uptake
      • We will also not have to update the index when adding records to the popular table

 

Post Secondary Non-Clustered Index Delete

Query Plan

QueryPlan

Statistics I/O

StatisticsIO

 

Commendation

I am going to have to plug Jason Strate’s nice work.

Links

  1. Strate SQL – Index Resources
    Link
  2. That’s Actually A Duplicate Index
    Link

 

Summary

Removing that lone secondary index, reduces the clutter a bit.

SQL Server – Delete and Worktable

Background

This morning noticed a lot of blocking, wanted to go back and dig more into the lead blocker.

 

Code

 

Delete Code

Here is the failing code ….

Outline

  1. Delete Record

Actual Code

 

declare @ID varchar(100)

set @ID = 'daniel@gmail.com' 

delete 
from [dbo].StudentVoiceVerification 
where [studentID] = @ID 

Query Plan

QueryCost

 

Statistics I/O

StatisticsIO

 

Measure impact of Foreign Key Constraint

Outline

  1. Delete Record
  2. Disable Foreign Key
  3. Delete Record
  4. Enable Foreign Key
  5. Delete Record

Actual Code



set nocount on;
go

declare @ID varchar(100)

set @ID = N'daniel@gmail.com'


begin tran			

	print ''
	print 'Foreign Key Exist/Enabled/Delete Step'
	print '===================================='

	delete 
	from   [dbo].StudentVoiceVerification
	where  [studentID] = @ID

	print ''
	print 'Foreign Key Exist - Disabling Step'
	print '=================================='

	ALTER TABLE [dbo].[StudentVoiceVerificationLog]
		NOCHECK CONSTRAINT [FK_StudentVoiceVerificationLog_StudentVoiceVerification]

	print ''
	print 'Foreign Key Exist/Disabled/Delete Step'
	print '====================================='

	delete 
	from   [dbo].StudentVoiceVerification
	where  [studentID] = @ID

	print ''
	print 'Foreign Key Exist - Enabling Step'
	print '=================================='
	ALTER TABLE [dbo].[StudentVoiceVerificationLog]
		with CHECK CHECK CONSTRAINT [FK_StudentVoiceVerificationLog_StudentVoiceVerification]

	print ''
	print 'Foreign Key Exist/Enabled/Delete Step'
	print '====================================='

	delete 
	from   [dbo].StudentVoiceVerification
	where  [studentID] = @ID

rollback tran
go

 

Execution Plan

QueryPlan

 

Explanation

  1. Step : Delete Record ( Condition of Foreign Key : Exist & Enabled )
    • Cost 35%
    • dbo.StudentVoiceVerification ( Targeted Table)
      • ClusteredIndexDelete
    • dbo.StudentVoiceVerificationLog ( Referencing Table )
      • IndexSeek
  2. Step : Delete Record ( Condition of Foreign Key : Exist & Disabled )
    • Cost 30%
    • dbo.StudentVoiceVerification ( Targeted Table)
      • ClusteredIndexDelete
  3. Step : Delete Record ( Condition of Foreign Key : Exist & Enabled )
    • Cost 35%
    • dbo.StudentVoiceVerification ( Targeted Table)
      • ClusteredIndexDelete
    • dbo.StudentVoiceVerificationLog ( Referencing Table )
      • IndexSeek

 

Statistics I/O

 

QueryPlan

 

Explanation

  1. Step : Delete Record ( Condition of Foreign Key : Exist & Enabled )
    • Worktable
    • StudentVoiceVerification
  2. Step :- Disable Foreign Key
    • Metadata only operation
  3. Step : Delete Record ( Condition of Foreign Key : Exist & Disabled )
    • StudentVoiceVerification
  4. Step :- Enable Foreign Key
    • StudentVoiceVerificationLog
    • StudentVoiceVerification
  5. Step : Delete Record ( Condition of Foreign Key : Exist & Enabled )
    • Worktable
    • StudentVoiceVerification

Query Plan Complexity

The presence of Dependant Foreign Key results in a more complex query plan; as it has this additional operators:

  • Index Seek on the dependant table
  • Nested Loops/ Left Semi Join
    • Join the results of the Delete on targeted table and dependant table
  • Assert Operator

 

AssertOperator-Cropped

 

Introspection

Foreign Key

Let us review the Foreign Keys that are dependant on our targeted table.

Code

Syntax


exec sp_fkeys  
	  @pktable_name = table-name
	, @pktable_owner = schema

Sample


exec sp_fkeys  
	  @pktable_name = 'StudentVoiceVerification'
	, @pktable_owner = 'dbo'

 

Output

ForeignKeyConstraint

Summary

Conclusively, we can say that worktable is traced back to validating the Foreign Key constraint relationship.

Also, the query plan is a bit more complex.

Plan Cache – Adhoc Shell Query

Background

In a recent post, we touched on the our attempts at finding at out whether our Plan Guides are working.

BTW, our SQL Engine is v2005.

When we queried sys.syscacheobjects we saw a lone parameterized query logged as a prepared statement.

But, also saw numerous Ad-hoc queries.

So what gives.

 

Research

Googling found us this gem in the book referenced below:

Microsoft SQL Server 2012 Internals
By Kalen Delaney, Craig Freeman
Troubleshooting Plan Cache Issues
https://msdn.microsoft.com/en-us/library/cc293620.aspx
Consider the same procedure being called dozens or hundreds of times. Remember that SQL Server 2005 will cache the adhoc shell query that includes the actual parameter for each individual call to the procedure, even though there may be only one cached plan for the procedure itself.

Instrumentation

Let us write a query and see what the DMVs says

Code



/*
	Credits:

		a) @ParameterizedPlanHandle
		   SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'
		   Parameterization and filtered indexes (part 1)
		   http://sqlblog.com/blogs/hugo_kornelis/archive/2014/01/19/parameterization-and-filtered-indexes-part-1.aspx

*/
set quoted_identifier on;

declare @sql            varchar(800)
declare @sqlFull        nvarchar(max)
declare @sqlParmAndText nvarchar(max)
declare @sqlWildcard    varchar(800)
 
declare @database       varchar(800)
declare @databaseWildcard   varchar(800)

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

DECLARE @maxNumberofRecords	    int
 
set @sqlFull = 'select email from Student where studentID=''daniel@gmail.com'''
set @sql = 'select email from Student'

set @maxNumberofRecords = 10

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


/*
	Get Query Template
*/
if (@sqlFull is not null)
begin

	exec sp_get_query_template 
					  @querytext = @sqlFull
					, @templatetext = @templatetext output
					, @parameters = @parameters     output


	set @sqlParmAndText = '(' + @parameters + ')' + @templatetext

	select 
		  [@parameters] = @parameters
		, [@templatetext] = @templatetext
		, [@sqlParmAndText] = @sqlParmAndText


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]
)
as
(

	select 
			  tblDECP.[refcounts]
			, tblDECP.[usecounts]
			, tblDECP.[plan_handle]
			, tblDECP.[size_in_bytes]
			, tblDECP.[cacheobjtype]
			, tblDECP.[objtype]
			, tblDest.[text]
			, [queryPlanAsXML]
				= tblDEQP.query_plan

	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

	where  (
 
				--Query does not target sys schema
				( tblDEST.[text]  not like '%sys.%' )
 
		   )


	and  (

			  (
						( objType = 'Prepared' )

					and (

							    ( tblDEST.[text]  = @sqlParmAndText )
							or  ( @sqlParmAndText is null )
						)

			  )


		)

	union all 

	select top (@maxNumberofRecords)
					  tblDECP.[refcounts]
					, tblDECP.[usecounts]
					, tblDECP.[plan_handle]
					, tblDECP.[size_in_bytes]
					, tblDECP.[cacheobjtype]
					, tblDECP.[objtype]
					, tblDest.[text]
					, [queryPlanAsXML]
						= tblDEQP.query_plan

	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

	where  (
 
				--Query does not target sys schema
				( tblDEST.[text]  not like '%sys.%' )
 
		   )


	and  (

			(

					( objType = 'Adhoc' )

				and (

							( tblDEST.[text]  like @sqlWildcard )
						or ( @sqlWildcard is null )

					)

			)


		)

)
select 
		  cteXML.[refcounts]
		, cteXML.[usecounts]
		, cteXML.[plan_handle]
		, [ParameterizedPlanHandle]
			= stmt.value
				(
					  '(@ParameterizedPlanHandle)'
					, 'varchar(64)'
				) 
		, cteXML.[size_in_bytes]
		, cteXML.[cacheobjtype]
		, cteXML.[objtype]
		, cteXML.[text]


		, [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)

order by
             cteXML.[usecounts] desc
		   , cteXML.[size_in_bytes] desc

 

Output

AdhocShellQuery

 

Explanation

  1. The prepared plan
    • Is referenced and used quite a bit ( refcount and usecount )
    • The queries text has both the parameter and query text
    • In its query plan, it has the the actual query plan
  2. The Ad-hoc plans
    • Referenced and used minimally ( refcount and usecount )
    • The size_in_bytes is smaller
    • The text has the full query text

 

Show Plan

If one digs into the Query Plans XML, one will see and notice the following

Prepared

QueryPlan-Prepared

AdHoc

QueryPlan-Adhoc

 

Explanation

  1. Prepared Statement
    • Actual Query Plan with Query’s Entity, Operator and Cost
  2. Adhoc
    • Actual Query’s Text
    • Just Query Operator

 

Query Plan

Prepared

Here are some sample Query Plans for Prepared Statements.

Query 1

QueryPlan-1-StudentID

 

Query 2

QueryPlan-2-StudentCourse

 

Explanation

  1. ParameterList
    • You want to pay attention to ParameterList section
    • It lists each argument referenced as ColumnReference elements
    • The arguments are positional
    • And, they contain the actual value that the Query Plan was compiled with

 

References

  1. Combining SQL and TEMPLATE Plan Guides
    Kalen Delaney | SQL Server Pro
    August 21, 2007
    Link

Damon Thompson – 2016/May

Sermons

  1. First Things First  ( Published on 2014-02-02,  Added on 2016-05-19 )
  2. Sexual Perversion/Authenticity ( Published on 2014-10-24,  Added on 2016-05-19 )
  3. The Discipline of Deliverance  (  Added on 2016-05-19 )
  4. The Secret mountain of God’s presence  ( Published on Friday 2012-04-24,   Added on 2016-05-19 )
    • Insight Versus Revelation ( Clip )
    • Full ( Full )
  5. Jonah   ( Published on Friday 2015-11-24,   Added on 2016-05-20 )
  6. A Word of Faith    ( Published on Friday 2014-11-04,   Added on 2016-05-23 )

In Depth

First Things First

  1. Law of reciprocity
    • A law is not principle
    • But, principles bind together to form a law
    • Principle of Sowing and Reaping
    • Relatable Dimension
      • Deep does not cry out to Shallow
      • And, Shallow does not cry out of Deep
      • You never attract what you want, you attract who you are
  2. Manipulation takes you out of Intimacy
    • Good idea leads to insecurities
    • Insecurities lead to manipulation
    • Manipulation cuts off intimacy
    • You are hot and now become my sister
    • Diminish role of bride, bride becomes the sister
  3. 3 Seps
    • Faith Responding
      • Leave Haran go to Canaan
        • Because God told him so
    • Faith Retreating
      • Diminish role of the bride
        • Because of famine left Canaan and went to Egypt
    • Faith Returning ( Genesis 13:1 )
      • Start believing the word again
      • Intimacy Restored
        • Blessing to follow intimacy, not replace intimacy
      • Where his tent at been at beginning ( Genesis 13:4 )

Sexual perversion/Authenticity

  1. The root is the tender plant out of dry ground
  2. God’s plan for Nineveh was to send a prophet. It was not to send a Seeker sensitive Church
  3. Sin
    • Indulging in what God forbids
    • Not fully given in what the word of God demands
  4. If you have no interest in helping anyone then the revival did not go deep enough
  5. True fathers give authorization for authenticity
  6. Those who know how it should be done, should never interrupt people doing it
  7. Person in Holiness and Legalism live the same lifestyle
    1. Legalism – Fear of Judgement
    2. Holiness – Fear Dove will leave
  8. The Dove remains ( John )
    • All told the same story about Jesus getting baptized by John
    • I saw the Spirit come down from heaven as a dove and remain on him (John 1:32 )
  9. Pastor Bill Johnson
    • If there was a dove on your shoulder and you did not want it to go away, you take every step with the dove in mind
  10.  I do not want a revival, if it will not plant holiness
  11. When you meet the standard, you begin to inherit the Power that the consequent of that Power
  12. Power is coming back, but repentant is coming first
  13. It is bad theology to say that miracle is gone
  14. Was in a store and asked what is the return policy?
    • God says I have a return policy, and if you return to me, there are things that must be returned to you
  15. It is illegal to use kingdom lingo and not have dominion
    • Apostolic Ministry and not be bearing Apostolic Ministry

 

Discipline of Deliverance

  1. Spoke of the life of Archbishop Vernon Ashe
  2. Weapons
    • Godly Living
      • 1st Timothy 6
        • Godliness is a mean of Gain
          • Who have been robbed of the truth and who think that godliness is a means to financial gain ( 1st Timothy 6:5 )
    • Prayer

The Secret Mountain of God’s Presence

  1. Prayer Mountain
    • Transform our view of Life
    • Transform our view of God
  2. Watching Christian TV and saw a guy ( Segment 00:50:00 00:55:00 )
    • I know the way this man and I know the way he lives his life, why does he have so much revelation
    • Insight Versus Revelation
      • Insight
        • Consequence of the mind
        • Attracts you to a man
        • You uplift the man
        • He can have insight into any thing, he just happen to choose the Bible
      • Revelation
        • Attracts you to God
        • Lifts up God

Jonah

  1. God has a passion for a City
    • He sent a Prophet
    • He did not plan a seeker sensitive Church and give people Coffee and Donut
  2. All Sin is disobedience to God
  3. No one is
    • Testifying
    • Evangelizing
    • Praying for Sick
    • Really marrying each other
  4. Culture is keeping the Church from advancing
  5. Difference between now and early Church
    • Life Given
      • They have their lives
      • We give 45 minutes on Sunday, if that
    • Sold
      • Land
      • Houses
    • Broke bread and had all things in common
    • 3% of the American Church Tithe
      • Ready for God to come down and start pointing to each of us and say Not a Christian
  6. No submission to any authority
    • Being Free from religion
      • Does not mean talk about your Leader
  7. Sin
    • Indulging in what God says No to
    • Not being fully committed to what God calls us to
      • Disobedience to what God has called us to
      • Jonas was in Sin
        • He was not doing anything God called him not to do
        • But, he did not do what God called him to do
  8. Jonah Meaning
    • Name means Dove
    • John the Baptist baptised Jesus and the Holy Spirit come down as a Dove

 

A Word of Faith

  1. Burial Procession ( Luke 7:11 )
    • Large crowd went with him
    • Large crowd of the City was with her
    • Expect you to expect an intervention, even before the intervention
  2. Never let your situation, get you out of position
  3. Location is Beauty
    • You can be living in a City called beautiful, but living under intense pressure that is robbing you of the location
    • Peter & Paul
  4. We find ourselves spiritually lacking
    • Instead of changing Heavens, we change Churches
    • We change Churches, because we are not getting fed
    • If you still got to Church, to get fed, then you are communicating your infancy
    • You are not growing and so you can not help anyone
  5. Fresno, CA
    • Government has made
      1. Building not supposed to get
  6. Government can not ignore
  7. Jesus is doing what a 12-step program can not do
  8. Walking the Streets
    • Beautiful City is being robbed of her destiny
    • Light has stepped back and watched Darkness prevail
  9. John the Baptist
    • Darkness does not comprehend the Light
    • I am not the one, but there is someone coming behind me
  10. We change Churches, rather than to change Heaven
  11. Prove or Success of any Government
    • The validity of any Government is the Peace in which its citizens live
    • You can not be under God and still be living as you are in the Government of the evil one
    • Peace, Righteousness, and Joy in the Holy Ghost
    • Never allow the enemy to get you out of position
  12. Never allow the enemy to get you out of position
    • People walking into something that I am trying to get out of
    • You are saying “I rebuke the devil
    • And, God is saying the Devil will not take credit for this
    • You are going to bury something that I intend to resurrect
    • Quit on thing while God had resurrection in mind
    • Don’t put dirt on it yet