SQL Server : Table Spool – Most likely problematic SQL

Here are a few various SQL Statements that will likely lead you down the “table spool” blindsides:

Linked Server

Linked Server – Insert

TableSpoolAndRemoteInserts

Link7ed Server – Delete

RemoteScansAndTableSpool

Indexed Columns Updated

TableSpools

 

 

Deletes

Deletes – Two NonClustered Index And Clustered Index

Queries

declare @studentID varchar(100)
declare @courseID  varchar(100)
declare @timestamp datetime

begin tran

	set @timestamp = getdate()

	--Partially defined Non-Clustered Index
	delete tblSSS
	from   dbo.StudentScoringSheet tblSSS
	where  tblSSS.[studentID] = @studentID

	--fully defined Non-Clustered Index
	delete tblSSS
	from   dbo.StudentScoringSheet tblSSS
	where  tblSSS.[studentID] = @studentID
	and    tblSSS.[courseID] = @courseID

	--Partially defined Clustered Index
	delete tblSSS
	from   dbo.StudentScoringSheet tblSSS
	where  tblSSS.[time_stamp] = @timestamp

rollback tran

Indexes

Indexes

Query Plans

ExecutionPlans

 

Statistics Profile

 

StatisticsProfile

Explanation
  1. The first operation is a Table Spool
  2. And, the next is an Index Delete on the Index that is not used for the Search
  3. The Clustered Index is removed
  4. And, another Table Spool
  5. And, the Delete on the used Index Key

 

Deletes – Single NonClustered Index And Clustered Index

Queries

declare @studentID varchar(100)
declare @courseID  varchar(100)
declare @timestamp datetime

begin tran

	set @timestamp = getdate()

        drop index [dbo].[StudentScoringSheet].[idx_StudentScoringSheet_studentID_courseID]

	--Partially defined Non-Clustered Index
	delete tblSSS
	from   dbo.StudentScoringSheet tblSSS
	where  tblSSS.[studentID] = @studentID

	--fully defined Non-Clustered Index
	delete tblSSS
	from   dbo.StudentScoringSheet tblSSS
	where  tblSSS.[studentID] = @studentID
	and    tblSSS.[courseID] = @courseID

	--Partially defined Clustered Index
	delete tblSSS
	from   dbo.StudentScoringSheet tblSSS
	where  tblSSS.[time_stamp] = @timestamp

rollback tran

Indexes

singleNonClusteredIndex

 

Query Plans

QueryPlan

Statistics Profile

StatisticsProfile

 

Explanation
  1. First an Index Seek
  2. The Clustered Index Delete
  3. And, the Delete on the used Index Key
  4. Keep in mind no Table Spool

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