comment
Merge join asks for seemingly unnecessary sort before joining
This is incorrect, the parentheses did not change anything. What happened is that I realized that I had used the generated T-SQL code and afterwards cleaned it up for better readability here.
comment
Merge join asks for seemingly unnecessary sort before joining
You are correct, switching those two up, results in performing a Merge join, thanks a lot. I assumed the compiler would have resolved this for me.
awarded
awarded
awarded
awarded
comment
SQL server chooses hash match over merge join but the input fields to join should be sorted
Ah, I actually wondered earlier if the output list in the query plan is in the actual order that it is sorted (or unsorted, but in this case sorted) but could not find any actual info on this.
revised
SQL server chooses hash match over merge join but the input fields to join should be sorted
added 886 characters in body
Loading…
Loading…
comment
Free node memory and low PLE
We upgraded our SQL Server 2014 to SP3 CU 4 and for now, it seems to have resolved the issue. Thanks for the help!
revised
Free node memory and low PLE
added 123 characters in body
Loading…
awarded
comment
Free node memory and low PLE
So I monitored the situation more closely and it does throw out indexes that it would need because it is on one node and it keeps "less-used" indexes in memory on the other node. Any way to mitigate this issue, perhaps disable a node or some thing else? Is it a correct assumption that it will always try to save the same indexes to the same NUMA buffer pool, or should this more balanced? I am upgrading the sql server as we speak on our non-production environments, if all goes well, we upgrade this saturday.
comment
Free node memory and low PLE
We do face performance issues but unsure if it is completely related to this. We have to manually update the statistics often and are trying to find out why that is. I guess we will have to wait until the upgrade, I just find it very strange that the Optimizer keeps throwing out pages when there is such a huge amount of memory free. Surely, that must contribute to the performance hit. Once we have upgraded, I will monitor the situation, open a ticket with Microsoft if needed and keep you updated. Thanks for all your help.
comment
Free node memory and low PLE
So all was well and then a big query happened that cleared out most of the pages in memory and we are back to square one, except it is the other node now. i.imgur.com/3i0DVBP.png After reading the memorystatus results, it seems like it keeps a bunch of memory free for datapages, but never uses the space, is this a correct assumption? i.imgur.com/2DCBnlL.png I am planning on an upgrade as soon as possible, hopefully next weekend!
comment
Free node memory and low PLE
Ok, we enabled the setting today and rebooted the server. So far, it seems to balance the pages more evenly. Anything I could monitor to find out what might cause this issue?
comment
Free node memory and low PLE
I am not down to enable the Lock Pages In Memory settings. I had no idea what it is but after some Googling, it seems a bad idea if you run your SQL Server on a virtual machine.