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.
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.