Query Optimization is resource-intensive process that could introduce significant CPU load on busy servers. SQL Server tries to minimize such load by caching plans in a special part of process memory called plan cache.

This chapter talks about plan caching in detail, and it consists of two parts: the first part provides a high-level overview of plan caching, and it discusses several issues associated with it. The second part dives deeper into plan cache internals, and it discusses the various data management views that you can use for plan cache monitoring.

Plan Caching Overview

SQL Server prevents unnecessary recompilations of queries by caching plans in a special area of the memory called plan cache. In addition to prepared parameterized queries and ad-hoc queries and batches, it caches plans of the various objects, such as stored procedures, triggers, user-defined functions, and a few others.

SQL Server does not cache actual execution plans, but rather it caches a set of other plan-related entities, mainly compiled plans. Every time when a query needs to be executed, SQL Server generates an actual execution plan from the compiled plan, which is an inexpensive operation as compared to compiled plan creation. Execution plans are run-time structures, and they are unique for each query execution; that is, if multiple sessions need to execute the same compiled plan, multiple execution plans would be generated at one per session.

A compiled plan is generated for the entire batch, and it includes plans for individual statements from the batch. In this chapter, I typically reference query- or statement-level plans; however, plans for multi-statement batches behave in the same way.

In addition to compiled plans, SQL Server caches other structures, such as compiled plan stubs, shell queries, and a couple others. We will talk about all of them in detail later in the chapter.

The number of cached plans does not directly affect the performance of SQL Server. However, plan cache uses memory and, therefore, it can reduce the size of the buffer pool, which, in turn, can increase the number of physical reads and decrease system performance.

SQL Server uses different algorithms to determine which plans should be removed from the cache in case of memory pressure. For ad-hoc queries, this selection is based strictly on how often a plan is reused. For other types of plans, the cost of plan generation is also factored into the decision.

SQL Server recompiles queries when it suspects that currently cached plans are no longer valid. One such case is the schema changes of the objects referenced by the plan. It includes the creation or dropping of columns, indexes, constraints, triggers, and statistics defined in a table.

Another case relates to stale statistics. SQL Server checks to see if the statistics are outdated when it looks up a plan from the cache, and it recompiles the query if they are. That recompilation, in turn, triggers a statistics update.

Temporary tables can increase the number of recompilations triggered by outdated statistics. As you will remember, SQL Server outdates statistics based on the number of modifications of the statistics (and index) columns. For regular tables, statistics update thresholds are as follows:

• When a table is empty, SQL Server outdates statistics when you add the data to the empty table.
• When a table has less than 500 rows, SQL Server outdates statistics after every 500 changes to the statistics columns.
• When a table has 500 or more rows, SQL Server outdates statistics after every 500 + (20% of the total number of rows in the table) changes to the statistics columns.

However, for temporary tables, there is another threshold value of six changes, which can lead to unnecessary recompilations in some cases. The KEEP PLAN query hint eliminates that threshold, and it makes the behavior of the temporary tables the same as the regular ones.

Another query hint, KEEPFIXED PLAN, prevents query recompilation in the case of outdated statistics. Queries would be recompiled only when the schema of the underlying tables are changed or the recompilation is forced, for example when a stored procedure is called using the WITH RECOMPILE clause.

Plan Cache can store multiple plans for the same queries, batches, or T-SQL objects. Some of the SET options, such as ANSI_NULL_DLFT_OFF, ANSI_NULL_DLFT_ON, ANSI_NULL, ANSI_PADDING, ANSI_WARNING, ARITHABORT, CONCAT_NULL_YELDS_NULL, DATEFIRST, DATEFORMAT, FORCEPLAN, DATEFORMAT, LANGUAGE, NO_BROWSETABLE, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER affect plan reuse. Plans generated with one set of SET options cannot be reused by sessions that use a different set of SET options.

Unfortunately, different client libraries and development environments have different default SET options. For example, by default ARITHABORT is OFF in ADO.Net and ON in Management Studio. Remember this when you troubleshoot inefficient queries submitted by client applications. You can get different execution plans when you run those queries in Management Studio.

Another common reason for duplicated plans in cache is using unqualified object names without specifying the object’s schema. In that case, SQL Server resolves objects based on the default schema of database users and, therefore, statements like SELECT * FROM Orders could reference completely different tables for different users, which prevents plan reuse. Alternatively, SELECT * FROM Sales.Orders always references the same table regardless of the default database schema for the user.

Finally, SQL Server does not cache plans if the batch or object includes string literals greater than 8KB in size. For example, the plan for the following query is not going to be cached when a constant used in the where clause has more than 8,192 characters:

SELECT * FROM Table1 WHERE Col='<insert more than 8,192 characters here>'

We will dive deeper into the plan cache internals later in this chapter after discussing a few practical questions related to plan caching.

Parameter Sniffing

Plan caching can significantly reduce CPU load on systems by eliminating unnecessary query compilations. However, it also introduces a few problems. The most widely known problem is called parameter sniffing. SQL Server sniffs parameter values at the time of optimization and generates and caches a plan that is optimal for those values. Nothing is wrong with this behavior. However, in some cases, when data is unevenly distributed, it leads to a situation when the generated and cached plan is optimal only for atypical, rarely used parameter values. Those cached plans could be suboptimal for further calls that are using more common values as parameters.

Most database professionals have experienced a situation when customers are suddenly complaining about system response time. Some queries or stored procedures took a much longer time to complete, even though there were no recent deployments to production. In most cases, these situations happened due to parameter sniffing when queries were recompiled due to a statistics update.

