Warning about memory “Excessive Grant” in the query plan - how to find out what is causing it?
The most common two types of operators that consume memory are:
- Sorts
- Hashes
If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.
Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:
-
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
-
Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)