Improvement: Make several enhancements to the SQLIOSim utility
Article
Applies to:
SQL Server 2022 on Windows, SQL Server 2022 on Linux
Summary
This improvement makes the following enhancements to the SQLIOSim utility and fixes data integrity issues that might be caused by I/O handling. For more information about the SQLIOSim utility, see the Introduction of the SQLIOSim utility.
Enhanced logging
Adds a new ring buffer to track errors, in order to make it unlikely that errors in the history ring buffer are replaced because of the rollover.
Adds various outputs that contain "tick count" and/or "date and time" to make debugging an issue easier.
Adds the outputs of fsutil and volume information to the error file, in order to map the file offset to the offset on the physical disk and filter the I/O subsystem traces easier.
Adds the output for Coordinated Universal Time (UTC) and local time.
Breaks down the output of the page header to the error file, such as PageId, Checksum, and Timestamp.
Displays a list of the pages of the FILE HISTORY ring buffer to more easily locate ring buffer entries.
Displays Timestamp, Date Time, Logical CPU, and other columns in the Sqliosim.log.xml or ErrorLog.xslt view.
Increases the size of the ring buffers and reduces the frequency of the rollover.
Provides hexadecimal and decimal outputs for common outputs such as PageId, Checksum, and Timestamp.
Updates the layout of Sqliosim.log.xml to make it easier to read the XML file in a text editor.
Validated Sqliosim.log.xml and ErrorLog.xslt can be viewed correctly in Microsoft Edge that uses Internet Explorer (IE) mode.
Page header timestamp
Stores the GetTickCount64 value in the page header during the write operations. The tick count is useful for tracking when the page is written and filtering the I/O subsystem traces.
BNR (Bytes-Not-Read)
The I/O issue causes the read operation to be returned as if the read operation is successful, but the actual buffer isn't updated or read. The SQLIOSim utility receives a successful read, but fails the validation checks.
The SQLIOSim utility now stamps the read buffers by using the BNR pattern before issuing a read. When the read operation returns successfully but fails to perform the actual read, the validation continues to fail. However, the bytes read shows the .BNR pattern that indicates an I/O subsystem issue.
Write-retry
If the I/O subsystem encounters a reset (more common in direct-attached storage (DAS) installations), the write operation requests might complete incorrectly. The SQLIOSim utility doesn't guarantee write-retry capabilities. In some cases, the SQLIOSim utility tries to read and validate the data that fails to write.
The SQLIOSim utility is enhanced to log a write failure and then perform a write-retry operation.
Enhanced locking
The internal locking mechanisms and page protection (VirtualProtect) activity are optimized and enhanced to ensure that an issue reported by the SQLIOSim utility isn't caused by a logic flaw in the SQLIOSim utility.
Read-retry
Fixes an issue that causes the read-retry attempt to fail when retrying a failed LDX (log simulation) read.
Examples of the enhanced outputs
The first example of the enhanced output includes the following enhancements:
UTC Time
Hexadecimal and decimal
Timestamp/Tick count
BNR
Page header expansion
The second example of the enhanced output includes the following enhancements:
Ring buffer
UTC Time
Page range breakout
The third example of the enhanced output includes the following enhancements:
Tick count column
Logical CPU column
More information
This improvement is included in the following cumulative update for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for your version of SQL Server:
SQL Server 2022 introduces a range of new features and enhancements, providing powerful tools and capabilities that optimize performance and offer better control on SQL objects manipulation and creation.
