Wednesday, June 16, 2010

Backup and Restore of the SQL Server buffer pool

Have you ever missed this feature? Whenever you install the latest security update, it is very likely that your server requires a restart, leading also to a restart of SQL Server. If this ever happens, SQL Server needs to start from scratch, having nothing in the plan cache and also no data pages in the pool.I have many customers that complain about applications performing poorly after a server restart – for whatever reason that may have been necessary.

So finally, in the current project, we ended up developing a method for warming up the cache on our own after SQL Server has been started. Obviously not an easy task, but we had a smart guy who implemented this. The final solution is by no means perfect but almost sufficient according to our requirements.

When discussing about how we should implement this feature and also the pros and cons, we had the idea that we did nothing more than SQL Server itself should be capable of. Wouldn’t it be nice, if we could just perform a backup of the buffer pool and later restore it? Or even better, how about just sending SQL Server to hibernate mode (like Windows) and wake it up later? Clearly this won’t work under every circumstance, since some updates or patches may require a "cold" restart. But in those cases where it’s technically possible, I’d like to have this opportunity.

I like the idea so much that I’ve added a regarding change request on Microsoft’s connect platform (item# 561951). If you agree, you can vote for it here.

No comments:

Post a Comment