Simple Parameterization and Trivial Plans—Part 1

Series index: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6

Introduction

This is the first part of a series about simple parameterization and trivial plans. These two compilation features are closely connected and have similar goals. Both target performance and efficiency for workloads frequently submitting simple statements.

Despite the ‘simple’ and ‘trivial’ names, both have subtle behaviours and implementation details that can make how they work difficult to understand. This series does not dwell too long on the basics, but concentrates on less well-known aspects likely to trip up even the most experienced database professionals.

In this first part, after some quick background, I look at the effects of simple parameterization on the plan cache.

Simple Parameterization

It is almost always better to explicitly parameterize statements, rather than relying on the server to do it. Being explicit gives you complete control over all aspects of the parameterization process, including where parameters are used, the precise data types used, and when plans are reused.

Most clients and drivers provide specific ways to use explicit parameterization. There are also options like sp_executesql, stored procedures, and functions.

I’m not going to get into the related issues of parameter sniffing or SQL injection because, while important, they are not the focus of this series. Still, you should write code with both close to the front of your mind.

For legacy applications, or other third-party code that cannot be easily changed, explicit parameterization may not always be possible. You may be able to overcome some obstacles using template plan guides. In any event, it would be an unusual workload that does not contain at least some statements which are parameterized server-side.

Shell Plans

When SQL Server 2005 introduced Forced Parameterization, the existing auto-parameterization feature was renamed to Simple Parameterization. Despite the change in terminology, simple parameterization works just the same as auto-parameterization always did: SQL Server attempts to replace constant literal values in ad-hoc statements with parameter markers. The aim is to reduce compilations by increasing cached plan reuse.

Let’s look at an example, using the Stack Overflow 2010 database on SQL Server 2019 CU 14. Database compatibility is set to 150, and the cost threshold for parallelism is set to 50 to avoid parallelism for the moment:

EXECUTE sys.sp_configure
    @configname = 'show advanced options',
    @configvalue = 1;
RECONFIGURE;
GO
EXECUTE sys.sp_configure
    @configname = 'cost threshold for parallelism',
    @configvalue = 50;
RECONFIGURE;

Example code:

-- Clear the cache of plans for this database
ALTER DATABASE SCOPED CONFIGURATION 
    CLEAR PROCEDURE_CACHE;
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 2521;
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 2827;
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 3144;
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 3151;
GO

Those statements feature predicates that differ only in their constant literal values. SQL Server successfully applies simple parameterization, resulting in a parameterized plan. The single parameterized plan is used four times as we can see by querying the plan cache:

SELECT
    CP.usecounts,
    CP.cacheobjtype,
    CP.objtype,
    CP.size_in_bytes,
    ST.[text],
    QP.query_plan
FROM sys.dm_exec_cached_plans AS CP
OUTER APPLY sys.dm_exec_sql_text (CP.plan_handle) AS ST
OUTER APPLY sys.dm_exec_query_plan (CP.plan_handle) AS QP
WHERE 
    ST.[text] NOT LIKE '%dm_exec_cached_plans%'
    AND ST.[text] LIKE '%DisplayName%Users%'
ORDER BY 
    CP.usecounts ASC;

The results show an Adhoc plan cache entry for each original statement, and a single Prepared plan:

A Prepared statement is similar to a stored procedure, with parameters inferred from literal values found in the Adhoc statement. I mention this as a useful mental model when thinking about the server-side parameterization process.

Notice that SQL Server caches both the original text and the parameterized form. When simple parameterization is successful, the plan associated with the original text is Adhoc and does not contain a full execution plan. Instead, the cached plan is a shell that contains very little besides a pointer to the Prepared parameterized plan.

The XML representation of the shell plans contain text like:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4188.2">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple 
  StatementText="SELECT U.DisplayName&#xD;&#xA;FROM dbo.Users AS U &#xD;&#xA;WHERE U.Reputation = 3151"
  StatementId="1" 
  StatementCompId="1" 
  StatementType="SELECT" 
  RetrievedFromCache="true" 
  ParameterizedPlanHandle="0x0600050090C8321CE04B4B079E01000001000000000000000000000000000000000000000000000000000000" 
  ParameterizedText="(@1 smallint)SELECT [U].[DisplayName] FROM [dbo].[Users] [U] WHERE [U].[Reputation]=@1" />
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

That is the entire plan. The ParameterizedPlanHandle points from the Adhoc shell to the full parameterized plan. The handle value is the same for all four shell plans.

Plan Stubs

Shell plans are smaller than a full compiled plan—16KB instead of 40KB in the example. This can still add up to a significant amount of memory if you have very many statements using simple parameterization, or lots of different parameter values. Most SQL Server instances are not so awash with memory that they can afford to waste it like this. The shell plans are considered very disposable by SQL Server, but finding and removing them consumes resources and can become a point of contention.

We can reduce the total memory consumption for shell plans by enabling the optimize for ad hoc workloads option:

