Parameter Sniffing with sp_prepare and sp_prepexec

Introduction

The prepare and execute model is not specific to SQL Server. Almost all relational database engines provide a similar feature, though there are many implementation differences. Most usage originates with client connection drivers and APIs like ODBC/OLEDB/JDBC.

The general idea is to speed up the repeated execution of similar statements by performing much of the static work (e.g. parsing, validating, binding to database objects) once, reducing the cost of future executions.

Whether this pays off depends on how much work is saved in future executions compared with the extra costs involved in preparing. I’m being deliberately vague here because the specifics depend on the driver and client options, even when the database engine remains the same. Some implementations go as far as creating a final executable plan at the preparation stage. I’m only going to cover T-SQL usage in this article.

sp_prepare and sp_execute

Originally undocumented for T-SQL, sp_prepare and sp_execute have been present in the documentation since at least SQL Server 2008, with T-SQL example code added for the 2012 release. The current version is much the same, but includes the optional statement terminator and more T-SQL examples.

When used from T-SQL, sp_prepare takes a (usually parameterized) statement as input and returns an integer handle in an output parameter:

sp_prepare handle OUTPUT, params, stmt, options

The documentation doesn’t currently mention that the input handle value should be set to NULL, unless you’re intending to replace an existing valid handle with a new prepared statement.

Benefits

The primary benefit of sp_prepare is that it saves time and network bandwidth.

Once prepared, future calls to sp_execute need provide only the integer handle instead of sending the entire text to the server. It is also slightly quicker for the server to locate any existing cached plan using the handle than it is to hash the text (and any parameter definitions) to perform a regular cache lookup.

Handles are scoped to the connection and represent an entry in an internal hash table in memory that points to the saved text and parameter specifications, among other internal details. Handles remain active until sp_unprepare is called or the connection is terminated.

Contrary to the documentation, sp_unprepare does not discard any execution plan cached by sp_prepare. It simply releases the memory used by the hash table entry identified by the supplied handle. The plan cache is entirely separate from the prepared statement handles structure and functions completely independently.

Sniffing

You may notice that sp_prepare doesn’t provide a way for parameter values to be provided, only the parameter definitions. Particular parameter values will be provided later with each sp_execute call.

This means SQL Server cannot sniff parameter values at the time sp_prepare is called. If a plan is created and cached, it will be based on average density or other ‘educated’ (to varying degrees) guesses, exactly as if local variables had been used, OPTIMIZE FOR UNKNOWN was specified, or parameter sniffing was disabled via any other mechanism.

I say “If a plan is created” above because calling sp_prepare doesn’t always result in a plan being cached.

Plan production

When a prepared statement is created from a driver or API, plan creation behaviour depends in part on whether the client requests a deferred prepare or not. It seems likely that a client explicitly requesting a deferred prepare would not contact SQL Server at all at the preparation stage.

In any case, there is no way to specify the deferred prepare option from T-SQL, so we get a default behaviour.

Curiously, the modern default for API calls is to defer the construction of a prepared execution plan until execute is called. For T-SQL, the default is the opposite: SQL Server always creates a plan during sp_prepare when that is possible.

This behaviour is why you’ll often see people say that sp_prepare doesn’t sniff parameters—it can’t, because the plan is produced before parameter values are known.

An exception

The caveat, of course, is in the phrase “when that is possible”. While it is always possible in principle to produce a plan at the prepare stage, this is not what SQL Server does.

Simple, single-SELECT statements will always produce a plan during prepare. More complex, multi-SELECT batches will not. In the latter case, plan production is deferred to the first execute call (when any parameters will have specific values).

So, how can we tell if sp_prepare cached a plan?

Well, one obvious way is to check the plan cache after the call; however, there is another, more immediate, indication.

If sp_prepare produces a plan, it produces an empty result set. This is to provide the client with the shape of the results it should expect, including column data types and lengths.

If sp_prepare doesn’t produce a plan, no empty result set is produced.

Demo 1: Prepared plan

Using any SQL Server version and the AdventureWorks sample database, the following script demonstrates the common case of sp_prepare generating a plan (without sniffing parameter values):

-- Ensure no plans in cache
DBCC FREEPROCCACHE;
GO
DECLARE @handle integer = NULL;

EXEC sys.sp_prepare 
    @handle OUTPUT, 
    @params = 
        N'@Search nvarchar(50)',
    @stmt = 
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ';

-- Cached plan (after prepare, before execute)
SELECT
    P.usecounts,
    P.size_in_bytes,
    P.cacheobjtype,
    P.objtype,
    S.[text]
FROM sys.dm_exec_cached_plans AS P
CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) AS S
WHERE 
    S.[text] LIKE '%@Search%'
    AND S.[text] NOT LIKE N'%sp_prepare%';

-- Execute the prepared statement for a particular parameter value
EXECUTE sys.sp_execute @handle, @Search = N'%';

EXECUTE sys.sp_unprepare @handle;

That produces a blank result set from the sp_prepare call, details of the cached plan, and the (later) results produced by sp_execute:

Prepare not deferred

The runtime (actual) execution plan displayed in SSMS shows an estimate was produced for the LIKE predicate based on a fixed guess of 9% of the 504 rows in the Product table:

The plan root node shows only a runtime parameter value:

In summary, a plan based on a complete guess was produced by sp_prepare before parameter values were known. No parameter sniffing took place.

Demo 2: Deferred prepare

This version of the demo requires SQL Server 2012 or later.

