January 19, 2015 at 9:48 am
Hi Guys,
I am having serious issue on some of the procedures which is very slow comparing to sql 2005. No fragmentation/Outdated stats.
Query that takes 3 min on sql 2005 takes 25 min on sql 2014.
Please provide your suggestions.
Thanks a lot in advance!
January 19, 2015 at 10:33 am
I hope that this was a test migration rather than a "fingers-crossed-and-hope-for-the-best" migration;-)
We had some performance problems too. You may find this thread interesting. Or at least relevant.
I have not had time to investigate the reasons behind this, but we also found that performance of table variables, even where the TV concerned contained few rows, took quite a hit. Which was a good excuse to refactor and get temp tables in there instead.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 19, 2015 at 1:09 pm
It could be any number of things. First thought is the changes to the cardinality estimator. See how many rows the execution plans think it's going to get from the two different versions. Next, it could just be optimizer changes. Is the query, let's say, complex? Would it not be considered the best possible example of code generated out of the group? Those edge case queries often suffer during upgrades.
If it's the cardinality estimator, you can use traceflags on the query (or the database) to change the behavior. Here's a good MSDN blog on the topic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2015 at 2:05 pm
I'm with Grant on this one. Are your DBs in 2014 (120) compatibility mode? If so, you are using the new CE. We are going to 2014 in a few months here and we are actually not taking our DBs to level 120 (Leaving them at 110) until we can get some good workload testing done. There's a lot of improvements with the new CE but it's always a good idea to test it first on the off chance that a few queries start behaving badly with the new CE.
January 20, 2015 at 5:05 am
Yes, Agree with others.
search engine work differently in different version to improve the performance. However while doing upgraded one should keep the db in the original compatibility mode unless and until the performance of the SP'S tested and modified to the new compatibility mode.
HTH.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 20, 2015 at 6:32 am
free_mascot (1/20/2015)
Yes, Agree with others.search engine work differently in different version to improve the performance. However while doing upgraded one should keep the db in the original compatibility mode unless and until the performance of the SP'S tested and modified to the new compatibility mode.
HTH.
And this is why I would test prior to the migration. Why upgrade at all if you can't take advantage of all the new functionality.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply