[SOLVED] Win7 x64 guest with SQLServer 2012 = High CPU usage

[mat02]

Hello,
I've installed Proxmox 3.1-3 on my HP Proliant with 12GB of RAM, 1x Xeon E5120 @1.86GHz (2 cores).
At the moment I have 5 CT and 2VM - one VM is Pfsense Gateway and another one is Windows 7 x64 HP.

On that Windows machine I've installed SQLServer 2012 Express and VNC server (UltraVNC) as well as virtio drivers.
The problem is that even in idle state, guest CPU usage is about 30-40% (proxmox reports very similiar values) and there are spikes to 100%. What can be the cause of such problem?

Here's my VM .conf file:

balloon: 2048boot: cdn
bootdisk: virtio0
cores: 2
cpu: host
memory: 6144
name: Windows
net0: virtio=62:46:A0:EB:AE:83,bridge=vmbr1
onboot: 1
ostype: win7
sockets: 1
startup: order=3
tablet: 0
virtio0: local:104/vm-104-disk-1.qcow2,format=qcow2,cache=writeback,size=57G

And here is what dstat reports when this machine is off:

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
 17  14  69   0   0   0| 280k  408k|   0     0 | 125B  230B|6745    14k
 10   7  83   0   0   0|   0    40k| 454k  458k|   0     0 |3451  6679 
 10   8  82   0   0   0|   0   128k|  53k   50k|   0     0 |2659  5422 
 11   9  80   0   0   0|   0   128k| 557k  521k|   0     0 |3667  7097 
 24   6  70   0   0   1|   0    40k| 132k  226k|   0     0 |3145  5373 
 11   7  83   0   0   0|8192B  184k| 414k  440k|   0     0 |3254  6122 
  6   4  90   0   0   0|   0     0 | 162k  176k|   0     0 |2904  6134 
  9   6  85   0   0   0|   0    96k| 560k  554k|   0     0 |3390  6931 
  5   4  92   0   0   0|   0   112k|  48k   50k|   0     0 |2406  4514 
  3   3  94   0   0   0|   0     0 |  44k   41k|   0     0 |2320  4252 
  4   3  93   0   0   0|   0     0 |  71k   65k|   0     0 |2364  4408 
  4   3  93   0   0   1|   0     0 |  39k   47k|   0     0 |2236  4192 
  6   8  85   0   0   0|   0    72k|  55k   50k|   0     0 |2609  5480 
  3   3  94   1   0   0|   0   656k|  33k   38k|   0     0 |2280  4285 
  6   3  91   0   0   0|   0     0 |  61k   56k|   0     0 |2342  4273 
  5   4  91   0   0   0|   0     0 |  68k   69k|   0     0 |2445  4417 
  5   3  92   0   0   0|   0     0 |  34k   36k|   0     0 |2314  4252 
  7   4  89   1   0   0|  40k   72k|  64k   68k|   0     0 |2495  4472 
 14   8  78   0   0   0|   0    72k| 622k  552k|   0     0 |3998  7537

And here's with Windows VM running. Look at the increase in int and csw :/

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
 17  14  69   0   0   0| 282k  408k|   0     0 | 125B  249B|6751    14k
 32  18  29  21   0   0|  16M   80k| 360k  212k|   0     0 |6739    14k
 36  18  25  20   0   1|  11M  576k| 297k  325k|   0     0 |7105    16k
 30  17  35  18   0   0|7656k    0 | 297k  253k|   0     0 |6777    15k
 45  34  13   7   0   0|  13M   64k| 607k  913k|   0    60k|9570    14k
 26  20  30  23   0   1|  17M  224k| 553k  281k|   0   216k|8392    16k
 21  16  38  26   0   0|  16M   48k| 115k   69k|   0     0 |5567    15k
 32  25  23  19   0   0|  13M  856k| 387k  623k|   0     0 |8201    18k
 22  16  37  25   0   0|  14M   80k| 390k  158k|   0    76k|6339    15k
 22  16  61   1   0   1| 184k  344k| 232k  277k|   0     0 |5981    14k
 36  17  29  18   0   0|  15M    0 | 198k  199k|   0     0 |6721    14k
 27  17  32  23   0   0|  16M  152k| 110k  136k|   0   100k|6442    14k
 25  17  26  32   0   0|  14M  800k| 156k  132k|   0    16k|6232    14k
 34  27  27  14   0   0|  13M   40k|  85k  136k|   0     0 |8868    16k
 54  36   6   4   0   1|  15M  256k| 124k   90k|   0     0 |  29k   16k
 50  41   5   3   0   1|  17M    0 |  92k  121k|   0     0 |  31k   17k
 58  35   5   2   0   1|8464k  200k| 146k  129k|   0     0 |  31k   11k
 53  35   8   5   0   0|  13M  960k| 227k  192k|   0     0 |  24k   18k
 52  38   7   3   0   1|  14M    0 | 137k  167k|   0     0 |  28k   18k
 79  21   1   0   0   1|  10M  164k|  64k   97k|   0     0 |6132    11k
 83  17   1   0   0   0| 368k  664k|  41k   48k|   0     0 |5617  6146 
 83  17   0   0   0   0|6800k  344k|  88k   76k|   0     0 |6085  6537 
 82  18   0   0   0   1|9376k  108k| 133k  102k|   0     0 |5678  7933

