• Obviously, adding more memory results in a different, less efficient plan.

    Could you try what happens if you simulate even more memory, like let's say 16Gb.

    You can do this by using DBCC OPTIMIZER_WHATIF

    DBCC TRACEON(3604) WITH NO_INFOMSGS

    DBCC OPTIMIZER_WHATIF(MemoryMbs,16000) WITH NO_INFOMSGS;

    GO

    Run your Query now.

    For more info on this undocumented DBCC command see here:

    https://www.simple-talk.com/sql/database-administration/using-optimizer_whatif-and-statsstream-to-simulate-a-production-environment/

    [font="Verdana"]Markus Bohse[/font]