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

L

Lugitsch IT

Member
Proxmox Subscriber
Apr 24, 2024
5
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: Lugitsch IT, 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.
 
  • Like
Reactions: Johannes S, carles89 and Lugitsch IT
To test what @J-Rod said, temporarily disable sync on the DB zvol and start the app once. If it suddenly starts in a few seconds, you've found your bottleneck. Just do it briefly and switch it back after — you can lose data on a power failure otherwise.
Code: 
zfs list -t volume
zfs set sync=disabled <pool>/vm-<vmid>-disk-<n>
# nach dem Test
zfs set sync=standard <pool>/vm-<vmid>-disk-<n>
Then you need a proper SLOG on an enterprise SSD with power loss protection (not some cheap consumer drive). If nothing changes with sync=disabled, the problem is somewhere else and wait stats are what to check next.

---

Um das zu testen, was @J-Rod vermutet, deaktivier testweise sync auf dem DB-zvol und starte die App einmal. Wenn sie plötzlich in ein paar Sekunden hochkommt, hast du deinen Flaschenhals gefunden. Mach das nur kurz und stell es danach wieder um — du kannst bei einem Stromausfall Daten verlieren, wenn sync länger aus ist.
Code: 
zfs list -t volume
zfs set sync=disabled <pool>/vm-<vmid>-disk-<n>
# nach dem Test
zfs set sync=standard <pool>/vm-<vmid>-disk-<n>
Dann brauchst du ein ordentliches SLOG auf einer Enterprise-SSD mit Power Loss Protection (nicht so eine billige Consumer-SSD). Wenn sich mit sync=disabled nichts ändert, liegt das Problem woanders und schau dir die Wait Stats als Nächstes an.
 
  • Like
Reactions: carles89 and Lugitsch IT
Bu66as said:
To test what @J-Rod said, temporarily disable sync on the DB zvol and start the app once. If it suddenly starts in a few seconds, you've found your bottleneck. Just do it briefly and switch it back after — you can lose data on a power failure otherwise.
Code: 
zfs list -t volume
zfs set sync=disabled <pool>/vm-<vmid>-disk-<n>
# nach dem Test
zfs set sync=standard <pool>/vm-<vmid>-disk-<n>
Then you need a proper SLOG on an enterprise SSD with power loss protection (not some cheap consumer drive). If nothing changes with sync=disabled, the problem is somewhere else and wait stats are what to check next.

---

Um das zu testen, was @J-Rod vermutet, deaktivier testweise sync auf dem DB-zvol und starte die App einmal. Wenn sie plötzlich in ein paar Sekunden hochkommt, hast du deinen Flaschenhals gefunden. Mach das nur kurz und stell es danach wieder um — du kannst bei einem Stromausfall Daten verlieren, wenn sync länger aus ist.
Code: 
zfs list -t volume
zfs set sync=disabled <pool>/vm-<vmid>-disk-<n>
# nach dem Test
zfs set sync=standard <pool>/vm-<vmid>-disk-<n>
Dann brauchst du ein ordentliches SLOG auf einer Enterprise-SSD mit Power Loss Protection (nicht so eine billige Consumer-SSD). Wenn sich mit sync=disabled nichts ändert, liegt das Problem woanders und schau dir die Wait Stats als Nächstes an.
Click to expand...
Das hab ich bereits gemacht und hat nicht geholfen:
zfs list -t volume
zfs set sync=disabled <pool>/vm-<vmid>-disk-<n>
 
Something else to check: try to determine if the individual disks have their write cache enabled or disabled.
 
  • Like
Reactions: _gabriel
Ok, then the storage side is pretty much ruled out. 0.1 ms latency, 10% CPU, and sync=disabled changed nothing, so I'd bet most of those 25 seconds aren't spent inside SQL Server at all. To check, run an Extended Events session or Profiler during app startup and add up the duration of all statements. If that only comes to 2-3 s, the rest is lost between the app and SQL Server.

I'd check Defender real-time scanning on the new 2025 box first. For a test, exclude the DB files and the app folder. Also check the connection: if the connection string still points to the old server name, or if name resolution or the encryption handshake is slow, you pay that on every connection. If the app opens a new connection per query, you feel it on every click. What does the connection string look like (server/instance and protocol, without the password)?
 
  • Like
Reactions: Johannes S
I think this combination is disastrous: Proxmox + ZFS + 4xHDDs + 8KB volblocksize + 2×5TB virtual disks where data is.

I copied some data from this server to another server, and it frequently happens that this server becomes completely unresponsive due to extremely high disk I/O load.

I believe that with this type of storage, only a bare-metal installation can resolve these performance issues.

This say AI to this topic:

1. The Metadata Bottleneck (RAM Hunger)​

ZFS needs to know where every single data fragment is located on the disk and what its checksum is. This metadata grows significantly with the size of the disks and the number of files stored on them.

  • ZFS attempts to keep this metadata in RAM (the so-called ARC cache).
  • If your VM consumes 44 GB of RAM while you are simultaneously operating four disks with a total capacity of over 10.6 TB, the remaining RAM on the Proxmox host is often insufficient to keep the ZFS metadata in fast memory.
  • Consequence: ZFS must constantly reload the metadata from the disks themselves. This causes exactly the massive, sudden read spikes and blocks the VM.

2. The Block Size Problem (volblocksize)​

When you create a virtual disk on a ZFS pool in Proxmox, Proxmox typically uses a default block size of 8 KB.

  • For a 100 GB disk, this is not a problem.
  • For a 5,000 GB (5 TB) disk, an 8 KB block size means that the disk is divided into billions of tiny blocks. When the VM reads or writes a larger file, ZFS must gather countless 8 KB blocks. This leads to extreme fragmentation and can bring even fast SSDs to their knees.
This explains the problem 100%. ZFS combined with mechanical hard drives (HDDs) and virtualization is extremely vulnerable to exactly these kinds of complete system failures without specific optimizations.

Mechanical hard drives are excellent at reading sequential data (for example, a large movie file in one continuous operation). However, their performance collapses when many small, simultaneous random read and write operations (Random I/O) occur, because the read/write heads must physically move back and forth.

A typical HDD can often handle only around 75 to 150 IOPS (Input/Output Operations Per Second).

With your four disks (particularly the two 5 TB disks), the following happens when using HDD-based ZFS:

Why the VM Completely Freezes with HDDs​

1. The "IOPS Death" Caused by Virtualization​

An operating system running inside a VM (such as Linux or Windows) constantly performs hundreds of tiny read and write operations in the background (logs, temporary files, database updates).

Due to its internal structure, ZFS additionally transforms these operations into numerous small blocks.

Your HDDs quickly become overwhelmed by the physical seek operations required to process these requests.

The I/O queue becomes so long that the VM becomes blocked (I/O Pressure Stall).

2. Copy-on-Write and Fragmentation​

ZFS never overwrites data directly. Instead, it always writes modified data to a new free location (Copy-on-Write).

Over time, this leads to extreme fragmentation on large 5 TB HDDs.

When the VM requests data, the HDD heads must constantly move back and forth across the disk platters.

As a result, data transfer rates collapse, and the VM becomes unresponsive.
Click to expand...
 
You are aware that you can limit the maximal amount for the ARC cache? The default in new Proxmox installs is 10% of system RAM at install time with maximum of 16 GB see following doc how to control and change the settings?
https://pve.proxmox.com/pve-docs/pve-admin-guide.html#sysadmin_zfs_limit_memory_usage

And that it's point is to speedup the disc access so it's worth a try to give the arc cache a higher limit?
@chrcoluk did a great writeup how zfs and Linux regular caching /memory/swap mechanisms fit together:
C

Thread 'ZFS ARC compared to linux page cache.'

Hi guys, I have decided to make this thread as there is threads popping up related to the ARC.

In my experience ZFS ARC is a lot more polite than the old 'host page cache', and is also actually much more configurable. However its defaults are not necessarily suited for proxmox due to that ARC usage isnt considered as available by the linux kernel.

If you use ext4 on the host, then the page cache will be used by the operating system, this cache can consume almost all of your free ram, has a dumb eviction mechanism, is barely configurable, and if swap file is enabled will usually...
  • Like

The ai text isn't completely wrong but misses a lot of context. First as already explained by @J-Rod HW Raid often have a battery to speedup things with caching. In such cases HW RAID ( can be used as lvm/thin pool with ext4/xfs) tends to outperform ZFS. This is especially true for stuff like raidz/hw raid to zfs ( striped) mirrors/RAID10 since you won't get the speedup typical for mirrors. With HDDs this is escpically noticeable but not limited to them.
So before you migrate to bare metal I woukd first try a RAID10 ( striped mirror) setup with ZFS or using LVM/thin with HW RAID. If you happen to need a ZFS feature ( e.g. for Storage-Replication) @Falk R. mentioned several times how to setup ZFS on hw raid. This is usually not done and has some caveats, but can be worth it performance-wise. Searching the forum for older posts on this subject should give some insights. I also know that he and some other regulars ( including the other people already answering you here ) pay their bills with consulting so getting external help from Proxmox Partner might also be worth a try ;)

Imho the actual issue isn't ZFS per se but the combination of ZFS RAIDZ and hdds compared to hw raid or ZFS striped mirrors/RAID10.
 
Last edited:
  • Like
Reactions: Lugitsch IT and J-Rod
You should provide your hardware details.

Side comment: running OS/DB/Applications on hard drives became obsolete 20 years ago. I'm confused that there is money for Windows Server 2025 and SQL, but still no SSDs.

Some general possibilities for your performance difference:
- Your RAID card was doing a lot of heavy lifting in the previous setup (read/write caching, read/write optimizations, etc.)
- There is some software/driver issue between PVE/ZFS and your specific hardware
- There is some software/driver issue between Windows 2025/VirtIO (VM) and PVE/KVM/QEMU (host)

I could see some paths forward:
- Test various VirtIO versions, virtual hardware settings and PVE kernel tweaks to see if there is some undiscovered performance boost
- Add a ZFS SSD mirror and move the OS & database virtual disks there. Optionally add another SSD mirror and use for special VDEV for the RAIDZ HDDs
- Go back to hardware RAID, run ZFS if you need the specific features it offers, otherwise consider XFS or EXT4
- Purchase different/newer hardware, and definitely get some SSDs
- Bail from PVE completely and run Server 2025 bare metal and then your database/application VM via Hyper-V. This may avoid whatever software/hardware issue you're experiencing with PVE. You get rights to run two Windows Server 2025 VMs with the license purchase
 
Careful, there are two different problems mixed up here. The host freezing while you copy TBs around is what a 4-disk HDD RAIDZ1 does under heavy write load. Yeah, mirror or SSD is the fix. The app startup going from 3 s to 25 s is different though. You measured 0.1 ms on the DB file and sync=disabled changed nothing, so the disks aren't what the app is waiting on at startup. If you go bare metal on the same HDDs, you might fix the copy freezes but still have a slow app.

Before you rip it all out, I'd check what @aaron posted. Start the app once and run dmesg | grep -i "split_lock trap" on the host. If you see hits, that matches with CPU sitting at 10% while everything crawls. Btw, the AI text is wrong in places. IIRC current PVE creates zvols with 16K volblocksize by default, not 8K. Where does the 8K come from, are the two 5 TB disks older zvols?

---

Vorsicht, hier werden zwei verschiedene Probleme vermischt. Dass der Host einfriert, während du TB-weise Daten kopierst, ist eben das, was ein RAIDZ1 aus 4 HDDs unter hoher Schreiblast macht. Ja, die Lösung ist Mirror oder SSD. Dass die App statt 3 s jetzt 25 s zum Starten braucht, ist aber was anderes. Du hast 0,1 ms auf der DB-Datei gemessen und sync=disabled hat nichts geändert, also wartet die App beim Start nicht auf die Platten. Wenn du auf denselben HDDs auf Bare Metal gehst, sind die Freezes beim Kopieren vielleicht weg und die App ist trotzdem langsam.

Bevor du alles rausreißt, würd ich prüfen, was @aaron gepostet hat. Starte die App einmal und lass danach auf dem Host dmesg | grep -i "split_lock trap" laufen. Wenn da Treffer kommen, passt das gut dazu, dass die CPU bei 10% steht und trotzdem alles kriecht. Btw, der KI-Text liegt teilweise daneben. IIRC legt aktuelles PVE zvols standardmäßig mit 16K volblocksize an, nicht mit 8K. Wo kommen die 8K her, sind die zwei 5-TB-Disks ältere zvols?
 
You must log in or register to reply here.
Top Bottom
Back