Microsoft SQL Server 2012 Internals

Conclusion

For all the caching mechanisms, reusing a cached plan avoids recompilation and optimization. This saves compilation time, but it means that the same plan is used regardless of the particular parameter values passed in. If the optimal plan for a specific parameter value isn’t the same as the cached plan, the optimal execution time isn’t achieved. For this reason, SQL Server is very conservative about autoparameterization. When an application uses sp_executesql, prepare and execute, or stored procedures, the application developer is responsible for determining what should be parameterized. You should parameterize only constants whose range of values doesn’t drastically affect the optimization choices.

This chapter looked at the caching ...