Wednesday 12 August 2015

Query regression in SQL 2014 compared to earlier SQL versions

Lately I have had many queries like “I have a SQL server 2014 and a particular query does not complete in expected time frame, the same query was running without issues before migration in SQL Server2012/SQl2008R2” 
or 
“The Query that was running in SQL 2012/SQL 2008R2 stalls in SQL 2014 and never completes even though there are no resource issues”. 

In SQL 2014 Cardinality estimator for SQL Query optimizer has been modified for improved performance, although this may help most of your queries there are some queries which has shown regressive performance on SQL 2014 compared to earlier version. This is already a documented issue with Microsoft https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx . The only way to overcome this is to test your workload before migration and modify your query.

No comments:

Post a Comment