Lesson Learned #445:Unlocking the Mystery of Recompilations in SQL Server: A Practical Lab
Some days ago, I worked on a service request that our customer saw a CPU speaks time to time. This issue was cause a recompilation of a queries and I would like to share my lesson learned here. Imagine you have an application that frequently queries the database with changing parameter values. Sometimes, due to schema changes, statistics updates, or other reasons, the execution plan that SQL Server has cached for that query might not be optimal for a specific parameter value. Consequently, SQL Server might opt to recompile the query.
