SQL Server Execution Plan – Simplifying : Part 8

Dear Friends,

SQL Server engine generates SQL Server execution plan for all SQL queries in order to figure out best path of execution when we submit any request. In my previous blogs, I’ve covered SELECT statements; today we will take a look at execution plan for INSERT statement.

For simplicity, I picked up Person.[Address] table. Following is the insert statement that we are going to analyze today;

1_SQL_Server_Simplifying_execution_plans_Part8

Reading execution plan from right a new operator Constant Scan the insert statement starts with. As per MSDN ‘The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used after a Constant Scan to add columns to a row produced by the Constant Scan operator’.

In our query, it is building a row in order for the next two Compute Scalar operators to have a place to add output. There are two Compute Scalar operators that are appearing in our INSERT statement. First of them, calls getidentity which is the exact moment when the identity value is generated for the row to be inserted. This is the first thing that is done in the plan.

Next Compute Scalar operation occurs which outputs a series of placeholders for the rest of the data to be inserted and creates the new uniqueidentifier value and the date and time using the function GETDATE(). These are then passed to Clustered Index Insert which holds the majority of the cost of this plan. Output value of this INSERT statement Person.Address.StateProvinceId is passed to the Nested Loop Join which received another input from Clustered Index Seek against Person.StateProvince table.

   

2_SQL_Server_Simplifying_execution_plans_Part8

3_SQL_Server_Simplifying_execution_plans_Part8

In simple words, there was a read during INSERT to check for the referential integrity on the FK of StateProvinceId. Once checked, join outputs new expression which is tested by Assert Operator which verifies that a particular condition exist or not. Here in our case it checked if Expr1015 equals to zero i.e. that the data that was attempted to be inserted into the Address.StateProvinceId field matched a chunk of data in the Person.StateProvince table part of the referential integrity check.

Stay tuned, I’ll be back soon with the next one on this series.

 

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 →