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