Post reply

SQL Server Compatibility Mode 130

  • September 5, 2020 at 12:05 am

    #3786527

    Hello,

    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

     

  • September 5, 2020 at 1:45 am

    #3786537

    Hi Victor,

    on a side note before answering the questions:

    • When you jump from SQL Server 2012 to 2016(DB level 130) you get the cardinality estimator changes that were made in SQL Server 2014 which MIGHT impact your queries.

    Traceflags 1117 regarding tempdb must be configued in the properties of tempdb database, autogrowth all files equally, and 1118 comes on by default in 130.

    SQL 2016 - It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases

    Traceflag 2371 is now by default in database level 130.

    Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server

    Regarding 4199, check this Microsoft URL. but the TLDR; on 130 is enabled by default.

    SQL Server query optimizer hotfix trace flag 4199 servicing model

    "Note By default, databases that are created in SQL Server 2016 use compatibility level 130 and have new optimizer logic already enabled"

    Regards,

  • September 5, 2020 at 2:22 am

    #3786540

    Alejandro,

    Thanks for your response. After changing the compatibility mode to 130 from 110, I still see those trace flags mentioned above not turned ON. Hence, I asked that question which is whether to manually turn it ON or keep it as OFF. If I run the query DBCC TRACESTATUS (1117) for instance, it will show 0 meaning, it is not turned ON. Similar is the case with other trace flags mentioned.

    Victor

  • September 5, 2020 at 2:28 am

    #3786541

    Changing the compatibility level won't turn them "on" in the way that DBCC Tracestatus will return rows, its gonna be in the engine/database itself, its not something you can see with the DBCC Tracestatus.

    enabling these traceflags in compatibility 130 will do nothing since its gonna be the default behavior for databases in compatibility 130.

     

  • September 5, 2020 at 2:42 am

    #3786547

    Got it. Thanks for confirming that!

    Victor

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply