Using SQL Server’s Parameter Embedding Optimisation

Andrew Smith Posted on

In this post I’m taking a look at the Parameter Embedding Optimisation that can be used by the SQL Server query optimiser when statement level recompilation is used.

This can be taken advantage of, for example, when implementing dynamic filtering or dynamic sorting, i.e. when user input determines which columns of a table to filter by or to sort by.

My sources for this post are Itzik Ben-Gan’s SQLBits session on T-SQL Tips and Tricks and Paul White’s article on Parameter Sniffing, Embedding, and the RECOMPILE Options.

To begin with I’ll look at dynamic filtering, where we have a query within a stored procedure that needs to filter according to a column & value of the procedure caller’s choice.

I’ve created a table derived from Sales.SalesOrderHeader in AdventureWorks2012, and added 1 clustered and 2 nonclustered indexes:

ParEmbTableSQL

The following stored procedure can then do the dynamic filtering – on SalesOrderID, OrderDate or AccountNumber – based upon the parameters passed to it:

ParEmbProc

The WHERE clause here can be seen to be doing the filtering. Note that an alternative method for implementing dynamic filtering is by using dynamic SQL and building up a WHERE clause with only the predicates required (i.e. determined by the procedure parameters). Not all organisations allow the use of dynamic SQL though, due to the dangers of using it unsafely, so the solution presented here is making use of static SQL only.

So we’re ready to run our first query now, by calling the stored proc with a SalesOrderID filter:

ParEmbExec

One row comes back which is correct, but look at the actual query plan used:

ParEmbQPKeyWrong

Despite the fact that we’re filtering on the SalesOrderID column, which has a clustered index to support it, the query optimiser chose a completely different index and did a scan on it, and used the clustered index key (the SalesOrderID) within that index to find the desired matches. A key lookup is then used for the matches to locate the table rows in order to retrieve all the required columns.

The reason for this choice of plan is that the optimiser created it with reuse in mind, and there’s no reason to expect the filtering to be on SalesOrderID every time the query is run.

We can do better than this though, if we’re prepared to absorb the expense of recompiling the query each time it is run. If we add the OPTION (RECOMPILE) syntax to the end of the original query:

ParEmbProcRecompile

and rerun the EXEC statement from above, the query plan is now:

ParEmbQPRight

So we can now see a seek on the clustered index created over SalesOrderID – this is the plan we want.

And if we change the procedure call to filter by AccountNumber instead of SalesOrderID, then again we get an optimal plan:

ParEmbQPRight2

The reason this works is because of the parameter embedding optimisation that Microsoft introduced in SQL Server 2008 (post-RTM). This optimisation extends the parameter sniffing optimisation. Whereas the latter uses cardinality estimates for the sniffed parameters to influence the plan, the former works on the basis that the plan is never going to be used again (because of OPTION (RECOMPILE)) and so during query parsing it can replace query parameters with the constant values they actually represent. I.e. the optimiser doesn’t have to come up with a plan that’s suitable for reuse.

Obviously this isn’t a one-size-fits-all option – the recompilation penalty may be too high for fast, frequently executed queries – but it will be the right solution in some cases.

Whilst the example above was focused upon dynamic filtering, parameter embedding can also be used to optimise dynamic sorting. So using the same table as above we can now create this procedure:

ParEmbProcSort

Executing it with @sortOption = 1 (SalesOrderID – the clustered index column) gives us a suboptimal plan – there’s a sort operator doing the sort instead of the plan taking advantage of the ordering of the clustered index:

ParEmbProcSPSortWrong

If we add the OPTION (RECOMPILE) syntax to the end of the stored procedure’s query and run again with the same parameter, we get:

ParEmbProcSPSortRight

So the sort operator has disappeared and we get a nice clean plan with the index order being used to provide the sorted output.

Running the stored procedure with recompilation for @sortOption = 3 also changes the plan from one with a sort operator to one without:

ParEmbProcSPSortRight2

Again the natural ordering of the relevant index is being taken advantage of.

As with the dynamic filtering example shown earlier, the optimisation improvements gained here result from parameter embedding, which can occur because the OPTION (RECOMPILE) syntax means that the plan will not be reused and so a plan good enough for all possible parameter values is not required. The value of the parameter passed in to the procedure (@sortOption) is literally embedded into the query at the beginning of optimisation, thus simplifying the query and allowing a better plan to be created.

Note that parameter embedding does not occur when WITH RECOMPILE is used in the stored procedure header – this optimisation is only available for statement level recompilation, i.e. using OPTION (RECOMPILE).