March 18, 2015 at 5:15 am
The SQL servers are installed on 2 different VMs (Different IP Adresses). They have only one SQL Instance on each machine. The processor / ram / hard drive space is same between 2 machines expect the sql server. The machines have the same applications installed. We are running the same select query from the SQL managment studio directly.
Attached the execution plan.
March 18, 2015 at 5:18 am
Getting the error : AS#403 : Restrictions failed message and posted some duplicates..
Please remove the duplicate replies if possible.
March 18, 2015 at 5:30 am
Attached the Query plans. Please advice some solution on this.
March 18, 2015 at 6:07 am
Did you, at minimum run Update Stats on the db after you restored it into SQL2014? I make it a habbit of rebuilding the indexes when I upgrade versions. If the database isn't too terribly big try that.
March 18, 2015 at 7:18 am
I think the issue must lie somewhere besides the cardinality estimator. In fact, both plans have the exact same estimates. They even have the exact same costs. Something else is causing the issue, possibly in the configuration, memory settings, something along those lines.
"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
March 26, 2015 at 9:26 am
I am late to this discussion but reading with interest. I also see there is no answer/solution yet.
From the description it sounds similar to what we are experiencing with a software package (with SQL database at core) we have. Others with the same software have also noticed degraded query performance after migrating to SQL 2014. However, none of us are good SQL DBA's. TempDb was also growing incredibly large (compared to original DB size).
What was noticed though, changing the compatibility level of the database back to SQL 2012 brought back the performance and tempdb behaviour. Whether this was coincidence or something else I do not know. We now set this immediately after migrating as it is recommended by the supplier as a workaround until they find the real reason.
Before I suggest changing this setting on your software, I would wait for some of the great DBA's to comment on such a tweak. I guess it shouldn't be a problem as you have only recently migrated so there shouldn't be anything that only exists to 2014. If it is only a test system, take a snapshot and try.
March 26, 2015 at 9:53 am
Drenlin (3/26/2015)
I am late to this discussion but reading with interest. I also see there is no answer/solution yet.From the description it sounds similar to what we are experiencing with a software package (with SQL database at core) we have. Others with the same software have also noticed degraded query performance after migrating to SQL 2014. However, none of us are good SQL DBA's. TempDb was also growing incredibly large (compared to original DB size).
What was noticed though, changing the compatibility level of the database back to SQL 2012 brought back the performance and tempdb behaviour. Whether this was coincidence or something else I do not know. We now set this immediately after migrating as it is recommended by the supplier as a workaround until they find the real reason.
Before I suggest changing this setting on your software, I would wait for some of the great DBA's to comment on such a tweak. I guess it shouldn't be a problem as you have only recently migrated so there shouldn't be anything that only exists to 2014. If it is only a test system, take a snapshot and try.
That's exactly what the compatibility level is for, though it does come with downsides: all of the new features in 2014 are turned off for the databases you set this for.
And there is a solution: do your performance testing / analyses before you migrate and work out where the problems are. The chances are that the new cardinality estimator is the culprit. With some work, those issues can be resolved.
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
March 26, 2015 at 10:18 am
Phil Parkin (3/26/2015)That's exactly what the compatibility level is for, though it does come with downsides: all of the new features in 2014 are turned off for the databases you set this for.
And there is a solution: do your performance testing / analyses before you migrate and work out where the problems are. The chances are that the new cardinality estimator is the culprit. With some work, those issues can be resolved.
I thought Grant said 'somewhere besides the cardinality estimator'.
Would 2012 compatibility turn off the new estimator?
March 26, 2015 at 10:26 am
Drenlin (3/26/2015)
Phil Parkin (3/26/2015)That's exactly what the compatibility level is for, though it does come with downsides: all of the new features in 2014 are turned off for the databases you set this for.
And there is a solution: do your performance testing / analyses before you migrate and work out where the problems are. The chances are that the new cardinality estimator is the culprit. With some work, those issues can be resolved.
I thought Grant said 'somewhere besides the cardinality estimator'.
Would 2012 compatibility turn off the new estimator?
Yes.
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
March 26, 2015 at 3:30 pm
Drenlin (3/26/2015)
Phil Parkin (3/26/2015)That's exactly what the compatibility level is for, though it does come with downsides: all of the new features in 2014 are turned off for the databases you set this for.
And there is a solution: do your performance testing / analyses before you migrate and work out where the problems are. The chances are that the new cardinality estimator is the culprit. With some work, those issues can be resolved.
I thought Grant said 'somewhere besides the cardinality estimator'.
Would 2012 compatibility turn off the new estimator?
Yes it does.
You can use a traceflag (forget the number) to turn off the cardinality estimator at the server, database, or query level. Using it at the query level is a much more surgical way of solving the problem and would be preferred in most cases. Doing this instead of killing the compatibility level and removing ALL 2014 functionality makes more sense. After all, you upgraded to 2014 for a reason I assume.
Not sure why you'd see massive increases in tempdb unless, again, the cardinality estimator is leading to execution plans that dump to tempdb more often due to sorts or hash tables or something.
"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
April 2, 2015 at 1:58 am
I wonder if the migration issue is only among a small group of users. In an otherwise extremely positive review, the only drawback was the cost for going from 2012 to 2014: http://www.itcentralstation.com/product_reviews/sql-server-review-by-kleegeek
April 2, 2015 at 2:18 am
hadas.hadas (4/2/2015)
I'm not sure that those stats are available anywhere. But I've seen enough posts (and had personal experience) to suggest that this is a fairly common problem where the underlying databases are large and reasonably complex. Of course, the people doing such migrations, one would hope, do their testing first and take some time to iron out the issues, one way or another.
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
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply