Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
When one of our sql servers was migrated from SQL2k12 to SQL2k16, some of the user database compatibility levels were set to 110. Developers wanted that way at that time. Now, if we change it to 130, I do not think, we need to enable (or turn them ON) any of these trace flags?
1117, 1118, 2371 and 4199
Will greatly appreciate your quick input.
Thanks. Victor
I'm really tempted to not answer this question because you can find all this information with a simple search, but three of these are no-brainers and the other one is really an "it depends."
First, for the first three you mention, there are straightforward, single-sentence answers in DBCC TRACEON - Trace Flags:
Trace Flag 1117 (no-brainer: do not need)
Note: Starting with SQL Server 2016 (13.x) this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no effect.
Trace Flag 1118 (no-brainer: do not need)
Note: Starting with SQL Server 2016 (13.x) this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no effect.
Trace Flag 2371 (no-brainer: do not need)
Note: Starting with SQL Server 2016 (13.x) and under the database compatibility level 130 or above, this behavior is controlled by the engine and trace flag 2371 has no effect.
Trace Flag 4199
TL;DR: If you don't know you need it, you probably don't need it.
This was replaced by the DATABASE SCOPED CONFIGURATION option QUERY_OPTIMIZER_HOTFIXES; so if, after reading the below and all the links after that, please use that option per database instead of the server-wide trace flag.
This one is a little more complex, because it is not a trace flag that was suddenly shut off at some specific version or compatibility level, but the behavior it produces adjusts slightly depending on version / compat level. Opinions vary, but generally, you shouldn't need to have this option enabled unless you know for certain turning it on improves performance for a vast majority of your queries and only under the new compat level. If you needed 4199 to improve certain queries on your old compat level, it is unlikely you'll still need it in 130. You'd probably only need 4199 if you need to turn off post-RTM hotfix behaviors in 2016. I'd argue that if you identify queries that regress under 130, you should tune and/or hint those queries to use the old compat level, rather than use a database- or server-level option to do that to everything.
For this one, nobody here can tell you whether you should turn on the database option or not; please see a lot more background in the following links:
