Whitespace, Comments, and Forced Parameterization in SQL Server

A question came up in our company chat room the other day: does the forced parameterization database setting help with plan cache bloat caused by dynamic comments or whitespace?

I love the topic of parameterization in SQL Server, probably because it’s one of those things that’s really pretty weird (but seems like it’d be straightforward). So I immediately wanted to test it out and see if SQL Server would do what I thought it would do.

Parameterization reduces compilation and plan cache bloat

For anything but the simplest queries, SQL Server takes literal values in your query literally. If you don’t parameterize the query, SQL Server won’t do it for you. That means that each of these three queries will get its own execution plan (even if the execution plans are all identical):

If you do this a lot, it can impact performance, particularly in a busy transactional database. It costs CPU to compile each query and SQL Server has to work much harder to manage an execution plan cache which is churning through frequent queries that are never-reused.

(For super-simple queries, simple parameterization may be applied. This is also sometimes called “auto parameterization” by people who worked with SQL Server 2000.)

White space, formatting, and comments can increase compilation and plan cache bloat for parameterized queries

I’m going to use spaces as examples in this post, but basically this is just a shorthand for talking about any variance in the syntax for executing a query.

Basically, these three queries will each get their own execution plan in SQL Server because the string being executed is different– even though it’s only different inside of a comment bracket, and even though the difference is simply the number of spaces in that comment:

The differing format in the whitespace give you just as much compilation burn and plan cache bloat as if the query were not parameterized.

‘Forced parameterization’ reduces execution plan bloat for non-parameterized queries

You can’t always re-write your whole application. The ‘forced parameterization’ database setting can help out sometimes — it tells SQL Server to look for those literal values and to try to treat them like parameters. This database setting can dramatically reduce compilation work by enabling execution plan reuse in some databases.

Like anything else, it has some limitations:

  • It impacts the whole database. If you only want to do individual queries, you have to use Plan Guides.
  • It won’t do anything if the database compatibility level is set to 80. (But plan guides will work.)

The Question: Does the ‘forced parameterization’ setting help with whitespace in query strings?

If SQL Server is smart enough to find those parameters in the string and remove them, shouldn’t it be smart enough to find the whitespace / dynamic comments and ignore them, too?

To figure this out I wrote up some test TSQL and executed it against a restored copy of the StackOverflow database.

Forced parameterization test: non-parameterized queries

For this test, I set up some simple code to execute non-parameterized dynamic SQL which also had different whitespace for each query. I ran the query 1,000 times in a loop, and then checked the execution plan cache to see how many copies of the query I had. Here’s what the code looks like:

Here’s what the queries looked like:

Forced-Parameterization-Comments-Non-Parameterized-String-Queries

Here’s how many execution plans I got:

Forced-Parameterization-Comments-Non-Parameterized-String

Forced parameterization “worked”, and it overcame the different whitespace for the queries! I had one plan run 1,000 times.

Forced parameterization test: parameterized queries

So, what if you have a parameterized query, but it has a problem with dynamic comments/ whitespace? Will turning on forced parameterization help that, too?

Here’s what the queries looked like:
Forced-Parameterization-Comments-Parameterized-String-Queries

Here’s how many execution plans I got:

Forced-Parameterization-Comments-Parameterized-String

Oh, ouch. Everything does hash out to the exact same query hash, but I got 1 plan for each execution, 1,000 plans total. For a parameterized query, the ‘Forced Parameterization’ database setting didn’t clean up the dynamic comments / whitespace for me.

Recap: what we saw

The ‘forced parameterization’ database setting is smarter than you might think, but it doesn’t fix every problem.

For our test query, it was able to force parameterize a query even if there were comments with different amounts of whitespace in it.

However, for query that was already parameterized, it didn’t fix the issue of dynamic comments/whitespace causing extra compilation and plan cache bloat.

Previous Post
Tweaking the Defaults for Ola Hallengren’s Maintenance Scripts
Next Post
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)

