SQL Server Upgrade – Fix Timeout Error from 2008R2 to 2014

availability-groupsPHPsql server 2014sql-server-2008timeout

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.

Best Answer

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/

Related Solutions

SQL Server Timeout – Drop and Recreate Index to Solve Issue

There is possibility of fragmentation issue with the index.

Check possibility of index fragmentation -

Here is a simple query to check fragmentation on your existing indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count 
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats 
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY 
d.fragmentation DESC

DROP TABLE #indexDefragList

This will return a list of all indexes in your current DB with their fragmentation in %.

Rebuilding of indexes will solve the problem.

Rebuilding of indexes is not supported automatically in any version of Microsoft SQL Server - the reason being is that rebuilding indexes can potentially be very expensive operations, and so need careful scheduling and planning.

In many environments special scripts will be written to handle this, for example:

http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

Note that whilst SQL can automatically update statistics for you in many cases there is a performance gain to be had by managing these more carefully as well.

You can easily build a script to automatically rebuild or reorganise them. There is a great article from SQLFool on this including a complete pre-made script.

Reference link

Sql-server – RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?

No. At least not directly, and even indirectly is not fragmentation but could be outdated stats at play (which rebuilding index also fixes). Read Plan Caching and Recompilation in SQL Server 2012, even though is 2012 a lot of it applies to 2008 still. The problem is that your plans are not reused and you suffer from high re-compilations. Read the white-paper, it has tonnes of resources and explains a lot.

Related Question