![sql server option recompile sql server option recompile](http://www.thesqlreport.com/wp-content/uploads/2015/05/dm_tcp_listener_states_02.jpg)
The interesting thing happens in case if query has parameters. As result, SQL Server tries to cache execution plan and reuse it when the same query runs again. Compilation itself is not cheap, especially in case of complex queries. When we submit the query to SQL Server, one of the first things SQL Server is doing is compiling the query. Let’s look at extremely oversimplified picture. But first of all, let’s talk why do we need to worry about that at all. Such as parameter sniffing, search with optional parameters and filtered indexes.
![sql server option recompile sql server option recompile](https://3.bp.blogspot.com/-FY_ESoRPm78/WnV3dNDTR5I/AAAAAAAAP2U/A_mDXY0BQ3kpgDOI_-AyLP1IQxzK4LH6QCK4BGAYYCw/s640/99.png)
![sql server option recompile sql server option recompile](https://dzone.com/storage/temp/11598872-fig-8.png)
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.Today I’d like us to talk about statement level recompilation and how it could help us in a few particular scenarios. If you disagree, you are welcome as well. 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. 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. Of course, there is an additional cost associated when this stored procedure has been executed every single time as it has to recompile. This is a great adjustment and SQL Server makes every single time when the stored procedure is executed. When the query needs more resources, it takes more resources and when needed fewer resources, it uses appropriate resources. 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. Next, let us run the following two stored procedures with two different parameters. Now enable the execution plan for your query window in SQL Server Management Studio (SSMS).
![sql server option recompile sql server option recompile](https://blog.engineer-memo.com/wp-content/uploads/2019/03/image-25.png)
CREATE OR ALTER PROC GetCustomerOrders INT) OPTION (RECOMPILE)įirst, let us create a stored procedure that contains the keyword OPTION (RECOMPILE). 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. In SQL Server there is no right or wrong answer unless while using production we are facing the negative consequence of it. While many want to argue that this may be very bad advice, there are moments where it has been amazing advice as well. 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). It is quite possible that even though we use the average value due to statistics the performance problem has yet not resolved. 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. However, it is extremely difficult to overcome the performance problem which is introduced due to the parameter sniffing issue. It is extremely easy to overcome the problem of parameter sniffing.
#Sql server option recompile series#
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.