How to configure storage and memory for database servers

As server hardware becomes more and more powerful and prices fall sharply, many companies (especially small companies) find that there are many options to buy database servers. This means that less experienced database administrators are also required to design more and more powerful systems. When you design a database system for a large system, you can buy a large database server with many hard disks and sufficient memory. The following are some basic principles you should follow when designing the system.

Storage System

The most common mistake when designing a disk array is to calculate only the required idle capacity. The idle capacity is only part of the design of the storage subsystem. The other part is the number of input/output operations that the storage system needs to support.

A basic principle that should be followed is that raid 10 arrays are recommended for databases with frequent write operations, while RAID 5 arrays are usually recommended for databases with frequent read operations. The reason is that if data is written to the RAID 5 array, the performance will be affected. Since data is written to the RAID 5 array, the storage system must calculate the parity check bit before writing data, and it takes a long time to calculate the parity check bit, this means that the performance written to the RAID 5 array is reduced.

Due to this performance impact, we always recommend that you put transaction logs on the raid 10 array. Transaction logs are files with frequent write operations, regardless of whether the database is dominated by read operations or write operations. The tempdb database should also be placed on the raid 10 array, specifically, on another raid 10 array different from the array where the transaction log file is located.

When partitioning each disk array, ensure that the partitions are correctly aligned. By default, partitions are not correctly aligned for Windows 2003 and earlier versions, which leads to unsatisfactory performance of the disk subsystem. You can solve this problem by using the diskpart.exe utility (diskpar.exe in Windows 2000. The alignment offset of each created partition is 64kb. By default, the alignment offset of each created partition is 32kb. By default, Windows 2008 creates a partition whose alignment offset is kb.

Physical Database Construction

A relatively new technology that Microsoft has recently recommended is that each core of two to four CPU cores should have a physical database file. This should be done for each file group in the database.

If your server has two quad-core CPUs, there are eight cores in total. Assume that the database has two file groups: Data and indexes. Each file group should have two to four physical files. This technology allows SQL Server to optimize disk input/output. If possible, you should try to distribute files so that as few files are located on each storage array as possible.

The configuration of the tempdb database should be a bit different. When configuring the tempdb database, we recommend that you have a physical file for each CPU core. In this way, the system can speed up input/output operations for the tempdb database as much as possible. Like the user database, as few files should be placed on each disk array as possible.

You should always have at least two file groups in the database. The first file group includes tables, and the second group includes indexes. You need to place them in different file groups. When you query indexes, the operations that are loaded to the table will not be affected, and vice versa.

System memory

In the past, it was quite common to purchase database servers with only several GB of memory. That's because the memory is expensive.

Nowadays, the memory price is quite cheap; as long as you can afford it, you should purchase as much memory as possible. The more memory, the faster the database runs. The exception is that if your installed memory exceeds the size of the database. For example, if you have a 3 GB database with 8 GB of memory installed, adding more memory to the server does not help improve the database performance, because SQL Server may have loaded the entire database into the memory.

When deciding how much memory is allocated to SQL Server, never let SQL server allocate all the memory to it. Because Windows operating systems require memory to run, any software installed on the database server also requires memory to run, such as backup software and anti-virus software. We recommend that you set aside one or two GB of memory for the operating system and other software. The memory size depends on the software installed.

Because no two database servers are the same, there is no clear principle to define what your hardware solution should look like. You have many options. To design a reliable solution that can be used for many years in the future, you must understand your database requirements and the hardware you use, understand the steps in which these requirements are met, so that neither hardware with too low configuration for the present nor hardware with too high configuration for the next year are purchased.