SQL Server Execution Plan – Simplifying : Part 3

Dear Friends,

If you are reading this post before Part1 and Part2 ; I would recommend you to please read them first where I discussed on Clustered Index Scan, Clustered Index Seek and Non-clustered Index Seek to have better understanding on the SQL Server Execution Plan series.

Ok, today we will cover up following operators;

  • Key LookUp
  • Table Scan

Key LookUp

Let me modify my query that was used in Non-Clustered Index Seek as shown following, then explaining KeyLookUP;

1_SQL_Server_Simplifying_execution_plans_Part3

Okay, now you get a query plan as shown below. Yes, a plan with multiple operations.

2_SQL_Server_Simplifying_execution_plans_Part3

3_SQL_Server_Simplifying_execution_plans_Part3

We see a Non-clustered Index Seek on IX_SalesOrderDetail_ProductID which is a Non-Unique, Non-Clustered Index. This is a non-covering index i.e. it does not contain all the columns that required to be returned by the query. This is forcing optimizer to read the index as well as read clustered index to get into all data it needs to return back. Let us now again look at the query plan once before going further;

4_SQL_Server_Simplifying_execution_plans_Part3

   

Here, you can see that clustered key PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID used to return desired result-set. To explain further, Key lookups are book mark look ups on tables which have a cluster index defined. This is the answer to optimizer when it cannot retrieve the required rows in a single operation and had to use the clustered key to fetch intended results. This is an indication to us to start thinking covering/included index which might benefit query performance. Benefit is, covering or included index is, they will cover all the columns of each row and will be easily located in the index if defined correctly. As we talk Key lookup, this is always accompanied by Nested Loop join as seen below, this does not indicate any query performance issues, and this is just used to combine the rows of Non-Clustered Index Seek and Key look up. If we had a covering index/included defined then this wouldn’t have appeared on query plan.

5_SQL_Server_Simplifying_execution_plans_Part3

Table Scan

Let me run a select statement on dbo.DatabaseLog then explain though this is simple of all.

6_SQL_Server_Simplifying_execution_plans_Part3

7_SQL_Server_Simplifying_execution_plans_Part3

This indicates that, required rows are returned by scanning entire table. Table scan can occur if there are no supporting indexes defined on the table and query has to iterate through each row in order to return result set. When majority of the rows to be returned from any table irrespective or not it have an index defined, it is faster for query optimizer to scan through every row then return them instead index is looked for presence of each rows. If numbers of rows are less, table scan is not a problem but if table is large, definitely it is time to sit and re-design table by defining appropriate index, re-write query so on.

Time for today friends; will be back very soon, watch out this space for more.

 

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.