For my current server as below
4 Sockets with 10 cores per socket and 20 logical processors per socket. Total 80 per SQL licensing: 4 NUMA nodes with HT enabled
I've set CTOP 50 and MAXDOP 8 on this DB servers with just one database of approx 15 TB which is Log Shipping stand by database ie Read only state
I recently changed the MAXDOP from 0 to 8 as i see lots of crapy queries running over 4-5 hours [not all but most of them] assuming were all over the CPU threads when checked their DOP was 64 all the time.
From above i assumed there could be excessive work being done by specific CPU cores when those heavy query starts coming, while some might still be idle. Not sure if this is how it can work.
SO now i've changed to MAXDOP 8 and see DOP for those queries showing as 8 fpr each individual queries ,say for 10 different queries running at this time.
How can i test/check if those queries are actually distributing the work across all the cores evenly or is it still the case where all of them might be doing all the work on one NUMA node socket with 20 cores while remaining 60 are idle?
Yes there are performance issues with reports slow but its one of such case where i want to ensure first from my end if there is something that can be done at my server level while developers or people running the reports tune up their queries. Also this being ready only i cant change indexes or control better update to stats on LS restore occurring every 12 hours .