PHYData DBA (4/29/2014)
paul.goldstraw (4/29/2014)
PHYData DBA (4/29/2014)
shanjan.sapra (4/29/2014)
Nice question. But I guess the another answer is also true as an Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE.Hence the approach to change to the new CE would be the same and use the trace flag 9481 to enforce the old CE algorithms.
Enabling the trace flag would disable it for entire server not just one database.
The question clearly states the person wans just one database to not use the CE, not every database on the server.
This webpage[/url] clearly shows that the trace flag is used per query, so any queries on any database that you want to use the new optimiser, you can just leave the flag out
This is a great example of a correct statement that is still wrong.
What you are saying is true, but that was not one of the possible answers now was it?
It's not even what the post you are quoting is talking about.
The final option was "Alter all queries to use the trace flag 9481". Altering the queries in the way the link mentions, but using trace flag 9481 instead of 2312 as in the example given would force those queries to use the old optimiser, which is what the question is asking. The answer Steve is saying is correct wouldn't allow the use of memory optimised tables, which the question said was the point of upgrading in the first place.