Microsoft – SQL Server – Cached Plans – Looking for Table Spools

Introduction

Many times I have taking the time to sing the praise of Microsoft SQL Server Dynamic Management Views (DMVs).

If MSFT has an idealistic view of Science and Technology, it is its openness in allowing Operational Engineers and Developers to see and question how well things are going.

And, then being able to push and argue it out in public.

There is one vendor that has a closed knit support group and even upon commenting one’s comment are quickly hidden. It is almost like why bother!

Personally, I am not really writing for any particular Vendor, but more so for the community.

Here is a query that I have being meaning to write out for a while, but I could not find a good code (via Google) to fashion mine from.

So what I am trying to do is to is to get a good, firm view around which queries are using Table Spools.

I know Jonathan Kehayias has probably written the most around “Extended Events”.  And, I know he probably has something I can use.  But, I do not have it like that as I do not always have friends in High places…So I can not call or look in his cheat sheets.

All I can do is “Check out his demo(es)” @ http://sqlblog.com/blogs/jonathan_kehayias/.

Specifically, I am trying to comb through his “XEvent a Day”.  I mean he has an entry for each day; and he did not take a Leap Feb.  

Taking about high achievers and extra credits –  He did all 31.

Here is what I came up for Spools…


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

select 

          tblIn.plan_handle
        , tblIn.PhysicalOp        
        , tblIn.LogicalOp                
        , tblIn.useCounts
        , tblSQLText.text
        , tblQueryPlan.query_plan

from
(

  SELECT 

          cp.plan_handle
        , avg(cast(cp.usecounts as bigint)) as useCounts
        , max(operators.value('@PhysicalOp','nvarchar(50)'))
               as PhysicalOp
        , max(operators.value('@LogicalOp','nvarchar(50)'))
               as LogicalOp

   FROM sys.dm_exec_cached_plans cp

   CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

   CROSS APPLY query_plan.nodes('//RelOp') rel(operators)

where 
(

  ---Look for Spools
  (

      --Spool (Physical)
      (operators.value('@PhysicalOp','nvarchar(50)')  like 'Table Spool')
   or (operators.value('@PhysicalOp','nvarchar(50)')  like 'Index Spool')

  )

  or

  (

    --Spool (Logical)
       (operators.value('@LogicalOp','nvarchar(50)')  like 'Eager Spool')
   or  (operators.value('@LogicalOp','nvarchar(50)')  like 'Lazy Spool')

  )

)        

group by

    cp.plan_handle

) tblIn

   CROSS APPLY sys.dm_exec_query_plan(tblIn.plan_handle) tblQueryPlan

   CROSS APPLY sys.dm_exec_sql_text(tblIn.[plan_handle]) tblSQLText

 order by tblIn.useCounts desc

A good reference for what Microsoft calls “Relational Operation Values” is available @

Using the USE PLAN Query Hint
http://msdn.microsoft.com/en-us/library/ms186954(v=sql.105).aspx

Here are the relevant entries for Spools – Physical and Logical Operation:
Relational Operator Values

Use Cases

What are the most probably causes for Table Spools

A) INSERT/SELECT from same table

For Halloween protection


use [tempdb]
go

if OBJECT_ID('dbo.Course') is not null
begin

    drop table dbo.[Course]

end
go

create table dbo.course
(
      [courseID] int
    , [courseName] sysname
    , [calendarYear] int
)
go

declare @year int
declare @yearNext int

set @year = DATEPART(year, getdate())
set @year = @year + 1

insert into dbo.[course]
([courseID], [courseName], [calendarYear])
select 1010, 'CS100', @year

--triggers Halloween Protection
insert into dbo.[course]
([courseID], [courseName], [calendarYear])
select courseID, [courseName], @year + 1 
from dbo.course 
where calendarYear = @year 

drop table dbo.[course] 
go

Screen Dump

Table Spool - Haloween Protection - Insert--select

B) When accessing and storing data from System Tables

 

 


