Rebecca Lewis has a two-parter. First up is finding instances of long-running I/O Requests:
When diagnosing storage or latency issues, one SQL Server message factors in more than many:
“SQL Server has encountered X occurrence(s) of I/O requests taking longer than 15 seconds to complete on file…”
Where X might be 1, 5 or 50, and it could list a file from any one of your databases. When you see this, the next good question is when did it happen and where.
And then the question is, what do you do about it? Rebecca provides some guidance:
In a previous post, I shared a script to detect the I/O requests taking longer than 15 seconds warning across your SQL Server inventory. Now let’s talk about what to do when you find it.
Here are five of the most common causes with some tips to investigate each:
The neat part is, it’s not always due to slow storage or bad hardware.