Good Morning Experts,
Application users have reported slowness. We checked SQL Server CPU and its fine. We added some memory. We are seeing the below messages in ERRORLOG:
SQL Server has encountered 4 occurences of I/O requests taking longer than 15 seconds to complete...
Isnt this a strong indicator of I/O subsystem problem?
It's an indicator of disk latency which can be a problem with the I/O subsystem or it could be configuration issues with things such using compressed drives, not excluding database files from antivirus scans, it could be other processes depending on your I/O subsystem configuration and how it's used, if you are on a VM how the client is configured and the types of disks being used. In other words, it's telling you have some disk latency but not necessarily the cause of the latency.
You'd want to look things such as what database files does this happen with - tempdb can mean contention issues, inappropriate use of tempdb, does it happen with certain databases only, does it happen with certain drives only, does it happen at certain times or some pattern so when those are logged (for example whenever index maintenance is done or always at 4 am when a data load occurs), etc.
After checking configurations and making sure all the drivers , you would want to do some monitoring of some of the I/O metrics. The following articles go over different things to monitor, what performance counters to capture, configurations to check.
Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency
I/O requests taking longer than 15 seconds to complete on file
Sue
Could also be contention. Have you run profiler? Checked for deadlocks? Missing indexes?
I was going to say the same but someone came into my office and could not finish. So yes refresh statistics. I would check contention first because you can see those right... Then I would look at queries. Do you have a bunch of triggers? Are you using cursors? It could be so many things. Profiler is going to give you a heads up. Then if possible some of your larger or more complex stored procedures run them manually and tick actual execution plan. Get your eyes on what each step is doing and what cost factor those steps are relative to the rest of the query. Do you see index seeks or scans. You do NOT want to be scanning ever and if you are scanning a huge index that can be death to performance.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh