SQL Server Execution Plan – Simplifying : Part 10

Hi Friends,

In last two blog posts of this series Part 8 and Part 9 I’ve explained SQL Server execution plan using INSERT, UPDATE statements. You guessed it right; today we would cover kind of execution plan created using DELETE statement.

Let’s run following code and find out the execution plan;

1_SQL_Server_Simplifying_execution_plans_Part10

2_SQL_Server_Simplifying_execution_plans_Part10

Yes, the plan is quite big but we shall cover them one by one wherever required. Deleting a row or a set of rows is always related to the underlying table as we can understand, that to say any tables related to the primary key of the table on which we are removing data needs to be checked to ensure integrity of data.

Ok, let us start reading the plan on the right and top to bottom; at first we see a Clustered Index Delete operator. When you look at the seek predicate (image below) to DELETE operation is as following;

Prefix: [AdventureWorks2008R2].[Person].[Address].AddressID = Scalar Operator-(CONVERT_IMPLICIT(int, [@1],0))

   

3_SQL_Server_Simplifying_execution_plans_Part10

In essence this means that, a parameter @1 was used to lookup AddressID whereas we didn’t use any parameter in our code and have queried with AddressID = 50. This is an indication that query engine is generating a reusable plan as per the rules of simple parameterization.

After this there are Non-Clustered Index Seek, Clustered Index Scan combined with Nested Loop Joins. These Nested Loop Joins are Left Semi Joins. These operators will return a value if the join predicate between two tables matches or in case no join predicate supplied and each one returns value.

Going further left, there is an Assert operator which helps to check referential integrity from the values returned from each Join, all the tables related to the table from which we’re attempting to delete data. If there aren’t any, DELETE is completed and if they return any value and error is generated and DELETE operation aborted like following;

Msg 547, Level 16, State 0, Line 1

The DELETE statement conflicted with the REFERENCE constraint “FK_BusinessEntityAddress_Address_AddressID”. The conflict occurred in database “AdventureWorks2008R2”, table “Person.BusinessEntityAddress”, column ‘AddressID’.

The statement has been terminated.

At the end we see the generic T-SQL Language Element Catchall operator (image below), which notifies us a DELETE operation.

4_SQL_Server_Simplifying_execution_plans_Part10

I hope this explains DELETE operation plan. I will conclude this series on my next post, see you soon.

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →