In a nutshell, this spinlock is used to synchronize access to the list of pages that are present in a database snapshot, as follows:
- If a page in a database with one or more database snapshots is being updated, check each snapshot’s list to see if the page is already in the snapshot. If yes, nothing to do. If no, copy the pre-change image of the page into the snapshot.
- If a query is reading a page in the context of a database snapshot, check the list of pages to see whether to read from the snapshot or the source database.
This synchronization ensures that the correct copy of a page is read by a query using the snapshot, and that updated pages aren’t copied to the snapshot more than once.
The original question was because the person was seeing trillions of spins for the FCB_REPLICA_SYNC spinlock. That’s perfectly normal if there’s at least one database snapshot, a read workload on the snapshot, and a concurrent heavy update workload on the source database.
Great information. And a good reminder that if you are using database snapshots in SQL Server, you generally don’t want to have more than one on the same database.