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;
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.
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.
At the end, we see the generic T-SQL Language Catchall operator (image below), which notifies us that an UPDATE operation has been completed.
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 Twitter | Follow me on FaceBook
