January 27, 2020 at 9:05 am
Just a quick question
I'm putting in a new SQL 2019 server and an external consultant has advised that we should not use 2019 compatibility mode as there are issues with cardinality estimation - we should use 2017 compat mode
I've googled this and found nothing.
has anyone seen this ?
MVDBA
January 27, 2020 at 9:45 am
There have been changes to the CE in each version from 2014 onwards, not much mind but each compat level does have its own changes.
Below from Glenn may be of use
January 27, 2020 at 10:02 am
thanks for that
It's interesting in that article that there are new hint options and database specific settings, but not any opinion on whether they are good or bad.
I like the fact that some of the server level settings have been granularized into "database level" (particularly allow ad hoc) - and the advice on the issue of query store becomes moot - since it's a new server and new databases with no existing query plans.
MVDBA
January 27, 2020 at 10:54 am
My track on moving anything from <2014 to >=2014 is to leave it in it's old compat mode for a week or so, let it build up the query store. Then switch it to the highest compat mode and see what breaks. Obviously on DEV/UAT/PRELIVE first, not going to get 100% of everything that's going to break as potentially don't have the same load metrics, but it's definitely a starter for 10.
The old compat levels are not going to be around for long, so management buy in to run the potential risk in PROD is needed, but whats the alternative, stick with 2008/2008R2/2012 which are out of mainstream and extended support, can the company afford that risk especially in times of all the cyber attacks going on.
It is all about minimising risk, but then nothing is without risk.
January 27, 2020 at 11:09 am
so, to give some context - this is a consolidation of multiple servers (some 2014, some 2016) into a nice big server with "NO LINKED SERVERS" - it is however not going live straight away - this has 6 months of testing.
i'm not sure in the wisdom of testing application consolidation in 2017 mode and then having to re-do testing for 50 databases when the compat mode changes (and at that point the server will be live).
My gut is telling me - go 2019 and find all the issues in our test environment and if we find insurmountable issues then "back to 2017 compat mode"
Time to test on my laptop (the new hardware arrives today and is being assembled by Wednesday) - it's a loan from HP, so if nothing works out then they take it back..... (over my dead body) 🙂
MVDBA
January 27, 2020 at 12:01 pm
put them on the latest compat mode - if there are issues with CE you can change each db that requires it by using the ALTER DATABASE SCOPED CONFIGURATION (see link above)
note that 2017 and 2019 did add a few more things that these are also likely to give issues - the link above does explain what was added.
Testing is required - code changes may also be required in order to fix "issues" that didn't come into play on the old version.
January 27, 2020 at 12:23 pm
I'm not aware of any CE issues moving between versions greater than 2014. The optimization engine has changed at each version, and that could, in some edge cases, cause problems. That's why I'd still plan on using Query Store as part of an upgrade, leaving the compatibility mode at the older version for a week or two (depending on the database and the system) before swapping over to the new compatibility mode. However, yeah, I'd be going to the new compatibility mode.
Not sure where you saw that information, but I don't think it's reliable.
"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 27, 2020 at 1:17 pm
I'm not aware of any CE issues moving between versions greater than 2014. The optimization engine has changed at each version, and that could, in some edge cases, cause problems. That's why I'd still plan on using Query Store as part of an upgrade, leaving the compatibility mode at the older version for a week or two (depending on the database and the system) before swapping over to the new compatibility mode. However, yeah, I'd be going to the new compatibility mode.
Not sure where you saw that information, but I don't think it's reliable.
i've never seen this information - anywhere ... my info came from an email from the consultant dealing with the "allways on" side of things.... not sure why he wanted to specify compatibility mode for failover testing.. I think i'll fight my corner and go 2019.
MVDBA
January 28, 2020 at 11:47 pm
This was removed by the editor as SPAM
January 29, 2020 at 11:04 am
would using 2019 compatability mode, but setting the "legacy cardanility" option on be a wise idea?
MVDBA
January 29, 2020 at 11:42 am
would using 2019 compatability mode, but setting the "legacy cardanility" option on be a wise idea?
After extensive testing, yes, maybe, depending on the outcome of the tests.
"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 29, 2020 at 12:04 pm
as long as I can use scalar value function inlining and the new table variable cardinality estimation then i'm happy
MVDBA
January 29, 2020 at 12:09 pm
as long as I can use scalar value function inlining and the new table variable cardinality estimation then i'm happy
Hmmmm, I haven't tested it, but it's possible that the table variable thing might not work. I don't know. The other would, just fine.
"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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply