SQL SERVER – Understanding Table Hints with Examples

from http://blog.sqlauthority.com/2009/11/19/sql-server-understanding-table-hints-with-examples/

Introduction

Today we have a very interesting subject to look at. I tried to look for help online but have not found any other documentation besides what we have from the Book Online.

Let us try to understand what are the different kinds of hints available in SQL Server and how they are helpful.

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

Before we continue to explore this subject, we need to consider one very important fact and say some words of caution. SQL Server Query optimizer is a very smart tool and it makes a best selection of execution plan. Suggesting hints to the Query Optimizer should be attempted when absolutely necessary and by experienced developers who know exactly what they are doing (or in development as a way to experiment and learn).

There are three different kinds of hints. Let us understand the basics of each of them separately.

Join Hint

This hint is used when more than one table is used in a query. Two or more tables can be joined using different kinds of joins. This hint forces the type of join algorithm that is used. Joins can be used in SELECT, UPDATE and DELETE statements.

Query Hint

This hint is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query, as opposed to part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them.

Table Hint

This hint is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE are used. There are certain cases when the developer knows when and where to override the default behavior of the locking algorithm and these hints are useful in those scenarios.

Let us run the following simple query with different kinds of query hints and observe the actual execution plan. The analysis of execution plan is not part of this article and will be covered in future.

USE AdventureWorks
GO
/* No Query Hint */
SELECT *
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID =sod.ProductID
WHERE Weight = 20.77
GO
/* Merge Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER MERGE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID =sod.ProductID
WHERE Weight = 20.77
GO
/* Hash Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER HASH JOIN Sales.SalesOrderDetail AS sod ON p.ProductID =sod.ProductID
WHERE Weight = 20.77
GO
/* Loop Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER LOOP JOIN Sales.SalesOrderDetail AS sod ON p.ProductID =sod.ProductID
WHERE Weight = 20.77
GO
/* Remote Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER REMOTE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID =sod.ProductID
WHERE Weight = 20.77
GO

The above query will produce the following query execution plan.

In this article we will focus mainly on Join Hints. We will discuss other kinds of hints and their usage in a different article. There are a total of four kinds of join hints available.

Loop Join

This join is also commonly known as nested iteration. This kind of join is composed by an outer loop and an inner loop. When the query runs for each row of the outer loop, the inner loop is executed completely. This join is effective only when the outer loop query is small and the inner loop query has all the proper optimizations applied. This join method is very useful with small transactions.

Merge Join

This join has the unique requirement for tables involved in the operation to be sorted. This join keeps both of the tables sorted in parallel and compares each table row by row simultaneously with each other. It compares one row of the first table with one row of the second table. If they are equal, that row qualifies; otherwise, this join determines which row of each table has the lower value. Once the lowest value of the table is figured out, it moves on to next row of that table and compares that to the original row. This operation keeps going on until all rows from each table are completely examined. This operation can be very expensive when tables are not sorted and it’s required to sort them before they are joined. If tables have non clustered indexes over them and joins are using the same conditions, there are pretty good chances that this join performs better than other kinds of joins.

Hash Join

This is the most complex of all the other joins. There are two major components of this kind of join – build query and probe query. First, a smaller table is assigned as build query and a hash table for the same is created. This hash table is compared with the probe table. This comparison of input table and probe table is done one row at a time. One row of the probe table is hashed and compared against the other row, and qualifying rows are checked.

There are three different kinds of hash joins:  in-memory hash join, grace hash join, and recursive hash join. Let us look in more detail at these three kinds of joins.

In-Memory Hash Join

This kind of hash join is used when tables are very small and a complete table can be hashed and loaded in memory.

Grace Hash Join

This kind of hash join is used when tables are comparatively large and don’t fit in memory. Tables will be partitioned at input levels and processed in steps.

Recursive Hash Join

This kind of join is used for complex tables and for tables which are very large and require multilevel of table partitions.

SQL Server query optimizer is a smart tool and it knows when to use the right kind of join. When it comes to hash join, the query optimizer starts conservatively with in-memory hash join. If join is larger than in-memory it moves up to Grace hash join or Recursive hash join. Sometimes the optimizer makes a mistake in identifying a smaller table and it reverses the role of the build and probe table: this is called role reversal.

Remote Join

This is least-used tabled join ever. There is no example for it given in Book On Line. Every Join has two tables associated with it: Left Table and Right Table. The join usually happens on the Left Table. When Remote close is used, joins are performed on the site of Right Join. This join can only be performed on INNER JOIN as in case of OUTER join there may be NULL values on the right table which makes concept of joining on right table logically incorrect.

Summary

We have closely observed different kinds of Join Hints in this article. We will cover the remaining concepts in another article.