A primer on SQL Server database files

by Tim Chapman | Jul 24, 2006 7:00:00 AM

Tags: Storage, Databases, RAID, Tim Chapman, database, Microsoft SQL Server, transaction log, SQL Server Newsletter

Takeaway: Proper placement of your SQL Server database files is vital to good database performance. In this article, Tim Chapman discusses some of the details that can lead you on the right path towards SQL Server database file bliss.

A high-performance database system is the product of good database design, both physical and logical. To get the most out of your database system design, you must have a solid understanding of the files the database server uses, and the types of hardware you should deploy these files on.

The SQL Server database engine uses two types of files: data and log. Here is a quick rundown of both types:

Data files house the internal data the engine needs, along with any user defined data you place in the database. The database engine has a default data file, and you may add additional data files for storage or organizational reasons.
Log files are used to store any modifications made to data, and is heavily used in the process of committing and rolling back changes in the database. Because the log files are "write-only" (unless you are using transactional replication), it is important to place these files on a high-performing disk drive. A RAID 1+0 drive array will typically provide the best performance for this situation.

Where are your database files?

The placement of your database files is crucial to good SQL Server performance. When installing SQL Server, a default path is given to place your data and transaction log files. As a general rule, you will never want to use this path.

Weekly SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

To find out where your data and transaction log files are for a given database, run the following query:

SELECT * FROM sysfiles

If possible, you will want to place your data and transaction log files on a RAID disk array that will provide data redundancy so that, in the event of a disk failure, your data will not be lost. RAID arrays provide redundancy through data striping, mirroring, or a combination of the two. If a disk array is not available, you will want to place your data files and transaction log files on separate physical drives. The reason for this is that, if the data files and transaction log files share the same disk, you will not be able to recover the database from restoring the transaction log if the drive fails. Also, if the same drive is used to house both file types, your system IO will suffer because of the large amount of read/write contention between the two file types. Data files are constantly written to and read from in your OLTP environment, while transaction log files are typically only written to, and in a sequential fashion.

Other considerations

The placement of the data and transaction log files of your database system is certainly a critical design decision. However, many times it is the small things that are overlooked that can get you into trouble.

For example, I have encountered situations where the files for the system were very thoroughly thought out and properly placed. However, the drives the files were placed on were too small for the needs of the company. When the drives fill and transactions in your system begin to fail, if you are not aware that your drives are full, then you could be in big trouble.

When it comes to data file placement, you need to thoroughly consider where you are putting your files, as well as what kind of space you have available for your files. The more scenarios you consider as points of failure in your deployment, the better your chances of success.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

7 comment(s)