SQL server 2017 is running slow over time

[jsengupta]

Hi,

We are running 2 SQL 2017 server VMs over Windows 2016. Everything is running fine for one day. However, the CPU utilization is reaching very high after a day on both the servers and the performance is degrading. The catchy part is High CPU utilization is seen on both the servers at the same time. For resolving the situation we are restarting both the SQL VMs and things are again running fine for about next 24 hours.

We are also running a 3rd VM on the same environment. The VM does not face any problem during the SQL server performance issue. We have also tested the iops during that situation and it gives around 13,000 iops.

1. All of the VMs have virtio drivers installed (virtio-win-0.1.208)

Can anyone please tell what could be the possible problem for this performance degradation?

1. Is it an issue within proxmox specifically for SQL server? OR
2. Is it an issue on the SQL server itself?

 

[Moayad]

Hi,

Did you see the event log inside the Windows VM looking for any warning or error message?
What is the PVE version that you are using pveversion -v?

Could you please post the VM config qm config <VMID>?

 

[itNGO]

Hi,
for the SQL-Server-Installation you can test if setting the -T8038 TraceFlag in SQL-Server-Service does help. Might still be a Context-Switching-Issue which SQL-Server is known for.

And what @Moayad said.... VM-Config and Version and also some Information about Hardware/CPU/Disks-Subsystem might be helpful....

 

[jsengupta]

We need to check the VM logs then. However, please find the other information those you have requested.


pveversion -v

proxmox-ve: 7.1-1 (running kernel: 5.13.19-1-pve)
pve-manager: 7.1-6 (running version: 7.1-6/4e61e21c)
pve-kernel-5.13: 7.1-4
pve-kernel-helper: 7.1-4
pve-kernel-5.11: 7.0-10
pve-kernel-5.4: 6.4-7
pve-kernel-5.13.19-1-pve: 5.13.19-3
pve-kernel-5.11.22-7-pve: 5.11.22-12
pve-kernel-5.4.143-1-pve: 5.4.143-1
pve-kernel-5.4.140-1-pve: 5.4.140-1
pve-kernel-5.4.78-2-pve: 5.4.78-2
pve-kernel-5.4.73-1-pve: 5.4.73-1
pve-kernel-5.4.65-1-pve: 5.4.65-1
pve-kernel-5.4.34-1-pve: 5.4.34-2
ceph: 16.2.6-pve2
ceph-fuse: 16.2.6-pve2
corosync: 3.1.5-pve2
criu: 3.15-1+pve-1
glusterfs-client: 9.2-1
ifupdown: residual config
ifupdown2: 3.1.0-1+pmx3
ksm-control-daemon: 1.4-1
libjs-extjs: 7.0.0-1
libknet1: 1.22-pve2
libproxmox-acme-perl: 1.4.0
libproxmox-backup-qemu0: 1.2.0-1
libpve-access-control: 7.1-5
libpve-apiclient-perl: 3.2-1
libpve-common-perl: 7.0-14
libpve-guest-common-perl: 4.0-3
libpve-http-server-perl: 4.0-3
libpve-storage-perl: 7.0-15
libqb0: 1.0.5-1
libspice-server1: 0.14.3-2.1
lvm2: 2.03.11-2.1
lxc-pve: 4.0.9-4
lxcfs: 4.0.8-pve2
novnc-pve: 1.2.0-3
proxmox-backup-client: 2.1.2-1
proxmox-backup-file-restore: 2.1.2-1
proxmox-mini-journalreader: 1.2-1
proxmox-widget-toolkit: 3.4-3
pve-cluster: 7.1-2
pve-container: 4.1-2
pve-docs: 7.1-2
pve-edk2-firmware: 3.20210831-2
pve-firewall: 4.2-5
pve-firmware: 3.3-3
pve-ha-manager: 3.3-1
pve-i18n: 2.6-2
pve-qemu-kvm: 6.1.0-2
pve-xtermjs: 4.12.0-1
qemu-server: 7.1-4
smartmontools: 7.2-pve2
spiceterm: 3.2-2
swtpm: 0.7.0~rc1+2
vncterm: 1.7-1
zfsutils-linux: 2.1.1-pve3





root@host1:~# qm config 350
agent: 1
boot: order=scsi0;ide2
cores: 5
ide0: none,media=cdrom
ide2: none,media=cdrom
machine: pc-i440fx-6.1
memory: 81920
meta: creation-qemu=6.1.0,ctime=1637261114
name: FTT-50
net0: virtio=5A:91:9D:9E:E8D,bridge=vmbr204
numa: 1
ostype: win10
scsi0: VMDisks-Host1:vm-350-disk-0,size=300G
scsi1: VMDisks-Host1:vm-350-disk-1,size=700G
scsi2: VMDisks-Host1:vm-350-disk-2,size=500G
scsi3: VMDisks-Host1:vm-350-disk-3,size=500G
scsihw: virtio-scsi-pci
smbios1: uuid=834beb13-e966-4521-8d14-5caa5d62446e
sockets: 2
vmgenid: e2df0975-6c28-4db6-a345-7ffef3b91f41



root@host1:~# qm config 352
agent: 1
boot: order=scsi0;ide2;net0;ide0
cores: 4
ide0: none,media=cdrom
ide2: none,media=cdrom
machine: pc-i440fx-6.1
memory: 49152
meta: creation-qemu=6.1.0,ctime=1637261114
name: FTT-52
net0: virtio=AA:FD:29:E8:91:77,bridge=vmbr204
numa: 1
ostype: win10
scsi0: VMDisks-Host1:vm-352-disk-0,size=150G
scsi1: VMDisks-Host1:vm-352-disk-2,size=350G
scsi2: VMDisks-Host1:vm-352-disk-1,size=500G
scsihw: virtio-scsi-pci
smbios1: uuid=9215a574-2936-4f08-8848-2248d4dbba27
sockets: 2
vmgenid: dc548efc-d04e-4f8a-ba92-42fded32c3e1

 

[davemcl]

Did you find a solution for this?