What is the Plan Cache?
The plan cache is a mechanism in SQL Server that stores execution plans for frequently executed queries. When a query is executed for the first time, SQL Server generates an execution plan. This plan describes the steps the database engine will take to retrieve the requested data. The plan is then stored in the plan cache.
On subsequent executions of the same query, SQL Server checks the plan cache. If a matching plan exists, it's reused, avoiding the need to regenerate the plan. This can significantly improve query performance.
How Does the Plan Cache Work?
The plan cache operates based on a hash of the query text and any parameters. SQL Server uses this hash to identify matching plans.
When a query is executed, SQL Server looks for a matching plan in the cache. If a match is found, the plan is reused. If no match is found, a new plan is generated and stored in the cache. The plan cache has a limited size, so older plans may be evicted to make room for new ones.
The cache is divided into two parts: the online and offline caches. The online cache is used for plans that are currently being executed, while the offline cache stores plans that are no longer in use.
Controlling the Plan Cache
You can control the plan cache through various settings in SQL Server. Some important settings include:
- Max Cache Size: Specifies the maximum number of plans that can be stored in the cache.
- Cache Key Algorithm: Determines how the plan cache identifies plans.
- Use Temporary View Caching: Enables caching of temporary views.