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:

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 > '2019-02-03 08:48:03' then authorisations . authorisation_amount end from authorisations where auth_date > @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.

  • 1
    You can also use , optimize for ad hoc workloads option – Sabin Bio Mar 5 '19 at 12:30
  • @SabinBio Yes, that would solve the plan cache bloat, but would still result in new plans being compiled for each execution. I'd like to be able to reuse existing plans. – TimBrown Mar 5 '19 at 12:36
  • Plan guide could be also an option – Sabin Bio Mar 5 '19 at 13:10
  • You could try adding AND auth_date > '2019-02-03 08:48:03' - to make that literal also be examined in the where clause - but that is just a wild guess and I am not sure it will fix the underlying issue. – Aaron Bertrand Mar 5 '19 at 13:22

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.