2017-12-20

Troubleshoot SQL Server Disk I/O Slowness

Last week, as the storage device of my production sql server had a hardware component failure, I moved the production database files into a new SAN storage. But after that, some users complain that the system occasionally hang, so I firstly check anything abnormal on the disk I/O performance. There were some errors logged in the sql server error log, saying "SQL Server has encountered XX occurrence(s) of I/O requests taking longer than 15 seconds to complete on the file (...\MyUserDB.mdf) in database [MyUserDB]".
I'm a lucky guy, my company bought me SQL Sentry, which I found it is the best performance monitor for sql server. It shows me that during that time period, the database data file had a high disk read latency (>2,000ms during that period, comparing to normally within 5ms).
Then I tried to find any error on the windows system log, and found an iSCSI error.
Finally, I called my storage hardware vendor to fix it. Meanwhile, I also double checked the antivirus scan excludes SQL Server related files and directory, as listed in this knowledge base. Also, make sure no any disk defragmentation scheduled task on any database disk drives.