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:
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'
GROUP BY wait_type
ORDER BY 'Number of waiting tasks' DESC;
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:
And after this feature is disabled the waiting tasks on activity monitor shows 0 waiting tasks!
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.