When does a Query Get Trivial Optimization?

We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here’s a little glimpse into what we talked about.

For really simple queries, SQL Server can use “trivial optimization”. If there’s a very limited number of ways to run the query, why do a bunch of fancy, CPU burning cost-based optimization? Just chuck the plan at the query and let it go!

Downside: Trivial plans don’t ask for indexes

With a trivial plan, SQL Server never gets to the phase of optimization where it would ask for an index. This means you might have a really frequently executing query running against your SQL instance, burning lots of CPU which might be vastly improved by an index, but nothing ever registers in the missing index dynamic management views.

How Trivial Does A Query Need to be for this Optimization?

I find trivial plans frequently, but it’s also a little trickier than you’d think. Let’s look at some examples:

I’m starting off with a simple query, running against a large table:

The Posts table has only a clustered primary key on on the Id column. This query gets FULL optimization, and SQL Server asks for an index.

full optimization with an index request

I’d like this to go, with an index please.

Why wasn’t this plan trivial? Well, even though SQL Server didn’t have its choice of indexes to use, notice the parallel lines in there. SQL Server did have to decide whether or not to use more than one processor!

Let’s Take Away SQL Server’s Choices

Sorry SQL Server, I’m writing this blog post, and I know that I can remove this query’s chance to go parallel by raising one setting: Cost Threshold for Parallelism.  This setting is the “estimated cost bar” for who gets to use multiple cores. I’m going to pump this setting up so my query doesn’t qualify.

Be careful, this setting impacts all queries on the instance, and maxdop hints don’t override it.

Now, rerunning my query:

trivial plan, no missing index request

I’ll just scan the clustered index and keep my mouth shut.

This time I got trivial optimization – the query’s estimated cost for running a single threaded plan doesn’t even qualify for parallelism, so that choice doesn’t exist.

No missing index shows up, even though SQL Server did 346,913 logical reads for this query.

What if I Add an Unrelated Nonclustered Index?

Now that we have a trivial plan, let’s give SQL Server a choice– but not a very good choice. We’re going to create an unrelated non-clustered index:

Our query doesn’t reference the LastActivityDate column at all.

Rerunning our query…

full optimization due to an an NC index

I had to think about things this time.

Full optimization is back! Even though the LastActivityDate index seems really unrelated to what we’re doing, just adding it puts us back in FULL optimization.

Let’s Clean Up That Cost Threshold

Otherwise I’ll forget about it and not understand the weird plans on my test machine later. I’m just setting it back to the default here:

Takeaways: Beware the Creeping Trivial Plan

While most of your tables may be indexed well, it’s easy for changes in code to result in poorly indexed changes creeping out. While SQL Server’s default setting of 5 for “Cost Threshold for Parallelism” is generally far too low for modern processors, understand that raising it may increase your chances of getting trivial execution plans, which won’t ever ask for indexing help.

Want to find out if you’ve got trivial plans among your top queries? Our free sp_BlitzCache™ procedure sniffs around in your query XML and warns you right away.

Previous Post
Features SQL Server Needs to Add (And Drop)
Next Post
New Free Quizzes: Indexing, Query Writing, and More

6 Comments. Leave new

  • James Lupolt
    June 4, 2015 12:10 pm

    Nice post, thanks!

    Reply
    • 1: As someone who works on lots of different systems how often would you say you see this kind of behavior?

      2: Didn’t you warn about having that proc sniff around my xml being a server crushing process?

      Reply
      • Kendra Little
        June 4, 2015 2:17 pm

        I see trivial optimization pretty commonly. It’s not always a problem, though.

        BlitzCache should be pretty safe– it can take a while if you have a large plan cache, but it doesn’t parallelize and swamp your server.

        You might be thinking of tracing and capturing execution plans with BlitzTrace? That does hit the sql server much harder.

        Reply
  • Hi Kendra, interesting post !

    By doubt is :

    – is Full optimization back in 3rd query just because we have *more than two* indexes to choose from ?
    – Is this a general rule, regardless they are C on NC indexes ?

    Thanks
    Alberto

    Reply
    • Kendra Little
      June 8, 2015 8:48 am

      Hi Alberto,

      When ix_Posts_LastActivityDate is created, there are only two indexes at that time– the clustered index and that nonclustered index. I didn’t test the behavior with a heap.

      Kendra

      Reply
      • Hi Kendra,
        just tested…. and found the same behavior with a heap.

        Also, in my test, neither the first nor the second query used parallelism operator; however the first query got FULL optimization (and index suggestion), the second got TRIVIAL (and no index suggestion) after changing cost threshold for parallelism..

        Bye
        Alberto

        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