Let’s look at an example and create the table shown in Listing 26-1. We will populate it with data in such a way that most rows have Country value set to ’USA’. Finally, we will create a nonclustered index on the Country column.

Listing 26-1.  Parameter Sniffing: Table Creation

create table dbo.Employees
(
        ID int not null,
        Number varchar(32) not null,
        Name varchar(100) not null,
        Salary money not null,
        Country varchar(64) not null,
 
        constraint PK_Employees
        primary key clustered(ID)
);
 
;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
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2 ) -- 65,536 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.Employees(ID, Number, Name, Salary, Country)
        select
                Num,
                convert(varchar(5),Num),
                'USA Employee: ' + convert(varchar(5),Num),
                40000,
                'USA'
        from Nums;
 
;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
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N3)
insert into dbo.Employees(ID, Number, Name, Salary, Country)
        select
                65536 + Num,
                convert(varchar(5),65536 + Num),
                'Canada Employee: ' + convert(varchar(5),Num),
                40000,
                'Canada'
        from Nums;
 
create nonclustered index IDX_Employees_Country
on dbo.Employees(Country);

As a next step, let’s create a stored procedure that calculates the average salary for employees in a specific country. The code to do this is shown in Listing 26-2. Even though we are using a stored procedure in this example, the same situation could happen with parameterized queries called from client applications.

Listing 26-2.  Parameter Sniffing: Stored Procedure

create proc dbo.GetAverageSalary @Country varchar(64)
as
begin
        select Avg(Salary) as [Avg Salary]
        from dbo.Employees
        where Country = @Country
end

In this case, when the stored procedure is called with @Country='USA', the optimal execution plan is a Clustered Index Scan. However, for @Country='Canada', the better execution plan is a Nonclustered Index Seek with Key Lookup operations.

Let’s call the stored procedure twice: the first time with @Country='USA' and the second time with @Country='Canada', as shown in Listing 26-3.

Listing 26-3.  Parameter Sniffing: Calling a stored procedure

exec dbo.GetAverageSalary @Country='USA';
exec dbo.GetAverageSalary @Country='Canada';

As you can see in Figure 26-1, SQL Server compiles the stored procedure and caches the plan with the first call and reuses it later. Even though such a plan is less efficient with the @Country='Canada' parameter value, it may be acceptable when those calls are rare, which is expected with such a data distribution.

Now let’s take a look what happens if we swap those calls when the plan is not cached. We will use the DBCC FREEPROCCACHE command, which clears plan cache. Another instance when this might happen is with a statistics update that forces query to recompile. Listing 26-4 shows the code for achieving this.

Listing 26-4.  Parameter Sniffing: Clearing proc cache and calling the stored procedures again

dbcc freeproccache
go
 
exec dbo.GetAverageSalary @Country='Canada';
exec dbo.GetAverageSalary @Country='USA';

As you can see in Figure 26-2, SQL Server now caches the plan based on the @Country='Canada' parameter value. Even though this plan is more efficient when the stored procedure is called with @Country='Canada', it is highly inefficient for @Country='USA' calls.

There are a few ways to address the issue. You can force the recompilation of either stored procedure using EXECUTE WITH RECOMPILE or a statement-level recompile with OPTION (RECOMPILE) clauses. Obviously, a statement-level recompile is better because it performs the recompilation on a smaller scope. SQL Server sniffs the parameter values at the time of the recompilation, generating the optimal execution plan for each parameter value.

Listing 26-5 shows the statement-level recompile approach.

Listing 26-5.  Parameter Sniffing: Statement-level recompile

alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
        select Avg(Salary) as [Avg Salary]
        from dbo.Employees
        where Country = @Country
        option (recompile)
end
go
 
exec dbo.GetAverageSalary @Country='Canada';
exec dbo.GetAverageSalary @Country='USA';

As you can see in Figure 26-3, SQL Server recompiles the statement on every call, and it generates the most efficient execution plan for every parameter value.

The statement-level recompile may be a good option when the queries do not execute very often or in the case of complex queries, when the compilation time is just a fraction of the total execution time. However, it is hardly the best approach for OLTP queries, which are constantly running in the system due to the extra CPU load that recompilation introduces.

Another option is using an OPTIMIZE FOR hint, which forces SQL Server to optimize a query for the specific parameter values provided in the hint. Listing 26-6 illustrates such an approach.

Listing 26-6.  Parameter Sniffing: OPTIMIZE FOR hint

alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
        select Avg(Salary) as [Avg Salary]
        from dbo.Employees
        where Country = @Country
        option (optimize for(@Country='USA'))
end
go
 
exec dbo.GetAverageSalary @Country='Canada';
exec dbo.GetAverageSalary @Country='USA';

As you can see in Figure 26-4, SQL Server ignores the parameter value during the compilation and optimizes the query for the @Country='USA' value.

Unfortunately, the OPTIMIZE FOR hint introduces supportability issues, and it can lead to suboptimal execution plans in cases where the data distribution has changed. Listing 26-7 shows such an example. Let’s consider the situation, albeit unrealistic, when a company and all of its employees moved from the United States to Germany.

Listing 26-7.  Parameter Sniffing: OPTIMIZE FOR and data distribution change

update dbo.Employees set Country='Germany' where Country='USA';
 
exec dbo.GetAverageSalary @Country='Germany';

