Forced Parameterization Doesn’t Work on Partially Parameterized Queries.

Over and over again, I run into applications that don’t parameterize their queries, and as a result:

  • Compilations/sec is nearly as high as Batch Requests/sec
  • CPU is high due to all those compilations
  • Plans rarely last for more than a few hours in the cache
  • Monitoring tools that track resource-intensive queries are useless because every query looks “unique” to them
  • Optimize for Ad-Hoc Workloads doesn’t help because compilations still cause high CPU, and the queries aren’t grouped in the plan cache (and age out so quickly)

Normally, this is where Forced Parameterization saves the day. Hit this one switch, and bam, CPU falls down to nothing. I have a love/hate relationship with Forced Parameterization, though, because there is a long list of scenarios where Forced Parameterization doesn’t work:

  • INSERT…EXECUTE statements.
  • Statements inside the bodies of stored procedures, triggers, or user-defined functions. SQL Server already reuses query plans for these routines.
  • Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Statements inside a Transact-SQL cursor. (SELECT statements inside API cursors are parameterized.)
  • Deprecated query constructs.
  • Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
  • Statements that contain more than 2,097 literals that are eligible for parameterization.
  • Statements that reference variables, such as WHERE T.col2 >= @bb.
  • Statements that contain the RECOMPILE query hint.
  • Prepared statements that have already been parameterized on the client-side application.
  • (And more, but I’ll stop there)

That last one struck me recently, and in a way I wasn’t expecting. The code was partially parameterized, like this contrived example:

Note that the @Age one is parameterized, but the Reputation value is not. SQL Server looks at the string and says, “Well, it’s already parameterized – my work here is done, no need to force parameterization.” I get one plan in the cache per hard-coded Reputation value:

Unforced parameterization

Diabolical. The moral of the story: either parameterize EVERYTHING in your strings, or don’t parameterize ANYTHING.

Believe it or not, it gets worse. Some of the queries were built with dynamic SQL, but didn’t use the parameters – they just reused the same framework to build the strings. So they had hard-coded Reputation numbers like this, but didn’t use the @Age parameter at all:

Forced parameterization

And yet, they didn’t get parameterization forced. SQL Server just assumed the query had already been parameterized since we were using sp_executesql and passing in a parameter – even if that parameter was completely irrelevant to the query.

Just to be clear: the app involved wasn’t doing something quite this simple, but rather building lists of “in” strings in Entity Framework with a technique like this. <soapbox>Do not copy/paste code from Stack Overflow into production without analyzing the performance impact.</soapbox>

Is it a bug? I’d say yes – but not a SQL Server bug, rather a bug in the query-building app code. It doesn’t make me angry at Forced Parameterization – it’s a really cool tool, and I’m just so thankful for the times when it works. (Did that sound convincing? I hope it sounded convincing.)

Previous Post
Now is the time to sharpen your cloud database skills. Here’s how to start.
Next Post
Contest: What’s Your Worst Database Horror Story?

3 Comments. Leave new

  • I do not entirely agree with “The moral of the story: either parameterize EVERYTHING in your strings, or don’t parameterize ANYTHING.”. When building a predicate on a field with a filtered index, you have to use a constant value instead of a parameter, otherwise the filtered index will not be used.

    Also, if only one predicate is a constant value (+only a few values are used) and the other predicates are parameters, there will be a limited amount of plans for that query. I would conclude that, in this case, the overhead of compiling is minimal.

    For me this is a case of “it depends”, as it is often so in dba-world :).

    Reply
  • Another thing to watch out for: values in a SELECT list _cannot_ be parameterized. This might also leave you with a partially paramterized query – I ran into this problem when trying to make use of Forced Parameterization for queries with literals in a CASE statement:

    https://dba.stackexchange.com/questions/231331/forced-parameterization-and-case-statements/231333

    Reply
  • Mark Freeman
    June 18, 2019 9:50 am

    Azure’s Automatic Tuning is telling me to SET PARAMETERIZATION FORCED for many of my databases, but I suspect it won’t really help because I think it is detecting a lot of partially parameterized ad-hoc statements like this:

    SELECT C1
    FROM Foo
    WHERE C2 = @__ToGuid_0
    AND IsDeleted = 0
    AND StateCode] IN (‘Mandatory’, ‘OptionalInUse’, ‘UserDefined’)
    ORDER BY C2, C1

    From what you wrote, parameterization forcing will ignore such a statement, yes?

    I haven’t been able to get any information about whether Automatic Tuning is smart enough to exclude partially parameterized queries when it decides to make that recommendation.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}