set nocount on;

	declare @databaseMirroringState TABLE
	(
		  [id] int not null identity(1,1)
		, [DatabaseName] sysname not null
		, [mirroringState] sysname not null
		, [mirrroringSafetyLevel] sysname null

	)

        if OBJECT_ID('tempdb.dbo.#databaseMirroringState') is not null
        begin

          drop table #databaseMirroringState

        end

	create table #databaseMirroringState
	(
		  [id] int not null identity(1,1)
		, [DatabaseName] sysname not null
		, [mirroringState] sysname not null
		, [mirrroringSafetyLevel] sysname null

	)

	select 

	  	  tblD.name
		, tblDM.mirroring_state_desc
                , tblDM.mirroring_safety_level_desc

	from   sys.databases tblD

	  inner join sys.database_mirroring tblDM

	     on tblD.database_id = tblDM.database_id					

	where tblDM.mirroring_state_desc is not null;


	insert into @databaseMirroringState
	(
		  [DatabaseName]
		, [mirroringState]
		, [mirrroringSafetyLevel]
	)

	select 
	  	  tblD.name
		, tblDM.mirroring_state_desc
                , tblDM.mirroring_safety_level_desc

	from   sys.databases tblD

	  inner join sys.database_mirroring tblDM

	     on tblD.database_id = tblDM.database_id					

	where tblDM.mirroring_state_desc is not null


        insert into #databaseMirroringState
	(
		  [DatabaseName]
		, [mirroringState]
		, [mirrroringSafetyLevel]
	)

	select 

  		  tblD.name
		, tblD.mirroring_state_desc
                , tblD.mirroring_safety_level_desc

	from   sys.databases tblD

  	  inner join sys.database_mirroring tblDM

		    on tblD.database_id = tblDM.database_id					
	where tblDM.mirroring_state_desc is not null

    drop table #databaseMirroringState

    return

Querying and Capturing data in System Tables

Table Spool - Persising results of quering system tables

  • Noticed that Table Spools were occurring when we did not expect it to occur
  • Traced it to a piece of code where we are capturing the results of accessing system tables
  • If we only display the results of querying the system table, we do incur the Table Spool penalty

BTW, if you take a little time and review Query Plans, you will see that a lot of DMVs are just interfaces to a main table.

ie sys.databases and sys.database_mirroring is merely an interface to sys.sysdbreg

Kalen Delaney has a good coverage in:

Geek City : The Resource Database

http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

 

C) Merge Statement

Merge Statements causes Table Spools, as well.

Here is a DDL and a DML.

 

DDL



if OBJECT_ID('dbo.customerMerge') is not null
begin
    drop table [dbo].[customerMerge] 
end    
go	

CREATE TABLE [dbo].[customerMerge]
(

    [customerID] [varchar](30) NOT NULL,
    [regionName] [varchar](50) NULL,

    [InsertTime] [datetime] 
        CONSTRAINT defaultCustomerMergeInsertTime 
          DEFAULT (getutcdate()) NULL,

    [salesDate] [datetime] not null 
        constraint defaultCustomerMergeSalesDate
          DEFAULT (getutcdate()),

    [salesYear]  
     as cast(isNull(datepart(year,[salesDate]), 1990) as smallint) 
        persisted not null,

 	CONSTRAINT [PK_customer_merge] PRIMARY KEY NONCLUSTERED 
	(
		  [customerID] ASC
		, [salesYear]
	)
	WITH (
	              PAD_INDEX  = OFF
	            , STATISTICS_NORECOMPUTE  = OFF
	            , IGNORE_DUP_KEY = OFF
	            , ALLOW_ROW_LOCKS  = ON
	            , ALLOW_PAGE_LOCKS  = ON
	            , FILLFACTOR = 50
            ) 
	--ON Default
)  --Default
go

DML


