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