Introduction

Have you ever tuned a query to perfection, only to have its execution plan change unexpectedly and performance tank? It’s so frustrating when this happens! I’ve been there too. But luckily, there’s a handy query hint in SQL Server called KEEP PLAN that can help prevent this exact scenario. In this article, I’ll teach you all about what the KEEP PLAN hint does, when to use it, and walk through some examples so you can start applying it in your own queries. By the end, you’ll have this valuable tool in your SQL Server toolkit to help keep your query performance stable and speedy.

What is the KEEP PLAN query hint?

The KEEP PLAN query hint in SQL Server does exactly what its name suggests – it tells the query optimizer to “keep the plan” it has generated for a particular query, even if data or statistics change in the future that would normally cause the optimizer to choose a different plan.

Normally, the query optimizer uses statistics about the data, like the number of rows and data distribution, to choose an optimal execution plan for a query. But if those data characteristics change significantly, like after a large data load or purge, the original plan may no longer be optimal and a recompilation will occur to select a new plan. The KEEP PLAN hint overrides this behavior and forces the optimizer to reuse the same execution plan even if statistics change.

When should you use KEEP PLAN?

In most cases, you want to let the query optimizer do its job of adapting plans as needed. But there are some scenarios where using KEEP PLAN can be beneficial:

• You have a mission-critical query that has been carefully tuned and you don’t want its plan changing unexpectedly and risk a performance regression
• The data profile is highly variable, causing frequent recompilations, but you know the original plan will still perform well enough
• You want to prevent the overhead of recompilation for very frequent queries, even if suboptimal plans may be used at times

The key is to use this hint judiciously on the queries that really need that extra plan stability, rather than broadly everywhere.

Example walkthrough

Let’s look at an example of using the KEEP PLAN hint. Say we have a query that joins the SalesOrderHeader and SalesOrderDetail tables like this:

SELECT soh.OrderDate, sod.LineTotal  
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate > '2022-01-01';

After analyzing and tuning it, we’ve arrived at an execution plan we’re happy with that uses a hash join and performs well. To make sure this exact plan is reused in the future even if statistics change, we can add the KEEP PLAN hint:

SELECT soh.OrderDate, sod.LineTotal  
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate > '2022-01-01'
OPTION (KEEP PLAN);

Now this query’s plan is locked in and won’t change unexpectedly on us. We can rest easy knowing its performance is stable.

It’s important to note that if the schema changes, like a new index is added, the plan won’t be kept and a recompile will occur. The hint only prevents recompiles due to data and statistic changes.

Conclusion

The KEEP PLAN hint is a handy option to have for those certain critical queries where stable performance is paramount. By locking in a known good execution plan, you can prevent unexpected recompilations and keep your query running smoothly.

Just remember to use this hint sparingly, as most of the time you do want to let the optimizer pick the best plan for current conditions. Consider it one more tool in your query tuning toolbelt to apply in select cases.

I hope this explanation of the KEEP PLAN hint has been helpful! Let me know if you have any other questions. Now go forth and keep those execution plans in place!

Click here to dive deeper into QUERY HINTS