Understanding of Execution Plan - III - B [ The OPERATORS ]

Introduction

IN my previous Article we just focus about Cluster Index Scan and Clustered Index Seek operators. Continuing my Article series named Understanding of Execution Plan – part III here we are moving with some others operators. Please be focus with our article that you can learn with Execution Plan well.

Non Clustered Index Seek

The operation is a Seek operation against a Non Clustered Index. The operation is effectively no difference with Clustered Index Seek but the only data available is that which is stored in the index itself.

Like a Clustered Index Seek, a Non Clustered Index Seek uses an index to look up the required rows. Unlike a Clustered Index Seek, a Non Clustered Index Seek has to use a non-clustered index to perform the operation. Depending on the query and index, the query optimizer might be able to find all the data in the non-clustered index. However, a non-clustered index only stores the key values; it doesn't store the data. The optimizer might have to look up the data in the clustered index, slightly hurting performance due to the additional I/O required to perform the extra look up.

When Need Covering Index

If the SELECT statement contains some columns of Clustered Index or Any columns that is not a part of non clustered index, in this situation the Non Clustered Index seek cannot be performed an we always get the Clustered Index Scan. To get the Non clustered index Seek we must use theCOVERING Index.

CREATE INDEX IX_NONCLUST_EMPNAME ON
tbl_EMPLOYEERECORD(EMPNAME)
INCLUDE (EMPID, EMPGRADE, SALARY)

Property Seek Predicates is important here and we got all information from it.

Key Lookup

A Key Lookup operator is required to get data from the heap or the clustered index, respectively, when a non-clustered index is used, but is not a covering index.

First operation we can see here the Index Seek against the Non clustered Index named IX_NONCLUST_EMPNAME

This is a non-unique, non-clustered index and, in the case of this query, it is non-covering. A covering index is a non-clustered index that contains all of the columns that need to be referenced by a query, including columns in the SELECT list, JOIN criteria and the WHERE clause.  

Since this index is not a covering index, the query optimizer is forced to not only read the non-clustered index, but also to read the clustered index to gather all the data required to process the query. This is a Key Lookup and, essentially, it means that the optimizer cannot retrieve the rows in a single operation, and has to use a clustered key to return the corresponding rows from a clustered index.

In the tool tips of the Index seek the Output List and Seek Precedence is Important. If we look at the Seek Precedence carefully we find that

Seek Keys[1]: Prefix: [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPNAME = Scalar Operator('Joydeep Das')

As we are using in WHERE Clause EMPNAME = ‘Joydeep Das’

If we write LIKE ‘Joydeep Das’

Seek Keys[1]: Start: [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPNAME >= Scalar Operator('Joydeep Das'), End: [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPNAME <= Scalar Operator('Joydeep Das')

If we write LIKE ‘Joydeep%’

Seek Keys[1]: Start: [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPNAME >= Scalar Operator('JoydeeØþ'), End: [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPNAME < Scalar Operator('JoydeeQ')

How it change the code.

Here from Output List we find the

[PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPID, [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPNAME

Now we look at the tool tips of the Key Lookup on Clustered Index named PK__tbl_EMPL__14CCD97D5D626C84

Here in the Output list we find

[PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPGRADE, [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].SALARY

In Seek Precedence we find

Seek Keys[1]: Prefix: [PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].EMPID = Scalar Operator([PRACTICE_DB].[dbo].[tbl_EMPLOYEERECORD].[EMPID])

If this table had been a heap, a table without a clustered index, the operator would have been a RID Lookup operator. RID stands for row identifier, the means by which rows in a heap table are uniquely marked and stored within a table. The basics of the operation of a RID Lookup are the same as a Key Lookup.

Related Reference

Understanding of Execution Plan [What happened When SQL statement Execute]

http://www.sqlknowledgebank.blogspot.in/2014/06/understanding-of-execution-plan-what.html

Understanding of Execution Plan – II [Reuse of the Execution Plan]

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-ii.html

Understanding of Execution Plan – III - A [ The OPERATORS ]

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-the.html

Summary

In our next level we are going to discuss about more operator one by one. Hope you like it and need your valuable comments related to it.