[FEATURE] Memory Grants - Add message to check for big text columns

[ClaudioESSilva]

Which component(s) does this affect?

• Desktop App
• CLI Tool
• SSMS Extension
• Plan Analysis Rules
• Documentation

Problem Statement

I was analysing a plan that had a +600 MB memory grant.
On the warnings, it also had some implicit conversion (CONVERT_IMPLICIT) of some columns to nvarchar(max).

Note

Query has a CTE with 4 queries joined by 3 UNION ALL
The warning for the implicit conversion comes from 2 of the UNION queries having a column as nvarchar(max)

By removing those columns, the memory grant went down to a <5 MB.

1. This type of CONVERT_IMPLICIT isn't being raised.
2. No indication that we may have big columns that should be checked and tested by remove them.

Proposed Solution

1. Can we try to check if the implicit conversion exists and is to this data type's max issue a warning too?
2. Can we add a comment on the Human/Robot Advice for the user to check if big columns are being requested and to double-check if those are really necessary? Something like Check whether big text columns are being selected and if they are really necessary.

Would this be better under [Warning] Excessive Memory Grant or [Warning] Large Memory Grant ?

Use Case

Remind the user that the query may be requesting huge (text) columns, and maybe those aren't needed or can be requested separately from it.

Alternatives Considered

N/A

[erikdarlingdata]

@ClaudioESSilva The only thing here is that data types aren't generally listed in query plans, except in the case of various CAST and CONVERT functions and the like, and then the Compute Scalar responsible for it would have to be a child operator of a memory consuming operator in order for it to make sense to flag as contributing to the memory grant. I'd likely start with Sorts, but Hash Aggregates would also be worth checking. It's potentially quite a complicated check to not become noisy.

[ClaudioESSilva]

I know what you mean.
Maybe we could add a more generic message when we have an Excessive Memory Grant?
Or do we feel that will be very loose feedback?

It's more pointing to a possible reason: "Hey, this has an excessive memory grant, you may be selecting columns with wide data types, double-check that".

[erikdarlingdata]

@ClaudioESSilva Oh, if we're just looking for some potentially generic wording about large grants, then something like what you said plus checking cardinality (mis)estimates would be fine. Additional wording around making sure column data types are appropriate is probably going a bit too far.