Average Write Performance

October 11, 2026
| No Comments

The average write performance in SQL Server can be measured using the Average Disk sec/Write metric. This metric indicates the average time, in seconds, that it takes to write data to the disk. Points to bear in mind:

  • Performance Counters: SQL Server uses performance counters like Avg. Disk sec/Write to measure I/O latency. Ideally, this value should be below 10-15 milliseconds
  • I/O Bottlenecks: High values in these counters can indicate I/O bottlenecks, which can slow down SQL Server performance
  • Monitoring Tools: Tools like Windows Performance Monitor can help track these metrics over time to identify trends and set baselines for normal operation

Ways to improve write performance:

  • Index Management:
    • Add Missing Indexes: Make sure frequently written columns have appropriate indexes
    • Remove Unused Indexes: Unused indexes can slow down write operations as they need to be updated with each write
  • Batch Processing:
    • Batch Inserts: Instead of inserting rows one by one, batch them together to reduce the overhead
    • Bulk Operations: Use bulk insert operations for large datasets to improve performance
  • Transaction Management:
    • Minimise Transaction Size: Keep transactions small to reduce lock contention and improve write speed
    • Use Proper Isolation Levels: Choose the appropriate isolation level to balance between performance and data integrity
  • Hardware Optimisation:
    • SSD Storage: Use SSDs instead of traditional HDDs for faster write speeds
    • Optimise Disk I/O: Make sure your disk I/O subsystem is optimised for high throughput
  • Database Configuration:
    • Tune SQL Server Settings: Adjust settings like max server memory and max degree of parallelism to optimise performance
    • Optimise TempDB: Configure TempDB properly, as every temporary object lives there
  • Query Optimisation:
    • Avoid Unnecessary Writes: Make sure your queries are optimised to avoid redundant write operations
    • Use Efficient Data Types: Choose appropriate data types to reduce storage and improve write performance

Tools for monitoring transaction performance:

  • Performance Counters:
    • Transactions/sec: This counter tracks the number of transactions processed per second, giving you an idea of the transaction load
    • Batch Requests/sec: Measures the number of SQL batches processed per second, which can help identify performance bottlenecks
  • Dynamic Management Views (DMVs):
    • sys.dm_tran_database_transactions: Provides information about active transactions at the database level
    • sys.dm_exec_requests: Shows details about currently executing requests, including transaction-related data
  • Extended Events:
    • Extended Events: A lightweight performance monitoring system that captures detailed event data with minimal overhead. You can create sessions to monitor specific transaction-related events.
  • SQL Server Profiler:
    • SQL Server Profiler: Allows you to trace and analyse SQL Server events, including transaction performance. You can filter events to focus on transaction-related activities.
  • Replication Monitor:
    • Replication Monitor: Useful for monitoring transactional replication performance, including latency and synchronisation statistics
  • Database Engine Tuning Advisor (DTA):
    • DTA: Analyses the performance effects of your SQL statements and provides recommendations to optimise indexes, partitions, and more
  • System Monitor (PerfMon):
    • PerfMon: Tracks resource usage and performance metrics, including transaction-related counters. You can set up alerts and thresholds to monitor performance trends.
SELECT DB_NAME(database_id) AS 'Database_Name',
       CASE
           WHEN file_id = 2
           THEN 'Log'
           ELSE 'Data'
       END AS 'File_Type',
       ((size_on_disk_bytes / 1024) / 1024.0) AS 'Size_On_Disk_in_MB',
       io_stall_read_ms / num_of_reads AS 'Avg_Read_Transfer_in_Ms',
       CASE
           WHEN file_id = 2
           THEN CASE
                    WHEN io_stall_read_ms / num_of_reads < 5
                    THEN 'Good'
                    WHEN io_stall_read_ms / num_of_reads < 15
                    THEN 'Acceptable'
                    ELSE 'Unacceptable'
                END
           ELSE CASE
                    WHEN io_stall_read_ms / num_of_reads < 10
                    THEN 'Good'
                    WHEN io_stall_read_ms / num_of_reads < 20
                    THEN 'Acceptable'
                    ELSE 'Unacceptable'
                END
       END AS 'Average_Read_Performance',
       io_stall_write_ms / num_of_writes AS 'Avg_Write_Transfer_in_Ms',
       CASE
           WHEN file_id = 2
           THEN CASE
                    WHEN io_stall_write_ms / num_of_writes < 5
                    THEN 'Good'
                    WHEN io_stall_write_ms / num_of_writes < 15
                    THEN 'Acceptable'
                    ELSE 'Unacceptable'
                END
           ELSE CASE
                    WHEN io_stall_write_ms / num_of_writes < 10
                    THEN 'Good'
                    WHEN io_stall_write_ms / num_of_writes < 20
                    THEN 'Acceptable'
                    ELSE 'Unacceptable'
                END
       END AS 'Average_Write_Performance'
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads > 0
      AND num_of_writes > 0;

From my SQL Server DBA handbook.

Performance