New Sberry

Hint, Hint Hint SBerry out to execute it’s task. Let’s explore SQL SERVER hints with SBerry

Basic and Simply put:
A hint is a sort of suggestion or order given out to the SQL Server query processor to follow the rule that we specify in the hint in order to execute  SELECT, INSERT, UPDATE, or DELETE statements.

Formal Definition:
Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.
Hints are categorized into three like

Query Hint
Table Hint
Join Hint

Join Hint
Join hints specify that the query optimizer enforces a join strategy between two tables in SQL Server 2017.


< join_hint > ::= { LOOP | HASH | MERGE | REMOTE }

LOOP Join:
Before we begin with Loop join, let’s consider the general join operator.
A join operator is a type of an algorithm which the SQL Server Optimizer chooses in order to implement logical joins between two sets of data. The SQL Server Optimizer may choose a different algorithm for different scenarios based on the requested query, available indexes, statistics and number of estimated rows in each data set.

Nested Loop join is the simplest join operator of the rest.
Nested Loop demonstration

The operator on the top right in the figure is referred as the outer input and the one just below it is called the inner input. Nested Loop join works as : For each record from the outer input – find matching rows from the inner input.

As the execution plan in the figure shows that the Clustered index scan is done to retrieve all the records of the outer input while Clustered index seek is applied for each record of the outer input.

Merge Join:
The Merge algorithm is the most efficient way to join between two very large sets of data which are both sorted on the join key.

The Merge Join simultaneously reads a row from each input and compares them using the join key. If there’s a match, they are returned. Otherwise, the row with the smaller value can be discarded because, since both inputs are sorted, the discarded row will not match any other row on the other set of data.

This repeats until one of the tables is completed. Even if there are still rows on the other table, they will clearly not match any rows on the fully-scanned table, so there is no need to continue. Since both tables can potentially be scanned, the maximum cost of a Merge Join is the sum of both inputs.

Hash Join:
The “Hash” join type is what called “the go-to guy” of the join operators. It’s the one operator chosen when the scenario doesn’t favor any of the other join types. This happens when the tables are not properly sorted, and/or there are no indexes. When SQL Server Optimizer chooses the Hash join type, it’s usually a bad sign because something probably could’ve been done better (for example, adding an index). However, in some cases (complex queries mostly), there’s simply no other way.
SQL Server will choose the smaller of the two inputs to serve as the build input and it will be the one used to build the hash table in memory. If there’s not enough memory for the hash table, SQL Server will use physical disk space in TEMPDB. After the hash table is built, SQL Server will get the data from the larger table, called the probe input, compare it to the hash table using a hash match function, and return any matched rows. In graphical execution plans, the build input will always be the one on top, and the probe input will be the one below.

REMOTE
Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table. If both tables are remote tables from the same data source, REMOTE is not required.

It’s time to see how to use join hints in T-SQL.
SELECT sc.Code,
sc.[Name],
esc.Amount  FROM EmployeeSalaryComponents esc
INNER JOIN SalaryComponents sc
ON esc.SalaryComponentId = sc.SysId
option(Merge join)
Merge join

Advertisements