Aggressive buffer pool warm-up after restart in SQL Server

Abstract

In many settings, a database server has to be restarted either in response to a failure event, or in response to an operational decision such as moving a database service from one machine to another. However, such restarts pose a potential performance problem as the new database server starts off with a cold buffer pool. As a result, the database application experiences a dramatic reduction in performance right after the restart, since just before the restart the database buffer pool was filled with hot pages and after the restart the database buffer pool is empty. To address these issues, traditional database systems use mechanisms such as SQL Server's aggressive page expansion and MySQL's buffer pool preloading. However, these approaches have key limitations including long warm-up times, possible early hot page eviction, user query performance saturation, and failure restart. In this paper, we present a new framework for SQL Server that allows continual capturing of the state of the buffer pool, and restoring the server state quickly with a snapshot of the buffer pool at restart. Our empirical evaluation demonstrates that our method reduces the time to regain peak performance by a factor of 2X or more over the previous approaches.