SQL SERVER – Identifying and Fixing PREEMPTIVE_OS_RSFXDEVICEOPS Wait Type

While we say that every day is the learning opportunity is truly correct if you get to work with the new clients every single day Comprehensive Database Performance Health Check. Recently, I walked into a client engagement where I really got stuck with a very interesting scenario with a client with PREEMPTIVE_OS_RSFXDEVICEOPS Wait Type.

PREEMPTIVE_OS_RSFXDEVICEOPS Wait Type

I have worked with over 100s of clients in just the last year helping them with SQL Server Performance Tuning, however, this is the first time I was encountering this particular wait type. I have seen this kind of wait a few of the clients who are using FileStream but not many of the clients still use it. In the case of this client where I was called in to help with the SQL Server performance, they were NOT using the FileStream at all.

I was in a very strange position as I always promise that I will fix my client’s SQL Server Performance problems during the Comprehensive Database Performance Health Check and this time, I was not finding any clue why this particular wait was generated so high and their system was running slower.

During the investigation, at one point we just started to focus on various currently running queries. While looking at the queries we noticed a very interesting syntax of waitfor delay. Here is the sample text for the query.

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

I suggested to pull out all the queries which had the syntax for the WAITFOR and run queries again. At that time we did notice the reduction in the wait type PREEMPTIVE_OS_RSFXDEVICEOPS. We did multiple tests on the client’s server and we did noticed the same behavior. Microsoft has suggested that this wait is for their internal use only.

My client was also very surprised looking at the waitfor delay syntax in their queries as they never needed them. They were not sure why, when and who added them from the beginning. Once they removed the waitfor delay syntax their queries started to run faster and then got the necessary performance back.

I must say, my life is indeed exciting while fixing SQL Server Performances.

Reference: Pinal Dave (https://blog.sqlauthority.com)