You are a DBA who wants a faster database every morning and bigger bonus at year end. Sign up here!
Every day it’s the same thing, the first folks in the office grumble how the database is a little slow to get going. You know it’s because nightly maintenance (indexing or ETL etc.) “kill” the buffer pool. Maybe you have thought, why can’t we just “save the buffer pool state” before we run our nightly jobs and then restore it afterwards so the cache is “hot” when business gets going the next day. Or maybe you have thought, wouldn’t it be great if developers (and testers) could run their big nasty queries against a restored production database WITH a buffer pool that resembles production so they can get more relevant performance metrics… Perhaps you lament, heck even lowly MySql has this feature – why doesn’t SQL Server have it?
Well, now we do! In this session I am going to do a brief overview of the buffer pool, associated metrics, and Numa etc. (25%). Then the good stuff! I will run through Demo Code that saves the buffer pool state and restores the buffer pool state (75%). I will explain how it works, implications of doing this and other considerations. I think I will call them usp_SaveCleanBuffers and usp_RestoreCleanBuffers… But hey, don’t like the names, no problem! I will be giving the code to all who attend so you can do with (and name) it what you want!
Why I Want to Present This Session:
I have always enjoyed exploring the many ways we all try to squeeze better performance out of SQL Server and wanted to find my own niche to explore. After attending the SQL Saturday in Rochester this year I had this epiphany during a session by Edward Pollack “Insight into the SQL Server Buffer Cache”. I was going to find a way to save and restore the buffer pool! It has been an awe inspiring few months as I have slowly pieced this project together. Now that I have it working I am very excited to share it with the SQL Server community!