Hey guys, I am Venkat and in this video, we'll discuss two very common operations
in sql server execution plans - Key lookup and RID lookup. Understanding these concepts
is very important when we are tuning SQL queries for performance. Now, to better understand what
we'll discuss in this video, you need to have a good knowledge of how indexes work in sql server
and heap tables. If you're new to these concepts, we've discussed them in our previous two videos.
I'll include the links in the description of this video. Consider this Employees table. There is no
clustered index on this table. This means the physical order in which data rows are stored
in this table is not guaranteed. A table without a clustered index is called a heap. A heap table
may have one or more non-clustered indexes. We discussed all these heap table concepts
in our previous video. Now, here is the sql script that creates Employees table and the second part
of the script here inserts 1 million rows into this table. This is the same script we have been
using in our previous videos in the series. I'll have it available in the description of this video
if you need it and notice, at the moment on this Employees table we don't have any indexes
and we want to execute this query where we are looking up an employee by "Name" and keep in mind,
there is no index at the moment to help this query and I also have "Include Actual Execution Plan"
turned ON. So, let's execute this query. We have the one row that we are expecting and if we take
a look at the execution plan, notice the operation is "table scan" and when I hover the mouse over,
notice "Number of rows read" and "Actual number of rows for all executions". To get
the one row we want, sql server has to read all the one million rows in this table, why?
because there is no index to help this query and if you notice, "Estimated subtree cost",
it is 11.4877 which is extremely high and there's a massive opportunity here to improve
the performance of this query by including a non-clustered index on the "Name" column.
Why on the "Name" column? Well, that's because we are looking up employee rows by Name,
so it makes sense to create a non-clustered index on this Name column and here is a query for that.
Let's execute it. Index creation successful. Now, let's execute this SELECT query again. Now,
notice in the execution plan, we have "index seek" operation instead of "table scan" and when I hover
the mouse over this operation, notice the values for "Number of rows read" and "Actual number of
rows for all executions". With the help of the non-clustered index on the "Name" column, SQL
server is able to read that one specific record we want and if you look at "Estimated subtree cost",
it is also massively reduced to 0.0032831, and in this execution plan, in addition to index seek
operation we also have RID lookup operation. RID stands for Row ID. Now, why do we need this Row
ID lookup operation? Well, in the non-clustered index we only have Name column and Row ID, but if
you take a look at our SELECT query, we want the rest of the columns as well, that is Employee Id,
Name, Email and Department, basically all the columns. So SQL server uses this Row ID from the
index and looks up the respective employee record in the Employees table. Hence, we need this Row ID
lookup operation here. Now, let me execute this SELECT query. Notice, in the select list,
we only have Name column. Now, it doesn't really make sense to request Name when you already know
the name of the employee, but let's just execute this query and see what's going to happen. Notice
now, in the execution plan, we only have index seek operation, we don't have that RID lookup
operation, why? Well, that's because, in the select list we're only requesting Name column now
and this non-clustered index already contains Name column. This means, the index has everything the
select query needs and this is the reason we don't need that RID lookup operation in the execution
plan now. Now, without a clustered index on this Employees table, the data rows are not guaranteed
to be sorted in any specific order, but if we create a clustered index for example on EmployeeId
column, the data in the table is physically stored in the sorted order of EmployeeId. So, if we zoom
into one of the leaf nodes at the bottom of the tree, notice employee data rows are sorted by
the ID column. This SQL query, creates clustered index on the ID column of our Employees table.
Index created. Now, let's execute our SELECT query again. If we take a look at the execution plan,
notice, we now have key lookup instead of RID lookup, why? Well, the data rows in the
Employees table are stored in the sorted order of Employee Id column, because we have a clustered
index on the Employee Id column now. We have a non-clustered index on the employee Name column.
So, if we zoom into one of the row locators at the bottom of this non-clustered index tree,
notice employee Names are sorted and stored in alphabetical order. Along with the Name,
we now have employee Id in the index. So, sql server uses this employee Id and with the help
of the clustered index, the database engine quickly locates the respective employee record.
Hence, we now have key lookup instead of rid lookup in the execution plan. Now, take
a look at this query. In the SELECT list we are requesting Id and Name columns. Let's execute it.
Notice, now in the execution plan we only have index seek, we don't have key lookup. This is
because, both Id and Name columns requested in the SELECT list are available in the index,
basically the database engine has everything it needs in the index itself, so there's no need
to look up the data row in the Employees table. This is the reason, we don't see "Key lookup"
in the execution plan. Now, let's execute this select query again. In the select list,
we are again requesting all the columns and if we take a look at the execution plan, notice along
with index seek we also have key lookup and if we take a look at the "Overall estimated subtree
cost" of this entire SELECT statement, it is 0.0065704. At this point, you might be wondering,
what is better for performance - Key lookup or RID lookup? Well, we have the "Estimated subtree cost"
for both key lookup and RID lookup side by side here, and as you can see, it's the same -
0.0065704. This obviously means, for this specific example,
both are same from performance standpoint. So, the bottom line is, which operation yields
better performance really depends on several factors like what's the query trying to do?
The columns you have in the index and in the SELECT list of your query, data distribution
etc. Only by testing and looking at the stats can tell us which yields better performance.
That's it in this video. Thank you for listening.