Does a Database Restore 'Prime' the Buffer Pool?

A co-worker asked me one day last week if, when a database is restored, the restored pages are retained in the buffer pool. I immediately answered 'no', but I realized this was something I'd either always assumed or someone had told me once upon a time. I was sure I was right, but I wanted to remind myself of the how and why.

The first thing I did was to clear the buffer pool, restore a database and then checked the DMV sys.dm_os_buffer_descriptors to see if there were pages for that database in the bufferpool.

SELECT
    (CASE WHEN ([database_id] = 32767) THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [Size_MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id]
ORDER BY DB_NAME ([database_id]);

Sure enough, the amount of the buffer pool used by that database did not change and remained zero. In fact, if I perform the opposite test of doing some selects against the database to read pages into the buffer pool and then restore the database, we can see that again the used buffer pool for that database returns to zero after the restore. So what's going on? Why doesn't a restore 'prime' the buffer pool? In short, because the restored data pages never enter the buffer pool in the first place!

During backup and restore operations, SQL Server uses a separate memory area for buffering the data being handled. This buffer is NOT the buffer pool! In fact, this memory is allocated from the OS, outside of the server memory that you can allocate using the "max server memory" and "min server memory" settings. You can configure the size of this buffer using the BUFFERCOUNT and MAXTRANSFERSIZE options in the BACKUP and RESTORE commands. The total buffer size is BUFFERCOUNT*MAXTRANSFERSIZE. By default, MAXTRANSFERSIZE is 1MB and the BUFFERCOUNT is variable and version dependent, but can be calculated by the formula "NumberofBackupDevices* 3 + NumberofBackupDevices + (2 * NumberofVolumesInvolved)" for SQL 2005+. Details on this formula can be found here. In a very simple situation of a single backup device and a single volume, there would be 7 buffers for a total of 7MB used. Modifying these values can, in some cases, offer a performance benefit, but you'll need to test various scenarios in your environment in order to determine the optimal settings. In general, the benefits to be gained are minimal, but that doesn't mean it's not worth testing! Note that it is possible, if using large values, especially on 32-bit systems, to request more buffer space then the OS can deliver, in which case you'll receive the error:

Msg 701, Level 17, State 17, Line 1
There is insufficient system memory to run this query.d]);


When testing, you can use trace flags 3605 & 3213 to have SQL Server write backup/restore statistics (including the values used for BUFFERCOUNT and MAXTRANSFERSIZE) to the error log. Also, Ben Snaidero wrote a great article talking about performance tuning backups.