Hi Friends,

In one of my recent assignments, I was demonstrating Plan Caching & Recompilation and someone talked about dynamic SQL helping in plan reuse. That triggered me to show this simple demonstration on how dynamic SQL can help in plan reuse.

Let us take a simple example. I execute the following query:

For the above query, I get a Seek plan:

1_sp_executesql_helps_in_plan_reuse_in_SQL_Server

Now when I change the search condition for the same predicate:

I get a Scan plan:

2_sp_executesql_helps_in_plan_reuse_in_SQL_Server

This is known as parameter sniffing where the optimizer sniffs the value of the parameter and generates an optimized plan based on the parameter. And you can see that 2 different plans are being created in memory.

Whenever a new plan is created, it has to pass through the various stages of compilation and optimization, etc, which is a CPU intensive process, takes time and some amount of memory is being used to store the plan in the cache. It’s a good idea, in some cases, to reuse the plans if there isn’t much performance difference between the execution of 2 different plans. You can use dynamic SQL for this.

In SQL Server you can build and execute dynamic SQL using sp_executesql.

I re-write the above query as follows: do not forget to free the procedure cache before testing this:

When you run the above query, you will observe that you get a seek plan:

3_sp_executesql_helps_in_plan_reuse_in_SQL_Server

And when you run the same dynamic query with a different parameter value,

you will observe that the Seek plan is being re-used: you get the same plan:

4_sp_executesql_helps_in_plan_reuse_in_SQL_Server

You can observe the tool tip of the arrow between SELECT operator and Nested Loops operator:

5_sp_executesql_helps_in_plan_reuse_in_SQL_Server

Which clearly demonstrates that the actual number of rows is 31 and estimated was only 1.

So what if you run the same queries in different order? Which means , run the second one first and the first one, second? Do this after freeing the procedure cache and observe the behavior.