SQL Timeout for client, but no error in SQL logs
-
Using SQL 2008 R2 and .NET 4.0 Application on separate Windows 2008 R2 servers.
Occasionally, the client application will get an SQL exception like this:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
When this is error is reported to the DBA, he finds no indication of the error in the SQL error logs. This error happens with several different jobs.
The error happens more often with medium to high activity on the client server.
Any ideas where to start on this?
Thanks
-
There won't be anything in the SQL error logs.
Timeouts are a client-side error. It's the application deciding that it has waited too long for the query to complete and aborting. As far as SQL Server is concerned, the client sent a query then shortly after the client sent an 'attention' message cancelling the query's execution.
As far as SQL Server is concerned, it's the same as you, from Management Studio, running a query then pressing the 'stop' button.
Timeouts are indicative of a performance problem. Default timeout in most DB access libraries is 30 seconds. Unless that's a reporting system, most queries shouldn't take 30 seconds.
This is a little dated, but should get you started:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi Gail,
Thanks for your valuable information. You are the first DBA that is not surprised that there was no SQL error with this client exception.
Seems like I need to concentrate on improving query performance by fine tuning of indexes.
Your information saved me a lot of time.
Many Thanks!
Tom
-
tom.sage 70179 (4/9/2015)
You are the first DBA that is not surprised that there was no SQL error with this client exception.I'm not a DBA.
Seems like I need to concentrate on improving query performance by fine tuning of indexes.
Not necessarily.
You need to identify the problematic queries, you need to identify *why* the queries are performing poorly and then you need to fix the root cause. Tuning indexes could be a colossal waste of time if the root cause is badly-written queries, or inadequate hardware, or poor database design, or etc, etc, etc
Grant Fritchey's book (which you can google for, or wait until he runs across this thread, sorry, I don't have time right now to find the link) is an excellent resource on tuning in general, index, query and far, far more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You mean this book?
Like Gail says, to SQL Server, there's no difference between a client side timeout and you just killing a query that's running in SSMS. Neither one generates an error.
You can monitor for timeouts though. I put up a blog post[/url] on how to get it done 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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant,
Thanks for taking time to reply.
I have ordered your book.
Thanks
Tom
-
Not a problem. Happy to help. You know where to go if you have questions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply