Plan cache internals
Knowing when and how plans are reused or recompiled can help you design high-performing applications. The more you understand about optimal query plans, and how different actual values and cardinalities require different plans, the more you can determine when recompilation is useful. When you are getting unnecessary recompiles, or when SQL Server isn’t recompiling when you think it should, your troubleshooting efforts will be easier the more you know about how plans are managed internally.
This section explores the internal organization of the plan cache, the metadata available, how SQL Server finds a plan in cache, plan cache sizing, and the plan eviction policy.
The plan cache in SQL Server is made up of four separate ...