pveversion --verbose

proxmox-ve-2.6.32: 3.1-109 (running kernel: 2.6.32-23-pve)pve-manager: 3.1-3 (running version: 3.1-3/dc0e9b0e)
pve-kernel-2.6.32-20-pve: 2.6.32-100
pve-kernel-2.6.32-19-pve: 2.6.32-95
pve-kernel-2.6.32-16-pve: 2.6.32-82
pve-kernel-2.6.32-22-pve: 2.6.32-107
pve-kernel-2.6.32-23-pve: 2.6.32-109
lvm2: 2.02.98-pve4
clvm: 2.02.98-pve4
corosync-pve: 1.4.5-1
openais-pve: 1.1.4-3
libqb0: 0.11.1-2
redhat-cluster-pve: 3.2.0-2
resource-agents-pve: 3.9.2-4
fence-agents-pve: 4.0.0-1
pve-cluster: 3.0-7
qemu-server: 3.1-1
pve-firmware: 1.0-23
libpve-common-perl: 3.0-6
libpve-access-control: 3.0-6
libpve-storage-perl: 3.0-10
pve-libspice-server1: 0.12.4-1
vncterm: 1.1-4
vzctl: 4.0-1pve3
vzprocps: 2.0.11-2
vzquota: 3.1-2
pve-qemu-kvm: 1.4-17
ksm-control-daemon: 1.1-1
glusterfs-client: 3.4.0-2

 

[spirit]

Hi, this is because of interrupts generated by sqlserver timer.

http://pve.proxmox.com/wiki/Performance_Tweaks

Trace Flag T8038

Setting the trace flag -T8038 will drastically reduce the number of context switches when running SQL 2005 or 2008.
To change the trace flag:

1. Open the SQL server Configuration Manager
2. Open the properties for the SQL service typically named MSSQLSERVER
3. Go to the advanced tab
4. Append ;-T8038 to the end of the startup parameters option

 

[mat02]

Hi, thank you for your reply.
Unfortunately, this doesn't seem to work on SQL 2012, as I've added this option to Startup parameters beforme my OP (stats in OP are with this parameter and several VM reboots).
Although, I can't add this parameter in Advanced tab, so I've did it like this:
https://www.dropbox.com/s/xydw91ez16jixf6/Zrzut ekranu 2013-08-28 (godz. 10.35.33).png
https://www.dropbox.com/s/t3oxhdbpxmr3wbm/Zrzut ekranu 2013-08-28 (godz. 10.36.03).png
On that last screenshot you can see, that CPU usage fluctuates, and it's at 7-8% for brief moments, but then System process starts to hog the CPU.

 

[spirit]

Hi, thank you for your reply.
Unfortunately, this doesn't seem to work on SQL 2012, as I've added this option to Startup parameters beforme my OP (stats in OP are with this parameter and several VM reboots).
Although, I can't add this parameter in Advanced tab, so I've did it like this:
https://www.dropbox.com/s/xydw91ez16jixf6/Zrzut ekranu 2013-08-28 (godz. 10.35.33).png
https://www.dropbox.com/s/t3oxhdbpxmr3wbm/Zrzut ekranu 2013-08-28 (godz. 10.36.03).png
On that last screenshot you can see, that CPU usage fluctuates, and it's at 7-8% for brief moments, but then System process starts to hog the CPU.

Indeed, this is strange. I don't think it's related to interrupts in this case, as you see high cpu inside the vm.
If you stop the sqlserver service, does the system cpu go down ?
Do you use proxmox 3.1 ?

 

[mat02]

Yes, I use Proxmox 3.1, with all updates.
10 minutes after turning off all SQL services the average CPU usage is slightly smaller, but those System process spikes still exists:
https://www.dropbox.com/s/g6ddvuw3r4fbvlm/Zrzut ekranu 2013-08-28 (godz. 15.44.29).png
Maybe there is a problem with some driver? I read about KrView tool to check what is going on with NT kernel, but I can't find the x64 version of this tool.

 

[spirit]

Yes, I use Proxmox 3.1, with all updates.
10 minutes after turning off all SQL services the average CPU usage is slightly smaller, but those System process spikes still exists:
https://www.dropbox.com/s/g6ddvuw3r4fbvlm/Zrzut ekranu 2013-08-28 (godz. 15.44.29).png
Maybe there is a problem with some driver? I read about KrView tool to check what is going on with NT kernel, but I can't find the x64 version of this tool.

Do you use ballooning and dynamic memory ?

 

[mat02]

Yes, I do, with virtio Balloning Driver & installed service.

UPDATE:
I've disabled balloning and now it's about 6-15% with SQL I think that solves the problem.
Thank you

 

[spirit]

Yes, I do, with virtio Balloning Driver & installed service.

UPDATE:
I've disabled balloning and now it's about 6-15% with SQL I think that solves the problem.
Thank you

Great

Never use balloning with databases, sql server try to alloc all memory by default, and with balloning in the game, it'll go up and down all the time. (and use cpu)