Statistics are outdated at the time of the update, which forces SQL Server to recompile the statement in the stored procedure. At this point, there are no rows in the table with Country='USA', and the recompilation produces a suboptimal execution plan, as shown in Figure 26-5. As a side note, the query uses more reads than before due to the index fragmentation introduced by the update.

SQL Server 2008 introduced another optimization hint, OPTIMIZE FOR UNKNOWN, which helps to address such situations. With this hint, SQL Server performs an optimization based on the most statistically common value in the table. Listing 26-8 shows the code involved in doing this.

Listing 26-8.  Parameter Sniffing: OPTIMIZE FOR UNKNOWN hint

alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
        select Avg(Salary) as [Avg Salary]
        from dbo.Employees
        where Country = @Country
        option (optimize for(@Country UNKNOWN))
end
go
 
exec dbo.GetAverageSalary @Country='Canada';

Figure 26-6 illustrates the execution plan. Germany is the most statistically common value in the table, and therefore SQL Server generates an execution plan that is optimal for such a parameter value.

You can achieve the same results with an OPTIMIZE FOR UNKNOWN hint by using local variables instead of parameters. This method also works with SQL Server 2005, where the OPTIMIZE FOR UNKNOWN hint is not supported. Listing 26-9 illustrates that approach. It introduces the same execution plan with a Clustered Index Scan, as shown in Figure 26-6.

Listing 26-9.  Parameter Sniffing: Using local variables

alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
        declare
                @CountryTmp varchar(64)
        set @CountryTmp = @Country
 
        select Avg(Salary) as [Avg Salary]
        from dbo.Employees
        where Country = @CountryTmp
end

You can troubleshoot issues introduced by parameter sniffing by analyzing cached plans with the sys.dm_exec_query_stats view and the sys.dm_exec_query_plan function. We will discuss this in more detail, including how to obtain execution plans for currently running statements, later in this and in the following chapter.

Plan Reuse

Plans cached by SQL Server must be valid for any combination of parameters during future calls that reuse the plan. In some cases, this can lead to situations where a cached plan is suboptimal for a specific set of parameter values.

One of the code patterns that often leads to such situations is the implementation of stored procedures that search for data based on a set of optional parameters. The typical implementation of such a stored procedure is shown in Listing 26-10. This code also creates two nonclustered indexes on the Employees table.

Listing 26-10.  Plan Reuse: Creation of stored procedure and indexes

create proc dbo.SearchEmployee
(
        @Number varchar(32) = null
        ,@Name varchar(100) = null
)
as
begin
        select Id, Number, Name, Salary, Country
        from dbo.Employees
        where
                ((@Number is null) or (Number=@Number)) and
                ((@Name is null) or (Name=@Name))
end
go
 
create unique nonclustered index IDX_Employees_Number
on dbo.Employees(Number);
 
create nonclustered index IDX_Employees_Name
on dbo.Employees(Name);

A plan cached by SQL Server should work with any combination of input parameters, regardless of their values at the time when the query was compiled. If you call stored procedures multiple times using the code from Listing 26-11, SQL Server decides to generate a plan with an IDX_Employees_Number Index Scan and Key Lookup Operations.

Listing 26-11.  Plan Reuse: Stored procedure calls

exec dbo.SearchEmployee @Number = '10000';
exec dbo.SearchEmployee @Name = 'Canada Employee: 1';
exec dbo.SearchEmployee @Number = '10000', @Name = 'Canada Employee: 1
';exec dbo.SearchEmployee @Number = NULL, @Name = NULL;

Figure 26-7 demonstrates Listing 26-11 execution plans for the stored procedure calls. As you can see, the query does not use the IDX_Employees_Number Nonclustered Index Seek operation, even when the @Number parameter has a NOT NULL value, because this plan would not be valid when @Number is NULL. When it is provided, SQL Server performs a Key Lookup operation for every row in the table, which is highly inefficient.

Similar to parameter sniffing issues, you can address this problem with statement-level recompilation. Listing 26-12 shows a new version of stored procedure.

Listing 26-12.  Plan Reuse: Stored procedure with statement-level recompile

alter proc dbo.SearchEmployee
(
        @Number varchar(32) = null
        ,@Name varchar(100) = null
)
as
begin
        select Id, Number, Name, Salary, Country
        from dbo.Employees
        where
                ((@Number is null) or (Number=@Number)) and
                ((@Name is null) or (Name=@Name))
        option (recompile)
end

If you call the stored procedures with the code from Listing 26-11, you would obtain the execution plans shown in Figure 26-8. As you can see, SQL Server recompiles the query on every call, and therefore it can choose the most beneficial execution plan for every parameter set. It is worth mentioning that the plans are not cached in cases where statement-level recompile is used.

Even though a statement-level recompile solves the problem, it introduces the overhead of constant recompilations, which you would like to avoid, of course, if stored procedures are called very often. One of the options that you have is to write multiple queries using IF statements covering all possible combinations of parameters. SQL Server would cache the plan for each statement in that case.

Listing 26-13 shows such an approach, however it quickly becomes unmanageable with a large number of parameters. The number of combinations to cover is equal to the number of parameters squared.

Listing 26-13.  Plan Reuse: Covering all possible parameter combinations

