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

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:

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.




About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “sp_executesql help in plan reuse in SQL Server”

Leave a Reply

Your email address will not be published.