1

I am investigating a slow SQL Server 2012 server in a virtualized (VMWare 6.5) environment, and have come across three different waits on the server that are... confusing. These are HADR_FILESTREAM_IOMGR_IOCOMPLETION, DIRTY_PAGE_POLL, and SQLTRACE_INCREMENTAL_FLUSH_SLEEP waits.

All three of these have essentially the same wait time (about 1000ms / sec). From research, I see that the DIRTY_PAGE_POLL and SQLTRACE_INCREMENTAL_FLUSH_SLEEP waits are (usually) nothing to be concerned about. However, documentation for the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait states that it is a wait generated by the system while waiting for HA replication to complete.

This particular Dev server has never had HA replication configured on the server (AFAIK / AFAICT), and does not now have replication or Always On High Availability of any type configured.

In this case -

  • Why would the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait occur at all?
  • Would this wait possibly cause, or indicate a cause of, a slowdown in a data load situation (5x-10x longer data loads than expected)
  • Since all three waits are running the same duration, is there any significance to the other two waits in this specific case, and could they indicate a slowdown situation on the server?

This server appears to be configured identically to the live server (also virtualized) that we are attempting to duplicate, with the exception of the OS version (Nt6.3 Build 9600 on live, NT6.1 SP1 Build 7601 on Dev). None of these three waits show up significantly on the live server.

Any ideas?

  • Filestream is not enabled on either of the servers in question. Each is a single node system. – Laughing Vergil Aug 14 '18 at 21:56
1

Why would the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait occur at all?

According to Paul Randal's wait stats library entry for this, waits are to be expected whether AGs are configured or not:

This wait occurs every 0.5 seconds for 0.5 seconds, regardless of whether any availability groups are configured.

Note however that this wait type was introduced in SQL Server 2012, so if your "live server" is an older version, you won't see this wait type on it.

Since all three waits are running the same duration, is there any significance to the other two waits in this specific case, and could they indicate a slowdown situation on the server?

It's also not surprising that the wait duration is similar for these three waits, as they each occur on a routine schedule.

Would this wait possibly cause, or indicate a cause of, a slowdown in a data load situation (5x-10x longer data loads than expected)

Unfortunately, these three specific waits are very unlikely to be the culprit for your slower-than-expected data load. I would filter them out, and check wait stats again. Consider filtering out all "benign" wait stats (example here: Wait statistics, or please tell me where it hurts) and reviewing what's high.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.