(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
This wait type is when a thread is waiting for a synchronous write into a database snapshot by the buffer pool. This can occur in two ways:
- Just before the page in the snapshot source database is going to be changed for the first time after the snapshot was created (the ‘push’ case in the Books Online description below)
- When a page from the snapshot source database needs to be changed in the snapshot as part of running crash recovery into the snapshot when it is first created (the ‘pull’ case in the Books Online description below)
(Books Online description: “Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.”)
Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
Removed in SQL Server version:
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 175 in 2008 and 2008 R2, and 179 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
I have not seen this wait type be a noticeable contention point.
However, I can imagine a case where there are many database snapshots for a single source database, and lots of updates happening, that would cause a lot of these waits to happen and slow down the updates on the source database, as a pre-change page image must be synchronously pushed to all existing and relevant snapshots before the page can be changed in the source database. In that case, the solution would be to not have so many database snapshots on such a volatile source database.
Known occurrences in SQL Server (list number matches call stack list):
- Pushing a page into a snapshot (in this case, while inserting a record into an index page)
- Pulling a page into a snapshot (in this case, as part of running crash recovery during the creation phase of a snapshot, where the snapshot is the hidden snapshot created by DBCC CHECKDB)
- Pushing a page into a snapshot (in this case, while modifying bits in a differential bitmap during the initial checkpoint performed by a data backup)
And many other similar call stacks.
Abbreviated call stacks (list number matches known occurrences list):