5

This is a client environment which needs significant overhaul so all I am trying to do is slow the bleeding.

Situation

CPU is getting crushed, often 80% or higher utilization

VMWARE 5.5, Windows Server 2008r2, SQL Server 2012

1 vCPU with 6 cores (red flag here)

70GB of RAM

16 separate instances, the one driving most of the CPU use has 80 user databases

All most all instances MAXDOP 0, all Cost threshold for parallelism is 5

Outage is not an option right now, so no reboots, no vCPU changes but I need to slow the bleeding that is the high CPU.

This a sample of top 10 current waits for the instance with 80 DBs, the CPU hog:

╔══════════════════════╦══════════════════╦════════════╦═════════════╗
║      wait_type       ║ Wait Time (Secs) ║ # of Waits ║ Avg ms/Wait ║
╠══════════════════════╬══════════════════╬════════════╬═════════════╣
║ WRITELOG             ║ 9.8              ║ 5563       ║ 1.8         ║
║ PAGELATCH_SH         ║ 7                ║ 21742      ║ 0.3         ║
║ SOS_SCHEDULER_YIELD  ║ 6.5              ║ 8891       ║ 0.7         ║
║ PAGELATCH_EX         ║ 5.3              ║ 20106      ║ 0.3         ║
║ CXPACKET             ║ 4.2              ║ 174        ║ 24.2        ║
║ LATCH_EX             ║ 2.7              ║ 1287       ║ 2.1         ║
║ LCK_M_S              ║ 2.2              ║ 123        ║ 18.1        ║
║ LCK_M_U              ║ 1.7              ║ 385        ║ 4.5         ║
║ PAGEIOLATCH_SH       ║ 0.9              ║ 972        ║ 0.9         ║
║ ASYNC_IO_COMPLETION  ║ 0.5              ║ 3          ║ 150.3       ║
╚══════════════════════╩══════════════════╩════════════╩═════════════╝

My thought, set MAXDOP at 3, cost threshold at 25 for all instances.

|improve this question
  • I recently ran into some performance issues on one of the server and the biggest wait for WRITELOG did a lot of digging and finally got to the bottom of it, worth a read have a look here ` Biggest wait WriteLog` – M.Ali Aug 15 '15 at 21:05
9

Things I see wrong right away

  1. Way to many instances
  2. Not enough vCPUs
  3. vNUMA probably doesn't match NUMA
  4. I'm guessing there's a ton of missing indexes
  5. MAXDOP of 1 or 2
  6. Cost threshold of 50
  7. Optomize for adhoc workloads = on (it's probably off)

Questions

  1. What do the waits look like?
  2. What are all the memory configs setup for?

Fixing this is going to take an outage. Probably a few of them.

|improve this answer
  • I did not end up putting this in place, but it is what I would have done if the client's position had not changed. – pshore73 Aug 18 '15 at 13:25
2

I agree with the above. I would also check your queries hitting the CPU and determine if any query tuning could be implemented or at the very least some plan guides. But all in all, your configuration is just not sustainable.

|improve this answer
1

Just to update, we are going to end up moving some of the load to a different server, followed by a complete rebuild of the environment.

Thank you for your time and responses.

|improve this answer

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.