To get a deferred prepare, it might seem inconvenient to include multiple SELECT statements in the text when we only want one set of results. This is true, but luckily there are ways to write an extra SELECT without returning results.

A short form of the trick is to use a dummy DECLARE that assigns a value to a local variable. This is enough to make SQL Server think multiple SELECT statements are present. You could also write the shorthand assignment out as separate DECLARE @var and SELECT @var= statements if you prefer. Using SET @var= works too.

The only thing different in the script below is the (unused) variable declaration with assignment. Just declaring the variable or having more than one statement is not sufficient—the important part is the hidden SET/SELECT assignment.

-- Ensure no plans in cache
DBCC FREEPROCCACHE;
GO
DECLARE @handle integer = NULL;

EXEC sys.sp_prepare 
    @handle OUTPUT, 
    @params = 
        N'@Search nvarchar(50)',
    @stmt = 
        N'
        DECLARE @Defer bit = ''true''; -- NEW!

        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ';

-- Cached plan (after prepare, before execute)
SELECT
    P.usecounts,
    P.size_in_bytes,
    P.cacheobjtype,
    P.objtype,
    S.[text]
FROM sys.dm_exec_cached_plans AS P
CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) AS S
WHERE 
    S.[text] LIKE '%@Search%'
    AND S.[text] NOT LIKE N'%sp_prepare%';

-- Execute the prepared statement for a particular parameter value
EXECUTE sys.sp_execute @handle, @Search = N'%';

EXECUTE sys.sp_unprepare @handle;

Running that script does not produce an empty result set from sp_prepare. No plan is cached before sp_execute is called.

The empty result set shown below indicates the lack of a cached plan after the sp_prepare call:

Deferred prepare results

The runtime plan as shown in SSMS is produced during the sp_execute call (the prepare was deferred):

Deferred prepare plan

Notice the estimates are exactly correct. The @Search parameter value N'%' was sniffed and used to optimize the cached plan for that value.

The supplied LIKE predicate with a sniffed N'%' value matches all non-null rows, which means all 504 rows in the table given that the Name column is defined as NOT NULL. This cardinality estimate was the basis for the optimizer choosing an index scan over a seek.

The plan root node shows both compile and runtime parameter values, further proof that parameter sniffing occurred:

Sniffed seek

If we change the sp_execute @Search parameter to N'[A-P]%' and run the whole script again, we get a dynamic seek plan again but with estimates from the string column’s trie tree statistics instead of a guess:

Deferred prepare dynamic seek

The estimate of 374.4 rows is very close to the 366 rows produced at runtime.

The important thing is that the extra SET/SELECT variable assignment caused SQL Server to defer plan creation from sp_prepare to sp_execute. This means the parameter value was available and could be sniffed to optimize the cached plan.

This behaviour was introduced in SQL Server 2012. Before that, sp_prepare (called from T-SQL) never resulted in a deferred prepare and parameters were never sniffed.

sp_prepare will cause parameters to be sniffed when deferred prepare is used.

sp_prepexec

The sp_prepexec documentation says:

Prepares and executes a parameterized Transact-SQL statement.
sp_prepexec combines the functions of sp_prepare and sp_execute.

That is an accurate statement, but ‘combining the functions’ does not mean the equivalent behaviour is identical in all respects.

For one thing, sp_prepexec does not return the extra empty result set produced by sp_prepare (there’s no need since the combined call is going to return real results anyway).

The second—most important—difference is that sp_prepexec is called with specific parameter values so it can always sniff parameters for the cached execution plan.

A statement that does not cause a deferred prepare with sp_prepare (producing a plan based on guesses) will sniff parameter values when called using sp_prepexec.

-- Ensure no plans in cache
DBCC FREEPROCCACHE;
GO
DECLARE @handle integer = NULL;

-- Combine prepare and execute in a single call
EXEC sys.sp_prepexec 
    @handle OUTPUT, 
    @params = 
        N'@Search nvarchar(50)',
    @stmt = 
        N'
        SELECT 
            UniqueProducts = COUNT_BIG(DISTINCT P.ProductNumber)
        FROM Production.Product AS P 
        WHERE 
            P.[Name] LIKE @Search;
        ',
    @Search = N'%';

EXECUTE sys.sp_unprepare @handle;

That produces the sniffed scan plan optimized for @Search = N'%' without the variable assignment trick needed to get a deferred prepare from separate sp_prepare and sp_execute calls.

Sniffed plan with sp_prepexec

Parameter List plan properties

sp_prepexec always sniffs parameter values.

Final Thoughts

That might leave you wondering why anyone would use sp_prepare in preference to sp_prepexec.

• It might be convenient to prepare statements for an application early, before you know exactly when or where they will be used.
• You might want advance details about the shape of the result set.
• You might know a deferred prepare would not occur and you don’t want parameter sniffing. There are better ways to accomplish this in modern versions of SQL Server.

Regardless, you’ll rarely use any of these system extended procedures instead of sp_executesql from T-SQL, but you never know when these details might help you understand a problem or execution plan.

I’ll cover some less well-known details about sp_executesql in my next article.

Bear in mind that undocumented details can change at any time, and other drivers and APIs use the prepare and execute model differently from T-SQL.

The brief demos in this article are unrealistic in that they only call sp_execute once. Prepared statements can only pay off if they are executed many times. Further executions would produce plans with generally different parameter runtime values compared with the original parameter compiled value shown at the root node of execution plans.

Thanks for reading!