1

OK, I was looking at potentially reducing this (as more of an exercise than a need mind you) and it seems that Im getting this warning on what appears to be a better performing query and not on the query that requires more memory to execute? Is this weird or am I just over thinking?

Anyone want to educate me here (sorry for the large images)?

1st query and hover over:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c 
    INNER JOIN SCS.scs_auto_sra.scs_contracts cc ON c.icontract_id = cc.icontract_id ;

enter image description here

second query and hover over:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c 
    INNER JOIN 
        (
            SELECT icontract_id, scontract_holder_hphone 
            FROM SCS.scs_auto_sra.scs_contracts cc 
            WHERE EXISTS (SELECT icontract_id FROM #Claims x WHERE x.icontract_id = cc.icontract_id )
        ) cc ON c.icontract_id = cc.icontract_id ;

enter image description here

3
  • You should past (both) plans in PasteThePlan so people can look through it entirely. And preferably have the actual execution instead of only estimated. If i had to guess, your estimated rows vs actual rows are wildly off in the first.
    – D Kramer
    Mar 11, 2020 at 14:44
  • Do you have any (max) data types for the columns--this will cause large memory grants. Mar 11, 2020 at 15:18
  • @TonyHinkle there was not. I was mainly asking about what appeared to be inconsistencies with the warning. Or at least perceived odd behavior.
    – Doug Coats
    Mar 11, 2020 at 16:05

1 Answer 1

2

Actually, the warning for excessive memory grant uses an hardcoded logic. It raise an alert when a certain % of the allocated memory is not use but it does not considere the amount of wasted memory.

It cannot be "trusted" and you should not worry about it if, like in this case, it's only 1Mb that is allocated and not use.

I've seen cases where I got a warning for something like this (1Mb allocated, 1 Mb non-used) and cases where there was 256Mo allocated and like 136 Mo used (so 120Mo non-used) and there was no warning.

1
  • I was suspecting this to be the case but was wanting to hear someone say it or at least confirm it.
    – Doug Coats
    Mar 11, 2020 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.