使用Diskspd测试SQL Server存储子系统

In this article, we will learn how to test our storage subsystems performance using Diskspd. The storage subsystem is one of the key performance factors for SQL Server because SQL Server storage engine stores database objects, tables, and indexes on the physical files. Therefore, the storage engine always interacts with the disk subsystem because of data processing. In this context, when a bottleneck occurs on the storage subsystems, it causes a negative impact on SQL Server performance. It would be the right approach to measure the performance of the disks to be used before the SQL Server installation based on their usage purposes. For example, OLTP databases have to complete delete, insert, and update processes in a short time but OLAP databases handle a huge amount of batch data. In this case, the storage requirement of these two database systems should differ from each other. In short, it is a best practice to test and analyze the performance of the storage subsystems according to their usage purposes so that we can eliminate the I/O problems in advance.

在本文中，我们将学习如何使用Diskspd测试存储子系统的性能。 存储子系统是SQL Server的关键性能因素之一，因为SQL Server存储引擎将数据库对象，表和索引存储在物理文件上。 因此，由于数据处理，存储引擎始终与磁盘子系统交互。 在这种情况下，当存储子系统上出现瓶颈时，它将对SQL Server性能产生负面影响。 根据安装用途，在安装SQL Server之前评估要使用的磁盘的性能是正确的方法。 例如，OLTP数据库必须在短时间内完成删除，插入和更新过程，但是OLAP数据库处理大量的批处理数据。 在这种情况下，这两个数据库系统的存储要求应该彼此不同。 简而言之，最佳实践是根据其用途来测试和分析存储子系统的性能，以便我们可以提前消除I / O问题。

Firstly, we will shortly look into the essential disk measurement metrics before going into details about the Diskspd.

首先，在介绍有关Diskspd的详细信息之前，我们将很快研究基本的磁盘度量指标。

基本概念 (Basic Concepts)

IOPS (input/output operations per second) indicates how many operations are completed by the disk per second and this indicator is used to evaluate the performance of the storage. The IOPS is calculated by counting the transactions performed by the disk in one second and the amount of the data is not considered for this measurement method. So, only using this metric to decide the performance of the storage will be improper.

IOPS （每秒输入/输出操作）指示磁盘每秒完成多少操作，并且该指示器用于评估存储的性能。 IOPS是通过对磁盘在一秒钟内执行的事务进行计数来计算的，因此该测量方法不考虑数据量。 因此，仅使用此指标来确定存储性能将是不正确的。

Throughput indicates how much data can be transferred by the storage unit in a second. In general, it is measured in megabytes per second (MB/sec).

吞吐量指示存储单元在一秒钟内可以传输多少数据。 通常，以兆字节每秒（MB / sec）为单位进行度量。

Latency is a measurement of the response time of a storage device to the request it receives and its unit is the millisecond. Particularly for databases, high latency negatively affects performance. For example, the log files of the OLTP databases that are planned to receive high modification request should be placed on the disks which have low latency.

延迟是存储设备对接收到的请求的响应时间的度量，单位是毫秒。 特别是对于数据库，高延迟会对性能产生负面影响。 例如，应将计划接收高修改请求的OLTP数据库的日志文件放在具有低延迟的磁盘上。

什么是Diskspd？ (What is Diskspd?)

The DiskSpd is a disk testing tool and it is used to measure and report the capability of the disk through generating various types of fake workloads. There are various disk performance benchmark tools (IOmeter, ATTO, HDTune, etc.) but this performance testing tool offers more realistic scenarios and reliable resul