SQL Q&A: Disaster Recovery and Database Mirroring

Backups, disaster recovery and database mirroring have endless variations, all suitable for endless scenarios.

Paul S. Randal

Temp Solution

Q. I’ve read a lot of conflicting advice about how many data files on my server I should configure for tempdb to reduce PAGELATCH contention. Can you shed any light on this?

A. You’re correct—there’s a lot of poor advice out there about tempdb configuration. PAGELATCH contention in tempdb comes from workloads where many concurrent connections create and drop small tables. These operations require allocating and de-allocating data file pages in tempdb. This in turn requires exclusive access to in-memory allocation bitmap data file pages (special pages that make note of which data file pages are in use or not).

If there are many concurrent connections trying to concurrently allocate and de-allocate, only one connection can have access to an allocation bitmap at once. This leads to contention and a reduction in performance.

One way to alleviate a bit of this contention is to enable trace flag 1118 (you can learn more about this on my SQLskills.com blog). A more effective way is to create multiple tempdb data files. By creating multiple data files, SQL Server will perform allocations (and de-allocations) round-robin over the data files. This way, the number of allocation bitmaps increases (one or more per data file) and the overall system contention will decrease.

The question is: How many data files should you create? For the longest time, the best advice people could give was that the official Microsoft stance of creating one tempdb data file for each logical processor core (for example, two CPUs with four cores each and hyper-threading enabled equates to eight logical cores) was incorrect. This approach can lead to slow-downs with memory spills on servers with more than eight cores. Another widely held belief was that starting with one-quarter to one-half the number of processor cores was a good start.

Then at the SQL PASS Summit conference in late 2011, Bob Ward from Microsoft product support presented a more elegant formula for determining the number of files you should create. If your server has fewer than eight logical cores, use the number of logical cores as the number of tempdb data files. If your server has more than eight logical cores, start with eight tempdb data files and then add four more at a time if contention continues.

Keep in mind this is generalized advice. There are at least three occasions where servers with 64 cores need 128 tempdb data files—twice the number of cores—to alleviate contention. Your actual mileage will definitely vary.

The Perfect Plan

Q. I recently reviewed our disaster recovery plans and found we’re not making regular backups of our system databases. Do you advise doing this? What’s the worst that could happen if we don’t do it?

A. It’s a good idea to periodically review your disaster recovery plans. It’s even better to practice those plans. One of the things you’d have discovered if you ran through a practice bare-metal restore is that your SQL Server environment wouldn’t have come back to full functionality, because you’d be missing the system databases.

Many DBAs don’t consider the system databases (master, model, msdb and any replication distribution databases) when planning or testing a disaster recovery procedure. This is a big mistake. These databases are critical to your instances of SQL Server. You need to protect these and check their integrity just as much as you do with your user databases.

There’s no point in having your data available if you can’t connect to an instance of SQL Server.

The same is true if you can’t bring the instance into a working state when the master is missing because you don’t have all of the necessary login information. Without a backup of the master, you’re looking at recreating all the login information for all your databases before the applications can come online.

It’s critical to back up the msdb database because it contains all of your SQL Agent jobs (such as  backups and consistency checks), SQL Agent alerts (such as high-severity errors and early warnings that your I/O subsystem is going wrong), your SSIS packages and your backup history tables. If you have any kind of automated system that generates a set of RESTORE statements to facilitate easy database disaster recovery, it’s likely using the backup history tables in msdb to do so. Without a copy of msdb (if the disaster took out your entire I/O subsystem), you’d need to piece together the RESTORE statements by hand, which is tedious work that adds to downtime.

The model database is critical if you’ve come up with a configuration you want to replicate to all new databases. For example, if you have an environment where each hosted client has its own database, you would need the model. Without it, you’d have to set those configuration options again.

Replication distribution databases are critical for reestablishing replication data streams without having to perform time-consuming re-initializations of subscription databases. Overall, you don’t have a disaster recovery strategy unless you’re backing up your system databases as well as your user databases.

To get you started, check out these SQL Server Books Online about system database backup and restore:

Growing, Growing, Growing

Q. We’re having trouble understanding an issue where the transaction log keeps growing, even though we manually shrink it down. We’re committing the work in our inner transactions and performing log backups, so why does the log keep growing?

