About SQL Server 2012 Query Processing–Plan Caching

The SQL Server Database Engine uses a set of memory caches as a storage space for data and for execution plans of queries. The first time such a query is executed, the compiled version of the query is stored in the memory. (The part of memory used to store compiled query plans is called the plan cache.) When the same query is executed for the second time, the Database Engine checks whether an existing plan is stored in the plan cache. If so, the plan is used, and the recompilation of the query does not take place.

 NOTE

The process of plan caching for stored procedures is analogous.

Influencing execution plans

There are several ways in which you can influence execution plans. This article describes two of them:

•     The optimize for ad hoc workloads option
•     DBCC FREEPROCCACHE

optimize for ad hoc workloads is an advanced configuration option that prevents the system from placing an execution plan in cache on the first execution of the corresponding statement. In other words, the Database Engine places only a stub of the execution plan instead of the entire plan. This stub contains the minimum information, which is necessary for the system to find matches with the future queries. The idea behind this is to reduce the uncontrolled growth of the plan cache. (Keep in mind that the execution plan for the simple query with a couple of indexed columns in the SELECT list needs about 20KB of memory. The plan cache for complicated queries can be significantly larger.)

DBCC FREEPROCCACHE removes all plans from the plan cache. This command can be useful for testing purposes. In other words, if you want to determine which plans are cached (in other words, when particular query plans are reused), you can clear the cache using this command. (You can also use the command to remove a specific plan from the plan cache by specifying the parameter for the plan handle.)

Displaying information concerning the plan cache

To display information concerning the plan cache, you can use the following dynamic management views (DMVs):

•     sys.dm_exec_cached_plans
•     sys.dm_exec_query_stats
•     sys.dm_exec_sql_text