Information About Waits Stats in SQL Server FILESTREAM Waits

SQLServerF1

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.

FS_FC_RWLOCK
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.

FS_GARBAGE_COLLECTOR_SHUTDOWN
Occurs when the FILESTREAM garbage collector is waiting for cleanup tasks to be completed.

FS_HEADER_RWLOCK
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).

FS_LOGTRUNC_RWLOCK
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.

FSA_FORCE_OWN_XACT
Occurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.

FSAGENT
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.

FSTR_CONFIG_MUTEX
Occurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.

FSTR_CONFIG_RWLOCK
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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *