Get IO warning from log

If you're managing a SQL Server instance, you know how crucial fast disk I/O is for overall database performance. When I/O operations start to slow down, it can lead to noticeable performance degradation and user frustration. Thankfully, SQL Server logs warnings when I/O operations take longer than 15 seconds, and you can easily access this information to pinpoint potential bottlenecks.

By querying the SQL Server error log, you can identify these long-running I/O warnings. This can be an invaluable first step in diagnosing disk-related performance issues, allowing you to investigate further and optimize your storage subsystem.

Here's a handy SQL script you can use to retrieve these I/O warning messages from your SQL Server error logs:

CREATE TABLE #IOWarning (LogDate datetime
 , ProcessInfo sysname
 , LogText nvarchar(1000));

INSERT INTO #IOWarning EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarning EXEC xp_readerrorlog 5, 1, N'taking longer than 15 seconds';

SELECT LogDate, ProcessInfo, LogText
FROM #IOWarning
ORDER BY LogDate DESC;
  

How the Script Works:

• The script first creates a temporary table named #IOWarning to store the retrieved log entries.
• It then uses xp_readerrorlog to query the SQL Server error logs. The parameters `0, 1, N'taking longer than 15 seconds'` specifically look for the current log (0) and archived logs (1 through 5) for entries containing the phrase "taking longer than 15 seconds," which is the standard warning message for slow I/O.
• Finally, it selects and orders the results by LogDate in descending order, showing you the most recent warnings first.

This script provides a quick way to gain insight into potential I/O performance issues. If you consistently see these warnings, it's a strong indicator that you need to investigate your disk configuration, storage array, or underlying hardware. Happy troubleshooting!