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.

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