SQL Server 2012’s AlwaysOn functionality rightly deserves to ‘steal the show’ in terms of all of the press and attention it gets when people learn about what’s new with SQL Server 2012 – because it IS a powerful set of features that allow DBAs to address both HA and DR situations with a SINGLE solution in a wide variety of highly flexible ways.
But AlwaysOn isn’t the only cool new thing that debuts with SQL Server 2012. For example, I recently outlined a number of developer-related features and improvements for SQL Server 2012 in Dev Pro magazine.
Then, among a number of other improvements or benefits that SQL Server 2012 brings to the table, there are a few hidden and almost overlooked gems – such as SQL Server 2012’s new support for a feature known as indirect checkpoints.
An Overly-Simplistic Overview of Checkpoints
Because of the way SQL Server works (i.e., how it doesn’t actually ‘write’ changes from INSERT, UPDATE, DELETE and other similar operations immediately to your .mdf/.ndf files and because it, instead, writes those changes directly to the log and then just makes changes to memory), it would be possible for the number of changes in memory (and in the logs) to slowly ‘overwhelm’ the process of recovery – meaning that IF SQL Server were to crash or be shut down with too many changes listed ONLY in memory and in the log, then when the server rebooted or re-started after a crash, it would conceptually have to spend way too much time ‘reconciling’ changes found only in the logs and not on disk (i.e. in .mdf/.ndf or data files). As such, the CHECKPOINT process periodically spins up and is basically used by SQL Server to ask itself: “If I crashed right this second, how LONG would it take me to go through all the transactions in the transaction log (across all databases) and roll-forward any transactions that completed as well as roll-back any transactions that didn’t complete?”
And, by default, if the answer is that it would take greater than 1 minute (for this process to occur across all databases), then SQL Server will begin the process of reconciling changes in memory (and the log) with your databases’ .mdf/.ndf data files. And, in this process, SQL Server effectively just takes ‘dirty pages’ in memory and writes them out to disk – such that committed transactions end up not only being durable (since the transaction log ensured that already), but persisted or reconciled to disk in your data files – meaning that the changes you and your applications have made to your data end up being ‘saved’ into your data files.
And, again, that’s an obscenely overly-simplified perspective of what’s going on. (Because I’m skipping lots of details including the fact that OTHER things can force dirty-pages to be written to disk (such as memory problems) and so on.) For more information – or for a better overview of how this all works, check out my free SQL Server video on SQL Server Logging Essentials.
Managing the CHECKPOINT Interval Prior to SQL Server 2012
The key take-away from knowing how logging and checkpoints work is to note that there’s a default checkpoint interval – which is set to 1 minute. Prior to SQL Server 2012, you could change this value – using sp_configure to toggle the ‘recovery interval’ setting. (Note too that you can actually manually kick-off the checkpoint process by issuing the T-SQL CHECKPOINT command – though, as books online calls out, you don’t want to just ‘willy-nilly’ play around with this without knowing what you’re doing.)
Only, prior the problem with that option in versions of SQL Server prior to SQL Server 2012 is that you could only set this value at the server-level (meaning that whatever value you set was going to be applied not ONLY across all databases, BUT as a function of the total recovery interval target for all databases). That, and the minimum value was/is 1 minute – meaning that all you could do prior to SQL Server 2012 was set this to a WIDER or longer time-interval than 1 minute.
So, the translation here is that whenever SQL Server fails/crashes/shuts-down, unless it is shutdown gracefully (using the SHUTDOWN command – where SQL Server will reconcile logs/databases), it’ll have to start the recovery process each time it restarts. And, that recovery process has to be run against all databases. To see what this means or looks like, just take a look at your SQL Server logs and look at what takes place during startup – within the first minute of so of what’s going on on the server and you’ll see SQL Server working through the RECOVERY process for all of your databases – as per the screenshot below:
Confession: One of my absolute favorite things about SQL Server is how insanely well it handles this process of keeping the CHECKPOINT process perfectly timed such that the recovery process can take place in roughly that 1 minute interval specified by default. In other words, SQL Server’s ability to pull this off is an amazing feat of engineering – and on literally hundreds of servers that I’ve been able to work with in my career as a DBA and SQL Server consultant, I’ve always made it a point of checking this value by looking at the logs. And in performing this check, the ‘Recovery is complete’ message almost always shows up in the error logs within 1 minute of startup – just like clock-work (over and over and over again on virtually every server I’ve ever worked with – with only a few exceptions here and there).
Failure, Failover, and STARTUP without INDIRECT CHECKPOINTs
So, what’s the point of this recap on checkpoints, recovery, and so on?
Nothing more than to call out that whenever SQL Server has to spin up, it’s going to incur that overhead/time associated with running the recovery process. And obviously that’s going to apply when a single SQL Server running in ‘isolation’ crashes, powers-off, or whatever and then spins back up.
But it also applies in cases where a SQL Server might be clustered or when a mission-critical database might be mirrored.
And so what this all means is that if you’ve got a mirrored or clustered database, then even if that ‘failover’ process is magical (and it is – when configured correctly), and speedy (and it is – when configured correctly), then you’re still going to have to incur the cost/time associated with the recovery process.
So, for example, if you’ve got a 2-node cluster, and node A is running along just fine and dandy, every few seconds the CHECKPOINT process is kicking in, and making sure that recovery won’t take > 1 minute. But if node A crashes or goes down, then the failover process is initiated – where node B detects that node A has gone down and then works with the Domain Controller to quickly take control of the virtual IP/machine-name associated with your cluster. And then begins the process of accessing the shared drives where your databases’ .mdf and .ldf files live.
And, at that point, node B finds what effectively amounts to a ‘crashed’ system – where the databases weren’t reconciled prior to shutdown. So, it has to execute the recovery process (meaning that all in-flight transactions that weren’t completed are rolled back/out of the system, and all fully completed transactions are correctly ensured in your .mdf/.ndf files so that no committed changes were lost). And, of course, that process takes roughly 1 minute (unless you’ve configured SQL Server to let it know that you’d like it to take longer).
And the same thing goes, effectively, with mirroring.
Likewise, this same recovery interval will also be incurred when AlwaysOn Failover Groups failover – because they’re required to walk through the same recovery process as well.
INDIRECT CHECKPOINTS
With SQL Server 2012, we finally get a new option as DBAs to address this recovery interval – and to tweak and toggle it.
Best of all, the option comes to us with the ability to toggle/tweak on a database-by-database basis. Which, frankly, is fantastic – because not all databases are created equal. So, in this sense, with SQL Server 2012’s new INDIRECT CHECKPOINT feature, DBAs can actually
- set checkpoint intervals in seconds – rather than in less-granular minute-only options.
- set checkpoint intervals LOWER than 1 minute if desired.
- make these changes on a database-by-database basis
Translation: If up-time is essential for, say, a single database on a clustered instance, you can issue an ALTER DATABASE command and SET the TARGET_RECOVERY_TIME to say, 30 seconds in stead of 1 minute. And, in such a case, if such a change is made, then the expectation is that this database will be able to recover within 30 seconds after a restart or after a failover of a cluster-node, mirrored instance, or even of an AlwaysOn Availability Group – whereas other databases (which haven’t had this value set), will still default to their 1 minute recovery time.
Now, obviously, this new feature is quite powerful in terms of helping DBAs achieve better RPOs and RTOs – by means of helping them dramatically decrease recovery times. Best of all, while AlwaysOn IS an Enterprise Edition only feature of SQL Server 2012, Indirect Checkpoints are available on all SKUs of SQL Server 2012 – meaning that you can pick up this benefit if you’re using mirroring, traditional clusters, or just want to boost recovery time on a stand-alone box.
But, as you’re probably thinking to yourself: yes, this does come with a cost – in the sense that you’re now forcing SQL Server to work just a bit harder to keep logged/in-memory changes more readily synchronized with your data files in order to decrease recovery times.
As such, IF this is a feature you’re interesting in using, make sure you follow the precautions outlined in SQL Server 2012 Books Online (check this link and the section on ‘Impact of Recovery Interval on Recovery Performance’) in terms of making sure that you VERY carefully ‘ease’ your way into using this feature – rather than risking overloading your servers in trying to keep up with too high of a demand being placed upon the checkpoint/reconciliation process. (That, and be aware that if you’ve got large numbers of long-running transactions in your system, then this feature might not provide you with the benefits you’re hoping for.)
