Optimal Database Setup Hardware Guide

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More .

With server hardware getting more and more powerful, and prices dropping just as dramatically, companies, especially smaller ones, are seeing their options open up when it comes to buying database servers. This means that database administrators who are newer in the field are being asked to design increasingly powerful systems.

When designing database systems for a large system you are able to purchase large database servers with a large number of hard drives and plenty of RAM. There are some general rules to fallow when designing your system.

Storage
Physical Database Layout
System Memory
Conclusion
RELATED NEWS AND ANALYSIS

Storage

The hard drives of your SQL Server are the easiest place to ensure that you have enough performance as well as the most common place to skimp on resources. The most common mistake that people make when designing the disk arrays is to only calculate the amount of free space that is needed. The amount of space is only one part of what the storage subsystem needs to be designed for. The second part is the amount of IO operations that the storage needs to be able to support.

One general rule to follow is that a high write database is going to be best served by a RAID 10 array. A high read database is typically going to be best served by a RAID 5 array. The reason for this is that there is a performance hit when writing data to a RAID 5 array. This is because when writing to a RAID 5 array, the storage must calculate a parity bit before writing the data. It takes a great deal of time to calculate this parity bit and this time translates into lower write performance to the RAID 5 array.

Because of this performance, it is always recommended to put your transaction logs on a RAID 10 array. Transaction logs are always high write files no matter if the database is a mostly read database or mostly write database. The tempdb database should also be placed on a RAID 10 array, specifically on a separate RAID 10 array from the transaction log files.

When each of your disk arrays is partitioned you should ensure that the partitions are correctly aligned. By default, Windows 2003 and below incorrectly aligns partitions, which causes suboptimal performance of the disk subsystems. This can be corrected by creating the partitions using the diskpart.exe utility (diskpar.exe in Windows 2000). Each of the partitions should be created with an alignment of 64kb where the default when partitions are created is 32kb. Windows 2008 creates its partitions with an alignment of 128kb by default.

Microsoft KB Article 923076 describes diskpart.exe and how to use it.

Physical Database Layout

A newer technique which Microsoft has recently begun recommending is that your databases should have one physical database file for each two to four CPU cores. This should be done for each file group in the database.

If your server has two quad chip CPUs, you have a total of eight cores. We’ll also assume that the database has two file groups one named Data and the other Indexes. Each file group should have two to four physical files. This technique allows the SQL Server to optimize the IO to the disks. Whenever possible you should spread out the files so that as few files as possible are on each storage array.

The tempdb database should be configured a little differently. When configuring the tempdb database, it is recommended that you have one physical file per CPU core in the database. This allows the system to push the most IO to the tempdb database as possible. As with the user databases, you should put as few files as possible per disk array.

You should always have at least two file groups within the database. The first should contain your tables. The second should contain your indexes. You want to have these in separate file groups so that when the indexes are being queried, the load to the tables is not impacted and vice versa.

System Memory

In the past it was quite common to buy a database server with only two or four gigs of RAM installed. This is because the RAM was expensive to purchase.

These days, RAM is quite inexpensive and you should generally buy as much as you can afford.More RAM will almost always make your database run faster. The exception to that is when you have more RAM installed than the database is in size. As an example, if you have a 3 GB database, and you have 8 GB of RAM installed, adding more RAM to the server will not help your database performance since SQL Server can probably already load the entire database into memory.

When deciding how much RAM to allocate to the SQL Server, do not simply allow the SQL Server to have all the memory on the system. The Windows operating system needs memory to run, as does any other software installed on the database server such as your backup software, anti-virus software, etc. It is recommended that you leave between one and two gigs of RAM for the OS and other software to use. This number may be higher or lower depending on what software you have installed.

Conclusion

Because no two database servers are the same, there are no hard and fast rules about what your hardware solution should look like. There are a wide set of choices available to you, and the key to designing a solid solution that you can continue to use for many years into the future is to understand your databases requirements, and understand the hardware which you are using and where these requirements meet so that you aren’t under-purchasing today nor over-purchasing for next year.

This article was first published on EnterpriseITPlanet.com.