Microsoft SQL Performance Tweak?

[killmasta93]

Hi,
I was wondering if someone else has any tips for the best Performance for MSSQL inside Proxmox. I was reading this

Trace Flag T8038 with Microsoft SQL Server
Setting the trace flag -T8038 will drastically reduce the number of context switches when running SQL 2005 or 2008.

To change the trace flag:

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

Currently this is my fsync

root@prometheus:~# pveperf /vmdata
CPU BOGOMIPS:      63967.52
REGEX/SECOND:      3043825
HD SIZE:           5537.01 GB (vmdata)
FSYNCS/SECOND:     35669.25
DNS EXT:           95.68 ms
DNS INT:           74.55 ms (mydomain.local)

Running ZFS RAID 10 with 32gigs of ram with the VM no cache (as on the website on performance tweaks supposedly its the best)
I also been reading that MSSQL uses alot of Writes rather then reads or am i wrong?

I also gave me arc

# Min 2048MB / Max 4096 MB Limit with 32 gigs of ram
options zfs zfs_arc_min=2147483648
options zfs zfs_arc_max=4294967296

Thank you

 

[killmasta93]

Bump?

 

[TwiX]

Hi,

I plan to build same kind of VM
Win2012R2 with 32 Gigs of RAM
VM Disk on RAID-10 ZFS (4 SSD)
SQL Server 2016

Any tricks to give me ?

 

[TwiX]

Ok so another question :

I plan to add 2 vdisk (on zfs pool), one for OS and the other for datas.
Adding more than one vdisk on a scsi virtio controller affects IOPS ?

iothread could not be used due to backup issue...

What are your recommendations ?

Thanks.

 

[killmasta93]

Hi,

I plan to build same kind of VM
Win2012R2 with 32 Gigs of RAM
VM Disk on RAID-10 ZFS (4 SSD)
SQL Server 2016

Any tricks to give me ?

well funny thing i had before os 2 disks of raid 1 and vmdata raid 1 another 2 disk. it went well no issue. But i have realized its better RAID 10 4 DISKS using only rpool, ofcourse i would wish when proxmox installs it can partition but as you know when you install it cleans the disk so if the grub or proxmox ever gets damaged you have to have backup of the vms. Im running 4 disks 1tb 7200RPM works great with SSD would even be better but your going to kill them quick. Also i did set sync disabled which is another way to cheat but high risk if you dont have backups or UPS

 

[guletz]

Hi,

For anybody who wish to run any kind of SQL using zfs as backend storage, it is a must to use on the zfs zvol the same blocksize(volblocksize) as the DB manufacter recomand. Yes you can use what you want, it will works great at the begning, but in a day you will see a important disk performance degradation.
As I see(google), the file allocation unit size (cluster size) recommended for SQL Server is 64 KB!

 

[killmasta93]

@guletz
so do you recommend on installing on the rpool? and as for the file allocation unit you mean the disk that is given to the vm?

 

[guletz]

@guletz
so do you recommend on installing on the rpool? and as for the file allocation unit you mean the disk that is given to the vm?

Yes(and i have do it myself). zfs volblocksize for the zvol disk! And also primarycache/secondarycache=metadata, so you do not cache the same date in VM and at the zfs cache!

 

[killmasta93]

Thanks for the reply, but i got real lost, what do you mean that you do it yourself?

 

[guletz]

Thanks for the reply, but i got real lost, what do you mean that you do it yourself?

The zfs settings for a SQL guest!

 

[killmasta93]

thanks for the reply, so the block size you mean that your changing also from the local-zfs pool? But doesn't come by default with 64kb?
or do you edit the /etc/modprobe.d/zfs.conf

 

[guletz]

thanks for the reply, so the block size you mean that your changing also from the local-zfs pool

zfs set volblocksize=64k rpool/your-zvol-disk-name

 

[spirit]

since sql 2014, you can also enable "delayed durability" for journal flush

ALTER DATABASE TT SET DELAYED_DURABILITY = FORCED

https://blog.sqlauthority.com/2016/...ability-story-speed-autotests-11-2-5-minutes/

if you have a lot of small writes, without transaction, by default each write is commited in the journal (so, if you have a 512bytes write, it's 512bytes write).

with delayed_durability, the journal is flushed to disk each 64k or 1s.

 

[killmasta93]

zfs set volblocksize=64k rpool/your-zvol-disk-name

This would only change the 64k blocksize for the disk of that vm? not all the vms? would a reboot be needed?

 

[killmasta93]

since sql 2014, you can also enable "delayed durability" for journal flush

ALTER DATABASE TT SET DELAYED_DURABILITY = FORCED

https://blog.sqlauthority.com/2016/...ability-story-speed-autotests-11-2-5-minutes/

if you have a lot of small writes, without transaction, by default each write is commited in the journal (so, if you have a 512bytes write, it's 512bytes write).

with delayed_durability, the journal is flushed to disk each 64k or 1s.

This only applies for 2014SQL and above? and for only if I have small writes without transaction? But isnt most of the SQL are small writes