MS SQL

Recommended NTFS cluster size for MSSQL is 64kB. So ZFS's volblocksize also should be 64kB.

Move MSSQL guest data files to dedicated VM disk.

First create new VM disk:

zfs create nvmpool/data/vm-703-disk-3 -b 64k -V 16G
qm disk rescan

attach new storage to guest.

Format it under Windows guest to use 64kB cluster size. Verify cluster size:

fsutil fsinfo ntfsinfo g:

Info based on

• How to move SQL database files (MDF and LDF) to another location
• Move database files

ALTER DATABASE MY_DATABASE
    MODIFY FILE (NAME = MY_DATABASE, FILENAME = 'G:\MY_DATABASE.mdf');
GO
ALTER DATABASE MY_DATABASE
    MODIFY FILE (NAME = MY_DATABASE_log, FILENAME = 'G:\MY_DATABASE_log.ldf');  
GO

ALTER DATABASE MY_DATABASE SET OFFLINE;
GO

Stop MSSQL service.

Give new location rights for MSSQL service user (check what user is set in services).

Move database files to new location.

Start MSSQL service.