We Need to Talk About the Warnings In Your Query Plans.

SQL Server, thanks for coming today. Have a seat.

Yes, this might seem odd – there are a lot of us here.

And yes, we are locking the door behind you.

We all care about you – all of us care very deeply – but we need to talk about some warning signs. Specifically, the warning signs you’ve been putting in query plans.

You’ve been exaggerating things that aren’t a problem, and it needs to stop. We’ve already had an intervention about your false nested loop warnings, but your behavior has been getting worse.

Type conversion warnings are crying wolf.

In the Stack Overflow database, run this simple query:

We’re fetching just one row from the Users table, and Id is the clustered index. SQL Server dive-bombs directly to one row, pulls it out, and we’re done here. But look what the query plan shows:

WARNING TYPE CONVERSION MAY AFFECT CARDINALITY ESTIMATE

Uh, no, SQL Server, the cast in a select – the data going out the door after the row has already been found – will not affect the cardinality estimate. I realize that you’re using the wishy-washy “may affect” language, but for that matter, you could say that Erik may turn down a free glass of Lagavulin. You’re welcome to keep trying, but I haven’t been able to get it to happen. (Disclaimer: I have not really been trying.)

“Excessive Grants” is a part-time Chicken Little, too.

Let’s take this query plan. See the terrible yellow bang on the SELECT operator? Hover your mouse over it to see what it’s whining about:

EXCESSIVE GRANT

It’s screaming that we have an “Excessive Grant, which may impact the reliability.”

The memory grant is 10MB.

On a server with 16GB of RAM.

There is no chance, barring the entire population of Earth trying to run this query at the same time on this server, that this query is going to impact the reliability of this server. Yes, I understand that only 296KB of the 10MB was used, and that from a percentage standpoint, that might seem like a big deal – but but we’re only talking 9.7MB of extra memory grant here.

Whereas this next query has no warning signs whatsoever, but…

WHERE IS YOUR GRANT NOW

But check out the metrics:

  • Desired memory: 11GB
  • Granted memory: 3GB
  • Used memory: 161MB
  • Wasted memory: 2.9GB every time this query runs

But there’s no warning! This is where memory grants can actually impact reliability, causing resource_semaphore waits, and this is the kind of query we need to be alerted about.

SQL Server, we know your heart is in the right place. We don’t have a problem with you – just some of your behaviors. We don’t want to get to the point where we start just ignoring your warning signs in query plans altogether because they’re so unreliable.

Previous Post
First Responder Kit Release: Fingers Crossed!
Next Post
SQL Server Management Studio 18’s Execution Plans Will Change The Way You Look At Plan Tuning

1 Comment. Leave new

  • Marvin Schenkel
    October 5, 2018 6:20 am

    SQL Server should just calculate ‘Wasted memory’ as a percentage of ‘Total Server memory’ and use that to throw warnings.

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