Choosing which queries to parameterize (or don’t work so hard, SQL Server)

Auto-parameterization, i.e. simple parameterization, and forced parameterization will parameterized queries for you while you sit back sipping on a beer.  We don’t really have a choice with auto-parameterization (though we can force individual queries to not be stored in the plan cache if we choose) and forced parameterization is like using a Sledgehammer to remove coffee stains from a tea cup.  Forced parameterization works against many ad-hoc queries. It might parameterize some queries you would rather have compiled each time they run and it might fail to parameterize some queries you want to keep in the plan cache.  The question is how do we get more granular in deciding which queries to parameterize and which queries to not parameterize?

SQL Server gives us two basic options.  We can turn on forced parameterization and choose individual queries to be compiled at each use or we can leave forced parameterization off and choose specific queries to parameterize (and cache) each time they are run.

One of the ways of choosing the queries to parameterize is the system stored procedure sp_executesql. Sp_executesql allows us to selectively parameterize queries and cache the plans for those queries for easy reuse.  Using sp_executesql, forced parameterization can be disabled and individual queries written to take advantage of SQL Server ability to parameterize.

A confusing things about sp_executesql is the number of times the word parameter is used as part of understanding the syntax. First there are the parameters that are passed into sp_executesql. Then there are the parameters that are part of the sql statement, the parameter list, which is a single string that contains a list of parameters. And then there are the parameters themselves which correspond to the parameters in the parameters list which correspond to the parameters in the sql statement.  That last sentence makes me tired. And in some cases the parameters are part of a single string variable while in another they are individuals.  It can get confusing for someone using sp_executesql for the first time.

I use these definitions to help clarify which parameters are which.
Procedure parameters: these are the parameters of sp_executesql. The number of parameters is variable.  It includes the SQL Statement, and can include the string that contains the list of parameters for the SQL Statment plus a parameter for each parameter in the SQL statement.

Statement parameters: the parameters that are part of the SQL statement to be executed. The SQL statement and the included parameters are all part of the same string variable.

List Parameters: This is a list of the parameters of the SQL statement. The items in this list will correspond with the statement parameters. It is a single string of all the parameters, not individually declared variables.

Value Parameters: These are the parameters that hold the values that are eventually passed to the SQL Statement.  They are actually parameters of sp_executesql so they are also procedure parameters.

An example:
First we need to create the SQL statement which will include the statement parameters
SET @stmt = N’SELECT Col1 FROM Test WHERE Col1 = @1 AND Col2 = @2′

Then create the list parameters.  These are actually part of a single string
SET @listparams = N’@1 int,@2 nchar(2)’

Notice that the parameters in the list parameters string match the statement parameters in the SQL statement.
Then some variables that will hold the values that will eventually be passed to the SQL Statement.  These are also the value parameters.
SET @val1 =1
SET @val2 = ‘aa’

Put them all together and this is what you get.
DECLARE @stmt nvarchar(200)
DECLARE @listparams nvarchar(200)
DECLARE @val1 int
DECLARE @val2 nchar(2)
SET @stmt = N’SELECT Col1 FROM Test WHERE Col1 = @1 AND Col2 = @2′

SET @listparams = N’@1 int,@2 nchar(2)’
SET @val1 = 1
SET @val2 = N’aa’

EXEC sp_executesql @stmt,@listparams,@val1,@val2

The variables aren’t technically needed.   It could have also been written like this:
EXEC sp_executesql N’SELECT Col1 FROM Test WHERE Col1 = @1 AND Col2 = @2′
,N’@1 int,@2 nchar(2)’,1,N’aa’

When executing a query using sp_executesql the query is parameterized.  This can be seen by looking at sys.dm_exec_query_cache along with sys.dm_exec_sql_text.

One thing to notice is that unlike when using auto and forced parameterization there is no shell execution plan that contains a pointer to the full plan.  There is only the prepared query.  This is great if you have a procedure cache littered with shell execution plans that all point to a small set of cached plans.  While the shell query plans are typically smaller than the full query plan they still take up memory which may be better used for caching other plans or, since the procedure cache memory is stolen from the buffer pool, better used for caching data and index pages.

Using sp_executesql is a great way to control what gets into the procedure cache. It provides the ability to parameterize queries that won’t be auto-parameterized or to control the auto-parameterization process.

sp_executesql has other benefits as well which I hope to blog about in a future post.