Today is the last blog post of the year 2019 and this is also a final blog post in the series of Parameter Sniffing which I have been writing. I strongly recommend that you read my earlier blog posts on this topic before you consider the option of recompile suggested in this blog post.
Before continuing this blog post, I strongly suggest that you read the following blog posts to get an understanding of the background of the concept which I am going to discuss today.
- 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 query.
Here is what we have learned so far. It is extremely easy to overcome the problem of parameter sniffing. However, it is extremely difficult to overcome the performance problem which is introduced due to the parameter sniffing issue. There is no sure solution to overcome the problem if your stored procedure has sniffed the parameter and used that to build the execution plan. It is quite possible that even though we use the average value due to statistics the performance problem has yet not resolved.
There is only one sure resolution of the performance issue if the SQL Server execution plan is building execution plan which is not efficient for the parameter passed and that is the Recompilation of the stored procedure at every single run with the query hint OPTION (RECOMPILE).
While many want to argue that this may be very bad advice, there are moments where it has been amazing advice as well. In SQL Server there is no right or wrong answer unless while using production we are facing the negative consequence of it. It is important to know how each feature works and with the same spirit let us see how option recompile can help us in certain situations.
First, let us create a stored procedure that contains the keyword OPTION (RECOMPILE).
CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT) AS SELECT * FROM WideWorldImporters.Sales.Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE)
Now enable the execution plan for your query window in SQL Server Management Studio (SSMS).
Next, let us run the following two stored procedures with two different parameters.
EXEC GetCustomerOrders 1060 EXEC GetCustomerOrders 90 GO
When we see the execution plan of the queries you will notice that this time the execution plan has a correct estimation of the rows and accordingly the query is consuming the resources which are needed to optimize that particular execution plan.
When the query needs more resources, it takes more resources and when needed fewer resources, it uses appropriate resources. This is a great adjustment and SQL Server makes every single time when the stored procedure is executed.
Of course, there is an additional cost associated when this stored procedure has been executed every single time as it has to recompile. However, this is when exactly you will have to make a decision considering various different baseline what works for you – better execution plans or cached execution plan.
I do not prefer to recompile every single stored procedure but whenever I ended up in the situation where I see the cached execution plan is creating more problems than it solves.
If you disagree, you are welcome as well. I have personally experienced enough examples while working with Comprehensive Database Performance Health Check in my career that I have started to use this feature of recompilation when I see no other options to improve the query performance.
On this note, happy new year 2020!
Reference: Pinal Dave (https://blog.sqlauthority.com)
One situation where OPTION(RECOMPILE) is almost always beneficial is where a query would massively benefit from a filtered index, but SQL isn’t using it because parameters.
If I have 10 queries in a store procedure, do I have to add option recompile for each query or can I do mix and match?
We hit exactly the same problem, and your blog series really helped me to understand, what’s going on.
Thanks for documenting this.
Thank you for this article. Very informative. However, I have a similar problem and wondering if you have any suggestions. A simple query from one table with one where clause runs faster in view than store procedure. Ideally, we learned by experience that SP is always perform better than view. But this is not the case in our 2019 sql server. Any advise to solve it or debug the cause?
Thank you very much.