WHERE SalesOrderID < we want to make sure we get a new plan every time we run this query, with potentially vastly different values for we can add the RECOMPILE hint as shown:įor stored procedures, we have three options. Using FORCED parameterized (discussed in the previous article), this query will be parameterized. For individual statements, such as with PREPARED plans that have been autoparameterized, we can add the RECOMPILE hint to a query. If inappropriate use of parameter sniffing is a problem, a simple solution is to just tell SQL Server to come up with a new plan.

However, most people use the term ‘recompile’, so that’s what I’ll use here. Usually, we call the act of coming up with a new plan ‘recompiling’, but it probably should be called ‘reoptimizing’. So, what can we do when parameter sniffing is a problem? We can force SQL Server to come up with a new plan. If a plan is based on an initial value that causes the optimizer to generate a plan appropriate for that value, and then the same plan is used for a different value, the plan may no longer be optimal. I ended the discussion with a look at an inappropriate reuse of a plan, which can happen when SQL Server applies parameter sniffing in the wrong situations. We looked at three types of query plans: adhoc, prepared, and procedure. In my previous article, I talked about SQL server diagnostics, including the various options that SQL Server has for reusing a query plan. In these posts I’ll share with you how I look at some of the features or aspects of SQL Server, sometimes along with a bit of historical perspective I’ve seen a lot of changes over the years (and decades!) and versions of this incredible product. SQL Server has been around over 30 years, and I’ve been working with SQL Server for almost as long. Be sure to read Kalen's first post on this topic.

This is part of a SQL Server Internals Plan Caching series.

Option recompile stored procedure