High HADR_FILESTREAM_IOMGR_IOCOMPLETION Wait Time Reported in Foglight (4379277)

Foglight has triggered an alarm for unusually high wait time on the SQL Server wait type HADR_FILESTREAM_IOMGR_IOCOMPLETION. The alert may raise questions, especially if the SQL Server instance is not configured with Availability Groups or FILESTREAM, leading some to assume this is a misclassification or monitoring error.

The wait type HADR_FILESTREAM_IOMGR_IOCOMPLETION is a valid internal SQL Server wait that typically relates to:

• FILESTREAM I/O operations

• Always On Availability Groups (AGs), specifically for replica synchronization involving FILESTREAM-enabled data

However, even if AGs and FILESTREAM are not explicitly configured, this wait type can still appear in environments such as:

• Azure SQL Managed Instance or complex hybrid setups

• Systems where AG or FILESTREAM features were previously enabled

• Internal SQL Server subsystems using FILESTREAM-style operations (rare but possible)

Foglight is not misclassifying this wait type. It is pulling the value directly from the SQL Server dynamic management view sys.dm_os_wait_stats. The same value can be confirmed by executing:

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = 'HADR_FILESTREAM_IOMGR_IOCOMPLETION';

This DMV tracks cumulative wait times since the last SQL Server service restart. Therefore, any value reported reflects actual internal activity recorded by SQL Server.

1. Verify the Source of the Wait:
   • Run the query above to confirm the wait stats from SQL Server.
   • Note the wait_time_ms and waiting_tasks_count.
2. Clear and Monitor:
   • Use the following command to clear existing wait statistics:
     DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
   • Wait several hours or a day, then recheck to see if the wait time is growing.
3. Confirm Configuration Status:
   • Check whether AGs are active:
     SELECT * FROM sys.availability_groups;
   • Check FILESTREAM status:
     EXEC sp_configure 'filestream access level';
4. Investigate Internal Use:
   • Even if AG/FILESTREAM are disabled, some internal SQL Server processes may still register under this wait type.
   • If using a managed platform (e.g., Azure SQL Managed Instance), some HADR subsystems may be active at the infrastructure level.
5. Tune or Suppress Alert If Appropriate:
   • If the wait time is not growing or has no measurable performance impact, consider adjusting the Foglight threshold or suppressing the alert for this wait type after proper evaluation.

• Unless the HADR_FILESTREAM_IOMGR_IOCOMPLETION waits stop accumulating when the agent is deactivated, this is not a Foglight issue; the tool is accurately reflecting what SQL Server reports.
• Misinterpretation may stem from incorrect assumptions based on input to an AI or misreading wait type behavior.
• If in doubt, consult with your DBA or Microsoft Support to confirm underlying configuration. If the HADR_FILESTREAM_IOMGR_IOCOMPLETION waits only occur while the SQL Server agent is monitoring then contact Quest Support.