18 Comments. Leave new

  • “If you don’t parameterize the query, SQL Server won’t do it for you.” I thought that parameter sniffing would do just that – replacing hard-coded values with parameters in the cached plan?

    Reply
  • Steve LaRochelle
    December 18, 2014 11:39 am

    Very interesting. Was there a client case that led to this testing & discovery? I’d be interested to hear the story behind the question.

    Reply
    • Oh, you know, I’m not sure. The question came up in our company chat room and I don’t really remember the context, I just thought it was interesting enough to test.

      Reply
  • First I would like to thank you and all of Brent Ozar et al for what you do and do so well! I’m a big fan!

    How did the last stmt in the example even execute? it looks like a string with no parameters and as such much the same as a string with hard coded values?

    Reply
    • Hi Gorden,

      Thanks for the kind words!

      In the last example I only printed the “inner” code run by sp_executesql. This code snippet may help:

      [code]
      SET @dsql2=REPLACE(@dsql,N’%REPLACE-COMMENT%’,REPLICATE(N’ ‘,@i));

      PRINT @dsql2;
      EXEC sp_executesql @dsql2, N’@Id int’, @Id=@i;
      [/code]

      So passing in the values for the parameters was handled outside of where you see the print statement. (I just wanted to keep the printed statement relatively narrow so it would fit on the page easier and be easy-ish to read.)

      Reply
      • I was keenly interested in this b/c prior to Sql Server we had and still have an Oracle db with an App. that used un-parameterized strings which in Oracle translated into “Hard Parse” counts through the roof. So very much the same thing here in SS land. Appreciate you nifty code now that I dived in! Thanks again. Happy Holidays!

        Reply
  • Wilfred van Dijk
    December 21, 2014 3:35 pm

    Any idea how the new optimizer is handling non-parameterized queries? Is it smarter, so we don’t have to use the forced parmeterization?

    Reply
    • The new cardinality estimator doesn’t automatically force-parameterize.

      I personally wouldn’t say that doing different plans for different literal values is a bad thing or not smart of the optimizer– having the option to leverage this can be really powerful. I actually like the way parameterization works in SQL Server (with the exception of how spacing/comments are handled, which is kind of a weird thing). But to each their own!

      Reply
  • Brett Westover
    December 22, 2014 5:09 pm

    Awesome explanation and demonstration as always, thank you so much Kendra!

    I usually like to run your examples myself (for some reason the copy/paste and running it on my test systems really helps solidify it for me).

    While doing that I noticed one issue where your syntax highlighter didn’t render the ‘less than (<)' character.

    I see:

    WHILE @i < 1001

    instead of:

    WHILE @i < 1001

    Could be my computer… thought I'd mention in case its not just me.

    Reply
    • Thank you for letting you know! It’s me. And my eternal battle with wordpress. I’m going to fix it right now, hopefully it won’t creep back in!

      Reply
  • Brett Westover
    December 22, 2014 5:18 pm

    Confirmed, fixed from my view. You are awesome!

    As you can see from my failure to escape the “wrong” HTML tags in my original comment, none of us are immune to these kinds of formatting issues!

    Reply
    • Ha! I read your comment in the wordpress comment view in the administrative panel and it actually showed the HTML, not the formatted value. WordPress works great, but code samples are just crazy. (We’re actually in process working on a new way to insert and format code samples that may be a little less painful. Maybe. Hopefully someday.)

      Reply
  • Hello!

    I have the following situation:
    – the database has the Parametrization set to FORCE
    – if i run a query like:
    SELECT * FROM TableT WHERE col1 = ‘aaa’ and col2 = ‘bbb’
    , where the values of ‘aaa’ and ‘bbb’ can change, it does reuse the execution plan (which is good)
    – if I run a query like:
    exec sp_executesql N’SELECT * FROM table WHERE col1 = ”aaa” and col2 = @colb’, N’@colb nvarchar(50)’, @colb=’bbb’
    , where again the valus of “aaa” and “bbb” can change, it does not reuse the execution plan.

    Is this behavior as expected? If yes, could you please explain why it does not reuse the execution plan or how could I somehow enforce it to use it?

    Thank you!

    Reply
  • Hello Kendra,
    Forced parameterization works for white spaces, that’s right but what about capitalization changes? Shouldn’t it work for them also? When I check the exception list, I couldn’t see anything about capitalization. But according to my tests,
    SELECT * FROM Person.Address WHERE ADDRESSID = 1
    SELECT * FROM Person.Address WHERE AddressID = 1 are compiled twice.
    What do you think about that?
    Thank you.

    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":""}