SQL Server Execution Plan – Simplifying : Part 4

Friends,

As of now we covered SQL Server Execution Plan Clustered Index Scan on Part 1, Clustered Index Seek and Non-clustered Index Seek on Part 2 and Key LookUp and Table Scan on Part 3. Today, as part of this post we will cover RID LookUp and Hash Match (Join) operators.

RID LookUp

Let me modify the query that was used for Table Scan by introducing a WHERE clause;

1_SQL_Server_Simplifying_execution_plans_Part4

2_SQL_Server_Simplifying_execution_plans_Part4

Ok, we can observe Nested Loop, then Index Seek followed by RID LookUp on execution plan. To retrieve result of this query, query optimizer first performed an Index Seek on PK_DatabaseLog_DatabaseLogID as this was mentioned on WHERE clause (see below) however all required details weren’t part of index.  Question is obvious, how do we conclude this? Answer is simple, if you look at the ToolTip test carefully for Index Seek it shows ‘Bmk1000’ in output list which says that, Index Seek is part of query plan that have bookmark lookup.

3_SQL_Server_Simplifying_execution_plans_Part4

Next we see RID Lookup (see below image), which is a kind of bookmark lookup that is performed on a Heap table (table with no clustered index). It uses a row identifier to retrieve the rows since the table doesn’t have a clustered index. This is an additional I/O overhead which are then combined by Nested Loop.

4_SQL_Server_Simplifying_execution_plans_Part4

   

If you look at Seek Predicates section on ToolTip, it shows Bmk1000’ again which tells us, a bookmark lookup was used in query plan. In our example, only one row is returned which may not affect anything but given a scenarios, where you have number of rows that are being looked up it is worth considering either to re-write the query or to introduce appropriate indexing.

Hash Match (Join)

Hash join is formed using two processes i.e. Build and Probe. Optimizer chooses the smaller process as build input. It creates a Build  hash table by computing a hash value for each row from build input after that, from the Probe input it creates the has value for applicable rows using same hash function which looks in build has table while matching. There are three types of hash join i.e. In-Memory Hash Join, Grace Hash Join and Recursive Hash Join which are explained on MSDN. Let us try hash join using a simple query;

5_SQL_Server_Simplifying_execution_plans_Part4

6_SQL_Server_Simplifying_execution_plans_Part4

You can observe Hash Match Join in execution plan then Non-Clustered Index Scan and Clustered Index Scan. We are going to focus on Hash Match for now. Let us check how ToolTip looks like;

7_SQL_Server_Simplifying_execution_plans_Part4

In our case Sales.SalesOrderDetail table is used as Probe input because it is having more records as compared to Production.TransactionHistory table which is used a Build input.

I hope this explains RID LookUp and Hash Match Joins, stay tuned for more on this series.

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.