Why Multiple Plans for One Query Are Bad

I’m going to demo this using the Stack Overflow public database. We’ll use the Users table – which has exactly what you think it has, everyone who’s asked/answered/commented at StackOverflow.com.

I need to search for people by their DisplayName, so I’ve created an index on that:

And now I’m going to search for a couple of different people – me, and the lady in the meat dress – and then examine what’s in my plan cache:

Here’s the results:

The only place where my reputation is higher than hers

Hey, whaddya know, Lady Gaga uses Stack Overflow too! We’re practically meant for each other.

But let’s zoom in a little on that last result set, the output of sp_BlitzCache:

Two queries, two plans

SQL Server built and cached two query plans.

This has a few interesting problems:

  • It built an execution plan for each one of them – which meant the query took a little longer to finish
  • It cached each execution plan separately – meaning it takes up more memory
  • Each plan could be different – in cases where the different name has a different number of estimated rows, SQL Server might choose to use (or avoid) an index

With just 2 queries, who cares? But if your app is sending in the same query thousands of times, each with different parameters, this can add up to more CPU time, more memory used for caching plans, and less memory used for caching data.

Our tools warn you about this in a few different ways:

  • sp_Blitz warns you that you have a large number of plans for a single query, and that it’s time to dig deeper by looking at the plan cache
  • sp_BlitzCache shows a warning for queries that have multiple plans (indicating that the query you’re looking at might just be a bad one out of several)
  • SQL ConstantCare® suggests forced parameterization when we see that you have a ton of these over time, and can’t keep plans in the cache as a result

You could fix this by changing the application so that it uses parameterized SQL instead of strings. Run this query to tell your developers which queries are involved:

That gives you the top 10 most duplicated queries in cache, plus for each one, 10 sample texts, plans, and a more-info query for sp_BlitzCache to let you slice & dice them by reads, CPU, etc. Note that the “Total” numbers like Total_Reads and Total_CPU_ms are for ALL of the different executions of the query text, not just the one line you’re looking at.

Click to zoom

And then when they say, “Sorry, we can’t fix those,” keep reading.

Optimize for Ad Hoc does not fix this.

When turned on, this server-level setting tells SQL Server to expect a lot of different queries that will never be seen again. That means:

  • SQL Server still compiles every string, every time it sees it
  • Just now it doesn’t cache that query’s plan until it sees the query a second time (which it almost never will, because you’re sending in a different string every time)

So your CPU is still high – you’ve just saved some memory, but not a lot. This isn’t a full fix.

Forced Parameterization fixes this.

If you right-click on a database, click Properties, Options, and scroll to the Miscellaneous section, you’ll see Parameterization. The default is Simple, but you can also choose Forced.

Chuck Norris has the option of Brute Force

Setting it to Forced takes effect instantly, doesn’t require a restart, and then has a different behavior.

If I run the same queries again, here’s the new output:

sp_BlitzCache showing forced parameterization

SQL Server takes a little more time with each incoming query, turns the literals into variables, and then checks to see if there’s an execution plan already compiled for it. That means:

  • Faster query runtime because we can skip compiling a full plan for it
  • Less memory wasted on duplicate plans being cached in memory
  • Easier to spot performance issues because now the same query is grouped together easier in tools like sp_BlitzCache

Things to know about Forced Parameterization:

  • It’s set at the database level, and needs to be set in the database where users are running queries.
  • It could theoretically be a performance drag – if all of your queries had literals, but they really were totally different queries, this could slow things down. I only recommend using this tool to fix a problem, not to proactively prevent a problem.
  • When enabled, plans do get reused – which means you may suddenly have parameter sniffing issues that you didn’t have before (because before, every query got its own hand-crafted plan.)

So when should you use Forced Parameterization?

  • When our tools are alerting you about a high number of plans for a single query (like, say, 10,000 or more)
  • You can’t fix that query to be parameterized
  • You want to reduce CPU usage and increase memory available to cache data
  • You’re comfortable troubleshooting parameter sniffing issues that may arise with that query
Previous Post
If You Can’t Index It, It’s Probably Not SARGable
Next Post
SQL Server 2017 CU5: Finding Problems With Parallelism

