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

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.
 
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.
 
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.
 
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.
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.
 
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:

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:
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
 
  • Like
Reactions: Johannes S
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?
 
  • Like
Reactions: Johannes S
I created a RAIDZ test setup on an older server using 4x6TB SATA drives and launched a couple VMs on it. The performance was expectedly quite painful. Trying to interact with one VM wile restoring the second VM from backup was basically impossible. I then added a special VDEV and there was a small, but noticeable bump in performance, but really nothing compared to running 100% on SSDs.

PVE does have the relatively fixed bandwidth and I/O settings available per VM, but otherwise from what I can see there isn't a dynamic latency prioritization feature similar to vSphere Storage I/O Control. Regardless, HDDs are just not a good fit for OS/DB workloads, as mentioned.
 
  • Like
Reactions: Johannes S
Based on my experience troubleshooting similar SQL Server performance issues on Proxmox VE with ZFS storage, I would suggest the following steps.

1. Review the ZFS storage architecture​

My first recommendation would be to consider moving from RAIDZ1 to a RAID10-like ZFS configuration (striped mirrors), if your hardware and budget allow it.

RAIDZ1 can be less suitable for SQL Server workloads involving frequent small random reads and writes, particularly when using mechanical HDDs.

Striped mirrors generally provide better random I/O performance and lower latency, which can make a significant difference for database workloads.

If your budget allows it, you could also consider adding SSDs or NVMe drives. However, I would first evaluate whether they should be used for database storage, L2ARC, or a dedicated SLOG device, depending on the actual workload. Simply adding SSDs as ZFS cache devices does not automatically improve SQL Server performance.

Ideally, moving the SQL Server data and transaction logs to SSD/NVMe storage would provide a more predictable performance improvement.

2. Verify and update the VirtIO storage driver​

This is one of the first things I would investigate before making any major changes to the storage architecture.

Check the exact version of the VirtIO storage driver currently installed and actively used by Windows Server 2025.

If you are using VirtIO SCSI, the relevant driver is normally vioscsi, rather than viostor.

In a previous troubleshooting case, updating the affected VirtIO driver to version 0.1.271 helped address a storage-related issue.

However, since your environment uses Windows Server 2025, I would recommend checking the compatibility and known issues of the driver version before upgrading or downgrading.

Also check whether older driver packages remain in the Windows Driver Store.

3. Review the Proxmox VM disk configuration​

I would recommend testing the following settings:

  • Disk cache: Change from writeback to cache=none (No cache), particularly for SQL Server data and transaction log disks.
  • Async I/O: Test aio=io_uring, which is supported by Proxmox and can be suitable for ZFS-backed virtual disks.
  • IO thread: Keep IO thread enabled where supported.
  • ZFS sync: Verify that the ZFS property is set to sync=standard. Do not use sync=disabled as a performance workaround for a production SQL Server database, because of the potential data-loss risk.
These settings should be tested individually, with performance measurements before and after each change.

4. Review the virtual CPU configuration​

I would also investigate the VM's CPU configuration.
For troubleshooting purposes, you could try:
  • Testing a configuration with less vCPUs (1 socket × 8 cores), if the VM currently has more allocated.
  • Disabling NUMA.
  • The CPU type x86-64-v2-AES instead of host
However, the host CPU type is generally a good choice for performance in Linux not Windows.

5. Optimize SQL Server database file settings
Inside SQL Server, I would also recommend:
  • Enabling Instant File Initialization (IFI) for supported database file operations.
  • Configuring database autogrowth using fixed-size increments rather than percentage-based growth.

6. Investigate the performance measurements​

One particularly interesting detail in your report is that changing the ZFS volblocksize from 16K to 64K reduced the reported average disk latency from approximately 100–200 ms to 0.1 ms, while application performance improved by only 10–20%. This suggests that storage performance may not be the only contributing factor.
In my setup, I use a 64K ZFS volblocksize and also format the Windows NTFS partition with a 64K allocation unit (cluster) size to ensure consistent block alignment between the Windows filesystem and the underlying ZFS storage.

My recommended troubleshooting order​

Before rebuilding the ZFS pool or investing in new hardware, I would proceed in this order:
  1. Verify the installed VirtIO SCSI driver and its Windows Server 2025 compatibility.
  2. Test cache=none and aio=io_uring.
  3. Verify ZFS synchronous-write settings.
  4. Review the VM CPU topology and change the CPU type from host to x86-64-v2-AES.
  5. Measure ZFS pool latency, IOPS, and disk utilization during the slow operations.
  6. If the storage layer proves to be the bottleneck, consider moving from RAIDZ1 HDDs to ZFS striped mirrors or, preferably, SSD/NVMe storage.
  7. If you are using frequent automatic snapshots (every few minutes) or short-interval replication between Proxmox servers, temporarily disable the QEMU Guest Agent and test the application performance again to see whether it makes any difference.
In my experience, SQL Server performance on Proxmox is heavily influenced by the combination of storage layout, VirtIO driver, disk caching configuration, and SQL Server settings.

I would focus on those areas before concluding that SQL Server 2025 or Proxmox itself is responsible for the slowdown.

I hope these suggestions help you narrow down the issue.

Note: All the technical suggestions and recommendations shared above are my own, based on my real-world experience managing and troubleshooting Proxmox VE clusters running SQL Server. English is not my native language, so I used AI solely to help translate, structure, and improve the wording of my post.
AI assisted with the writing, not with the technical ideas, analysis, or recommendations. These come entirely from my personal hands-on experience.
 
Last edited:
What's the point of posting an ai-generated summary of all suggestions in the thread? It's nice wanting to help but I'm not sure whether this way it achieves anything.
 
What's the point of posting an ai-generated summary of all suggestions in the thread? It's nice wanting to help but I'm not sure whether this way it achieves anything.
I think there is a misunderstanding here.

These are my own ideas, based on my personal experience managing Proxmox clusters, including environments where I run SQL Server on virtual machines.

English is not my native language, so I use AI to help me translate, structure, and express my thoughts more clearly in English. That does not mean the ideas or technical suggestions come from AI.

If you believe these are simply AI-generated suggestions, you are mistaken. They come from real-world experience, practical challenges, and lessons learned from managing production environments.

And frankly, what would be the point of taking someone's forum post, feeding it into AI, and simply copying and pasting the result? The original poster could easily do that themselves.

AI helped me with the wording, not with the thinking.

My intention was simply to share my experience and contribute something useful to the discussion. You are welcome to disagree with my technical suggestions, but I would appreciate it if they were judged on their technical merit rather than on how the English was written.

To avoid any further misunderstanding, I have also added a clear note at the end of my original post explicitly stating that the technical ideas and recommendations are entirely my own, based on my hands-on experience, and that AI was used solely to assist with translation, structure, and wording.