June 26, 2019 at 9:01 am
Hello everyone
I migrated my server SQL SERVER 2008R2 to 2016 I changed the compatibility level of my database to 130
USE [master]
GO
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 130
GO
Is it necessary after this activation? Activated traceflag 4199
is It automatically included when you change the COMPATIBILY LEVEL to 130 ?
thanks
June 26, 2019 at 10:25 am
Quote:
In SQL Server 2016, trace flag 4199 hotfixes that are made to previous releases of SQL Server will become enabled under database COMPATIBILITY_LEVEL 130 without trace flag 4199 enabled. Trace flag 4199 will be used to release any future SQL Server 2016 hotfixes for databases by using the 130 compatibility level. Because trace flag 4199 is recommended only for customers who are seeing specific performance issues, customers are advised to remove trace flag 4199 after they migrate their databases to the latest compatibility level because trace flag 4199 will be reused for future fixes that may not apply to your application and could cause unexpected plan performance changes on a production system. This means that different trace flag 4199 hotfixes are enabled for each compatibility level that is supported in a given product release. The latest compatibility level already enables all previous fixes under trace flag 4199. This means that upgrading a database to the latest compatibility level and removing trace flag 4199 still enables all fixes that a workload was leveraging before database upgrade, just not new fixes. If at a later time customers experience query performance issues, experiment with enabling the database scoped option QUERY_OPTIMIZER_HOTFIXES or query hint ENABLE_QUERY_OPTIMIZER_HOTFIXES in a test environment to determine if the issues are resolved. Refer to the documentation about QUERY_OPTIMIZER_HOTFIXES.
June 26, 2019 at 10:28 am
I would also rebuild existing stats
July 8, 2019 at 3:25 pm
I would leave that TF on.
On SQL 2016 RTM, the traceflag does nothing, but starting with CU1, it starts doing something again. I didn't know this until very recently and saw immediate benefits in performance, an only one regression due to a necessary oddity in one query that was caused by a nearly perfect cardinality estimation. Changing it to the legacy CE while leaving tf 4199 on improved performance dramatically.
If you are extremely worried about query regression, you can leave it off, many query optimizer fixes are baked into 2016, but there are still going to be new ones that come along. Generally I have found in the last few weeks that I flipped it back on, that the performance benefits of enabling the traceflag have greatly outweighed the one regression incurred.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply