Very poor SQL Server application performance on Proxmox VM compared to bare metal

L

Lugitsch IT

Member
Proxmox Subscriber
Apr 24, 2024
3
0
6
Hi everyone,

I'm experiencing a significant performance issue after migrating a Microsoft SQL Server database from an older bare-metal Windows Server to a new Windows Server VM running on Proxmox.

I'm trying to determine whether the problem is related to Proxmox, ZFS storage configuration, or SQL Server itself.

Previous environment (working perfectly)​

  • Operating system: Windows Server 2016
  • Storage: RAID5 consisting of Hard Disks
  • Installation: Bare metal (physical server)
  • SQL Server edition: SQL Server Developer
  • Database size: Approximately 30 GB (MDF file)
  • Application: Custom-developed business application connected to the SQL database
  • Performance: The application starts in approximately 3 seconds and all database operations are very fast.

New environment (very slow)​

  • Operating system: Windows Server 2025
  • Virtualization: Proxmox VE
  • Storage: ZFS RAIDZ1 (RAID 5 equivalent) consisting of Hard Disks
  • Virtual disk controller: VirtIO SCSI
  • IO thread: Enabled
  • Disk cache: Write back
  • CPU type: Host
  • SQL Server: Initially SQL Server Express 2025, later also tested with SQL Server Developer
  • Database: The same migrated database, approximately 30 GB (MDF)
  • Database disk: Dedicated virtual disk with ZFS volblocksize set to 64K
  • Application: Installed and executed directly on the Windows Server VM, where SQL Server is also running
  • Performance: Application startup takes approximately 25 seconds, and almost every operation is extremely slow.

What I have already tested​

1. SQL Server Express vs. Developer

Initially, I suspected that SQL Server Express memory limitations were causing excessive disk I/O.

I installed and tested SQL Server Developer on the new VM to eliminate the Express edition limitations.

Result: No noticeable performance improvement. The application remained extremely slow.

2. ZFS volblocksize: 16K vs. 64K

Initially, the database was stored on a ZFS virtual disk with a 16K volblocksize.

Using SQL Server Management Studio and sys.dm_io_virtual_file_stats, I measured average database read/write latency of approximately 100–200 ms.

I then created a new virtual disk with a 64K volblocksize and moved the database to it.

The reported SQL Server read/write latency dropped dramatically to approximately 0.1 ms.

However, actual application performance improved by only around 10–20%, which is nowhere near sufficient.

3. Disk utilization

On the new Windows Server VM, I frequently observe:
  • Disk C: active time reaching 20-30%, especially during database access
  • CPU utilization around 10%
  • RAM utilization around 10%
The database is now located on a separate dedicated virtual disk.

4. Application performance

The same application and database work extremely fast on the old bare-metal server.

On the new Proxmox VM, application startup takes approximately 25 seconds instead of 3 seconds.

The application is slow during almost every operation, not just when executing large reports or complex queries.

The application can be launched directly on the Windows Server VM, so the slowdown is not simply caused by a remote client connecting over the LAN.

SQL Server I/O measurement​

I used the following query to measure average read/write latency:

USE [cbiVENTS];
GO

SELECT
DB_NAME(vfs.database_id) AS Datenbank,
mf.name AS Datei,
mf.type_desc AS Dateityp,
mf.physical_name AS Pfad,
vfs.num_of_reads AS Lesevorgaenge,
CAST(
CASE
WHEN vfs.num_of_reads = 0 THEN 0
ELSE vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads
END
AS decimal(18,2)
) AS Lesen_ms,
vfs.num_of_writes AS Schreibvorgaenge,
CAST(
CASE
WHEN vfs.num_of_writes = 0 THEN 0
ELSE vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
END
AS decimal(18,2)
) AS Schreiben_ms
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY
mf.type_desc,
mf.name;
I understand that this query reports cumulative average I/O latency rather than instantaneous performance.

My questions​

  1. Could Proxmox or ZFS RAIDZ1 still be responsible for the poor application performance even though SQL Server reports approximately 0.1 ms average disk latency?
  2. Could the ZFS configuration, synchronous writes, caching, or virtualization overhead cause this kind of slowdown?
  3. Is a 64K ZFS volblocksize appropriate for a SQL Server database workload on RAIDZ1?
  4. Are there any known performance issues or recommended optimizations for Windows Server 2025 and SQL Server 2025 running under Proxmox?
  5. What would be the best way to identify whether the bottleneck is at the Proxmox/ZFS layer or inside SQL Server?
Has anyone else experienced similar performance issues with Microsoft SQL Server 2025 running on Proxmox, especially with ZFS storage?

I'm also wondering whether there could be any compatibility or performance-related issues between SQL Server 2025 (Express or Developer Edition), Windows Server 2025, and Proxmox VE.

Has anyone successfully used this combination in a production environment without performance problems?

Any suggestions regarding Proxmox, ZFS, VirtIO, or SQL Server performance diagnostics would be greatly appreciated.

Thanks in advance!
 
Last edited:
Hard to say from here, but with 0.1 ms average latency the disk probably isn't your main problem anymore. What's left is usually per-query overhead, and a chatty business app firing thousands of tiny queries will feel that everywhere, not just in big reports. First thing I'd check is the database compatibility level after the migration. If the DB got moved to SQL 2025 and the level was raised, you get the new cardinality estimator and the old queries can end up with much worse plans. Run SELECT name, compatibility_level FROM sys.databases, try the old level for a test, and do a sp_updatestats while you're at it.

Then look at sys.dm_os_wait_stats (reset it, run the app startup once, check again). That shows clearly whether SQL is waiting on IO, on WRITELOG or on something else entirely. Also, in your vfs output, what's the write latency on the log file (LDF) specifically? Log writes are sync, and sync writes on an HDD RAIDZ1 without a SLOG can hurt even if the average looks fine. You also mention C: hitting 20-30% active time even though the DB is on its own disk. Is tempdb or the pagefile still on C:?

Small thing, but check the Windows power plan in the guest too. "Balanced" vs "High performance" made a noticeable difference for latency-sensitive SQL workloads at a customer of mine. Btw, RAIDZ1 on HDDs isn't great for DB random IO in general, mirrors would be better. Right now a lot of it is probably coming out of ARC though.
 
  • Like
Reactions: fba and waltar
Did the bare metal install have a RAID controller with battery-backed RAM/cache? If so, I think that might explain the performance difference since ZFS does not confirm writes back to the OS until they have been fully written to all disks, whereas such a RAID controller would confirm immediately once it lands in RAM/cache.

You also end up with write-back caching on a lot of basic RAID controllers without batteries whereas with ZFS it does not do that by default.
 
You must log in or register to reply here.
Top Bottom
Back