June 15, 2015 at 10:59 am
Hi, i am facing a huge issue in sql 2014 (SP1)
i have a very very complex query from a third party software.
It involve 10 table variable and a lot (seriously a lot) of subqueries
Of course the query is horrible, it had to be completly redesigned but anyway at least i mustn't have this so big difference (13seconds=>23 minutes)
The execution plan is very very hard to read
By the way on the server i did some test : 23 minutes when i run it on sql 2K14
If i switch the compatibility level on level 100 it comes back to 13 seconds (so same hardware, only one instance, and of course same configuration as i just switch compatibility level)
When i did the migration i did that :
backup/restore of the db
switch to compatibility level 120
DBCC CHECKDB WITH DATA_PURITY;
http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';
the difference in performance is so huge...
I check cumulative updates, it seems some fixes are looking the issue i have but as i have already put SP1 i cannot put CU7 (i didn't found the cu post SP1 as they used to have before)
Any idea where i can dig?
I can provide execution plan but they are really big
(i follow this guide for the upgrade :
http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/)
June 15, 2015 at 3:20 pm
It sounds like you have run into an issue with the new cardinality estimator in SQL 2014.
https://msdn.microsoft.com/en-us/library/dn600374.aspx
You can use the compatibility level of the database or trace flags to control which cardinality estimator is used. The old CE is for SQL 2012 and older. The new CE is for SQL 2014.
June 15, 2015 at 3:26 pm
Would help if you provided the actual execution plan for the fast and slow query.
June 15, 2015 at 4:36 pm
here the 2 execution plans
the good plan is generated by sql 2008R2 (the same is generated with compatibility level 100 or by using the traceflag 9481)
again the query is really horrible and the plans are really hard to read because they are big.
I don't expect someone to have a magical trick or something like that, i already said to the people in charge that their query is horrible and i have proposed to them to rewrite it.
But anyway at constant query/database/server, going from a couple of seconds to many minutes sounds really strange. So if we can understand what is going on maybe it will help people to prevent it!
thanks,
nkl
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply