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
the COVERING 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 – II [Reuse of the Execution Plan]
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.
Posted
by: MR. JOYDEEP DAS
No comments:
Post a Comment