I need some help in understanding if there is any problem here with very high utilization of CPU on my read-only server. Read-only means this server is secondary read-only configured via Log Shipping (LS) where DW team can run all sort of crappy queries which runs for hours and hours.
Issue- Primary LS server is mostly OLTP and its vendor based product where Clustered indexes are avoided as DB being LS on primary goes through heavy writes throughout the day. Restore on secondary happens once a night and users are DC for that time, but rest of the day all sort of SELECT queries keep coming.
Index tuning here is sort of tough (no support from vendor) as primary cant be running with many indexes like Clustered indexes or more Non-Clustered indexes. Therefore most of the tables read on secondary goes through TABLE SCAN or KEY LOOKUP.
For Read-only server with below config:
Logical processors = 80 Total RAM= 512, MAX=440 Numa node =4 MAXDOP=8 and CTOP=5 ( i know this is too low but setting this to even 100 on this server does not work , per my analysis sub tree cost of most of the queries on this server is over 1000 avg)
Now at most there are 6-10 of those crappy queries keep coming and start running in parallel. CPU usage is most of time 90%. Users does not complain on this as they don't care for those queries running over 4 hours. But from server perspective i believe its not doing any good job, I'm worried that especially when 10-15 of them start together and CPU can go 100% which may cause server down.
Since index tuning is currently out of our control now, I'm thinking of following options, but unable to conclude:
- Increase MAXDOP: Should i increase to 16 (to make those queries finish fast) as 600 worker threads are used on avg from 2944 available ?
- Decrease MAXDOP: Should i decrease to 4 to 6 to make lower CPU usage?
CXPACKET waits are the top wait with 95 % on avg wait collection for last week.
From memory perspective : Memory grants pending is mostly 0 but outstanding is on avg 30-35 and PLE on avg 1000 but goes too low on 100 when those parallel queries run
Update with more info-- Top 3 Wait stats collected for last 5 mins when such queries are running
1.Wait type == PAGEIOLATCH_SH wait time= 12703.18 Seconds Signal_wait=708.6 s reswait time=11995.12s
2.Wait type == CXPACKETS wait time= 6754.18 Seconds Signal_wait=78.6 s reswait time=6678.12s
2.Wait type == SOS_SCHEDULER_YIELD wait time= 523.18 Seconds Signal_wait=528.6 s reswait time=1.12s
I increased MAXDOP=20 based on some understanding in msdn link, will keep on tweaking as needed
Some weird observation:--
I see CPU utilization dropped below 30 %
few of those queries running with DOP=20 but some of them with weird DOP like 5,7,11 as checked from script here
i am only able to see 10 % of queries lil faster but rest seem to have gone , which were faster it seems somewhat slower