alter proc dbo.SearchEmployee
(
        @Number varchar(32) = null
        ,@Name varchar(100) = null
)
as
begin
        if @Number is null and @Name is null
                select Id, Number, Name, Salary, Country
                from dbo.Employees
        else if @Number is not null and @Name is null
                select Id, Number, Name, Salary, Country
                from dbo.Employees
                where Number=@Number
        else if @Number is null and @Name is not null
                select Id, Number, Name, Salary, Country
                from dbo.Employees
                where Name=@Name
        else
                select Id, Number, Name, Salary, Country
                from dbo.Employees
                where Number=@Number and Name=@Name
end

In the case of a large number of parameters, dynamic SQL becomes the only option. SQL Server will cache the execution plans for each dynamically generated SQL statement. Listing 26-14 shows such an approach. Remember that using dynamic SQL breaks ownership chaining, and it always executes in the security context of CALLER.

Listing 26-14.  Plan Reuse: Using dynamic SQL

alter proc dbo.SearchEmployee
(
        @Number varchar(32) = null
        ,@Name varchar(100) = null
)
as
begin
        declare
                @SQL nvarchar(max) = N'
select Id, Number, Name, Salary, Country
from dbo.Employees
where 1=1'

        if @Number is not null
                select @Sql = @SQL + N' and Number=@Number'
        if @Name is not null
                select @Sql = @SQL + N' and Name=@Name'
        exec sp_executesql @Sql, N'@Number varchar(32), @Name varchar(100)'
                ,@Number=@Number, @Name=@Name
end

Remember this behavior when you are using filtered indexes. SQL Server will not generate and cache a plan that uses a filtered index, in cases when that index cannot be used with some combination of parameter values. Listing 26-15 shows an example. SQL Server will not generate the plan, which is using the IDX_Data_UnprocessedData index, even when the @Processed parameter is set to zero because this plan would not be valid for a non-zero @Processed parameter value.

Listing 26-15.  Plan Reuse: Filtered Indexes

create unique nonclustered index IDX_Data_UnprocessedData
on dbo.RawData(ID)
include(Processed)
where Processed = 0;
 
-- Compiled Plan for the query would not use filtered index
select top 100 *
from dbo.RawData
where ID > @ID and Processed = @Processed
order by ID;

Plan Caching for Ad-Hoc Queries

SQL Server caches plans for ad-hoc queries (and batches), which use constants rather than parameters in the where clause. Listing 26-16 shows an example of ad-hoc queries.

Listing 26-16.  Ad-hoc queries

select * from dbo.Customers where LastName='Smith'
go
select * from dbo.Customers where LastName='Smith'
go
SELECT * FROM dbo.Customers WHERE LastName='Smith'
go
select * from dbo.Customers where LastName   =  'Smith'
go

SQL Server reuses plans for ad-hoc queries only in cases where the queries are exactly the same and a complete character-for-character match with each other. For example, the four queries from Listing 26-16 would introduce three different plans. The first and second queries are identical and share the plan. The two other queries would not reuse that plan due to the keywords’ upper- and lowercase mismatch and the extra space characters around the equality operator in the where clause.

Due to the nature of ad-hoc queries, they do not reuse plans very often. Unfortunately, cached plans for ad-hoc queries can consume a large amount of memory. Let’s look at an example and run 1,000 simple ad-hoc batches, as shown in Listing 26-17, checking the plan cache state afterwards. The script clears the content of the cache with the DBCC FREEPROCCACHE command.

Listing 26-17.  Ad-hoc queries memory usage: Running ad-hoc queries

dbcc freeproccache
go
 
declare
        @SQL nvarchar(max)
        ,@I int = 0
 
while @I < 1000
begin
        select @SQL =
                N'declare @C int;select @C=ID from dbo.Employees where ID='
                        + CONVERT(nvarchar(10),@I)
        exec(@SQL)
        select @I += 1
end
go
 
select
        p.usecounts, p.cacheobjtype, p.objtype, p.size_in_bytes,
        t.[text]
from
        sys.dm_exec_cached_plans p
                cross apply sys.dm_exec_sql_text(p.plan_handle) t
where
        p.cacheobjtype like 'Compiled Plan%' and
        t.[text] like '%Employees%'
order by
        p.objtype desc
option (recompile)

As you can see in Figure 26-9, there are 1,000 plans cached, each of which uses 32KB of memory, or 32MB total. As you can guess, ad-hoc queries in busy systems can lead to excessive plan cache memory usage.

SQL Server 2008 introduces a server-side configuration setting called Optimize for ad-hoc workloads. When this setting is enabled, SQL Server caches small, less than 300-byte structures, called Compiled Plan Stubs, instead of actual compiled plans. A Compiled Plan Stub is the placeholder that is used to keep track of which ad-hoc queries were executed. When the same query runs a second time, SQL Server replaces the Compiled Plan Stub with the actual compiled plan and reuses it going forward.

The Optimize for ad-hoc workloads setting is disabled by default. However, it should be enabled in most systems. Even though it introduces slight CPU overhead on the second ad-hoc query recompilation, it could significantly decrease plan cache memory usage on systems with heavy ad-hoc activity. That memory would be available for buffer pool, which could reduce the number of physical I/O operations and improve system performance.

You can enable that setting with the code shown in Listing 26-18. In addition, it can be enabled in the Advanced tab of the Server Properties window in Management Studio.

Listing 26-18.  Enabling Optimize for ad-hoc activity setting

exec sys.sp_configure N'optimize for ad hoc workloads', N'1'
go
reconfigure with override
go

If you ran the code from Listing 26-19 with the Optimize for ad-hoc workloads setting enabled, you would see the plan cache content shown in Figure 26-10. As you can see, now it uses just 272KB of memory rather than the 32MB it used before.

