SQL Server : Simulate Long I/O in SQL Server

Question

I was reading about I/O delays & the error message 833 in the error log in SQL Server. How do I simulate a long I/O scenario so I can get this error message in the log. Can someone please help?

Answer 1

You may find it difficult to get the 833 error message in the SQL error log using T-SQL alone if your I/O subsystem is appropriately sized. However, if you stress the I/O subsystem using SQLIO (https://www.microsoft.com/en-us/download/details.aspx?id=20163) or SQLIOSIM (on SQL install media) and run I/O intensive queries at the same time, that might generate the error.

Below is a T-SQL script to create and load a test table, along with an I/O intensive query. Run multiple instances of the SELECT loop in different SSMS windows if needed.

--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

EDIT: I ran across another I/O test tool developed by Microsoft, DiskSpd, and was able to produce the error reliably on my test system in conjunction with the above T-SQL script. This open-source tool is a free download available from https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223. DiskSpd has the same functionality as SQL but is a bit easier to use, more well documented, and has the option to produce XML output to facilitate analysis.

The parameters I used for the test are below. The error was produced within 30 seconds after starting the test with the SQL Server data file also on the D drive.

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