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.

How to Use Multiple Hints Together for a Query? - Interview Question of the Week #241 multiplehints-800x310

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:

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

, , ,
Previous Post
Do Stream Aggregate Operator Always Need Sort Operator? – Interview Question of the Week #240
Next Post
How to List All DML Triggers Created or Modified in the Last N Days in SQL Server? – Interview Question of the Week #242

Related Posts

2 Comments. Leave new

  • Genevieve Morrison
    September 9, 2019 4:38 am

    Hello Dave,
    Is it a good performance practice o set your fill factor at the database level or object level? Please advice

    Reply
  • Genevieve Morrison
    September 9, 2019 4:43 am

    Hello Dave,
    I have read, watched a series of your videos and blogs on Index rebuild and indexes in general and its impact on DML and select operations. Took away some nuggets but now i am confused on if/how often to rebuild my indexes on a busy OLTP environment where fragmentation can be pretty high…knowing the performance trade off index rebuild has. Do you have any material on this topic or can you weigh in on this please and Thank you

    Reply

Leave a Reply

Menu