Auto-Parameterization

In some cases, SQL Server may decide to replace some constants in ad-hoc queries with parameters and cache compiled plans as if the queries were parameterized. When this happens, similar ad-hoc queries that use different constants can reuse cached plans.

For example, queries in Listing 26-19 could be parameterized and share the compiled plan.

Listing 26-19.  Parameterization

select ID, Number, Name from dbo.Employees where ID = 5
go
select ID, Number, Name from dbo.Employees where ID = 10
go

Internally, SQL Server stores the compiled plan as shown below:

(@1 tinyint)SELECT [ID],[Number],[Name] FROM [dbo].[Employees] WHERE [ID]=@1

By default, SQL Server defines a parameter data type based on a constant value, choosing the smallest data type where the value fits. For example, the query SELECT ID, Number, Name FROM dbo.Employees WHERE ID = 10000 would introduce another cached plan, as shown here:

(@1 smallint)SELECT [ID],[Number],[Name] FROM [dbo].[Employees] WHERE [ID]=@1

When parameterization occurs, SQL Server stores another structure in the plan cache, called Shell Query, in addition to the compiled plan of the parameterized query. The shell query uses about 16KB of memory and stores information about the original query and links it to the compiled plan.

You can see the content of plan cache after we ran the queries from Listing 26-19 in Figure 26-11. As you can see, it stores the compiled plan and two shell queries.

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
,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 rows
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 rows
,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2 ) -- 65,536 rows
,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
insert into dbo.RawData(Processed)
        select 1
        from Ids;
 
insert into dbo.RawData(Processed)
        select 0
        from dbo.RawData
        where RecId <= 16;
 
create unique nonclustered index IDX_RawData_Processed_Filtered
on dbo.RawData(RecId)
include(Processed)
where Processed = 0;

For the next step, let’s run the queries that count a number of unprocessed rows in both simple and forced parameterization mode. Listing 26-21 shows the code to achieve this.

Listing 26-21.  Forced Parameterization and Filtered Indexes: Test queries

select count(*)
from dbo.RawData
where Processed = 0
go
 
alter database ParameterizationTest set parameterization forced
go
 
select count(*)
from dbo.RawData
where Processed = 0

If you examine the execution plans shown in Figure 26-12, you will notice that SQL Server utilized a filtered index in the case of a simple parameterization. SQL Server can cache this plan because of the constant in the Processed=0 predicate. That plan would be valid regardless of the value of the @RecId parameter. Alternatively, with forced parameterization, SQL Server parameterizes the query using the parameter in the Processed=@0 predicate. Therefore, it cannot cache the plan with the filtered index because it would not be valid for the case when a query selects processed (Processed=1) rows. SQL Server generated the execution plan with a Clustered Index Scan, which is far less efficient in this case.

One of the good use-cases for forced parameterization is the complex ad-hoc queries submitted by a client application in cases when the choice of execution plan does not depend on constant values. While it is better to change the client application and parameterize queries, it is not always possible.

Listing 26-22 shows an example of such a query. Every query execution leads to a compilation, and it adds an entry to plan cache. Such a query benefits from forced parameterization because the most optimal execution plan for the query is a Clustered Index Seek, and it does not change based on the constant/parameter value.

Listing 26-22.  Example of a query that benefits from forced parameterization

select top 100 RecId, /* Other Columns */
from dbo.RawData
where RecID > 432312 -- Client application uses different values at every call
order by RecId

With all that being said, you should be careful with forced parameterization when you enable it at the database level. It is safer to enable it on an individual query level if needed.

Plan Guides

Query hints can be extremely useful in helping to resolve various plan caching related issues. Unfortunately, in some cases, you are unable to modify the query text, either because you do not have access to the application code or if the recompilation and redeployment is impossible or impractical.

You can solve such problems by using plan guides, which allow you to add hints to the queries or even force specific execution plans without changing a query’s text. You can create them with sp_create_plan_guide and manage them with sp_control_plan_guide stored procedures.

There are three types of plan guides available:

• An Object plan guide allows you to specify a hint for a query, which exists in a T-SQL object, such as a stored procedure, trigger, or user-defined function.
• A SQL plan guide allows you to specify a hint for a particular SQL query, either standalone or as part of a batch.
• A Template plan guide allows you to specify a type of parameterization—FORCED or SIMPLE—for a particular query template overriding the database setting.

The code in Listing 26-23 removes the query hint from the dbo.GetAverageSalary stored procedure and creates a plan guide with an OPTIMIZE FOR UNKNOWN hint. The @Stmt parameter should specify a query where a hint needs to be added, and @module_or_batch should specify the name of the object.

Listing 26-23.  Object plan guide

alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
        select Avg(Salary) as [Avg Salary]
        from dbo.Employees
        where Country = @Country
end
go
 
exec sp_create_plan_guide
        @type = N'OBJECT'
        ,@name = N'object_plan_guide_demo'
        ,@stmt = N'select Avg(Salary) as [Avg Salary]
        from dbo.Employees
        where Country = @Country'
        ,@module_or_batch = N'dbo.GetAverageSalary'
        ,@params = null
        ,@hints = N'OPTION (OPTIMIZE FOR (@Country UNKNOWN))';

Now if you ran the stored procedure for @Country = 'Canada', you would get the execution plan shown in Figure 26-13, similar to what you had with the query hint within the stored procedure. You can see that a plan guide was used during optimization in the properties of the top operator in the graphical plan as well as in its XML representation.

