4

We had a SQL Server 2008R2 Enterprise edition for our database to support a front-end application. We never had any timeout issues before. Recently the company decided to upgrade the database in to SQL Server 2014 Enterprise edition with 2 node always on cluster setup. The new server has better CPU, Memory than the old server.

After the upgrade I made all the necessary modifications, Checking Database consistency, run DBCC UPDATEUSAGE, update Statistics, index rebuilding, recompiling the stored procedures and so on. The database switching and migration all went well. However, our users start complaining about timeout issues.

I have been reviewing different articles,blog posts and made some modification like changing the connection string and add MultiSubnetFailover = 'True', which seems helped a lot and minimize the frequency of timeout but still the issue is there. Does anyone know what can cause this issue and how to resolve it? I would highly appreciate your suggestions and recommendation to resolve this problem.

Improve this question
4
  • Is it connection timeouts or query timeouts?
    – Tara Kizer
    Aug 24, 2016 at 16:46
  • 1
    Its a connection timeout. The error from one of our applications that process a request and sends a report says " Unexpected error while sending email. (The operation has timed out)"
    – Hailegziabher Dechassa
    Aug 24, 2016 at 18:16
  • 1
    Can you isolate the problematic query, and run it manually on both the new server and the old server?
    – datagod
    Aug 24, 2016 at 19:38
  • 1
    The timeout issue is not tied to specific query. First that what it clicked my mind and I placed a trace for but could not capture anything gloomy. We are getting all the errors when the application try to access the database. The timeout issue occur for the same process at some point and will pass without any error the other time.
    – Hailegziabher Dechassa
    Aug 24, 2016 at 19:50

2 Answers 2

Reset to default
6

The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance metrics. You could do this by lowering the compatibility level to <120.

I would perform all this testing on a test server and not in production.

https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx https://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eats-bad-tsql-breakfast/

Improve this answer
1
  • 2
    Having done a few 2000/2005/2008 to 2014 upgrades over the past few years, I'd agree with Andy Jones on this one. Check Cardinality and compare execution plans. You may have T-SQL in there from your 2008R2 version which is causing delays. DataTypes, Index Hints, etc. can cause issues during version upgrades.
    – SQLDevDBA
    Aug 24, 2016 at 18:32
6

I finally find out the problem. After days of straggling I checked the SQL Server report and noticed that there were very large occasions of autogrowth on temp db database. The cause of the problem was, after the database migration to our new server the system database file size was not configured and tempdb had a way to less file size than what it supposed to have.

It looks like whenever an autogrowth operation occurred on tempdb, the query is forced to roallback and cause the problem. I just change the file size as well as the file growth size. Now it is working like a charm and no issues since then.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.