How to Use Multiple Hints Together for a Query? – Interview Question of the Week #241

Question: How to Use Multiple Hints Together for a Query?

Answer: This is a very interesting question I have received recently from my client of Comprehensive Database Performance Health Check. Of course, you can use multiple hints for a query. Here is an example of the same.

Run the following query for the sample database WideWorldImporters.

SELECT i.AccountsPersonID, i.InvoiceID
FROM Sales.InvoiceLines il
INNER JOIN Sales.Invoices i ON il.InvoiceID = i.InvoiceID
WHERE i.AccountsPersonID = 3003

The execution plan show the adaptive join in SQL Server 2017 and when we mouse-over it shows us the join type is Merge Join.

Now run the following command with a single hint and check the execution plan. You will realize that SQL Server has changed the execution plan and it is now using Merge Join instead of Adaptive Join.

SELECT i.AccountsPersonID, i.InvoiceID
FROM Sales.InvoiceLines il
INNER JOIN Sales.Invoices i ON il.InvoiceID = i.InvoiceID
WHERE i.AccountsPersonID = 3003
OPTION(MERGE JOIN)
GO

Now once again run the following query where we have specified two different kinds of query hints. We can specify the second hint by using a comma in the OPTIONS keyword. As a second hint, we are instructing the query force the order of joining.

SELECT i.AccountsPersonID, i.InvoiceID
FROM Sales.InvoiceLines il
INNER JOIN Sales.Invoices i ON il.InvoiceID = i.InvoiceID
WHERE i.AccountsPersonID = 3003
OPTION(MERGE JOIN, FORCE ORDER )
GO

When you inspect the execution plan, you will see now it has changed significantly from the previous execution plan as when the join order is forced the execution plan has changed.

Long story short, SQL Server queries can use multiple hints together with the help of OPTION keyword.

Personally, I am very much against using a single query hint as well and during Comprehensive Database Performance Health Check, I actually advise against this one to tune queries. I always find a workaround where query runs faster without using any hints.

Here are a few additional blog posts related to this topic:

• SQL SERVER – 5 Important Steps When Query Runs Slow Occasionally
• SQL SERVER – Sample Long-Running Query for Demonstrations
• SQL SERVER – T-SQL Script to Identify TOP 10 Parallel Running Queries
• Read What My Clients Say About Comprehensive Database Performance Health Check

Reference: Pinal Dave (https://blog.sqlauthority.com)