We have a server with 8 CPUS across 2 NUMAs with hyperthreading enabled. Currently Maxdop is set to 8, but actually should be set to 4 as per the Maxdop section of this article:
So we need to change it to 4.
However my question is what is the impact of having maxdop set to 8? So going parallel across two NUMAS ? The reason I ask is that we just had a strange issue where queries were very slow to return and PLE dropped off rapidly.
Even when nothing was running against SQL PLE didn't not improve. CXPACKET wait types went up. Then all of a sudden CXPACKET wait type dropped completely and PLE began to rise and now has returned to normal.
Throughout this time small queries were being executed against a database, however it was not the case that one query finished causing the CXPACKET wait type to drop off and PLE to rise again - we don't know what causes that.
A possible explanation is the incorrect MAXDOP setting.
Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?