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.

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.

• SQL SERVER – Parameter Sniffing Simplest Example
  In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
• SQL SERVER – Parameter Sniffing and Local Variable in SP
  It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure.
• SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
  You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
• SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
  This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
• SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
  The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
• Performance and Recompiling Query – Summary
  This post summarizes the entire series of parameter sniffing, performance, and recompiling queries.

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.

SELECT *
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)