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:


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:


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…


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

5 Comments. 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.

  • Jeff Mergler
    July 2, 2021 4:17 pm

    If I had a nickel for the time spent trying to fix “WARNING TYPE CONVERSION MAY AFFECT CARDINALITY ESTIMATE” on something like CONVERT(varchar(20), ID … well I’d have a handful of nickels. I’m not amused that I’ve wasted time on that warning and I will now treat that “may affect” warning with the attention is deserves (very little). Þakka þér fyrir Brent.

  • Thank you for that.
    I was spending some time looking at these, wondering why is was getting the CardinalityEstimate warning.
    Unfortunately, the reason I was looking at it is because I was told about warnings by sp_BlitzCache and used the query here: https://www.brentozar.com/blitzcache/query-plan-warnings/
    It would probably be good if the query given there filtered out these useless warnings, or barring that at least if the page mentioned that some of these warnings may be misleading and gave us specific information on it like this page does. I hope these are the only two bad warnings. 🙂
    I assume that for the CardinalityEstimate warning, if the type conversion is used to filter the results somehow, then that warning would be valid, but when simply displaying the end result, or possibly grouping, it would not.

  • I usually take care of the big warnings first (missing index, tempdb spills, horrible parallel joins etc). But I came here today because I saw this warning while prototyping something new and wondered whether I should dig deeper, or ignore it. I was scratching my head wondering how a SELECT CAST could affect cardinality.

    Now I know. Thanks!


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.