SQL Server Buffer Pool Persistence Feature Request

Question

SQL Server Buffer Pool Persistence Feature Request

Steven Lam 20

Could SQL Server implement a feature to export buffer pool data? This would eliminate the need for data warm-up after database restarts or initial startups. By directly importing previously saved buffer pool data, the database could achieve optimal performance immediately.

Referencing MySQL 8.0's implementation - MySQL automatically exports buffer pool data (stored in ib_buffer_pool file) and imports it during the next startup. This bypasses the warm-up phase and delivers peak performance right away.

[root@2_mysql_68 data]# ll /data/mysql/mysql3306/data/

total 156732

-rw-r----- 1 mysql mysql       56 Mar  4 15:22 auto.cnf

-rw------- 1 mysql mysql     1705 Mar  4 15:22 ca-key.pem

-rw-r--r-- 1 mysql mysql     1108 Mar  4 15:22 ca.pem

-rw-r--r-- 1 mysql mysql     1108 Mar  4 15:22 client-cert.pem

-rw------- 1 mysql mysql     1705 Mar  4 15:22 client-key.pem

-rw-r----- 1 mysql mysql  6291456 May  3 16:18 #ib_16384_0.dblwr

-rw-r----- 1 mysql mysql 14680064 Apr 12 12:53 #ib_16384_1.dblwr

-rw-r----- 1 mysql mysql     3638 Apr 12 13:37 ib_buffer_pool

-rw-r----- 1 mysql mysql 67108864 May  3 16:16 ibdata1

Dan Guzman 9,376 Reputation points

2025-05-03T17:06:20.8533333+00:00

Note SQL Server Enterprise/Developer edition already aggressively warms buffer bool cache until it reaches max size, mitigating the need to implement such a feature.

2 answers

Your answer

Dan Guzman 9,376 Reputation points

2025-05-03T17:06:20.8533333+00:00

Note SQL Server Enterprise/Developer edition already aggressively warms buffer bool cache until it reaches max size, mitigating the need to implement such a feature.

Answer 1

Currently, SQL Server does not have an equivalent native feature for buffer pool persistence. However, recent versions have introduced improvements related to memory management:

SQL Server 2019 (15.x): Introduction of the Hybrid Buffer Pool concept, which allows buffer pool objects to reference data pages stored in persistent memory (PMEM) devices, avoiding the need to copy those pages to volatile DRAM. This approach improves performance by reducing latency for data access. (learn.microsoft.com)
SQL Server 2022 (16.x): Introduction of Buffer Pool Parallel Scan, which improves performance for buffer pool scan operations by utilizing multiple CPU cores, significantly increasing scalability, especially on machines with large amounts of memory. (microsoft.com)

Steven Lam 20 Reputation points

2025-05-04T02:44:56.13+00:00

I understand all of this, but this has no direct relation to database startup loading and warm-up. Moreover, if customers are using subpar hardware, these measures won’t help at all.

Answer 2

Erland Sommarskog 120.3K MVP

In addition to Wagner's answer: the best place to submit suggestions like this is https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0.

My direct reflection is that this is a matter of trade-off. If the server has 2TB of RAM, exporting the buffer pool will take some time, and so will importing it. The way it is "imported" now is that the pages actually being referred to are read from their regular storage of disk. Then again, reading pages from the database comes with a overhead that can be avoided with a plain dump. Then again, there has to be 2 TB of disk space somewhere.

Steven Lam 20 Reputation points

2025-05-04T02:42:39.7366667+00:00

Yes, I have already created a post to provide feedback on this feature. Here is the post link:

https://feedback.azure.com/d365community/idea/fe14942a-2828-f011-9d48-7c1e52b9d984

As you mentioned, for large-memory servers, exporting and importing the buffer pool can indeed be very time-consuming. However, consider the scenario with in-memory tables: when a database instance starts, it recompiles all stored procedures, views, and functions, and rebuilds table data from disk.
Erland Sommarskog 120.3K Reputation points MVP

2025-05-04T07:54:31.1833333+00:00

However, consider the scenario with in-memory tables: when a database instance starts, it recompiles all stored procedures, views, and functions, and rebuilds table data from disk.

Not sure what you mean here. With In-Memory OLTP the tables are reconstructed from the checkpoint files, and that could take some time for larger tables. But it would be pointless to export the tables to disk - the whole idea is that the tables are in memory only. (And they have their own memory; they are not part of the buffer cache.)

Also, when it comes to in-memory OLTP procedures and functions, they are DLLs, so they are not recompiled on start up. (Well, if there has been a failover to another node, they might have to.)

If we go back to traditional disk-based tables, they are also subject to constant updates, which means that an export of the buffer cache becomes stale pretty quick. This could be addressed by reading the transaction log, but that would add quite a bit of complexity for limited benefit.

The plan cache may seem like a better target, but again it is a state of a flux. What if the cache is exported, an index is dropped, and then we restart and the exported cache is read, but the plan is no longer valid.

What possible could make sense is if you are able to lock the server, export the cache and then restart - for instance to apply a CU - and now the restart could make use of the saved plan cache. I'm still not sure that the returns are worth the investment for Microsoft.

Share via

SQL Server Buffer Pool Persistence Feature Request

2 answers

Your answer