I am new to SQL execution plans and I'm trying to learn more about them.

I have a few questions.

  1. How long does a SQL Execution Plan stay in memory? If I disconnect my connection, I assume it won't be available next time? Is there any way to change this?
  2. Can I export an Execution Plan and load it to memory the next time I reconnect to SQL Server to bypass the Query Optimizer and save time?

Once the compiler has generated a query plan, it stores it in the cache. Query plans are automatically reused when the compiler recognizes that it can do so.

For this to happen, the query has to either be the same ad-hoc batch (down to the last bit of whitespace) or it is part of a stored procedure which has not been changed since.

The statistics used to calculate the plan must still be valid and unchanged, as do all objects used (tables, indexes, columns, constraints).

  1. The plan remains in memory until the SQL Server service is shutdown. It remains available for any other connection until then. It can be evicted by other plans filling the cache, or by calling DBCC FREEPROCCACHE.
  2. You cannot restore a plan from a file. You can force the compiler to use a particular plan that has already been compiled, by using Query Store, although there are cases when that does not work. You can force a plan guide, however it's just a guide, it does not always mean that the compiler will honour it, and it still requires some compilation.

The plans can be queried from various system views and functions, known as DMVs. This query, for example, will give you SQL text, executions plans as XML, as well as a whole bunch of other data.

FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

If you are worried about an unstable plan, which keeps flipping between a good and bad version, you should look at things like parameter sniffing, and try to understand what would cause the bad plan to surface in order to avoid it. You may want better indexing or statistics, or perhaps to reformulate the query.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.