SQL SERVER – Performance and Recompiling Query – Summary

Today is the first day of the year 2020 and I would like to share an interesting conversation that I have been having with many of the readers after reading the latest blog post series on Performance and Recompiling Query.

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.

However, please note that avoiding parameter sniffing does not mean additional performance gain, it can very well reduce the performance as well.

After reading all the blog posts the common question which I had received was lots of people got confused what is the final solution to overcome slow query performance issues which are introduced due to the issue of the parameter sniffing.

Remember: 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.

If your query is continuously misbehaving with the poor performance and you identify the issue is query cache, I think it is best to use the Recompiling Query while executing it. A common argument against this option is that it will actually forfeit the original purpose of the caching mechanism as well as also adds extra resources (CPU, Memory, IO) to each query. While I totally agree with the notion of more resource consumption, sometimes it just helps to recompile the query instead of running it with an incorrect plan.

Here is the syntax to recompile the query:

SELECT *
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE)

So hear to the new year 2020 to recompiling query for performance.

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