SQL Server Execution Plan – Simplifying : Part 9

Dear Friends,

In my previous blog post, Part 8 of this series we analyzed SQL Server execution plan using INSERT statement. Today, we will try UPDATE statement followed by explanations.

Let us run an UPDATE statement on Person.Address table;

1_SQL_Server_Simplifying_execution_plans_Part9

Reading from Right, first operator is Non-Clustered Index scan which scans all necessary rows from non-clustered index scanning through them. Purpose of this operator is to find out all the rows WHERE City IS ‘Bothell’ (image below). This could be a pointer that this table requires a better indexing to boost up performance.

2_SQL_Server_Simplifying_execution_plans_Part9

Coming next operator TOP which used in UPDATE execution plan to restrict row count limit in case any. In our case we did not use TOP clause in UPDATE statement hence no limits has been enforced. Next Table Spool which takes each of the rows to be updated and stores them in hidden temporary object in tempdb database.

   

The next three operators are all Compute Scalar operators, which we have seen before. In our case, they are used to evaluate expressions and to produce a computed scalar value, such as the GETDATE() function used in UPDATE query. Now, when we get to the core of the UPDATE statement Clustered Index Update operator is observed (image below). In our example case, the values being updated are part of a clustered index. This operator identifies the rows to be updated, and performs required update operation.

3_SQL_Server_Simplifying_execution_plans_Part9

At the end, we see the generic T-SQL Language Catchall operator (image below), which notifies us that an UPDATE operation has been completed.

4_SQL_Server_Simplifying_execution_plans_Part9

I hope this explains UPDATE operation execution plan in our example. Thanks for reading this, I will be back with next part on this series very soon, stay tuned.

 

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 →

Leave a Reply

Your email address will not be published.