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.

One thought on “SQL Server – Delete and Worktable

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