How to address this MSSQL Disk Latency to less than 10ms?

jagregorio019

I’ve generated a file for NPM Orion Insights and noticed that some MSSQL write operations are exceeding 10ms. Would anyone be able to share the procedure to address or clear this?

jeremymayfield

Yes — getting MSSQL write latency consistently under 10 ms is a very common goal (and the exact threshold Orion Insights uses as a best-practice flag for SQL Server). SolarWinds documentation and SQL experts (Microsoft, Paul Randal, SQL Sentry/DPA) all agree:

• < 5 ms → Excellent / Ideal (especially for transaction logs)
• 5–10 ms → Good / Target
• > 10 ms (sustained or frequent peaks) → Warning → needs attention
• > 20 ms → Problematic (performance impact visible to users)

Orion Insights flags anything consistently over 10 ms on write operations because that’s where transaction log flushes, checkpoints, and tempdb contention start hurting query response times.

Step-by-Step Procedure to Diagnose & Fix

1. Pinpoint the Exact Problem in SolarWinds

• Go to the SQL Server node → AppInsight for SQL (best view) or PerfStack.
• Look at:
  • Physical Disk → Avg. Disk sec/Write
  • Which volume is spiking (Data, Log, TempDB, or System)?
• Regenerate the Orion Insights file after changes to confirm improvement.

2. Get Precise Per-File Latency from SQL Server (Run This Query)

SQL

SELECT     DB_NAME(vfs.database_id) AS [Database],    vfs.file_id,    mf.type_desc AS [File Type],    mf.name AS [Logical Name],    mf.physical_name AS [Physical Path],    vfs.num_of_writes,    vfs.io_stall_write_ms,    (vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0)) AS [Avg Write Latency (ms)]FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfsINNER JOIN sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_idWHERE vfs.num_of_writes > 0ORDER BY [Avg Write Latency (ms)] DESC;

Focus on any file showing > 10 ms (especially .ldf transaction logs and tempdb files).

3. Most Effective Fixes (in order of biggest impact)

Priority	Fix	Expected Improvement	Notes
1	Move transaction log (.ldf) files to dedicated fast storage (separate SSD/NVMe volume)	Huge (often drops from 15–30 ms → 2–5 ms)	#1 cause of high write latency
2	Optimize tempdb (multiple data files + fast storage)	Very high	1 file per CPU core (max 8) on fastest LUN
3	Pre-size database and log files + set sensible autogrowth (64–512 MB)	Medium–High	Avoid 10% autogrowth and tiny increments
4	Exclude SQL data/log/tempdb files from antivirus real-time scanning	Medium	Very common hidden cause
5	If virtualized: Move to higher-IOPS tier (Azure Premium SSD v2, VMWare VVols, etc.) or dedicated datastore	High	Check for storage contention (other VMs on same LUN)
6	Enable Instant File Initialization (SQL service account needs "Perform volume maintenance tasks")	Medium for growth events	Reduces spikes during auto-growth
7	Query/index tuning + update statistics (reduce unnecessary I/O)	Medium	Long-term

4. Quick Implementation Checklist

1. Identify the high-latency volume from the query above.
2. Create a new fast volume/LUN if needed.
3. Move the transaction log(s):ALTER DATABASE [YourDB] MODIFY FILE (NAME = 'YourDB_log', FILENAME = 'E:\Logs\YourDB_log.ldf');
4. For tempdb: Add files + restart SQL (or use online method in 2016+).
5. Restart SQL instance (planned maintenance window) or failover if clustered/AG.
6. Monitor for 24–48 hours in Orion PerfStack / AppInsight.

5. Verify the Fix

• Re-run the sys.dm_io_virtual_file_stats query.
• Check SolarWinds → AppInsight for SQL → Disk Activity or regenerate the Orion Insights file.
• You should see write latency drop below 10 ms (ideally < 5 ms for logs).

Orion-Specific Monitoring

Assign AppInsight for SQL template if not already done — it gives you the best built-in visibility into these exact metrics and will clear the Insights warning once resolved.

This exact issue (and the same 10 ms threshold) appears in many SolarWinds + SQL environments. The fixes above resolve it for the vast majority of cases.