SQLTeam.com | Weblogs | Forums

Timeout Error in SQLLog when .net application is trying to fetch data from SQL DB


#1

Hi All,

We are currently experiencing "timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding " error message occasionally when we are trying to extract the data from SQL DB through .NET application.
We are having application on different server and SQL DB is on different server. Application is using Connection string through .NET application configuration file and connecting to DB.
When we are running Update Statistics job in SQL DB, the timeout error is vanished and application is extracting data successfully.
All the time whenever timeout error is appearing, we need to run Update Statistics job.
Can anyone help is there anything we can fix not to run Update statistics job all the time whenever timeout occurred. Any alternative solution available?
Please help.

Regards,
Swapnil


#2

Swapnil,

To fetch the data from SQL Server are you using

  1. Stored procedures?
  2. Direct SQL Statements ie Select a, b, c, from dbo.Products
  3. Entity Framework
  4. x
  5. y
  6. z ?

#3

Thanks for the response.

We are using Stored procedure.

Regards,
Swapnil


#4
  1. please post sproc here if possible, if not please post the tables/views in sproc, the join columns and filter columns?
  2. how many databases does this sproc span
  3. When was the last time a full database back up was done on database(s)

#5

You can have the Stats update ASYNC (e.g. when they get out of shape), that would help as a Bandaid.

However, a query that times-out JUST because of Stats could probably be optimised to run faster. Maybe by creating an appropriate index (that covers the query), or perhaps by some other means - for example, sometimes a query is made on VIEWs which contains lots of tables which are not actually required, and looking at that critically can make the query faster.

Stats Update may also indicate that there are a very large number of INSERTS on the table - if so then the query needs to be built to perform efficiently in that scenario.

Its also possible that the SProc is inefficiently written - e.g. processing RBAR (row-by-agonising-row), LOOPs or Cursors (although not all cursors are bad, I very rarely see one that is good, so its often an indicator of poor-performing code)

That's a good point. If the DB is in FULL Recovery Model and LOG backups are NOT being taken, then I have known that slow things down horribly.


#6

The main thing which I have observed that when dB ison same server...no issue identified.

We have recently moved dB and apps on different server and from that point onwards frequently we have identified timeout issue


#7

Sounds like there is a cross server JOIN

In that situation we normally pull the data locally (for the external-server JOIN'd table) and THEN do a JOIN - in effect "locally"


#8

until you provide more details it will be shooting in the dark. guess work.