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).
- 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
- 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.