When does SQL Server Cache an execution plan?

By | August 4, 2020

Ever wondered under what circumstances SQL Server cache an execution plan. What is the difference between simple and forced parametrization. What are the disadvantages of plans being cached? Should I recompile a procedure or are there other options. How does plan caching differ between Adhoc queries vs stored procedure. How does single use plans eat up memory. Is parameter sniffing the only issue we need to worry about when a plan has been cached? This video with a demo shows how SQL Server caches the plan and how this behavior can be manipulated by different settings.

In depth understanding of tuning practices