How It Works: FileStream (RsFx) Garbage Collection–Part (2)

In a previous post I outlined the basics of File Stream, garbage collection: https://blogs.msdn.com/b/psssql/archive/2011/06/23/how-it-works-filestream-rsfx-garbage-collection.aspx

This post continues the discussion, outlining specific details as to how the garbage collection progresses.

A Single GC Thread

The instance of SQL Server contains a single, background worker performing the garbage collection activities.   The FSGC worker, wakes up every ~5 seconds and loops over each database, managing the tombstone table entries in small batches. 

foreach database
      Do File Stream Garbage Collection (batch size)

Fixed Batch Size

The batch size for the background, file stream, garbage collection (FSGC) worker is currently 20 rows (a hard coded value.)   This allows FSGC to remain a background and unobtrusive process.

Note:   The type of tombstone row and status determines the action FSGC takes.   For example a delete of a row enters a single delete entry into the tombstone table.  A truncate table may enter a single row in the tombstone table and FSGC understands the single file delete vs truncate status and may take broader action.

sp_filestream_force_garbage_collection (https://technet.microsoft.com/en-us/library/gg492195.aspx)

If you are like me the first thing I did was the math on the 20 rows per batch every 5 seconds and determined there is a finite number of rows in a 24 hour period the FSGC can accomplish.  Enter sp_filestram_force_garbage_collection in SQL Server 2012 as a new feature.   

The procedure allows you to execute the FSGC as a foreground process without the batching limits.   This can be helpful if you have a large number of changes (inserts/updates/deletes) to file stream data where the tombstone rows and associated disk files have grown to a large number you wish to aggressively cleanup.

Delete and Status

When you do an update to a file stream column or perform a delete of the row an entry for the delete is placed in the tombstone table.

For example:

update tblDocuments set FSData = ‘New Image’ where PK = 1

Checkpoint and Log Backup: The ‘Orig Image’ can’t be deleted until properly synchronized with the backup stream activities.   The first log backup after the update secures the Orig and New Image.  This allows point-in-time restore capabilities, just before the update during restore.

If you select from the tombstone table before FSGC and proper checkpoint/log backup has been executed the status column for the entry will contain a (####7) such as 17 or 217.

Prepare for delete (7) : The status column is a combination of bits used by FSGC to determine the state of the row.   A value of (7) in the lower position is an indication of prepare for delete to the FSGC.

Do physical file deletion (8):   FSGC must transition the entry from the status of 17 or 273 to 18 or 283, requiring a second checkpoint and log backup take place before the physical file can be removed from disk storage.

Confused yet?   I was so let me walk you through the activity (checkpoints, log backups and status changes.)

1. Update – Enters new entry with status #7 into tombstone
2. FSGC runs – can’t do anything no log backup and checkpoint has advanced the checkups and backup LSNs (you can see with dbcc dbtable).  Entries stay at #7 status.
3. Checkpoint/Log backup executes
4. FSGC runs – finds first batch (20) rows by lowest LSN values and attempts appropriate actions.  Finds entry of status #7 with proper checkpoint/log backup of status #7 and updates status to #8 so next FSGC after another checkpoint/log backup can do physical removal of file.  ( * See below for update details.)
5. FSGC runs – can’t do anything no log backup and checkpoint has advanced the checkups and backup LSNs (you can see with dbcc dbtable).  Entries stay at #8 status.
6. Checkpoint/Log backup executes
7. FSGC runs – finds first batch (20) rows by lowest LSN values and attempts appropriate actions.  Finds entry of status #8 with proper checkpoint/log backup of status #8; removes the physical file and deletes tombstone entry.

* The update of #7 to #8 status is not an in-place update.   The update is a delete (old tombstone row) and insert (updated) row pairing.    This matters to you because of the following.

FSGC is looking at the top 20 rows during each batch.   When it transitions the 1st row (shown above) from #7 to #8 status the row is added to the end of the tombstone table because it is the newest LSN. 

In this example if you delete 10,000 rows from the base table you get 10,000 entries in the tombstone table.   The physical, file deletion won’t occur until the FSGC has first transitioned all 10,000 entries from #7 to #8 status.  Since FSGC runs every ~5 seconds in batches of 20 the math is really 10,000 * 2 (because you have to go through #7 to #8 status changes and then divide by 20 * 5 or  (20000/20) = 1000 FSGC batches @ 5 seconds per batch = 5000 seconds or ~83 minutes (assuming proper checkpoints and log backups) to physically remove the original files.

In Summary

Understanding the transitions states, batch sizes and requirements for log backups and checkpoints should allow you to maintain your databases with file stream data better.

• Make sure you have regular log backups
• If database is inactive you may want to run manual checkpoint(s)
• Consider sp_filestream_force_garbage_collection on SQL Server 2012 and newer versions

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

• Anonymous
  April 04, 2014
  Alternatively, you could have made the cleanup background process throttle itself up and down with the amount of outstanding work. I always find features like sp_filestram_force_garbage_collection inelegant as they just pass the buck on to the customer.

• Anonymous
  April 08, 2014
  You are thinking like we are.  We had already filed a work item to scale the background process just as you have suggested.