Configuring Database Files for Optimal Perfomance

From SQLServerPedia

Jump to: navigation, search

New article in wiki code:

Contents

Breaking Up SQL Server Databases into Multiple Files

File access is fundamental and essential consideration to an OLTP system. If your system is busy (it gets a high number of transactions), or you expect it will be in the future, put in a little planning to prevent wait times at the disk.

Here we will discuss file placement, adding data and log files, and how to determine if your system is experiencing disk contention. For even more information, check out the Recommended Reading links at the bottom of this article.

Intro to DB Files

First, a quick primer on DB files: A SQL Server database consists of one or more data files, and one or more log files.

  • Data files hold all database objects, like tables, indexes, and stored procedures. The primary data file is usually named filename.MDF. If the database has additional data files, they are typically named with the NDF extension.
  • Log files make up the transaction log, where each database transaction gets written before it is written to the data file. The SQL engine also makes extensive use of the log file (for example, for page splits). Log files typically have an LDF extension. Generally speaking, you only need one log file per database.

Rule 1: Separate Log Files

The first rule of an OLTP database is to store the data and log files on different physical disk structures, whether that means fixed disks or arrays in a SAN. SQL Server writes each transaction to the log before it writes it to the data file. If both data and log are on the same disk, this means a lot of jumping back and forth between the two files, and extra time taken up. But if the log is on its own disk, it has a dedicated read/write head to write the transactions, uninterrupted by data file writes.

It’s also important to keep your log files separated from a disaster recovery perspective. If the disk that houses your data files, fails, you’ll be glad to have the transaction log accessible on another disk. That way you can back up the tail of the log, and be able to recover to the point of failure.

Even with all of that, there are circumstances where this doesn’t hold true. For example, if you have a reporting database that gets wiped and reloaded every night, you don’t particularly care about recoverability; and, most (if not all) of your transactions will be read only, which doesn’t affect the transaction log. Another case might be a well protected SAN, where you’re far less likely to lose an entire array. But most of the time, rule #1 holds true: separate out your log files.

Using Filegroups and Secondary Data Files

First let’s look at the code to create a database with multiple data and log files: 

CREATE DATABASE DB1 ON 
Primary 
  (Name = DB1, FILENAME = 'd:\SQL\DB1.mdf'),
  (Name = DB1a, FILENAME = 'e:\SQL\DB1a.ndf')
LOG ON 
  (Name = DB1Log, FILENAME = 'f:\SQL\DB1log.ldf'),
  (Name = DB1, FILENAME = 'g:\SQL\DB1log2.ldf')

If we’re looking to gain room to grow, this is certainly a good way to make use of additional disk space. And, when the database is spread out across multiple disks, there’s not just one, but several read/write heads on that can access the data simultaneously.

But we need to be smart about adding files, or the gain could go largely unrealized…some disks might be writing furiously to a set of highly accessed tables, while other disks sit nearly idle. How do we maximize the benefits of multiple data files?

Note: Space is really the only reason to have multiple log files. SQL writes to a log file sequentially, so you don’t get the benefit of spreading the log across multiple disks.

What the database needs is to separate heavily used tables or indexes onto a different physical disk structure. And for that, we need filegroups. Filegroups are just a way to organize database objects. When a database is created, it has a default primary filegroup for all the database objects. You can also create the database with multiple filegroups, like this: 

create database DB1 ON 
Primary 
    (Name = DB1, FILENAME = 'd:\SQL\DB1.mdf'),
FILEGROUP BusyTables
    (Name = DB1a, FILENAME = 'e:\SQL\DB1a.ndf')
LOG ON 
    (Name = DB1Log, FILENAME = 'f:\SQL\DB1log.ldf'),
    (Name = DB1, FILENAME = 'g:\SQL\DB1log2.ldf')

You can also add filegroups, and data files, to existing databases: 

--  Add data file 
ALTER DATABASE DB1 ADD FILE (NAME = DB1, FILENAME = 'd:\SQL\DB1.ndf', SIZE=100MB, MAXSIZE=500GB, FILEGROWTH=50);
-- Add filegroup
ALTER DATABASE DB1 ADD FILEGROUP BusyTables

Once you have your filegroup, you can create and move objects to the new filegroup, and to the default primary filegroup. Separate out highly used tables from lesser used tables, into a filegroup on its own disk. Or again, separate your indexes from your tables.

Additionally, files and filegroups enable data placement, because a table can be created in a specific filegroup. This improves performance, because all I/O for a specific table can be directed at a specific disk. For example, a heavily used table can be put on one file in one filegroup, located on one disk, and the other less heavily accessed tables in the database can be put on the other files in another filegroup, located on a second disk.

So apart from these guidelines, how do you know that your system needs these techniques? We’ll have to look at some actual numbers.

Monitoring for Disk Bottlenecks

Adding files is not an exact science, but there are some good measures to help in your decision making. Monitor your disks using the Current Disk Queue Length and % Disk Time counters. If these values are consistently high, your system will benefit from a well-thought-out plan to add of files and filegroups and move high traffic objects.

TempDB Database Configuration

TempDB is critical to performance; many, many user and system actions make use of tempDB, like cursors, temp tables, hash table for sorts, reindexing, and so on. Take care of TempDB before you even separate out your OLTP data and log files.

A good rule of thumb is to have one TempDB data file per physical core. For example, if your server has two quad-core processors, you’d want eight TempDB files (one .mdf file and seven .ndf files)...But, that's only a rule of thumb. In this post, Paul Randal discusses how to monitor your system and come up with a much better idea of what is actually needed. TempDB Files Per Core

Remember that you really only need one log file – log files are written sequentially - but especially for TempDB, that log file should be on its very own personal disk.

Note: The TempDB files should definitely not be on the system (C) drive. If TempDB grows out of control, perhaps due to a large sort query, the system can run out of hard drive space and fail to start.

The following script will move TempDB from its current location to a folder on the T drive. Change the drive letter and folder location to suit your system. The script only uses a 1gb file size because of an odd behavior in SQL Server that checks the current file location to see if there's enough space - instead of checking the new file location. If the user specifies a 100gb TempDB data file on the T drive (which does have 110gb of free space), SQL Server checks the current location (C) for 100gb of free space. If that space doesn't exist, the script will fail. Therefore, use a small 1gb file size first, then after SQL Server restarts, alter the file to be the full desired size. 

use master
go
alter database tempdb modify file (name='tempdev', filename='T:\MSSQL\DATA\tempDB.MDF', size = 1gb)
go
alter database tempdb modify file (name='templog', filename='T:\MSSQL\LOGS\templog.LDF', size = 1gb)
go

Then restart SQL Server for the changes to take effect.

Configuring Partitioned Databases

SQL Server 2005 introduced the concept of partitioning: splitting tables and indexes up into multiple partitions, with different sets of data going into different filegroups.

For example, a data warehouse's 500-million-row sales table might be partitioned by year in order to keep the most recent data on faster, more expensive hard drives. On the other hand, it might be partitioned by state in order to facilitate faster data loads: data could be loaded by section of the country.

Partitioning is outside of the scope of this article, but we're mentioning it here because it affects file configuration. Each partition is usually stored in its own filegroup. For more information about how to configure partitioned databases, read through the Partitioning articles.

Author Credits

Jen McCown

This wiki article was adapted from the original SQLServerPedia post, by Jennifer McCown.

Jen McCown is a SQL Server developer and DBA with over 10 years experience. She writes book and product reviews for ITBookworm.com, makes training videos and blogs for MidnightDBA.com, and maintains both websites.

Her online presence includes:

Recommended Reading

For more information, see:

Retrieved from "http://sqlserverpedia.com/wiki/Configuring_Database_Files_for_Optimal_Perfomance"