July 28, 2014 at 7:10 am
I am currently running SQL 2014 in my production environment. Both machines are configured for Windows Failover Cluster Services so that we could take advantage of AlwaysOn Availability Groups.
Currently we are experiencing a issue where our web application is throwing an error, "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I am pretty sure that the error is being thrown from the application, but I can't prove it. Because the error states it is SQL, the consensus seems to be that SQL is having the issue. I need to know how to troubleshoot it to ensure that SQL is not the problem.
July 28, 2014 at 12:07 pm
What is the timeout value in the application's connection string? This typically occurs when a query's processing time exceeds this value. You can increase the value or tune the query to run faster.
July 28, 2014 at 12:31 pm
That is the thing. I don't see that information in the error log. However, I ran a trace on the server and it appears that the application isn't sending the information over to the SQL server. It seems that it is hung up in the application.
July 29, 2014 at 12:59 am
This was removed by the editor as SPAM
July 29, 2014 at 6:12 am
The issue, whatever is causing the slowdown, could very well be within SQL Server. Absolutely. But the timeout itself is controlled at the application connection setting.
So, to troubleshoot this, I wouldn't focus on the timeout. Instead, I'd set up monitoring on the server and start capturing long running queries, say ones that exceed 25 seconds, or long running transactions, same setting, in order to identify where the problem is coming from. You're on 2014, so the very best way to get this done is using extended events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2014 at 6:37 am
Grant Fritchey (7/29/2014)
The issue, whatever is causing the slowdown, could very well be within SQL Server. Absolutely. But the timeout itself is controlled at the application connection setting.So, to troubleshoot this, I wouldn't focus on the timeout. Instead, I'd set up monitoring on the server and start capturing long running queries, say ones that exceed 25 seconds, or long running transactions, same setting, in order to identify where the problem is coming from. You're on 2014, so the very best way to get this done is using extended events.
That is what I began to do; however, I noticed that the queries, when run directly from SQL, are running in less than a second. This is what has me confused. Am I missing some small detail in how SQL sends the data back?
July 29, 2014 at 6:46 am
mqbk (7/29/2014)
Grant Fritchey (7/29/2014)
The issue, whatever is causing the slowdown, could very well be within SQL Server. Absolutely. But the timeout itself is controlled at the application connection setting.So, to troubleshoot this, I wouldn't focus on the timeout. Instead, I'd set up monitoring on the server and start capturing long running queries, say ones that exceed 25 seconds, or long running transactions, same setting, in order to identify where the problem is coming from. You're on 2014, so the very best way to get this done is using extended events.
That is what I began to do; however, I noticed that the queries, when run directly from SQL, are running in less than a second. This is what has me confused. Am I missing some small detail in how SQL sends the data back?
No. If the query completes, then it's down to the network to move the data out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2014 at 6:46 am
Thank you. I figured as much.
August 13, 2014 at 1:51 pm
It turned out that the configuration for the NIC teaming was the issue along with errors in the switches. Once they were corrected, the timeouts went away.
August 14, 2014 at 8:42 am
mqbk (8/13/2014)
It turned out that the configuration for the NIC teaming was the issue along with errors in the switches. Once they were corrected, the timeouts went away.
Thanks for the follow up. Good to know what the root cause was, especially for anyone spotting this thread in a search of the internet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2014 at 6:13 pm
I've found that really poor indexes will also cause frequent and, sometimes, massive timeouts on the application side. If the application is doing inserts into a table that has an index with a leading column that has very low selectivity (A BIT column or some form of IsActive column, for example), the page splits that occur will stack up on a busy system and start causing timeouts. It might not take long for the stackup to occur... just a couple of seconds after the index is created on a busy system or it might take longer until "too many" users try the same insert.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply