Database Reference
In-Depth Information
Figure 26-11.
Plan cache content after parameterization occurred
By default, SQL Server uses
SIMPLE parameterization,
and it is very conservative in parameterizing queries.
Simple parameterization only happens when a cached plan is considered
safe to parameterize
, which means that the
plan would be the same in terms of plan shape and cardinality estimations, even when constant/parameter values
have changed. For example, a plan with a
Nonclustered Index Seek
and
Key Lookup
on a unique index is safe because
it would never return more than one row, regardless of the parameter value. Conversely, the same operation on a
non-unique index is not safe. Different parameter values lead to different cardinality estimations, which makes a
Clustered Index Scan
the better choice for some of them. Moreover, there are many language constructs that prevent
simple parameterization, such as
IN
,
TOP
,
DISTINCT
,
JOIN
,
UNION
, subqueries, and quite a few others.
Alternatively, SQL Server can use
FORCED parameterization
, which can be enabled at the database level with
the
ALTER DATABASE SET PARAMETRIZATION FORCED
command or on the query level with a
PARAMETRIZATION FORCED
hint. In this mode, SQL Server auto parameterizes most ad-hoc queries with very few exceptions.
As might be expected, forced parameterization comes with a set of benefits and drawbacks. While on one hand
it can significantly reduce the size of plan cache and CPU load, it also increases the chance of suboptimal execution
plans due to parameter sniffing issues.
Another problem with forced parameterization is that SQL Server replaces constants with parameters without
giving you any control about the constants you want to parameterize. This is especially critical for filtered indexes
when parameterization can prevent SQL Server from generating and caching a plan that utilizes them. Let's look at a
particular example and create a database with a table with a filtered index and populate it with some data, as shown
in Listing 26-20.
Listing 26-20.
Forced Parameterization and Filtered Indexes: Table creation
use master
go
create database ParameterizationTest
go
use ParameterizationTest
go
create table dbo.RawData
(
RecId int not null identity(1,1),
Processed bit not null,
Placeholder char(100),
constraint PK_RawData
primary key clustered(RecId)
);
/* Inserting:
Processed = 1: 65,536 rows
Processed = 0: 16 rows */
;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0) -- 2 rows
,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2) -- 4 rows
