Sql-server – Forced Parameterization and CASE statements

parametersql serversql-server-2008-r2

We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid cache bloat, and encourage plan re-use.

One of the more complex queries is not being completely parameterized, and I have narrowed it down to literal values being used in CASE statements:

SELECT
CASE WHEN auth_date > '2019-02-03 08:48:03' THEN authorisations.authorisation_amount END
FROM authorisations
WHERE auth_date > '2019-02-03 07:48:03'
AND merchant_id = 400000000031

(This query may not look intuitive, but it is a fragment of a larger set of nested subqueries, and we have to use this structure at the lowest level. This works as an example.)

The parameterized statement used by the query plan is:

StatementText="select case when auth_date &gt; '2019-02-03 08:48:03' then authorisations . authorisation_amount end from authorisations where auth_date &gt; @0 and merchant_id = @1"

You'll notice that the literals in the WHERE clause have been parameterized, but the date literal in the CASE statement has not.

Why is this? What am I missing? What could I try to fix this? The literals will change for every execution, so at the moment, even with forced parameterization, I will end up with a new plan per execution.

Best Answer

So I should have done some more digging before I posted this. The restriction is explained deep down on this page: Forced Parameterization | Microsoft Docs

Specifically, this section:

The <select_list> of any SELECT statement. This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.

This tells me it's not specifically the CASE, but that I have included literals in the SELECT list.

Still, if anyone has any suggestions for working around this, I'd be interested.

Related Solutions

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.

One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.

Another point of suffering could be high compilations/second - so in an environment with a heavy workload and a lot of activity, there is a cost associated with compiling over and over.

You can see the impact of compilations/sec in perfmon (SQL Server Statistics:Compilations/sec). This can look like CPU pressure. To your performance/applications, this can look like increased query duration waiting for needless compiles each time it runs.

You can see the impact to the plan cache from the memory bloat by this query borrowed from Glenn Berry's Diagnostic scripts. How big is your SQLCP plan cache?

SELECT TOP(10) [type] AS [Memory Clerk Type], 
       SUM(pages_kb)/1024 AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

Also the query that was used in the question to identify the number of plans helps as well.

Is This Ever a Good Thing?

There are some cases where this could be good, but the situation is rare. Basically if you were suffering from parameter sniffing gone bad (nutshell: if the data can vary widely from execution to execution based on parameters, one compilation for one set of parameters ideal may yield an excellent query plan for that one query but poor for others.). My guess is that you likely wouldn't be dealing with that as bad as the implications from poor plan reuse.

What Can You Do About It?

Optimize For Ad Hoc Workloads can certainly help with the memory implications since only a stub of the plan is stored in cache at first execution, and the full plan isn't stored until it is executed a second time with the same plan.
Forced Parameterization could help here also. It can sometimes force parameterization to happen and help solve both the issue of cache bloat and the cost of having to recompile.
Fix The Queries Ideally, you shouldn't have to resort to these options, but instead can be more strict in your database development, encourage plan reuse, consider stored procedures for all of their benefits, and attempt to head off the problem that way. The ways to help fix this through forced parameterization or optimize for ad hoc are good to help, but the best solution is always aimed at the root cause.

There is an excellent resource here that talks about some of the dangers of plan cache pollution and some things you can do. I'd recommend a read here. It is written for SQL Server 2012, but the concepts and solutions apply.

Sql-server – Plans created several times under Forced Parameterization

Okay, this is the reason:

From Caching Mechanisms on MSDN:

You should notice that the two individual queries with their distinct constants do get cached as adhoc queries. However, these are only considered shell queries and are only cached to make it easier to find the autoparameterized version of the query if the exact same query with the same constant is reused at a later time. These shell queries do not contain the full execution plan but only a pointer to the full plan in the corresponding prepared plan.

For more in-depth technical information, see:

4.0 Query Parameterization on the SQL Programmability & API Development Team Blog

There are benefits in caching the shell query: If the same query were to be re-executed, then we would compute the hash value of the sql text of the query and find an exact match in the cache i.e. the shell query. Since this shell query points to the compiled plan, the compiled plan is executed and we are done.

If we had not cached this shell query and if the same query was re-executed then the steps followed would be slightly different: first we would compute the hash of the sql text of the query and not find an exact match in the cache. Next, the query is auto-parameterized. Now for this auto-parameterized query we will search the cache and find an exact match in the cache avoiding the need to go to the query optimizer. Finally we execute this compiled plan and are done.

Clearly there are performance gains from caching the shell query, especially for applications that re-execute the same query with the same literal values as well. Note that we do not cache insert shell queries because the probability of re-using the exact same adhoc query is low.

For more information read the whole documents (and series of posts, in the second case).

Best Answer

Related Solutions

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

Sql-server – Plans created several times under Forced Parameterization

Related Question