Listing 26-24 shows an example of a SQL plan guide, which set the MAXDOP option for the query. In this mode, the @module_or_batch parameter should be set to null.

Listing 26-24.  SQL plan guide

exec sp_create_plan_guide
        @type = N'SQL'
        ,@name = N'SQL_plan_guide_demo'
        ,@stmt = N'select Country, count(*) as [Count]
from dbo.Employees
group by Country'
        ,@module_or_batch = NULL
        ,@params = null
        ,@hints = N'OPTION (MAXDOP 2)'  ;

Working with Template plan guides is a bit more complex. Unlike SQL and Object plan guides, where the @stmt parameter should be a character-for-character match with the queries, a template plan guide requires you to provide the template for the query. Fortunately, you can use another system stored procedure, sp_get_query_template, to prepare it.

Let’s look at an example and assume that we want SQL Server to autoparameterize the query from Listing 26-25. Even though the execution plan for the query is safe—a Clustered Index Seek on a unique index would always return one row, the TOP clause prevents SQL Server from parameterizing it. You can see the ad-hoc cached plan in Figure 26-14.

Listing 26-25.  Template plan guide: Sample Query

dbcc freeproccache
go
 
select top 1 ID, Number, Name from dbo.Employees where ID = 5;
go
 
select
        p.usecounts, p.cacheobjtype, p.objtype, p.size_in_bytes,
        t.[text]
from
        sys.dm_exec_cached_plans p
                cross apply sys.dm_exec_sql_text(p.plan_handle) t
where
        t.[text] like '%Employees%'
order by
        p.objtype desc
option (recompile);

Listing 26-26 shows you how to create a template plan guide and override the PARAMETERIZATION database option.

Listing 26-26.  Template plan guide: Creating a plan guide

declare
        @stmt nvarchar(max)
        ,@params nvarchar(max)
 
-- Getting template for the query
exec sp_get_query_template
        @querytext =
                N'select top 1 ID, Number, Name from dbo.Employees where ID = 5;'
        ,@templatetext = @stmt output
        ,@params = @params output;

-- Creating plan guide
exec sp_create_plan_guide
        @type = N'TEMPLATE'
        ,@name = N'template_plan_guide_demo'
        ,@stmt = @stmt
        ,@module_or_batch = null
        ,@params = @params
        ,@hints = N'OPTION (PARAMETERIZATION FORCED)'

Now if you ran the code from Listing 26-25, the statement would be parameterized, as shown in Figure 26-15.

As a final option, you can force SQL Server to use a specific execution plan by specifying it in the plan guide, or use it with the USE PLAN query hint. Listing 26-27 shows an example of both approaches. The full XML plan is omitted to conserve space in the book.

Listing 26-27.  Forcing XML Query Plan

