How Simple Parameterization works
Recently we got a customer who upgraded from SQL Server 2005 to 2008. But their performance degraded greatly. What happened was that they had an update query that was run many times in a batch. The query was submitted as ad hoc by the application with different values.
Upon further investigation, we discovered that in SQL 2005, the query was parameterized but in SQL 2008 the query wasn't. So the cost came from the compiling every time for the update. Eventually, we resolved the issue. But it prompts a post on simple parameterization.
In SQL Server 2000, there isn't the concept of simple parameterization. There is just one option (auto parameterization). Starting SQL Server 2005, we offer two options for parameterization (simple and forced). Simple parameterization is the default and is the same as auto parameterization in SQL 2000.
How do I know if my query is even parameterized?
The easiest way to find out if a query is parameterized is to use graphical XML plan. Just point to the operator and take a look at some of the seek predicate.
Let's use this update example:
update t1 set c1 = 12, c2 = 23 where c1 = 12
The table t1 has c1 as clustered primary key (the reason why I also update c1 is related to customer's problem which I will talk about later). So the plan has a clustered index seek predicate on c1.
If the plan is parameterized, you will see the seek predicate on c1 as "Scalar Operator (CONVERT_IMPLICIT(int,..)" or "Scalar Operator (@2)" as shown in figure 1. But if the plan is not parameterized, the hard coded value will show up in the seek predicate like "Scalar Operator (12)" as shown in figure 2 below.
Figure 1
Figure 2
When is a query parameterized?
If you set your database's parameterization as forced, SQL Server will try to parameterize every query except the conditions documented https://technet.microsoft.com/en-us/library/ms175037(v=SQL.105).aspx.
But what about when your database's parameterization is set simple (default)? Our books online documentation (https://technet.microsoft.com/en-us/library/ms186219(v=sql.105).aspx) states that only very small set of queries will qualify. There is no easy answer to what queries qualify. But in general, if your query involves multiple tables, chances are it won't be parameterized. A more precise answer is that simple parameterization can only occur if the plan is a trivial plan. In case you are wondering why your query is not parameterized, you need to look no further than the XML plan itself. In xml plan (you will need to open as XML), you will see an attribute called "StatementOptmLevel" as shown below. If the StatementOptmLevel="FULL", then the query will not be parameterized with default simple parameterization option.
What happened to this customer mentioned earlier?
For this customer, the application was really doing something not that optimal. I simplified the scenario like below. t1 is referenced by t2
create table t1 (c1 int primary key, c2 int)
go
create table t2 (c1 int references t1(c1))
go
In the update statement, they also seek on the primary key column but also update on the primary key column with the exact same value. The statement looks like something below.
update t1 set c1 = 12, c2 = 23 where c1 = 12
In 2005, the update was a simple trivial plan. But in 2008 and 2008 R2, we made an optimizer change to do some Halloween protection due to incorrect results. As a result, this type of query has to go through full optimization. Therefore, in simple parameterization configuration, the query can no longer parameterize.
Fortuhnately, it's easy to fix. The easiest is to set force parameterization. But this customer doesn't want to do that citing it can impact other queries. Fortunately, template plan guide solves the issue.
All you need to do is to create a template plan guide to force parameterization for that particular query (like below)
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'update t1 set c1 = 12, c2 = 23 where c1 = 12',
@stmt OUTPUT,
@params OUTPUT;
EXEC sp_create_plan_guide
N'TemplateGuide2',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)';
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support