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:
|
1 2 3 4 |
SELECT Id FROM dbo.Posts WHERE ParentId=3; GO |
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.
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.
|
1 2 3 4 |
exec sp_configure 'cost threshold for parallelism', 500 GO reconfigure GO |
Be careful, this setting impacts all queries on the instance, and maxdop hints don’t override it.
Now, rerunning my query:
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:
|
1 2 3 |
CREATE NONCLUSTERED INDEX ix_Posts_LastActivityDate ON dbo.Posts (LastActivityDate) GO |
Our query doesn’t reference the LastActivityDate column at all.
Rerunning our query…
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:
|
1 2 3 4 |
exec sp_configure 'cost threshold for parallelism', 5 GO reconfigure GO |
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.
6 Comments. Leave new
Nice post, thanks!
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?
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.
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
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
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