SQL Server Magazine

SQL Server can let other queries of the same template use the same plan only if the template is safe. A template is safe if the plan doesn't change even if the parameters change. You typically don't see autoparameterization if a WHERE clause involves an inequality or if the clause is searching for a set of duplicate values. This limitation ensures that autoparameterization won't degrade a query's performance.

When attempting autoparameterization, SQL Server is lenient about white space in your queries, but it won't reuse a plan if you change capitalization or fully qualify an object owner. Like caching the plans for ad hoc queries, autoparameterization is convenient, but it doesn't always result in the best plan. When deciding whether a template is safe, the SQL Server query processor is more conservative than your application can be. SQL Server guesses which values are parameters, whereas your application probably knows. SQL Server uses these techniques to optimize performance, but because the optimizer is so conservative about plan reuse, I recommend that you use one of the following two mechanisms to mark parameters.

The sp_executesql Procedure
The sp_executesql stored procedure falls between ad hoc caching and stored procedures. Using sp_executesql requires that you identify parameters when you call the procedure, but it doesn't require SQL Server to keep track of your code as a permanent object in the system tables, as it does for stored procedures.

The procedure requires that you specify the base text of your query, the parameter definitions, and the values of the parameters, by using this syntax:

sp_executesql @query_text, @batch_parameter
	_definitions,param1,...paramN

Repeated calls with the same query_text use the cached plan, with the new parameter values specified. All the following cases use the same cached plan:

sp_executesql N'insert mytable
	values(@p)',N'@p float',1.0 
go
sp_executesql N'insert mytable
	values(@p)',N'@p float',2.0 
go
sp_executesql N'insert mytable
	values(@p)',N'@p float',1.0
go

ODBC and OLE DB expose this functionality via SQLExecDirect and ICommandWithParameters. Refer to your ODBC and OLE DB documentation for more details.

The Prepare and Execute Method
With the prepare and execute method, as with sp_executesql, the application identifies parameters to the batch. However, the prepare and execute method doesn't require that the full text of the batch be sent at each execution. Instead, the full text is sent once at prepare time; a handle that you can use to invoke the batch at execute time is returned. ODBC and OLE DB expose this functionality via SQLPrepare/SQLExecute and IcommandPrepare. By using these functions, you tell SQL Server to use this batch repeatedly. This function isn't available through Transact SQL (T-SQL) programming; you must use the API.

Monitoring Plan Caching
In addition to using SET STATISTICS TIME ON and examining the syscacheobjects table, you can use the SQL Server Performance Monitor to monitor plan caching. The performance monitor has a counter called SQL Server:SQL Statistics and several counters dealing with autoparameterization. You can monitor these counters to determine how many failed or unsafe autoparameterization attempts have occurred. A failed parameterization attempt means that the query doesn't match one of the basic templates. An unsafe autoparameterization attempt means that the query matched one of the templates but is unsafe because its optimal plan changes if the parameters change. If the failed and unsafe autoparameterization numbers are high, inspect your applications for situations in which the application can explicitly mark the parameters.

SQL Server's ability to automatically reuse plans can go a long way toward making queries run as fast as possible. Whenever possible, tell SQL Server which values are the queries' parameters. There are many advantages to using stored procedures to save plans and identify parameters, and I'll talk about stored procedure plan caching in a future column. But when you choose not to use stored procedures, the new autoparameterization mechanisms are a good option.

End of Article

Prev. page     1 2 [3]     next page -->