SQLServerPedia

In the last post in this series, we investigated common problems with storage configuration. A closely related topic is that of data file management, something I’ll be focusing on in this post.

A well known attribute of SQL Server is its ease of use. In contrast with other database management systems, it’s a piece of cake to get up and running, and as many of you would agree, that’s not always a good thing.

SQL Server is designed with good out-of-the-box settings that strengthen security, reduce administration overhead and maximize performance, however, when it comes to individual databases, there’s a number of recommended configuration steps that SQL Server doesn’t perform, in large part due to dependencies on disk configuration and unknown future usage of the databases. In this post, we’ll concentrate on three such settings; autogrowth, file size and the recovery model.

Relying on Autogrowth

When a database is created using the default settings, it’s sized as per the model database, which by default has a 3MB data file set to autogrow in 1MB increments, and a 1MB log file with 10% autogrowth. Autogrow events occur when the files fill to capacity and automatically grow, therefore avoiding “out of space” errors. For very small test or development databases, that may be ok, but what about a large production database that’s growing rapidly? In such a case, the database would be constantly auto-growing every few seconds (or less). So what’s the problem?

Fragmentation; If a database’s files are sized up front for the expected growth over the medium term (let’s say 12 months), the disk space is allocated in one large chunk. In contrast, files that are constantly filling and growing have space allocated in small chunks from whatever disk space is free at the time. The end result is high levels of file fragmentation, a particularly nasty problem for the transaction log file.

Performance; There’s a performance overhead involved in each file expansion. You don’t need to be the sharpest tool in the shed to understand that a file that’s constantly auto-growing will incur a much larger performance overhead compared to one that’s appropriately sized for the expected growth. Instant File Initialization helps in this regard (and should be enabled in almost all cases), but can’t be used for the transaction log, which is arguably the most important file from a performance perspective.

Associated best practice; Autogrowth should not be used as a substitute for good capacity planning and proactive database maintenance. Configuring files with the autogrowth property is fine in handling unexpected surges in growth, so long as the files are appropriately sized in advance, and appropriate monitoring and alerting is in place for disk usage and autogrowth events.

Failing to presize tempdb

Those that rely on auto-growth for databases almost always fail to pre-size tempdb. The unique thing about the tempdb database is that when SQL Server is restarted, tempdb is recreated with the original file sizes. As an example, let’s say that over the course of normal production activity, the tempdb database grows to 20GB. When SQL Server is restarted, the tempdb database will be reset back to the defined size and growth increments, which by default is 8MB with 10% autogrowth. Such a setting would require close to 100 autogrowth events to return the file to the required operating size, bringing with it the corresponding performance and fragmentation overhead. In such cases, the classic outcome, particularly for databases with a heavy reliance on tempdb, is sluggish performance for a period of time after SQL Server restarts.

Associated best practice; Pre-size the tempdb database after observing the production system that’s been up and running for long enough to cover the full range of activities (index rebuilds, dbcc checks, application activity, reporting etc …) All of these things use tempdb, some more than others, and the correct value to use is therefore site specific. Of course, before the system goes into production, an educated guess is required, but a value greater than the default 8MB may be a good place to start!

Full recovery model with no transaction log backups

In the first post in this series we covered backup worst practices, the first of which was not taking backups. One of the less than obvious variations of this practice is not taking transaction log backups on the assumption that a full database backup will “take care of it”. It won’t. A database in the full recovery model (the default for the Standard and Enterprise editions of SQL Server) will retain transactions in the transaction log until an explicit transaction log backup is performed. If transaction log backups are never taken (frighteningly common), the log continues to grow forever.

The classic outcome of situations such as the one just presented are databases with a 20MB data file and a 900GB transaction log file. In most of these cases, the disk eventually fills to capacity and the system falls over with 9002 errors (out of disk space). What happens next is usually a combination of panic-induced hysteria and logic-defying feats of ignorance.

Among many other critical functions, the transaction log is used to ensure the integrity of the database by rolling forward committed transactions and rolling back uncommitted ones. Such a process is performed when the SQL Server instance is restarted. Let’s take the case of the full disk scenario presented earlier; someone notices the 900GB transaction log file and, in a moment of clarity, decides it’s not really required, so they delete it (after stopping SQL Server to remove the file lock). When SQL Server restarts, the usual roll forward/back process cannot happen, given the absence of the transaction log, therefore resulting in a potentially (probably) corrupt database containing half completed transactions.

Associated best practice; Backup the transaction log frequently, or use the simple recovery model if point in time restore is not required (and data loss since the last full backup is acceptable.)

In the next post in this series, we’ll examine indexing worst practices.