Virtualize SQL High CPU

Question

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.

Answer 1

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.

Answer 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.

Answer 3

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.