SQL Sever 2005 high usage CPU in ESX Server

[mitvix]

Hi everyone,

I want have the notice about somebody that have the experience of have a VM using high CPU when run Microsoft SQL Server 2005 64bits?

I have a virtual machine in VI3 (ESX 3.5) with Microsoft Windows 2003 Enterprise 64bits R2 running SQL 2005 64bits SP2 that run between 90% at 99% of CPU. All best practices about use of VM in ESX Server and configuration have make. This VM have 4 vCPU and 12GB RAM and run over a IBM xSeries 3850 with 4 physical processors dual core Intel Xeon of 3GHz, 24GB RAM and the average of 700 user connections in SQL.

In this moment only this VM run in this host isolated and the use of CPU are high with SQL running.

Somebody have this experience or know something to do?

I know, this VM never can be consolidated in virtual environment, but now, I only need adjust the best workload for this machine.

Regard´s

Alexander Manfrin

Brazil - Brasília/DF

VCP - VMware Certified Professional

[Dave_Mishchenko]

Hello Alexander, your post has been moved to the Virtual Machine and Guest OS forum. Your duplicate post has been deleted.

Dave Mishchenko

VMware Communities User Moderator

[mitvix]

Sorry Dave,

Thank you for your job

Alexander Manfrin

Brazil - Brasília/DF

VCP - VMware Certified Professional

[kjb007]

Was the server installed as a 4 vCPU machine from the beginning? I'm trying to see if you're using the correct SMP HAL for windows 2003.

Also, run esxtop, and post the numbers from the CPU/MEM/NET so we can help analyze your situation.

[mitvix]

Thank you, in this moment the workload is low for reason of weekend and tomorow is holiday here. In next work day I will can put the real value of day by day.

For now the values is:

7:19:05pm up 80 days 7:33, 73 worlds; CPU load average: 0.15, 0.19, 0.18

PCPU(%): 8.83, 0.28, 0.26, 0.13, 27.68, 11.36, 20.21, 15.40 ; used total: 10.5

LCPU(%): 8.71, 0.12, 0.21, 0.07, 0.11, 0.14, 0.08, 0.06, 1.54, 26.14, 4.10, 7.26, 10.34, 9.87, 15.34, 0.06

CCPU(%): 0 us, 7 sy, 93 id, 0 wa ; cs/sec: 280

ID GID NAME NWLD %USED %RUN %SYS %WAIT %RDY %IDLE %OVRLP

1 1 idle 16 774.62 800.00 0.00 0.00 409.03 0.00 1.37

2 2 system 6 0.01 0.01 0.00 600.00 0.00 0.00 0.00

6 6 helper 22 0.04 0.05 0.00 2200.00 0.03 0.00 0.01

7 7 drivers 14 0.01 0.02 0.00 1400.00 0.10 0.00 0.00

8 8 vmotion 1 0.00 0.00 0.00 100.00 0.00 0.00 0.00

9 9 console 1 7.71 8.55 0.04 98.69 0.00 98.65 0.14

30 30 vmware-vmkauthd 1 0.00 0.00 0.00 100.00 0.00 0.00 0.00

164 164 srvouro 12 73.32 74.16 1.25 1043.37 0.62 94.51 1.64

7:21:31pm up 80 days 7:35, 73 worlds; MEM overcommit avg: 0.00, 0.00, 0.00

PMEM /MB: 24575 total: 512 cos, 328 vmk, 12015 other, 11719 free

VMKMEM/MB: 23745 managed: 1424 minfree, 14057 rsvd, 9529 ursvd, high state

COSMEM/MB: 105 free: 541 swap_t, 541 swap_f: 0.00 r/s, 0.00 w/s

PSHARE/MB: 194 shared, 12 common: 182 saving

SWAP /MB: 0 curr, 0 target: 0.00 r/s, 0.00 w/s

MEMCTL/MB: 0 curr, 0 target, 7800 max

GID NAME NWLD MEMSZ SZTGT TCHD %ACTV %ACTVS %ACTVF %ACTVN OVHDUW

30 vmware-vmkauthd 1 5.59 5.59 1.91 0 0 0 0 0.00

164 srvouro 12 12000.00 12627.09 3360.00 15 15 15 13 217.95

Thank you.

Alexander Manfrin

Brazil - Brasília/DF

VCP - VMware Certified Professional

[kjb007]

I already see a couple things.

Even in a low-state, your %WAIT is 1043.37, and your %idle=94.51, so the time your CPUs are spending waiting on some I/O to complete is high. I don't see any Disk or Network I/O, so I can't tell what I/O the server is waiting on.

Again, when you built this VM, loaded Windows on it, did it have 1 processor, or were all 4 vCPUs assigned before loading Windows. You may have a problem with your HAL, in this case, you should be using an SMP HAL, which I can't tell if you are or aren't. Post this also, go into device manager, and look under the computer branch, and see what kind of kernel you are running.

[mitvix]

Hi,

Today I am in normal usage of environment and is possible see a real result of esxtop with high resources in virtual machine.

I attach a file with results of mem, cpu, disk and network.

Thank you for your help,

Alexander Manfrin

Brazil - Brasília/DF

VCP - VMware Certified Professional

[RParker · Accepted solution]

I doubt you need 4 vCPU especially in a VM. For SQL you want a separate box your performance is being severely reduced because the scheduling inside the VM isn't getting the response time for SQL, which is why the high CPU, SQL is waiting between cycles for data commits and such, and it's "busy" waiting for these changes, since your SQL server is in such high demand, I highly suggest you use a physical box for SQL, and discontinue the VM.

[mike_laspina]

Hi,

There are two things I would suggest if you have not done them already.

Turn hyperthreading and SQL priority boost off.

Your VM is also showing a memory optimization target of greater then 12G.

When a VM's memory target is higher that the grant it can indicate it would perform bettter if it was either tuned at the OS/App or granted more memory, usually it's then tuned option that works.

Over allocating memory in MSSQL is not optimum as it starves the OS side of the server and will degrade performance.

I think you need trim back some of your SQL memory allocation.

[mike_laspina]

One other big perf hit on I/O intensive apps is the pshare memory option on a 64bit OS. This is something that should be disabled for an SQL VM.

Add this memory optimization changes to the VM's vmx

sched.mem.pshare.enable = "FALSE"

It will disable page sharing

As well I see most of your disk activity on one hba do you have the logs and data on the same path, it's something to consider outside of the CPU util issue.

[mike_laspina]

I would like to see more info before saying this VM is not a well suited SQL server virtualization candidate.

Perhaps a perfmon collection of transactions per second would be handy.

[mitvix]

Thank´s for everyone that help.

The conclusion is that we will migrate this virtual machine to physical machine. We found serious app changes to do, storeprocedures, on line compilation that need change for increase workload, but is so much thing´s that is better to do in a physical box.

Thank you everybody.

Alexander Manfrin

Brazil - Brasília/DF

VCP - VMware Certified Professional