Simple parameterization is SQL Server feature which allow the optimizer to parameterize submitted queries. If submitted query has no parameters and has constant values plugged in, the optimizer can choose to treat constant values as parameters and automatically create parameterized query and execution plan for it. Simple parameterization is on by default and it’s designed to reduce cost of generating execution plans. SQL Server uses a simple parameterization for a relatively small set of queries where query plan doesn’t depend on particular constant values.
To see how simple parameterization works I will use AdventureWorks database. Let’s run some simple query and see what is written in SQL Server’s plan cache.
WHERE TransactionID = 213429
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
WHERE st.text LIKE '%TransactionHistory%'
AND st.text NOT LIKE '%sys.dm_exec_cached_plans%'
Here are the results from the plan cache query:
We can see that SQL Server created two entries in the cache. The first entry is submitted query with constant value and it’s cached as objtype adhoc. It is a shell query and it doesn’t contain execution plan. A shell query contains only the exact adhoc batch text and pointer to the parameterized (prepared) plan. The second entry is parameterized query and has objtype of prepared. Prepared query contains compiled execution plan and query text with a parameter in place of a constant value. We can also see that shell query use 16KB of cache while prepared plan use 32KB. If we open a query_plan of the shell query we can see that shell query contains only pointer to the prepared plan. From (1) we can see which parameterized plan is used with parameterized query text and in (2) we can see which statement is submitted.
Let’s run same type of the query but with different constant value (213430). If we observe SELECT operator in the actual execution plan of the query we can tell that query is parameterized.
(1) In “Parameter List” we can see “Parameter Compiled Value” and “Parameter Runtime Value”. (2) Shows us parameterized text and (3) gives us hash value of the query plan. We can use QueryPlanHash value to return query plan and execution count of the parameterized query.
SELECT st.text, qs.execution_count, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
WHERE qs.query_plan_hash = 0xAFB37EEDFFA28FE3
If we look at results of plan cache query again, we can see that the second shell query is created with the exact text of the second query and it points to a parameterized plan.
Why shell query is cached when it only points to parameterized query? It’s cached only to make it easier to find parameterized query if the exact same query with the same constant is re-executed later.
Now, I will show one interesting behavior of simple parameterization. Let’s run same type of the query but with constant value of 5. Here are the results from the plan cache query:
Two new entries are created. (1) One shell query and one (2) prepared but with parameter data type of tinyint. It’s strange that the optimizer created a new prepared query with tinyint data type parameter when there is a cached plan with int parameter value. It seems that the optimizer, during simple parameterization, use parameter with the lowest possible data type for the specified constant value of number data type. If we execute the query with varchar or nvarchar constant value, data type varchar(8000) and nvarchar(4000) is used as parameter.
As I mentioned at the begging of the post, relatively small set of queries will be simple parameterized. Only simple queries whose plan doesn’t depend on constant value will be considered “safe” for simple parameterization and will be cached. Also, there are a lot of statement types which SQL Server will not even attempt to auto-parameterize. For complete list of these statement types you can check Apendix A in the article Plan Caching in SQL Server 2008.
Let’s take a look at these simple queries.
SELECT TransactionID, ProductID
WHERE ProductID = 725
WHERE ProductID = 725
If we execute those 2 queries and check results from plan cache query we can see that first query is parameterized and second one isn’t. Why?
For the first query, Query Optimizer thinks that nonclustered index seek is the best option for the query even when constant value change, because only columns we need are contained in the nonclustered index (clustered index is created on TransactionID column).
The second query is different, we want all columns from the table and a nonclustered index seek and key lookup might be a good choice when only a few rows are returned, but it might be a terrible choice when many rows are returned. If we look at query_plan from the second query we can see that query is parameterized. It seems that query went through auto-parameterization process but query optimizer decided that it is “unsafe” and parameterized query isn’t cached.
SQL server will simple parameterize some simple queries whose plan doesn’t depend on different constant values. During simple parameterization, if the optimizer decide that parameterized query is “safe”, two entries are written to the plan cache: shell query and prepared query. Shell query doesn’t contain full plan, it only point to parameterized plan which contain full execution plan for the query. Shell and parameterized queries are cached to reduce cost of generating execution plans. If we run a lot of adhoc queries with different constant value, more parameterized queries for the same type of query but with different parameter data type and large number of shell queries will be written to the plan cache.
Republished from DBA Journey [55 clicks].
Read the original version here [32134 clicks].