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
databasesAll 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.
Best Answer
Things I see wrong right away
Questions
Fixing this is going to take an outage. Probably a few of them.