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:

  1. Increase MAXDOP: Should i increase to 16 (to make those queries finish fast) as 600 worker threads are used on avg from 2944 available ?
  2. 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

  • So there are no indexes at all? How much traffic does the main server get? Thousands of writes per second? Tens of thousands? And why is index tuning out of the question? – ypercubeᵀᴹ Sep 22 '19 at 20:07
  • @ypercubeᵀᴹ: On avg transactions/day on the P server is 40,000 and DB is over 12 TB and yes writes are over tens of thousands. Indexes are there( mostly non CI on avg 3-4 per table) but most of the tables queried does not have clustered indexes. Design is PK on unique non clustered index. Its not out of question but vendor does not want to work on this as priority and hence i am checking at my end what can be done – BeginnerDBA Sep 22 '19 at 20:32
  • I was wondering if you could add or tune indexes on the replica, while not touching the main one. – ypercubeᵀᴹ Sep 22 '19 at 20:38
  • @ypercubeᵀᴹ: We have LS secondary read only, so tuning them is out of question. We have a plan later next year to move this to tran replication unless we can convert this to AG but i dont think we can create indexes on readable secondaries , per my latest update – BeginnerDBA Sep 22 '19 at 20:41
  • 1
    Is there no way to increase the quality of the queries? It sounds like you're describing queries the would be more efficient if they were written differently. – HoneyBadger Sep 23 '19 at 1:21

It might not be direct answer to your question: whether to increase/decrease MAXDOP?

But both cannot be solution when you look at the problem and think out of box. I believe, it's time to replace LS with alternative (as anyone of following).

  1. Developing ETL Packages that transfer the data from primary to secondary
  2. Schedule Copy Database wizard with overwrite option - Consider this would do full copy of database over the night
  3. Any third party solution

The ideal solution would be to make secondary copy can be nightly synced and available for read/write operations in simple term data warehousing. So that you can have control over it and having proper index can reduce a lot pressure on server resources. Consider increasing/decreasing MAXDOP cannot be efficient solution, it's like we are putting unnecessary load and drying-out server resources. It might be good for vendor to restrict on index part (on primary replica) for their application activity which should not limit you on secondary replica that mainly intended for ready operations and eating-up resources.

  • Thanks for your answer. Appreciate it, but as i mentioned primarily we are looking into building a better alternative to reporting like using tran replication. Those are for next year plans. However with current setup, i need an understanding what is going to happen when i increase or decrease MAXDOP? Also if i try to change while queries are running does that change on FLY or creates any impact? – BeginnerDBA Sep 22 '19 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.