Microsoft SQL Server Knowledge Bank: Understanding of Execution Plan

Sunday, 19 October 2014

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

Introduction

Contusing the series of my Article related to Understanding of Execution Plan, here we are trying to look some more operations. Hope u can understand it and like it. We need more comments from our readers that all of us can share knowledge and understand the complexity of Execution plan well.

Table Scan

Table Scan occurs with Heap Table only, the table that does not have any clustered index. With clustered Index we can get the Clustered Index Scan which is more or less same as Table scan.

SELECT *

FROM tbl_EMPLOYEERECORD;

The Table Scan occurs for several reasons and the most common is if there is no useful index is there and to retrieve the desired records the query optimizer must search every row. Another common reason is that if we want to retrieve all records from table.

Please remember that, If the table have small number of records then the table scan is not a problem.

RID Lookup

RID Lookup is the heap equivalent of the Key Lookup operation.

As was mentioned in our previous article, non-clustered indexes don't always have all the data needed to satisfy a query. When they do not, an additional operation is required to get that data. When there is a clustered index on the table, it uses a Key Lookup operator. When there is no clustered index, the table is a heap and must look up data using an internal identifier known as the Row ID or RID.

If we specifically filter the results of our previous Database Log query using the primary key column, we see a different plan that uses a combination of an Index Seek and a RID Lookup.

CREATE NONCLUSTERED INDEX IX_NONC_EMPNAME

ON tbl_EMPLOYEERECORD(EMPNAME);

SELECT *

FROM tbl_EMPLOYEERECORD WITH(INDEX(IX_NONC_EMPNAME))

WHERE EMPNAME = 'Joydeep Das';

To return the results for this query, the query optimizer first performs an Index Seek on non clustered index columns on WHERE clauses. While this index is useful in identifying the rows that meet the WHERE clause criteria, all the required data columns are not present in the index.

If we look at the Tool Tip for the Index Seek, we see the value Bmk1000 in the Output List. This Bmk1000 is an additional column, not referenced in the query. It's the key value from the non-clustered index and it will be used in the Nested Loops operator to join with data from the RID Lookup operation.

Next, the query optimizer performs a RID Lookup, which is a type of Bookmark Lookup that occurs on a heap table (a table that doesn't have a clustered index), and uses a row identifier to find the rows to return. In other words, since the table doesn't have a clustered index (that includes all the rows), it must use a row identifier that links the index to the heap. This adds additional disk I/O because two different operations have to be performed instead of a single operation, which are then combined with a Nested Loops operation.