ASYNC_IO_COMPLETION Wait Type Explained

ASYNC_IO_COMPLETION waits signal that SQL Server's I/O completion port mechanism is processing asynchronous I/O requests that don't involve data pages. The SQL Server scheduler yields threads when these operations are submitted to Windows I/O completion ports, allowing other tasks to execute while the I/O subsystem handles the request.

These waits primarily occur in three scenarios: log shipping operations reading transaction log backups, database mirroring sending log records to mirror servers, and bulk operations like BULK INSERT or BCP that bypass the buffer pool. The key distinction from PAGEIOLATCH waits is that ASYNC_IO_COMPLETION doesn't involve buffer pool management or data page retrieval.

In SQL Server 2016 and later, Always On Availability Groups generate similar patterns but typically show as HADR-specific wait types rather than ASYNC_IO_COMPLETION. SQL Server 2019 introduced improvements to asynchronous I/O handling that reduced contention on I/O completion ports, making these waits less frequent but potentially longer in duration when they do occur.

The wait occurs at the Windows kernel level where SQL Server has submitted I/O requests through overlapped I/O operations. The thread remains in a wait state until the Windows I/O completion port signals the operation is finished. Storage subsystem performance, network latency (for mirroring), and file system fragmentation directly impact these wait durations.

-- Check current ASYNC_IO_COMPLETION waits and their prevalence
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'ASYNC_IO_COMPLETION'
AND waiting_tasks_count > 0;

-- Identify active sessions experiencing ASYNC_IO_COMPLETION waits
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'ASYNC_IO_COMPLETION';

-- Check for active log shipping or mirroring operations
SELECT 
    db.name AS database_name,
    db.state_desc,
    db.log_reuse_wait_desc,
    m.mirroring_state_desc,
    m.mirroring_role_desc
FROM sys.databases db
LEFT JOIN sys.database_mirroring m ON db.database_id = m.database_id
WHERE db.log_reuse_wait_desc LIKE '%MIRROR%' 
   OR m.mirroring_state IS NOT NULL
   OR db.name IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases);

-- Monitor I/O file stats for non-data files that might cause async waits
SELECT 
    f.name,
    f.type_desc,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    (vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS total_io_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
INNER JOIN sys.master_files f ON vfs.database_id = f.database_id 
    AND vfs.file_id = f.file_id
WHERE f.type_desc IN ('LOG', 'FILESTREAM')
ORDER BY total_io_stall_ms DESC;

-- Check for bulk operations that might generate async I/O waits
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    r.wait_type,
    t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('BULK INSERT', 'BCP', 'BACKUP DATABASE', 'BACKUP LOG')
   OR t.text LIKE '%BULK INSERT%'
   OR t.text LIKE '%BCP%';

Optimize Log Shipping Performance This script adjusts log shipping copy and restore job frequencies to reduce I/O pressure.

-- Increase log shipping backup frequency to reduce individual backup sizes
-- Test in dev first: affects RTO for log shipping
EXEC msdb.dbo.sp_change_log_shipping_primary_database 
    @database = 'YourDatabase',
    @backup_schedule_id = NULL, -- Will update existing schedule
    @backup_job_name = NULL,
    @backup_retention_period = 4320, -- 3 days
    @backup_directory = '\\LogShipShare\Backups',
    @backup_share = '\\LogShipShare\Backups',
    @backup_compression = 1;

-- Reduce backup frequency from default 15 minutes to 5 minutes
-- WARNING: Increases I/O frequency but reduces individual operation duration
EXEC msdb.dbo.sp_update_schedule 
    @schedule_id = (SELECT schedule_id FROM msdb.dbo.sysschedules 
                   WHERE name LIKE '%LSBackup%YourDatabase%'),
    @freq_subday_type = 4,
    @freq_subday_interval = 5;

Database Mirroring Performance Tuning Adjusts mirroring timeout settings to handle slow async I/O completion.

-- Increase partner timeout for slow networks/storage
-- Test carefully: affects failover detection time
ALTER DATABASE [YourMirroredDB] 
SET PARTNER TIMEOUT 20; -- Increased from default 10 seconds

-- Monitor mirroring performance after change
SELECT 
    db_name(database_id) AS database_name,
    mirroring_state_desc,
    mirroring_safety_level_desc,
    mirroring_partner_instance,
    mirroring_witness_state_desc
FROM sys.database_mirroring 
WHERE mirroring_guid IS NOT NULL;

Bulk Operation Optimization Configures bulk operations to use smaller batch sizes, reducing async I/O wait duration.

-- For ongoing bulk operations, implement batching to reduce I/O pressure
-- Replace large BULK INSERT operations with batched approach
DECLARE @BatchSize INT = 10000;
DECLARE @RowCount INT = 1;

-- Example batched bulk insert pattern
WHILE @RowCount > 0
BEGIN
    BULK INSERT YourTable 
    FROM 'C:\data\yourfile.csv'
    WITH (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n',
        FIRSTROW = 2,
        BATCHSIZE = @BatchSize,
        TABLOCK
    );
    
    SET @RowCount = @@ROWCOUNT;
    
    -- Brief pause to allow other operations
    WAITFOR DELAY '00:00:01';
END;

I/O Subsystem Configuration Check Validates Windows I/O completion port settings for SQL Server.

-- Check current max worker threads setting
-- May need adjustment for high async I/O workloads
SELECT 
    name,
    value,
    value_in_use,
    description
FROM sys.configurations 
WHERE name = 'max worker threads';

-- If value is 0 (auto), consider explicit setting for async I/O heavy workloads
-- WARNING: Test thoroughly before implementing
-- EXEC sp_configure 'max worker threads', 512;
-- RECONFIGURE;

Configure log shipping with appropriate frequencies based on network bandwidth and storage performance. For databases under 10GB, 15-minute backup intervals work well. Larger databases benefit from 5-minute intervals to prevent large async I/O operations.

Implement proper storage subsystem design with dedicated spindles for log files and separate from data files. Use RAID 10 for transaction logs rather than RAID 5 to minimize write latency that extends ASYNC_IO_COMPLETION waits.

Monitor database mirroring send queue length regularly. Queues consistently above 1MB indicate network or partner server bottlenecks causing extended async waits. Consider dedicated network links for mirroring traffic on busy systems.

Establish bulk operation standards that use BATCHSIZE parameters and TABLOCK hints. Operations processing over 100,000 rows should implement batching strategies to prevent monopolizing I/O completion ports. Schedule large bulk operations during maintenance windows when possible.

Set up automated monitoring for ASYNC_IO_COMPLETION wait statistics. Alert when average wait times exceed 100ms or when waiting task counts remain consistently above 5. These thresholds indicate I/O subsystem saturation affecting background operations.