Waiting Tasks Caused by FSAGENT in SQL Server 2008

 

After preparing a brand-new server hosting SQL server 2008 and before starting anything ,we’ve noticed that there are 5 waiting tasks on server,it was really weird as no users are connected and no operation took place yet! The activity monitor displayed 4/5 waiting tasks as follows:

clip_image002

After the investigation and running couple of scripts for troubleshooting we reached to root cause of the issue ,By running the following script that lists the waiting tasks on server , it shows that FSAGENT has 5 waiting tasks :

SELECT * FROM sys.dm_os_waiting_tasks

SELECT CAST(wait_type AS VARCHAR(30)) AS ‘Waiting task’, COUNT (*) AS ‘Number of waiting tasks’

FROM sys.dm_os_waiting_tasks

GROUP BY wait_type

ORDER BY ‘Number of waiting tasks’ DESC;

Go

clip_image004

FSAGENT is part of the FILESTREAM subsystem, FILESTREAM is a new feature introduced in SQL Server 2008 which provides an efficient storage and management option for BLOB data.

This wait type occurs when a FILESTREAM file I/O operation is waiting for a FILESTREAM agent resource that is being used by another file I/O operation, in our case this feature is not needed so as a quick solution we’ve just disabled file stream access level feature from server properties as follows:

clip_image006

And after this feature is disabled the waiting tasks on activity monitor shows 0 waiting tasks!

clip_image008

Now the question is, why there are 5 waiting tasks before disabling this feature?, well it seems that this is a known bug that’s been fixed in KB 958942 titled FIX: Activity Monitor shows the high wait times when the FSAGENT wait type is enabled on a server that is running SQL Server 2008.