EXECUTE sys.sp_configure
    @configname = 'show advanced options',
    @configvalue = 1;
RECONFIGURE;
GO
EXECUTE sys.sp_configure
    @configname = 'optimize for ad hoc workloads',
    @configvalue = 1;
RECONFIGURE;

This caches a tiny stub the first time an ad-hoc statement is encountered instead of a shell. The stub serves as a bookmark so the server can remember it has seen the exact statement text before. Upon encountering the same text a second time, compilation and caching proceed as if optimize for ad hoc workloads were not enabled.

Re-running the example with optimize for ad hoc workloads enabled shows the effect on the plan cache:

No plan is cached for the ad-hoc statements, just a stub. There is no ParameterizedPlanHandle pointer to the Prepared plan, though a complete parameterized plan is cached.

Running the test batches for a second time (without clearing the plan cache) gives the same outcome as when optimize for ad hoc workloads was not enabled—four Adhoc shell plans pointing to the Prepared plan.

Before continuing, reset the optimize for ad hoc workloads setting to zero:

EXECUTE sys.sp_configure
    @configname = 'optimize for ad hoc workloads',
    @configvalue = 0;
RECONFIGURE;

Plan Cache Size Limits

Whether plan shells or plan stubs are used, there are still downsides to all these Adhoc cache entries. I have already mentioned total memory use, but each plan cache also has a maximum number of entries. Even where the total memory usage is not a concern, the sheer quantity may be.

The limits can be raised with documented trace flag 174 (number of entries) and trace flag 8032 (total size). Depending on the workload and other memory demands, this may not be the best solution. After all, it just means caching more low-value Adhoc plans, taking memory away from other needs.

Caching Only Prepared Plans

If the workload rarely issues ad-hoc batches with exactly the same statement text, caching plan shells or plan stubs is a waste of resources. It consumes memory and may cause contention when the SQL Plans cache store (CACHESTORE_SQLCP) needs to be shrunk to fit within configured limits.

The ideal would be to parameterize incoming ad-hoc batches, but only cache the parameterized version. There is a cost to doing this, because future ad-hoc statements need to be parameterized before they can be matched to the parameterized cached plan. On the other hand, this would have happened anyway since we have already stated exact textual matches are rare for the target workload.

For workloads that benefit from simple parameterization, but not the caching of Adhoc entries, there are a couple of options.

Undocumented Trace Flag

The first option is to enable undocumented trace flag 253. This prevents the caching of Adhoc plans completely. It does not simply restrict the number of such plans, or prevent them ‘staying’ in the cache, as has sometimes been suggested.

Trace flag 253 can be enabled at the session level—restricting its effects to just that connection—or more widely as a global or start-up flag. It does also function as a query hint, but using those prevents simple parameterization, which would be counterproductive here. There is a partial list of the things that prevent simple parameterization in the Microsoft Technical Paper, Plan Caching and Recompilation in SQL Server 2012.

With trace flag 253 active before the batch is compiled, only the Prepared statements are cached:

ALTER DATABASE SCOPED CONFIGURATION 
    CLEAR PROCEDURE_CACHE;
GO
-- Do not cache ad-hoc plans
DBCC TRACEON (253);
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 2521;
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 2827;
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 3144;
GO
SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 3151;
GO
-- Cache ad-hoc plans again
DBCC TRACEOFF (253);
GO

The plan cache query confirms only the Prepared statement is cached and reused:

The Uncacheable Batch

The second option is to include a statement that marks the entire batch as uncacheable. Suitable statements are often security-related, or otherwise sensitive in some way.

This might sound impractical, but there are a couple of mitigations. First, the sensitive statement need not be executed—it just needs to be present. When that condition is met, the second benefit is that the user running the batch does not even need permission to execute the sensitive statement. Note carefully, the effect is confined to the batch containing the sensitive statement.

Two suitably-sensitive statements and example usage are shown below (with the test statements now in a single batch):

ALTER DATABASE SCOPED CONFIGURATION 
    CLEAR PROCEDURE_CACHE;
GO
-- Prevent caching of all statements in this batch.
-- Neither KEY nor CERTIFICATE need to exist.
-- No special permissions are needed.
-- GOTO is used to ensure the statements are not executed.
GOTO Start
    OPEN SYMMETRIC KEY Banana 
        DECRYPTION BY CERTIFICATE Banana;
Start:

/* Another way to achieve the same effect without GOTO
IF 1 = 0
BEGIN
    CREATE APPLICATION ROLE Banana 
    WITH PASSWORD = '';
END;
*/

SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 2521;

SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 2827;

SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 3144;

SELECT U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation = 3151;
GO

The Prepared plans created by simple parameterization are still cached and reused despite the parent batch being marked as uncacheable:

Neither solution is ideal, but until Microsoft provide a documented and supported solution for this issue, they are the best options I am aware of.

End of Part 1

There is a lot more ground to cover on this topic. Part 2 will cover the data types assigned when simple parameterization is employed.

Series index: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6