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

Trace Flags Used With Query Store

Erin Stellato describes two Query Store trace flags: Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752.  The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought […]

Read More

Disabling Nested Loop Join Optimization

Dmitry Pilugin explains the differences between trace flag 2340 and the DISABLE_OPTIMIZED_NESTED_LOOP query hint: This optimization provides a great boost with a sufficient number of rows. You can read more about its test results in the blog OPTIMIZED Nested Loops Joins, created by Craig Freedman, an optimizer developer. However, if the actual number of rows […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930