The buffer pool is cold: warming up SQL Server after restore, import and restart

Starting point

A large database is painfully slow in the morning after the nightly restore. In my setup a production database is restored every night onto a second SQL Server, so people can work and test against yesterday's data. The first queries of the morning take forever, and only after a while does it become bearable.

The cause is rarely a defect. It is a cold cache. You can do something about it without tuning the server: read in the data that is about to be needed.

When the buffer pool goes cold

After a restore the buffer pool is empty, but that is not the only case. It loses its hot pages in several situations:

• Restore (WITH REPLACE): All pages of the database are discarded. The first query on each object reads from disk, and the plans of the database have to be compiled again.
• Restart of instance or server: After a restart, or a failover of the instance to another node, the buffer pool starts at zero.
• Large imports and ETL runs: A bulk import or load job pulls many pages through the buffer pool and can push out the hot pages of the daily workload.
• Maintenance: Index rebuilds, statistics updates with FULLSCAN and DBCC CHECKDB read large amounts of data. They push pages out too.
• Big reports and scans: A single query that reads a large table completely is enough as well.

In all these cases the database is fine, just slow until the cache has filled again. The goal is to not let the users fill the cache for you.

Step 1: capture the hot list

Instead of reading everything blindly, I let SQL Server tell me what is actually in use. A procedure queries sys.dm_os_buffer_descriptors and records, per table and index, how many MB currently sit in the buffer pool. Simplified, the query looks like this:

SELECT s.name, o.name, i.index_id,
       SUM(b.Pages) * 8 / 1024.0 AS CachedMB
FROM  (SELECT allocation_unit_id, COUNT_BIG(*) AS Pages
       FROM sys.dm_os_buffer_descriptors
       WHERE database_id = DB_ID()
         AND page_type IN ('DATA_PAGE', 'INDEX_PAGE')
       GROUP BY allocation_unit_id) AS b
JOIN sys.allocation_units au ON au.allocation_unit_id = b.allocation_unit_id AND au.type = 1
JOIN sys.partitions p ON p.hobt_id = au.container_id
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
GROUP BY s.name, o.name, i.index_id
ORDER BY CachedMB DESC;

The result goes into a small table in a utility database (in my case DBAUtil), not into the database that gets restored or maintained. I drop objects with less than 8 MB in cache as noise.

The procedure runs as an agent job at a time when the buffer pool reflects the daily workload, for example late afternoon. What matters is that it runs before an import, a maintenance job or a restore changes the cache.

Step 2: read it in

A second procedure reads the list and runs one scan per entry:

SELECT COUNT_BIG(*) FROM [MyDatabase].[dbo].[Table] WITH (INDEX(2)) OPTION (MAXDOP 4);

A few details that matter:

• The INDEX(n) hint is required. Without it the optimizer picks the narrowest nonclustered index for COUNT(*), not the one I want to warm. I use the numeric index ID, which avoids trouble with names.
• Indexed views are read with NOEXPAND, INDEX(n), otherwise the hint does not apply.
• Entries are processed in descending order of cached size. The run stops at an MB budget (default: 80% of the buffer pool target) or a time limit (default: 30 minutes).
• If an object is missing in the target database, it is skipped and logged.
• The list can live locally or be read from another server through a linked server. That helps when the database is restored onto a second server every day.
• If the list is empty or the linked server is unreachable, a fallback reads the largest indexes of the database. That is size instead of usage, so it is worse, but better than nothing.

The calls per scenario

The same procedure covers all scenarios. Only the source of the list and the timing change.

After a restore onto a second server. The list is captured on the production server and read through a linked server:

EXEC DBAUtil.dbo.usp_Warmup_RestoredDb
     @TargetDb         = N'MyDatabase',
     @SourceDatabase   = N'MyDatabase',
     @ListLinkedServer = N'ProductionServer';

After an import or ETL run on the same server. The list is local and dates from before the import. The call goes in as the last step of the load job:

EXEC DBAUtil.dbo.usp_Warmup_RestoredDb
     @TargetDb       = N'MyDatabase',
     @SourceDatabase = N'MyDatabase',
     @MaxMinutes     = 15;

After a server restart. An agent job whose schedule is "Start automatically when SQL Server Agent starts" calls the same procedure. The table with the list lives in a database and survives the restart.

All three cases assume that the list is captured regularly. A stale list reads stale objects. The CapturedAt column in the table shows how old it is.

Pitfalls and limits

The warm-up has limits, and you should know a few of them before you use it:

• Not covered: LOB and row-overflow pages and columnstore indexes cannot be warmed sensibly with a scan.
• Order: Rebuilds and imports distort the hot list. The collector has to run before them, not after. The simplest way is to call it as the first step of the maintenance job.
• Memory: If max server memory is smaller than the hot data set, reading it in helps little.
• Load: Reading in generates I/O. Run it outside peak hours and limit it with budget, time limit and MAXDOP.
• Cost versus benefit: If the warm-up takes longer than the cold phase it replaces, you gained nothing.
• Restart: If the job runs before the database is online, all objects are skipped and logged.
• Linked server: If it fails, only the fallback runs. You can see that in the message of the run.

One note on my own behalf: these are my tools for my environment. If you adopt them, test them on a copy first.

Measure, don't believe

Only a measurement shows whether this pays off. A simple check query shows how much of each database currently sits in the buffer pool:

SELECT DB_NAME(database_id) AS DatabaseName,
       COUNT_BIG(*) * 8 / 1024 AS CachedMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY CachedMB DESC;

Run it right after the restore, after the warm-up and at the end of the working day. For a typical query you can also compare the physical reads with SET STATISTICS IO ON.

After a run, the procedure returns the number of indexes read, the MB and the seconds.

Conclusion

A cold buffer pool is not a defect but the normal state after a restore, a restart and larger jobs. Two small procedures are enough: a list of what is really used, and a run that reads it in again afterwards.