This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(217.60000000000002, 34%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Hi Experts,
Yesterday, we had run into a situation where some application users complained about database performance.
They claim SQL instance is very slow and some of the front jobs are taking more than 5 mins which is longer than usual thereby crossing 40 secs SLA.They also mentioned there are experiencing some timeouts.
When we took a look at the server. we don't see any blocking and the CPU usage is under 40% usage.
I don't see any timeout errors logged in the SQL Server ERRORLOG. However, I see some I/O related warnings that I/O taking more than 15 secs in the error log.
My question here is , what kind of information could have been requested from the Application team to handle this particular issue better rather than taking blame. We have no idea if any network was slow or some piece of code or anything else which was contributing to the slowness. Any relevant / specific data points or questions which DBA is missing here to get better control of such performance requests?
Best Regards,
Bob
I don't see any timeout errors logged in the SQL Server ERRORLOG.
Timeout is a client raised event, SQL Server don't know about that and so can't log it.
Ask the user for some sample queries they are running, test the queries and analyze the execution plan for the queries.
SQL Server will execute queries indefinitely. Query timeouts occur in the application when a query has run longer than the client configured command timeout. The client API then cancels the query (by sending an attention signal to SQL Server) and returns an error to the app. This is not recorded in the SQL Server error log or captured by default. You would need to create a trace including attention events for analysis. This will include client timeouts as well as queries cancelled deliberately like pressing the cancel button in SSMS during query execution.
The long IO warnings are a big red flag that should be investigated and may be the root cause of the problem.
Holistic troubleshooting requires information captured in application logs with sufficient detail as well as information gathered on the database server, like wait stats, long-running queries, wait stats, Query Store, etc.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
The message "I/O taking more than 15 secs in the error log" is SQL Server warning you about a serious disk problem exists, which must be fixed.
This can occur for a couple of reasons, however 99.999% of the time it is hardware related. You need to investigate your storage for issues. If you are using a RAID array, it might simply be a failed drive which was causing a temporary problem and is resolved.
However, I see some I/O related warnings that I/O taking more than 15 secs in the error log.
This is a big warning sign. With modern SSDs 15 milliseconds is quite a long time. And even for a floppy disc 15 seconds is way more than you can expect.
So the SAN (I bet that there is a SAN involved) and the ways leading to the SAN needs to be investigated.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Hi Bobsql-8788,
In addition, for the IO issue, the following things can indicate I/O problems:
Please check system and application Event log and check if there are any disk related error or warning messages.
Please check for wait types PAGEIOLATCH, WRITELOG, I/O Stalls or LOGBUFFER. If the disk has been slow then you should see waits type with high wait time.
You can use Performance Monitor to monitor counters such as Avg. Disk Sec/Read , Avg. Disk Sec/Write and Disk Transfers/sec to measure the disk latency. A high Avg. Disk sec/Write and Avg. Disk sec/Read performance counter value may occur due to a burst of disk write and read requests by either an operating system or application. In general, for the performance counter value of Avg. Disk sec/Write and Avg. Disk sec/Read, under 10ms is good. 10-20ms is average. Over 50ms is bad.
Please refer to SQL Server troubleshooting: Disk I/O problems and SQL Server performance troubleshooting which might help.
Best Regards,
Amelia