Autoparameterization

This one is interesting on SQL Server, it allows a given execution plan to be reused among many similar ad-hoc queries. but is very important to sql server to watch if the parameter may have some influence on the number of rows.  The index selection is the same for all the parametrized queries, so if one query returns one row and another execution of the same query returns many rows it can be a problem. In this cases the parameterization is considered unsafe.

Here are some examples of unsafe (for parameterization) structures in SQL Server:

• JOIN

• BULK INSERT

• IN lists

• UNION

• INTO

• FOR BROWSE

• OPTION <query hints>

• DISTINCT

• TOP

• WAITFOR statements

• GROUP BY, HAVING, COMPUTE

• Full Text operators (Contains / Freetext)

• Subqueries

• FROM clause of a SELECT statement has table valued return or full-text table or OPENROWSET or OPENXML or OPENQUERY or OPENDATASOURCE --> Derived queries

• Comparison of the form EXPR <> a non-null constant

You can take a look of the parameterization operations in sql server by running this query

 

-- Excerpt from book Inside SQL Server: Query Tuning and Optimization

USE Northwind2
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 2;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
   AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO

 

The dm_exec_cached_plans can show some information about the stored plans and the form and parametrization information about them.

 

Still talking about safe and unsafe parameterization operations, there is a property on databases called  Parameterization. It can be set simple or forced. Forced means that sql server will take as parameters all the constants inside your query with few exceptions. the benefit is you compile less queries... so you save cpu time for other operations.

I may take for example a financial system that i was doing a tuning for sql server, in one hour the system sends to sql server thousands of small queries, setting parameterization forced save a lot of cpu time and memory.

The dark side of the force is that when you use parametrized plans, for queries that maybe have different cardinalities the plans might not be optimal, in such way that your perofrmance is affected.

Then? what is the right path?

Young padawan, the right way is always test, test with simple, and monitor with profiler, and check the plans... test with forced see the cpu use, and check the plans... always testing and choosing based on testing experience. It depends on a lot of factors, the server, workload, application architecture... and so on.