sp_executesql help in plan reuse in SQL Server

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:

USE Northwind
GO

DBCC FREEPROCCACHE;
Go
 
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = N'05022'

For the above query, I get a Seek plan:

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

SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = N'83720';

I get a Scan plan:

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:

DBCC FREEPROCCACHE;
Go
 
exec sp_executesql
N'SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @P1', N'@P1 char(5)', '05022'

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

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

exec sp_executesql
N'SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @P1', N'@P1 char(5)', '83720'

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

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

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.