34 Comments. Leave new

  • 32k queries for a 3rd party app that’s been running for a month. Their developers will most certainly say, “Sorry, we can’t fix those,”

    Reply
  • PeopleSoft was pretty bad for this.
    And then there is the odd query that once in a while runs forever unexpectedly. Argh!

    Reply
  • If you’re using the Query Store in 2016+, do the two queries show as separate with individual query plans, or does the Query Store parameterize the queries much like the “Parameterization=Forced” database setting?

    Reply
    • Noah Engelberth
      March 21, 2018 1:38 pm

      Query Store stores query information in the same fashion as the pre-2016 DMVs did — if the query is getting multiple plans (due to non-parameterization as in this post’s examples, or because of an OPTION(RECOMPILE) hint, or due to the plan cache getting dumped), then Query Store will show the different plans that were compiled for the query.

      Reply
  • I really like the sound of this but am concerned about the impact on other queries. Is there a way to used Forced parameterization for specific queries?

    Reply
    • Markus Pöhler
      March 22, 2018 6:43 pm

      Hi Pete, as soon as you use a SqlParameter object in your Code in. Net, .Net will automatically turn your query into a parametrized query too and you will have the same effects, maybe this is an Option for you.

      Reply
      • Sadly I can’t touch the code. It’s Vendor supplied software which was badly ported from an IBM universe system to SQL Server. It’s riddled with dynamic SQL and scalar UDF’s wrapped in CLR’s. (more than 36,000 of them). I have thousands of query plans for trivial dynamic queries. I’m trying to find clever ways to help SQL cope better with it all.

        Reply
  • Ha, you got more downvotes than Lady Gaga!

    Reply
  • Awesome post! I love this. A post I wish I wrote myself.

    This is something that I probably should have known about a long long time ago.

    This helped me very quickly identify a bunch of sneaky queries that were hiding in our code. Sneaky code that concatenates integer values into a sql string rather than using real parameters. Our worst offender had 15K plans for a single query_hash in cache.

    I adapted your query to show me a single instance of each query because thousands of rows per query_hash was a bit unwieldy for me. You can find that here:

    https://gist.github.com/mjswart/109a641f35adb7ffcbe46a5f8d4bc259

    My plan cache thanks you.

    Reply
  • Jon Sparkes
    May 29, 2018 9:35 am

    I manage the database for an application that is based on Entity Framework, so my assumption was that all queries would be picked up as parameterised. However, it seems that almost two thirds of the cache (30k) are ad-hoc single use plans for what look to be the same query. I’m noticing that I see multiple versions of the same query in tools like Redgate SQL Monitor too.
    I’m considering enabling forced parametrization in the hope that plan reuse will kick in. Does this sound feasible?

    Reply
  • Roland Alexander
    July 19, 2018 7:14 am

    Is it possible that using a local variable to supply the value to a predicate could result in multiple plans for a single statement? I’ve got a pair of DELETE’s that have 28K rows from the query listed here; however, most of them have NULL as the QueryPlan. The statements are issued in a stored procedure; I wouldn’t think parameterization would be an issue, but I’ve been wrong before…

    Reply
  • Hardik talwar
    August 7, 2018 1:36 am

    “397 plans are present for a single query in the plan cache – meaning we probably have parameterization issues.”
    when i am doing forced parameterization ,it is not effecting any change in plans but also creating performance issues in database ( as after this sp_blitz) showing performance issue of forced parameterization ,may i know the solution as because of this i am getting my procedure cache hit ratio also bad

    Reply
  • The query in this post returns absurd results. Such as including CREATE PROC statements that I know for certain only execute once when generating the DB. But your query shows 24 executions for each of them. Do you need help with SQL?

    Reply
    • Thanks for your constructive criticism. Yes, I do need help with SQL! By all means, I’d love to see your updated version. Thanks in advance for your help!

      Reply
    • *Not sure if serious…* I can’t tell if this should be read at face value and given the benefit of the doubt. Or if it’s really sarcastic and arsey. Should I read it using Stimpy’s voice in my head or Ren’s?

      Reply
      • Hint – If executing a stored procedure is creating many cached plan versions, what is the SQL used for the query? Have you looked at sys.sql_modules lately?

        Reply
  • Seems like forced parameterization could cause issues with filtered indexes.

    Reply
  • […] Brent Ozar has a good overview of why this happens, but the short answer is to force parameterization on your queries. When you enable force parameterization, SQL Server will not automatically parameterize your queries if they aren’t already, reducing the number of one off query plans in your cache. […]

    Reply
  • Danielle Paquette-Harvey
    January 29, 2019 12:24 pm

    Our most frequent INSERT queries show about 6000 plans cached. These are queries that goest INSERT INTO Table (Col1, col2, col3) values (@Col1, @Col2, @Col3) and parameters are passed in the C# SqlCommand. Is is possible to optimize those? How would you optimize INSERT queries? I understand about SELECT but I don’t get it for INSERTs. Thanks

    Reply
    • Danielle – anytime you’ve got a development question, start by searching StackOverflow.com. You’d be stunned at how many of ’em have been answered over there, it’s bananas:

      https://stackoverflow.com/questions/19956533/sql-insert-query-using-c-sharp

      Reply
      • Danielle Paquette-Harvey
        January 29, 2019 12:46 pm

        Thanks, we are already inserting the parameters as said in the answers in the link you provided. But still, is it normal to have about 6000 plans for that kind of query? Is there something more to do to reduce the amount of query plans for INSERT statements? I have already enabled the “Optimize for AdHoc” since we had problems with that too.

        Reply
        • Danielle – I really do wish I could do unlimited free personal consulting in the Q&A, but that’s why I guided you to StackOverflow.com.

          Reply
        • If it’s just a few then if it can use an index then so much the better.
          For a lot then the impact of indexes being updated needs to be looked at and if you an bulk insert or at least order your inserts that can also help.
          Or the dba answer is …. it depends! Good luck!

          Reply
  • I have the forced parameterization parameter enabled on my hospital database but still, I found 4-5 plans for a single procedure on my SolarWinds dashboard. The stats are updated overnight and Index maintenance over the weekends. Any suggestions to drill down and make the optimizer pick the best plan.

    Reply
  • The title of this article is “Why Multiple Plans for One Query Are Bad” but I’m not sure that it explains why multiple query plans for one query are bad. Different parameters may require wildly different execution plans, and simply reusing the same execution plan every time can cause disastrous performance. One of your arguments is that they use memory, but it’s just a cache right? It will evict cache items from memory to make room as necessary.

    Another problem you list is that “in cases where the different name has a different number of estimated rows, SQL Server might choose to use (or avoid) an index”. Well yes, but isn’t that a good thing? I mean making those kinds of decisions is the whole point of a query optimizer.

    Not saying you’re necessarily wrong, but this text doesn’t have enough substance to convince me.

    Reply
  • Would enabling optimize for ad-hoc queries while boosting CPU cores be ‘more’ of a fix if I cant enable forced parameterization due to application constraints?

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