RSFXDEVICEOPS Wait: Unneeded WAITFOR DELAY Slowing Queries

The RSFXDEVICEOPS wait, listed as PREEMPTIVE_OS_RSFXDEVICEOPS, was high at a client that wasn’t using FileStream at all. Every new client teaches me something. This time I walked into an engagement where I got stuck on an interesting scenario.

The Error

The wait type was the symptom:

PREEMPTIVE_OS_RSFXDEVICEOPS

What the Client Saw

I have worked with hundreds of clients in the last year. This was the first time I met this wait type. I have seen it at a few clients who use FileStream, but not many use it. This client was not using FileStream at all.

I always promise to fix my client’s SQL Server performance problems during a Comprehensive Database Performance Health Check. This time I found no clue why this wait was so high while their system ran slower.

During the investigation, we started to focus on the currently running queries. We noticed an interesting syntax, a WAITFOR DELAY. Here is the sample text of the query:

BEGIN
SELECT * FROM YourTableName
WAITFOR DELAY '00:00:05'
END

How to Check

I suggested pulling out all the queries that had the WAITFOR syntax and running the queries again. At that point, we saw the PREEMPTIVE_OS_RSFXDEVICEOPS wait go down. We did multiple tests on the client’s server and saw the same behavior.

WAITFOR normally reports its own wait type, WAITFOR. So treat this link as what we saw on this server, not a general rule.

Why It Happens

Microsoft has suggested that this wait is for their internal use only, so I can’t point to a deeper cause. What I can tell you is what the tests showed. The WAITFOR DELAY in the queries went together with this wait.

The Fix

The client was surprised to see WAITFOR DELAY in their queries. They never needed it. They were not sure why, when or who had added it from the beginning. Once they removed it, their queries ran faster.

Confirm It Worked

With the clause gone, the wait dropped and the client got the performance back.

How to Prevent It

When a wait type has almost no documentation, read the text of the currently running queries next. Look for clauses that nobody remembers adding.

A mystery wait is not a mystery engine, it is sometimes a delay someone left in the query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.