Surprising memory usage with database snapshots

Question: I’ve noticed that when I use a database snapshot for end-of-month reporting, there seems to be memory pressure on the buffer pool. How can this be if a database snapshot doesn’t create a copy of the real database?

Answer: What you’re noticing is a little-known fact about how database snapshots work.

It’s true that when a database snapshot is created, the source database is not copied into the database snapshot. It’s also true that the only data file pages that are copied into the database snapshot are those that change after the database snapshot is created (and those required to make the database snapshot transactionally consistent when it is created—see this blog post for an explanation).

These two behaviors mean that a database snapshot of a database that does not change should take up a minimal amount of disk space. But what about memory in the buffer pool?

As an example, I took a contrived sales database called SalesDB (which you can download from our resources page here). I attached the database and created a database snapshot on it, using the code below:

CREATE DATABASE SalesDB_SnapshotON (    NAME = N'SalesDBData',    FILENAME = N'C:SQLskillstestSalesDBData.mdfss')AS SNAPSHOT OF SalesDB;GO

Next, I forced all the data file pages in the Sales table to be read using a SELECT COUNT (*) in the context of the SalesDB database and in the context of the SalesDB_Snapshot database:

GOSELECT COUNT (*) FROM Sales;GOUSE SalesDB_Snapshot;GOSELECT COUNT (*) FROM Sales;GO

You would expect the second SELECT statement to use the same set of pages as the first. We can check this using a script I published recently (see here) to examine the buffer pool contents.

The results show that there is 376MB in the buffer pool for both databases!

Although data file pages will not be copied into the database snapshot data files until they change, if a page from the source database is used in the context of the database snapshot to satisfy a query, it must be stored in its own buffer in the buffer pool. You can confirm this for yourself using DBCC PAGE on the same page in both databases and you’ll see them stored in buffer with different memory addresses.

This behavior means you can have many pages that are essentially duplicated in the buffer pool—which can cause memory pressure and excess I/Os, depending on what queries you’re running against the database snapshot. There is no way to change this behavior, and it’s unlikely to change in future as this is the simplest mechanism to use under the covers.