SQL Server – Virtualize SQL High CPU

configurationsql serversql-server-2008-r2vmware

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.

Best Answer

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.