Global Trace Flags Are Global

Kendra Little points out that when you turn a trace flag on globally, it cannot be off for individual sessions:

In SQL Server 2016, you can now enable the very same optimizer hotfixes controlled by Trace Flag 4199 at the database scope by using ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON.

If you have the setting configured at the database level, it’s much easier to test what would happen if the setting was NOT enabled, because you can compile your query from a different database.

Interesting results.  Check it out.

Related Posts

Creating A Spinlock Convoy

Lonny Niederstadt explains how two performance issues can intertwine in an interesting manner: I’d seen systems that implemented both trace flags as startup parameters simultaneously.  I’d helped organizations implement first T8048, then T4199 (based on the timing of my research and testing of the trace flags).  This was the first time that there was a […]

Read More

Trace Flag Basics

Erin Stellato explains the basics behind trace flags in SQL Server: Trace flag 1118 addresses contention that can exist on a particular type of page in a database, the SGAM page.  This trace flag typically provides benefit for customers that make heavy use of the tempdb system database.  In SQL Server 2016, you change this behavior […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

April 2017
MTWTFSS
« Mar  
 12
3456789
10111213141516
17181920212223
24252627282930