A deep dive into SQL Server Plan Cache Management
As an experienced DBA you have probably asked yourself questions like how the memory is consumed, is my system well-tuned, and is my memory configuration well defined. In order to understanding such processes as local and global Memory pressure, caches concurrency, adding and removing plans from cache we will go through 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. We will point out the important cache consumers that affect your workload and how to get metadata about them, what are the thresholds in Cache Size Management. At the end you will understand how to monitor the memory consumers in your SQL Server, what are wait types that we need to look for, how to understand if your system is well-tuned and the memory configuration is well defined. You will have a clear understanding of cache plan problems and how to avoid them.
Understanding SQL Server caches, parameterization, have experience in reading execution plans, have experience in performance tuning