A. The problem here seems to be your developers are using nested transactions in the code, without realizing that they don’t behave the way they look. An example code flow that illustrates what you’re doing is:

BEGIN TRAN; Do some work … BEGIN TRAN; Do some more work … COMMIT TRAN Continue with more work …

The second BEGIN TRAN, which starts a nested transaction, doesn’t really start a sub-transaction as far as the Storage Engine is concerned. All it does is increment @@TRANCOUNT by 1. There’s nothing written into the transaction log indicating that a new transaction has started. All the work done by the nested transaction is actually part of the initial transaction.

This means when the COMMIT TRAN is issued for the nested transaction, nothing happens except decrementing the @@TRANCOUNT, because there isn’t really a nested transaction. Nothing is committed until the initial transaction commits, bringing @@TRANCOUNT back down to zero. This is why your transaction log is growing. You still have a single, long-running transaction.

Also, you shouldn’t be performing regular transaction log shrink operations. Whenever the transaction log has to grow, the new portion of the log has to be zero-initialized. It’s overwritten with zeroes in what was previously that portion of the NTFS volume. This happens so any subsequent crash recovery operation doesn’t fail (see my SQLskills.com blog for an explanation).

While the new portion of the transaction log is being zero-initialized, all logging activity for that database is paused. Your workload stops momentarily. This pause could be quite long if you’ve set the transaction log auto-growth amount to be quite large.

It’s always better to avoid the transaction log having to auto-grow if at all possible. If the transaction log grows again every time you shrink it, leave it alone. It should be obvious it needs to be larger than the size to which you’re shrinking it.

Mirror Mirror

Q. We’ve just implemented database mirroring and found we can no longer perform index rebuilds for some of our tables. The huge amount of transaction log generated overloads our network and database mirroring slows down. Why does this happen and how can we work around it?

A. This issue is encountered by many who implement database mirroring. It stems from the fact that performance and reliability testing done live with database mirroring doesn’t include the regular database maintenance.

Many people use the bulk-logged recovery model when performing index rebuild operations. This limits the amount of transaction log generated, so the transaction log doesn’t grow during the operation. Database mirroring only allows the full recovery model, where index rebuild operations are fully logged. Then they can generate as much transaction log volume as the size of the index being rebuilt.

The amount of additional transaction log records when performing index rebuilds in the full recovery model might be really large and saturate the network link between the principal and mirrored database servers. If that happens, a SEND queue may build up on the principal database. This might cause transaction processing delays for any concurrent application workload.

What this means is that, for many people, index rebuild operations aren’t possible when using database mirroring. This is true even with the database mirroring log stream compression included with SQL Server 2008 and later versions.

An alternative index maintenance strategy is to use ALTER INDEX … REORGANIZE instead of ALTER INDEX … REBUILD. Reorganizing an index only addresses existing index fragmentation. You can interrupt it without losing already-completed work. An index rebuild, on the other hand, always builds a new index regardless of the extent of fragmentation. If you interrupt it, you get nothing. Everything is rolled back.

For larger indexes that aren’t practical to rebuild, perform the following steps:

  • Day One: Start running ALTER INDEX … REORGANIZE during your maintenance window. Let it run for an hour or so. Kill the command. It won’t roll anything back and you’ll have made some progress in terms of removing fragmentation from the index.
  • Day Two: Start the reorganize again. It doesn’t remember day one, but should quickly traverse the work it did on day one and start removing fragmentation from the next portion of the index. Kill it again after an hour or so.
  • After Day Two: Repeat until the fragmentation level drops below whatever threshold you’ve established, or just continue the day-by-day process indefinitely.

This lets you limit the amount of transaction log generated (and hence transmitted using database mirroring) by your regular index maintenance. If you want to get more advanced, instead of killing the reorganize process after a certain amount of time, you can monitor how much transaction log is generated and kill it once it reaches a certain threshold (see my SQLskills.com blog for more details).

Paul S. Randal

Paul S. Randal is the managing director of SQLskills.com, a Microsoft regional director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. He wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Randal is an expert on disaster recovery, high availability and database maintenance, and is a regular presenter at conferences worldwide. He blogs at SQLskills.com/blogs/paul, and you can find him on Twitter at twitter.com/PaulRandal.