Cautionary Tale of Recompilations, Excessive CPU Load and Plan Caching
Enterprise Database Administration & Deployment
SQL Server reduces compilation and optimization overhead by caching and reusing execution plans for the queries. Plan caching helps to reduce CPU load on the server; however, it may also introduce unexpected issues and lead to suboptimal execution plans and performance problems.
In this session, Dmitri will explain how plan caching works internally and demonstrate how to address several common issues related to the plan caching. He will talk about parameter sniffing, simple and forced parameterization, OPTIMIZE FOR UNKNOWN strategy, and several other related topics. Finally, Dmitri will provide the guidelines how to reduce CPU load and recompilation overhead in the systems with heavy ad-hoc workload and non-parameterized queries.