Application of SSD in SQLServer

I. first, review the read and write features of SSD (1) Limited write times; (2) best random read performance; (3) Good sequential read performance; (4) Poor sequential write performance; (5) the random write performance is the worst. In terms of storage, different content of SQLServer has its own unique personality. The following lists TempDB, Data, indexes, and transaction logs respectively.

I. first, review the read and write features of SSD (1) Limited write times; (2) best random read performance; (3) Good sequential read performance; (4) Poor sequential write performance; (5) the random write performance is the worst. In terms of storage, different content of SQLServer has its own unique personality. The following lists TempDB, Data, indexes, and transaction logs respectively.

I. First, let's review the read and write features of SSD.

(1) Limited write times;
(2) The best random read performance;
(3) Good sequential read performance;
(4) Poor sequential write performance;
(5) the random write performance is the worst.
In terms of storage, different content of SQLServer has its own unique personality.

The following describes the features of TempDB, Data, indexing, and transaction logs, and analyzes whether they are suitable and how to use the quick access media.

Ii. How to Use Quick storage for SQLServer Databases

1. TempDB

Features:

(1) A large number of sequential writes and random writes;
Analysis: the features of a large number of write operations on TempDB may make SSD quickly worn out. It is not good news for SSD with a limited number of Erasable writes;
(2) improving the read/write performance of TempDB can greatly improve the running efficiency of the entire database;
Analysis: TempDB requires high-speed storage media.
(3) data security of TempDB is not required. Data is automatically cleared after restart;
Analysis: Because you do not need to consider the solidification and security of your data, you can choose a medium with fast read/write but no limit on the number of times;
Example: RamDisk (simulate disk with memory)

Policy:

(1) Consider other high-speed storage media, such as memory, with the help of RamDisk technology;
(2) If SSD is used, it is recommended that:
A) use an SSD separately for storage. Even if the disk fails, you can change the disk, modify the storage address, and restart SQLS to provide services;
B) TempDB file splitting and try to use the SSD concurrency features
Total number of CPU cores <8 files equals to the total number of CPU cores;
Total number of CPU cores> = 8 files equals 8;

2. Data and Indexes

Features:

(1) sequential write
(2) random read
Analysis: benefits of SSD

Policy:

(1) SSD is recommended;
(2) Use a file group to separate data from indexes;
(3) create multiple file groups and partition data (also want to make full use of ssd I/O concurrency)

3. Transaction Log

Features:

(1) discrete continuous write;
(2) A database can have only one log file;
(3) high security requirements;
Analysis: although it is a discrete continuous write, it is actually a continuous write because of the existence of buffer. Strengths of continuous writing HDD;

Policy:

(1) HDD is recommended for transaction logs.
(2) If the transaction log write operation has a bottleneck, consider using SSD, but make sure that:
A) use an enterprise-level SSD for raid 10;
B) Select an SSD with a write cache and a power-off protection circuit;

Iii. Recommended Architecture

After all, what is stored in the database is important information. Some people may worry that What should I do if the SSD data disk fails in case of bad character?

We recommend that you use read/write splitting. Use SSD in the read database. This ensures data security and effectively utilizes the efficient discrete read feature of SSD.