Advice for hosting SQL Server

[jdl]

Hello,
We are hosting SQL Server 2008 R2 VMs in PVE4.
Storage hardware is RAID10 SSD.
Following SQL Server best practices, the VM filesystem should use 64K blocks (which can be easily achieved), but also the underlying filesystem in the physical server.
I think the maximum block size for ext4 is 1024.
What would you advise as filesystem for hosting SQL Server ? ext4, XFS, Btrfs ?
Thank you

 

[spirit]

you can keep the default size for ext4. 64k is important inside the vm only.

If you want to improve speed, you can create separate vm disk for log and datas, use virtio, and enable iothread on each disk.

 

[LnxBil]

Best practice for performance for any VM environment states that you should never use a filesystem (or more than one as you do), use real block storage as backend. This will always be faster, yet it cannot be done on most database servers. Database design 101 also teaches no filesystem between your data and your storage backend. The database should handle everything itself.

The crucial part is often that the access should be aligned, whereas the lowest level, biggest blocksize matters, if higher levels use bigger blocksizes than below, you will not have any problems - but the other way around. Disks do normally have 0.5 or 4 K blocksize on local storage, SAN is different and you have to ask your storage provider, but usually this is 64K, 128K or 256K. You have alignment and also read-ahead problems, because you can have read and write amplification which can cost a lot of performance.

For mssql, it depends further on the used NTFS blocksize (for M$ stuff) and then also the db blocksize. This introduces a lot of unnecessary layers:

Real Disk -> RAID (stripe size) -> Ext4 -> QCOW2 ->[VM]-> NTFS -> Datafiles with internal blocksize

What you want to have is something like this (e.g. for Oracle based systems with ASM):

Real Disk -> RAID (strip size) -> [VM]-> raw asm disk with datafiles

or even

Real Disk -> [VM]-> raw asm disk with datafiles and internal, intelligent replication

Normally, this yields good performance, but is bad for virtualization because most people want files (comfort zone)