Microsoft® SQL Server 2012 Unleashed

Query Plan Reuse

Query plan reuse for stored procedures is pretty straightforward. The whole idea behind stored procedures is to promote plan reuse. For stored procedures and triggers, plan reuse is simply based on the procedure or trigger name. The first time a stored procedure is executed, the query plan is generated based on the initial parameters. On subsequent executions, SQL Server checks the plan cache to see whether a query plan exists for a procedure with the same name, and if one is found, it simply substitutes the new parameter values into the existing query plan for execution.

Another method that promotes query plan reuse is using the sp_executesql stored procedure for executing dynamic SQL statements. When using sp_executesql, typically ...