-- Using USE PLAN query hint
select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = 'Germany'
option (use plan N'<?xml version="1.0"?>
<ShowPlanXML>
        <!-- Actual execution plan here -->
</ShowPlanXML>'),
go
 
-- Using Plan Guide
declare
        @Xml xml = N'<?xml version="1.0"?>
<ShowPlanXML>
        <!-- Actual execution plan here -->
</ShowPlanXML>'
 
declare
        @XmlAsNVarchar nvarchar(max) = convert(nvarchar(max),@Xml)
 
exec sp_create_plan_guide
        @type = N'SQL'
        ,@name = N'xml_plan_guide_demo'
        ,@stmt = N'select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = ''Germany'''
        ,@module_or_batch = NULL
        ,@params = null
        ,@hints = @XmlAsNVarchar;

While both the query hint and plan guide force SQL Server to use a specific execution plan, in SQL Server 2008 and above, they exhibit different behaviors when the plan becomes incorrect. Query Optimizer will ignore an incorrect plan guide and generate the plan, as if the plan guide has not been specified. A query with USE PLAN hint, on the other hand, would generate an error. An example of such an error is shown below. SQL Server 2005, however, fails the query if an invalid plan guide is specified.

Msg 8712, Level 16, State 0, Line 1
Index 'tempdb.dbo.Employees.IDX_Employees_Country', specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

Starting with SQL Server 2008, you can use the sys.fn_validate_plan_guide system function to check if a plan guide is still valid. The code in Listing 26-28 shows an example of this.

Listing 26-28.  Validating plan guides

select
        pg.plan_guide_id, pg.name, pg.scope_type_desc
        ,pg.is_disabled, vpg.message
from sys.plan_guides pg
        cross apply
        (
                select message
                from sys.fn_validate_plan_guide(pg.plan_guide_id)
        ) vpg

The sys.fn_validate_plan_guide function returns a row if the plan guide is incorrect. You can see an example of its output in Figure 26-16.

As a final note, plan guides are only supported in the Standard, Enterprise, and Developer editions of SQL Server. You can still create plan guides in the unsupported editions, however Query Optimizer will ignore them.

Plan Cache Internals

SQL Server separates plan cache into four different memory areas called cache stores. Each cache store caches different entities and plans.

• The SQL Plans cache store (internal name CACHESTORE_SQLCP) stores plans for parameterized and ad-hoc queries and batches, as well as for autoparameterized plans.
• The Object Plans cache store (CACHEHSTORE_OBJCP) stores plans for T-SQL objects, such as stored procedures, triggers, and user-defined functions.
• The Extended Stored Procedures cache store (CACHEHSTORE_XPROC) stores plans for extended stored procedures.
• The Bound Trees cache store (CACHESTORE_PHDR) stores bound trees generated during the query optimization stage.

You can monitor the size of each cache store with a select statement, as shown in Listing 26-29.

Listing 26-29.  Checking a cache store’s size

select
        type as [Cache Store]
        ,sum(pages_in_bytes) / 1024.0 as [Size in KB]
from sys.dm_os_memory_objects
where type in
        ('MEMOBJ_CACHESTORESQLCP','MEMOBJ_CACHESTOREOBJCP'
        ,'MEMOBJ_CACHESTOREXPROC','MEMOBJ_SQLMGR')
group by
        type

Each cache store uses a hash table in which hash buckets keep zero or more plans. There are about 40,000 buckets in the Object plan and the SQL plan stores in 64-bit instances, and about 10,000 buckets in 32-bit instances of SQL Server. The size of the bound trees cache store is about 1/10th of that number, and the number of buckets in the extended stored procedures store is always 127.

SQL Server uses a very simple algorithm to calculate the hash value for the plan based on the following formula:

(object_id * database_id) mod hash_table_size

For parameterized and ad-hoc queries, object_id is the internal hash of the query or batch. It is entirely possible that one bucket stores multiple plans for the same object or query. As we have already discussed, different SET options, database users, and quite a few other factors prevent plan reuse. SQL Server compares multiple plan attributes in looking for the right plan in the cache. We will discuss how to analyze plan attributes later in this chapter.

Compiled plans cached for multi-statement batches are, basically, the arrays of individual statement-level plans. When a statement from a batch needs to be recompiled, SQL Server recompiles the individual statement rather than the entire batch.

SQL Server treats a cached batch plan as a single unit. The entire batch must be a character-for-character match with original batch that produced the cached plan in order for that plan to be reused. SQL Server generates an execution plan from the compiled plan for the entire batch.

The amount of memory that can be used by Plan Cache depends on the version of SQL Server (see Table 26-1).

Visible memory is different in 32-bit and 64-bit instances of SQL Server. 32-bit instances of SQL Server have at most 2GB or 3GB of visible memory, depending on the presence of a /3GB switch in the boot.ini file. Even when AWE (Address Windows Extension) memory is in use, memory above 4GB can be used for the buffer pool only. No such limitation exists on 64-bit instances of SQL Server.

SQL Server starts to remove plans from the cache in cases of memory pressure. There are two kinds of memory pressure: local and global. Local memory pressure happens when one of the cache stores grows too big and starts using too much SQL Server process memory. Global memory pressure happens when Windows forces SQL Server to reduce its physical memory usage, or when the size of all cache stores combined reaches 80 percent of the plan cache pressure limit.

Local memory pressure is triggered when one of the cache stores starts to use too much memory. In SQL Server 2005-2008R2, where single-page and multi-page allocations are treated separately, memory pressure occurs when a cache store reaches 75 percent of the plan cache pressure limit in a single-page or 50 percent in multi-page allocations. In SQL Server 2012-2014, there is only one memory allocator, called the any-size page allocator, and memory pressure is triggered when a cache store grows to 62.5 percent of the plan cache pressure limit.

Local memory pressure can also be triggered based on the number of plans in the SQL and Object plan stores. That number is about four times the hash table size, which is 40,000 or 160,000 plans on 32-bit and 64-bit instances respectively.

Both local and global memory pressure remove plans from the cache using an algorithm called eviction policy, which is based on plan cost. For ad-hoc plans, the cost starts with zero and increments by one with every plan reuse. Other types of plans measure the cost of resources required to produce them. It is based on I/O, memory, and context switches in the units, called ticks, as shown below:

• I/O: Each I/O operation costs 1 tick with a maximum of 19
• Memory: Each 16 pages of memory costs 1 tick with a maximum of 4
• Context Switches: Each switch costs 1 tick with a maximum of 8.

When not under memory pressure, costs are not decreased until the total size of all cached plans reaches 50 percent of the buffer pool size. At that point, the Lazy Writer process starts periodically scanning plan caches, decrementing the cost of each plan by one on each scan, removing plans with zero cost. Alternatively, each plan reuse increments its cost by one for ad-hoc queries, or by the original plan generation cost for other types of plans.

Listing 26-30 shows you how to examine the current and original cost of cached entries in SQL and Object plan cache stores.

Listing 26-30.  Examining original and current cost of cache entries

select
        q.Text as [SQL], p.objtype, p.usecounts, p.size_in_bytes
        ,mce.Type as [Cache Store]
        ,mce.original_cost, mce.current_cost, mce.disk_ios_count
        ,mce.pages_kb  /* Use pages_allocation_count in SQL Server prior 2012 */
        ,mce.context_switches_count
        ,qp.query_plan
from
        sys.dm_exec_cached_plans p with (nolock) join
                sys.dm_os_memory_cache_entries mce with (nolock) on
                        p.memory_object_address = mce.memory_object_address
        cross apply
                sys.dm_exec_sql_text(p.plan_handle) q
        cross apply
                sys.dm_exec_query_plan(p.plan_handle) qp
where
        p.cacheobjtype = 'Compiled plan' and
        mce.type in (N'CACHESTORE_SQLCP',N'CACHESTORE_OBJCP')
order by
        p.usecounts desc

Examining Plan Cache

There are several data management views that provide plan cache related information. Let’s look at some of them in depth.

As you already saw, the sys.dm_exec_cached_plans view provides information about every plan stored in the SQL and Object plan cache stores. The key column in the view is plan_handle, which uniquely identifies the plan. In the case of a batch, that value remains the same even when some statements from the batch are recompiled. In addition to plan_handle, this view provides information about the type of plan (Compiled Plan, Compiled Plan Stub, and so forth) in the cacheobjtype column, type of object (Proc, Ad-Hoc query, Prepared, Trigger, and so on) in the objtype column, reference and use counts, memory size, and a few other attributes.

The Data Management Function sys.dm_exec_plan_attributes accepts plan_handle as a parameter and returns a set of the attributes of a particular plan. Those attributes include references to the database and object to which the plan belongs, user_id of the session that submits the batch, and quite a few other attributes.

One of the attributes, sql_handle, links plan to the batch to which plan has been compiled. You can use it together with the sys.dm_exec_sql_text function to obtain its SQL text.

Each attribute has a flag if it is included in the cache key. SQL Server reuses plans only when both the sql_handle and cache key of the cached plan match the values from the submitted batch. Think about the set_option attribute as an example. It is included in the cache key and, therefore, different SET options would lead to different cache key values, which would prevent plan reuse.

One SQL batch, identified by sql_handle, can have multiple plans, identified by plan_handle—one for each cache key attributes value. Listing 26-31 illustrates an example of this.

Listing 26-31.  SQL_Handle and plan_handle relations

dbcc freeproccache
go
 
set quoted_identifier off
go
 
select top 1 ID from dbo.Employees where Salary > 40000
go
 
set quoted_identifier on
go
 
select top 1 ID from dbo.Employees where Salary > 40000
go
 
;with PlanInfo(sql_handle, plan_handle, set_options)
as
(
        select pvt.sql_handle, pvt.plan_handle, pvt.set_options
        from
        (
                select p.plan_handle, pa.attribute, pa.value
                from
                        sys.dm_exec_cached_plans p with (nolock) outer apply
                                sys.dm_exec_plan_attributes(p.plan_handle) pa
                where cacheobjtype = 'Compiled Plan'
        ) as pc
        pivot (max(pc.value) for pc.attribute
                IN ("set_options", "sql_handle")) AS pvt
)
select pi.sql_handle, pi.plan_handle, pi.set_options, b.text
from
        PlanInfo pi cross apply
                sys.dm_exec_sql_text(convert(varbinary(64),pi.sql_handle)) b
option (recompile)

Figure 26-17 shows two different plans for the same SQL batch due to the difference in SET options.

You can obtain an XML representation of the execution plan with the sys.dm_exec_query_plan function, which accepts plan_handle as a parameter. However, it does not return a query plan if the XML plan has more than 128 nested levels due to XML data type limitations. In that case, you can use the sys.dm_exec_text_query_plan function, which returns a text representation of the XML plan instead.

You can retrieve the information about currently executed requests by using the sys.dm_exec_requests view. Listing 26-32 shows the query, which returns the data on currently running requests from user sessions sorted by their running time in descending order.

Listing 26-32.  Using sys.dm_exec_requests

select
        er.session_id
        ,er.user_id
        ,er.status
        ,er.database_id
        ,er.start_time
        ,er.total_elapsed_time
        ,er.logical_reads
        ,er.writes
        ,substring(qt.text, (er.statement_start_offset/2)+1,
                ((
                        case er.statement_end_offset
                                when -1 then datalength(qt.text)
                                else er.statement_end_offset
                        end - er.statement_start_offset)/2)+1) as [SQL],
        qp.query_plan, er.*
from
        sys.dm_exec_requests er with (nolock)
                cross apply sys.dm_exec_sql_text(er.sql_handle) qt
                cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where
        er.session_id > 50 and /* Excluding system processes */
        er.session_id <> @@SPID
order by
        er.total_elapsed_time desc
option (recompile)

The sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, and sys.dm_exec_trigger_stats views provide aggregated performance statistics for queries, procedures, and triggers that have cached plans. They return one row for every cached plan per object, as long as the plan stays in the cache. These views are extremely useful during performance troubleshooting. We will discuss their use in depth in the next chapter.

Sys.dm_exec_query_stats is supported in SQL Server 2005 and above. Sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats were introduced in SQL Server 2008.

Summary

Query Optimization is an expensive process, which increases CPU load on busy systems. SQL Server reduces such load by caching plans in a special part of memory called plan cache. It includes plans for T-SQL objects, such as stored procedures, triggers, and user-defined functions; ad-hoc queries and batches, and a few other plan-related entities.

SQL Server reuses plans for ad-hoc queries and batches only when there is a character-for-character match of the query/batch texts. Moreover, different SET options and/or reference to unqualified objects could prevent plan reuse.

Caching plans for ad-hoc queries can significantly increase plan cache memory usage. It is recommended that you enable the server-side “Optimize for Ad-hoc Workloads” configuration setting if you are using SQL Server 2008 and above.

SQL Server sniffs parameters and generates and caches plans, which are optimal for parameter values at the time of compilation. In cases of uneven data distribution, this could lead to performance issues when cached plans are not optimal for the typically submitted parameter values. You can address such issues with a statement-level recompile or OPTIMIZE FOR query hints.

You can specify hints directly in queries. Alternatively, you can use plan guides, which allow you to apply hints or force specific execution plans without changing the query text.

Cached plans should be valid for every possible combination of parameters. This can lead to suboptimal plans when a query has OR conditions to support optional parameter values. You can address such issues with a statement-level recompile, or by building SQL dynamically and omitting OR conditions.