SQL Server：模拟 SQL Server 中的长时间 I/O

Question

我正在阅读关于SQL Server中I/O延迟和错误消息833的内容。我该如何模拟长时间的I/O场景，以便在日志中获取此错误消息？请有人帮忙吗？

- Jean

Answer 1

您可以尝试以下类似的操作，这将在SQL日志和数据驱动器上产生大量IO负载。

1. Create simple [TestTable] with couple of fields

2. Insert the rows into above table continuously by any of the following code

   INSERT INTO [TestTable] VALUES ('Sample data')
   GO 10000
   

插入10,000条记录后，上述查询执行完成。 或者

WHILE (1=1)
BEGIN
    INSERT INTO [TestTable] VALUES ('Sample data')
END
GO

此查询将一直运行，直到显式停止。

- Rao Y

---

很酷，我不知道你可以这样做。显然智能感应也不行 :P - HoneyBadger

建议测试的问题在于实际的I/O操作非常小，我会天真地说它只会在db文件中写入1页（8k）和日志中的另一页。（+一些开销；+一些索引（如果有的话）+...）。您可能希望尝试在一个操作中插入更大的数据集以实际对系统施加一些负载，然后并行执行这些操作。像Steve Ford建议的SQLIO这样的工具将为您自动化这种测试。 - deroby

Answer 2

使用仅 T-SQL 可能很难在 SQL 错误日志中获取 833 错误消息，如果您的 I/O 子系统大小适当。但是，如果您使用 SQLIO（https://www.microsoft.com/en-us/download/details.aspx?id=20163）或 SQLIOSIM（在 SQL 安装介质上）压力测试 I/O 子系统并同时运行 I/O 密集型查询，则可能会生成该错误。

以下是一个 T-SQL 脚本，用于创建和加载测试表以及进行 I/O 密集型查询。如果需要，可以在不同的 SSMS 窗口中运行多个 SELECT 循环实例。

--create test table
CREATE TABLE dbo.TestTable(
      Col1 nchar(4000) NOT NULL
    , Col2 nvarchar(MAX) NOT NULL
    );

--load 10000 rows (about 2.8GB)
WITH 
    t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;
GO

--run query in loop (expect parallel execution plan with many read-ahead and LOB page reads)
SET NOCOUNT ON;
DECLARE @RowCount int, @Iteration int = 1;
WHILE @Iteration <= 100
BEGIN
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    SELECT @RowCount = COUNT(*) FROM dbo.TestTable WHERE Col2 LIKE 'X%';
    RAISERROR('Iteration %d completed',0,1,@Iteration) WITH NOWAIT; --display progress message
    SET @Iteration += 1;
END;
GO

编辑： 我发现了微软开发的另一个I/O测试工具DiskSpd，并且能够在我的测试系统上与上述T-SQL脚本一起可靠地产生错误。这个开源工具可以从https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223免费下载。DiskSpd具有与SQL相同的功能，但使用起来更加简单，文档也更加完善，并且具有生成XML输出以便于分析的选项。

我用于测试的参数如下。在SQL Server数据文件也在D驱动器上的情况下，30秒后就会产生错误。

diskspd.exe" -h -d60 -c1G -F32 -w50 -r -b8K -o1000 -L "D:\DiskSpd.dat"

- Dan Guzman

---

谢谢你的帮助。但是，我发现在“WITH”下面的部分（即从“--load 10000 rows (about 2.8GB)”到“GO”）很难理解你在做什么。你能否请解释一下？抱歉给你添麻烦了。我不明白：1）t(n)是什么，2）你通过“cross join”在做什么？ - Jean

@Jean，WITH 子句下定义的查询是常用表达式（CTE），可以通过名称引用和重复使用。我在这里使用它们来创建 100000 行测试表。t4 CTE 返回一个 4 行单列表，t256 CTE 交叉连接到 t4 四次以生成 256 行（444*4），而 tt6M CTE 类似地连接到 t256 以生成 16M 行，作为 INSERT 的源。t(n) 指定行构造器字面值的表名和列别名，语法上需要但此处未使用。请参阅 https://msdn.microsoft.com/en-us/library/ms175972.aspx。 - Dan Guzman

无论我如何尝试，即使插入了10万条记录，使用您提供的查询运行超过两天，SQL Server也没有抛出错误833。请问有什么帮助吗？ - Jean

是的。我正在运行SQLIOSim测试。与此同时，我在SSMS中打开了多个窗口，并运行了您建议的脚本（实际上每个窗口都有100,000个记录而不仅仅是10,000）。但还是徒劳无功！请帮忙！ - Jean

@Jean，我找到了另一个磁盘测试工具，可能会更好地帮助您产生错误，并相应地更新了我的答案。非常抱歉让您等待这么久。感谢您的耐心等待。 - Dan Guzman

Answer 3

我建议您使用免费的Microsoft SQLIO工具来测试磁盘性能。这将使您的磁盘负载，希望能够让您看到环境中的错误。

详细信息，请参见以下链接： Brent Ozar文章：查找SAN瓶颈 SQL IO、PowerShell 和存储性能 SQLIO 下载 您也可以尝试使用SQLIOSim实用程序进行压力测试，它使用随机模式，因此不可重复。

- Steve Ford

---

SQLiOSim似乎是我正在寻找的正确工具！让我检查一下然后回复你！ - Jean

@Jean，您需要在I/O子系统受到工具压力的同时运行一些需要I/O的查询，以便在SQL Server错误日志中获得错误消息。 - Dan Guzman

谢谢，@DanGuzman！有没有这样的查询示例？谷歌搜索只提供有关如何检测I/O问题的文章。对不起，我是一个尝试学习的新手。如果我的问题很蠢，请原谅。 - Jean