August 24, 2016 at 8:16 am
We have online production database which is running in SQL Server 2008 R2. Now we are planning to migrate to SQL Server 2014.
We notice SQL querys were performing very slow in SQL 2014 comparing SQL 2008 while testing performance in same hardware configuration.When we change compatibility to 100 in SQL 2014(i.e SQL 2008 mode),then it uses old execution plan(i.e sql 2008) and executes faster.
But our requirement is to set latest compatibility mode to 120 and ensure improved or at least same performance as that of SQL 2008.
I also tried by enabling trace flag 9481 works fine but it goes off after SQL server restart
But i heard there is cardinality estimator option in SQL 2016 which will support legacy versions to generate the execution plans of querys as like in older verions but it is not in SQL 2014.
So ps advise .
August 24, 2016 at 9:00 am
Best thing to do is identify the queries which are slower under the new CE and tweak them and/or indexes to improve the performance.
The legacy cardinality estimator option is the traceflag you mentioned, but if you turn it on as a traceflag, it's server-wide and you might as well just run the DBs in compat mode 110 in that case. Server-wide trace flag should just be for while you're fixing performance problems if you'd upgraded without testing (which a lot of people have done)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2016 at 10:51 am
Most of the time, the queries that are suffering because of the new cardinality estimator have other issues that can be addressed (or the architecture is off). Frequently the best fix is to identify what's wrong around the query and address that. In rare cases the issue is that the old estimation resulted in better plans and better performance, but that's rare.
"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
August 24, 2016 at 11:41 am
I don't think it is still "required" but it used to be that upgrading versions mandated full-scan statistics updates on all statistics in every database migrated.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 24, 2016 at 11:54 am
TheSQLGuru (8/24/2016)
I don't think it is still "required" but it used to be that upgrading versions mandated full-scan statistics updates on all statistics in every database migrated.
Not at all required, but not at all a bad idea. You will get a stats update on restored statistics from an old version now. However, that means waiting for each one to come up. I'm not crazy about that approach. I like yours better.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply