Autoparameterization
A second method for plan reuse is autoparameterization. For simple queries, SQL Server guesses which constants might be parameters and then attempts to treat them as parameters. This method lets SQL Server treat your query as a stored procedure. If the autoparameterization attempt is successful, subsequent queries that follow the same basic template can use the same plan.
SQL Server can use four templates for autoparameterization, which means that the query must be one of the query types shown below.
INSERT table VALUES ({constant | NULL |
DEFAULT}, ...)
DELETE table WHERE key-expression
UPDATE table SET colname = constant WHERE
key-expression
SELECT {* | column-list} FROM table WHERE
key-expression [ORDER BY column-list]
(Note that key-expression is an expression involving only column names, constants, AND operators, and comparison operators: <, >, =, <=, >=, and <>. It doesn't need to involve a key column.)
For example, these two queries use the same plan:
SELECT CustomerID, OrderDate, ShipName FROM
orders
WHERE orderID = 10255
SELECT CustomerID, OrderDate, ShipName FROM
orders
WHERE orderID = 10355
Internally, SQL Server parameterizes these queries as
SELECT CustomerID, OrderDate, ShipName FROM orders
WHERE orderID = @p
If you run the two queries with the STATISTICS TIME option on, rows return for parse-and-compile time for each query. Parse-and-compile results for the first query might look like those in Figure 2.
The first line reports the time to compile the query and save its plan in the parameterized form shown above. In the second parse-and-compile line, you see that SQL Server is using the parameterized form of the query. Note that the time is zero because the plan is already compiled. One way to tell that SQL Server is using autoparameterization is that two parse-and-compile lines appear in the output for STATISTICS TIME.
Another technique you can use to determine whether the query processor has used autoparameterization and not ad hoc query plan caching is to examine the syscacheobjects system table in the Master database. This system table keeps track of the compiled objects in cache at any time. This table is accessible only by system administrators. Looking at the rows in the table, you see that the objtype column tells you whether the query plan was saved as an ad hoc plan or as a prepared, parameterized plan. The syscacheobjects table also shows two different types of plans: compiled and executable. The value of the cacheobjtype column identifies the type. A compiled plan is the part of a plan that is reentrant, and multiple users can share it. Think of an executable plan as an instance of a compiled plan that contains information describing a process that executes the query. In most cases, both compiled and executable plans remain in the memory cache, subject to memory pressure from other processes or applications.
Prev. page
1
[2]
3
next page 
