Information About Waits Stats in SQL Server FILESTREAM Waits
SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.
If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.
Below are some of the SQL Server Wait Types related to FILESTREAM waits.
Occurs when there is a wait by the FILESTREAM garbage collector to do either of the following:
o Disable garbage collection (used by backup and restore).
o Execute one cycle of the FILESTREAM garbage collector.
Occurs when the FILESTREAM garbage collector is waiting for cleanup tasks to be completed.
Occurs when there is a wait to acquire access to the FILESTREAM header of a FILESTREAM data container to either read or update contents in the FILESTREAM header file (Filestream.hdr).
Occurs when there is a wait to acquire access to FILESTREAM log truncation to do either of the following:
Temporarily disable FILESTREAM log (FSLOG) truncation (used by backup and restore).
Execute one cycle of FSLOG truncation.
Occurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.
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.
Occurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.
Occurs when there is a wait to serialize access to the FILESTREAM configuration parameters.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.