5

Is there a way to preserve the buffer pool cache so that is can be loaded in after a restart? My research suggests that other DB servers can do this but SQL Server is not one of them. I'm looking for ways to maximize server performance after a maintenance restart.

2
  • I remember doing it during my SybaseASE days which helped during the load testing wherein we knew certain hot tables needed to be inmemory. If you are thinking of doing this after every restart on prod, I would say - do not do it since it will just overwhelm your disk subsystem if the tables are pretty big.
    – Kin Shah
    Commented Mar 4, 2018 at 22:26
  • Only with memory-optimized. And it's only sort-of, being a totally different beast (no buffer pool, to start with :))
    – dean
    Commented Mar 5, 2018 at 22:22

1 Answer 1

8

No, there is no way to restore the buffer pool. Some will suggest pre-filling it by selecting all rows from all tables, but that is just silly given how rare it is for systems to use all rows from all tables (among other reasons that it wouldn't do what such an action is perceived to do).

I'm not even sure that it makes sense conceptually that this should be a thing to do. What is the actual gain here? Saving the time it takes to read those data pages into memory from disk? If the pages in the buffer pool were saved to a file to be reloaded upon start-up, it would still take that same (or nearly same) amount of time to load those data pages into the buffer pool, just from a different file. Or, perhaps you don't want the application pages' initial response times to reflect that the data pages had to be loaded? How long does your system take to load pages? One person waiting an extra 1 second for that particular result set (cuz after that, those pages are in memory for subsequent queries, right?) is hardly noticeable, nor is it something that they will remember when subsequent page loads take less time. But if this is a web-based application, then can it be said that any lag is the fault of the DB and not of the app server or some segment of the route between the client computer and the app server?

Don't worry about initial load times of data pages. Any areas for improvement of initial load times are going to be in better data modeling and index structure (i.e. making sure that reading pages from disk is as efficient as possible). Instead, worry about areas that drag on performance for repeated requests once the data has been loaded.

Besides, who is to say that you want all of the pages that were in the buffer pool? How many ad hoc support queries were performed recently that are still in the cache that likely won't get looked at again for 6 months or so? Just let SQL Server take care of managing the cache as the pages that get loaded when users start hitting the system after the restart will necessarily be the pages that should be there.

Also, given that SQL Server can evict pages from the cache whenever wants (if it feels that more memory is needed for other items) then you might load a bunch of pages that get evicted a moment later.

1
  • I've done this (load all the pages from disk), and there's another ugly problem: when lots of servers restart at once, like during patches, you can overwhelm an underlying shared storage system. Sorry about that, boss.
    – Brent Ozar
    Commented Mar 4, 2018 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.