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.
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)