begin tran

    merge into [dbo].[customerMerge] as Target
    USING (
            VALUES 
                    (
                          'A1' --as customerID
                        , 'CA' --as RegionName
                        , getutcdate() --as InsertTime
                        , getutcdate() --as SalesDate                    
                    )
            )        
             As Source
                    (

                          [NewCustomerID]
                        , [NewRegionName]
                        , [NewInsertTime]
                        , [NewSalesDate]
                    )       

    ON Target.[customerID] = Source.[NewCustomerID]

    WHEN MATCHED THEN

        UPDATE
        SET  [regionName] = Source.[NewRegionName]

    WHEN NOT MATCHED THEN
    INSERT 
    (
          [customerID]
        , [regionName]
        , [InsertTime]
        , [SalesDate]
    )    
    VALUES
    (

          [NewcustomerID]
        , [NewRegionName]
        , [NewInsertTime]
        , [NewSalesDate]
    )    
    ;   

rollback tran


Screen shot:

Table - Spool - Merge Statements

D) When an output clause is used during Inserting\Update of a table that contains an un-persisted computed column.

Please read more here…

https://danieladeniji.wordpress.com/2013/05/08/technical-microsoft-sql-server-table-spool-causation-indexes-on-non-persisted-columns/

E) Insert Into Table Variable / from Scaler function



	set ansi_padding on;
	set statistics io on;
	set nocount on;

	declare @accountHolder TABLE
	(
		  [id]			int not null
		, [entityID]	nvarchar(60) not null
		, [firstname]	nvarchar(60) null
		, [lastname]	nvarchar(60) null
	)

begin

	-- Open the symmetric key with which to encrypt the data.
	OPEN SYMMETRIC KEY [SymmetricKeyPIA]
	   DECRYPTION BY CERTIFICATE  [certPIA];

		select

			 tblAH.id

		   , cast(
					DECRYPTBYKEY
					   (
							   tblAH.[entityID]
							 , 1
							 --, CONVERT(varbinary(8000), tblAH.[id])
							, dbo.Salt(tblAH.[id])
						)
					as varchar(255)
			) as [entityID]
 
		from    [dataEncrypted].[accountHolder] tblAH
 
		order by tblAH.[id] asc

		print replicate('*', 140)

		insert into @accountHolder
		(
			  [id]
			, [entityID]
		)
		select

			 tblAH.id
		   , cast(
					DECRYPTBYKEY
					   (
							   tblAH.[entityID]
							 , 1
						 	, dbo.Salt(tblAH.[id])
						)
					as varchar(255)
			) as [entityID]


		from    [dataEncrypted].[accountHolder] tblAH
 
		order by tblAH.[id] asc

		print replicate('*', 140)

		insert into @accountHolder
		(
			  [id]
			, [entityID]
		)
		select

			 tblAH.id
		   , cast(
			     DECRYPTBYKEY
			     (
				   tblAH.[entityID]
				 , 1
			 	--, dbo.Salt(tblAH.[id])
				, itvfS.[Salt]
			    )
			    as varchar(255)
			) as [entityID]


		from    [dataEncrypted].[accountHolder] tblAH

			  cross apply [dbo].[ITVF_Salt](tblAH.[id]) itvfS

 
		order by tblAH.[id] asc

	-- Close symmetric key
	CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

end

go

 

InsertIntoTableVaribleFromSelectThatIncludesScalerFunction

 


Table 'accountHolder'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
********************************************************************************************************************************************
Table '#0D7A0286'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'accountHolder'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
********************************************************************************************************************************************
Table '#0D7A0286'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'accountHolder'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 

Worst Offenders

Vendor – Microsoft

sp_tables

msdb.dbo.sp_sqlagent_get_perf_counters

  • Uses quite a bit of Table and Index spools
  • Violates Microsoft’s own advisory about using sp_ in a user database

#am_generate_waitstats

  • Activity Monitor ( I think)

#am_get_querystats

 Activity Monitor ( I think)

sp_sqlagent_get_perf_counters

Review

BTW, if you make some changes, and you want to clear your “Procedure Cache” and see how things play out thereafter, please do so via:


DBCC FREEPROCCACHE;

But, please do not clear everything all at once.  Rather clear pointed ones; get specific plan handle(s) and use those Plan Handles as the lone argument to FreeProcCache.

 


DBCC FREEPROCCACHE(plan_handle);

 

References:

 

 

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