SQL SERVER – Multiple Optimize Hint for a Query

Let us see today a very simple but interesting question asked by one of my clients of Comprehensive Database Performance Health Check. The question was how to have multiple optimize hints for a single query. Let us learn about it today.

SQL SERVER - Multiple Optimize Hint for a Query multipleparam-800x355

It would be a good idea to learn about parameter sniffing before continuing this blog post as the reason you may need the optimize for a hint is to overcome the issues which are related to parameterization of the query.

Now that you have a clear idea of what is parameter sniffing, let us see a script where we will learn how we can optimize any query for multiple parameters.

FROM items
WHERE ProductName = @ProductName AND StockNumber = @StockNumber
OPTION ( OPTIMIZE FOR (@ProductName = 'Toy', @StockNumber UNKNOWN) ); 

Here is the query you can see which has two different parameters and I have provided a hint for the Product Name and Stock Number. However, the type of hint is different for each parameter as they represent different statistics and datatypes. You can follow me on Twitter.

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

Execution Plan, Parameter Sniffing, Query Hint, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Scan Count Zero for Statistics IO
Next Post
SQL SERVER – PREEMPTIVE Waits – Simple Definition

Related Posts

Leave a Reply