Understand Query Execution Plan- Index Scan, Index Seek And Key Lookup

Will Indian Rupee Cross 55??



It is always recomended to use either Clustered or NonClustered Index on a table for better performance of query execution.
In this blog I  shall describe some important features of an Index which will help you to understand how Index plays a role in query execution i.e.-

 a-Clustered Index Scan
 b-Clustered Index Seek
 c-Key lookUp (In SQL Server 2008 it is KeyLookup and it was BookMark lookup in SQL Server 2005) 

Let's Do- I am creating a table by copying the data from an existing table EmployeeInfo, which is a very simple table having only four columns, see below (You can also create a fresh table)-
EmployeeInfo1
SELECT  * INTO EmployeeInfo1 FROM EmployeeInfo
EmployeeInfo1 table looks like-

Let's Do- Create Clustered Index on EmpID(INT, Identity) in EmployeeInfo1 with FillFactor=100
CREATE CLUSTERED INDEX X_EmpId_EmpInfo1ON EmployeeInfo1(EmpID) WITH (FILLFACTOR = 100)

Now if you will check the Execution Plan for the simple query on EmployeeInfo1-
SELECT * FROM EmployeeInfo1

You can see the storage engine doing the Clustered Index Scan means the storage engine scans the index in the optimal way, without necessarily sorting the output. Which consume more resources. It simply means that your table is not taking advantage of clustered Index.

But if you will see the Execution plan for query having where clause:
SELECT  Empname,Location
              FROM Employeeinfo1
                          WHERE Empname = 'Ab1001'

Here because the Argument column contains the WHERE clause, the query processor has requested that the output of the rows be returned in the order in which the clustered index has sorted it. This is called Index Seek. Index Seek is faster than Index Scan

Let's create now a NonClustered Index on EmpName in EmployeeInfo1 and see the changes in query Execution plan :

CREATE NONCLUSTERED INDEX IX_EmpId_EmpInfo1ON EmployeeInfo1(EMpname)

You can see Query Execution plan is using Index Seek and Key Lookup Operator both. Here the Argument column EmpName which has nonclustered index, is using a clustering key to look up the row data to retrieve the data in the clustered index, this is called Key Lookup (Operator). Key Lookup is always accompanied by a Nested Loops operator.

The use of a Key Lookup operator in a query plan indicates that the query might benefit from performance tuning e.g. performance of query plan can be improved by adding an NonClustered index on 'Location' column.

Let's add a nonclustered index on Location and see the quey execution plan-

CREATE NONCLUSTERED INDEX IX_EmpId_EmpInfo1ON

EmployeeInfo1(EMpname, Location)

Note- Here I re-created the same nonclustered index  IX_EmpId_EmpInfo1 on both the columns. If you will have different indexes on EmpName and Location column, your execution plan will still use the Key Lookup operator. To avoide this create same index on both the columns.

Query Execution Plan uses the Index Seek operation which take significantly less time than Key lookup Operator.

So having one clustered index and nonclustered index on almost all the columns may help for better performance of Query Execution Plan.

Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life

• Parental guidance how to control your kid in elementary school not obeying teacher
• Parental guidance for child development post terrible twos
• Parental guidance on how to handle child when they show their first infatuation at elementary school
• How to Handle fit of temper by fixing baby screen time
• Are Words Important

Reader of This blog also showed their interest in following blogs:

1. SQL Server DBA “Interview Questions And Answers”
2. SQL Azure Interview Questions and Answers Part – 1
3. SQL Azure Interview Questions and Answers Part – 2
4. Powershell Interview Questions and Answers

Regards,
http://tuitionaffordable.webstarts.com