ITMS Administrator Group

 View Only

SQL 2008 - 2012 Query Optimizer Trace Flag 4199 - Increase performance 

May 13, 2015 10:45 AM

Our team came across a great blog on TechnologyMountain.com written by Benjamin Pierce about SQL Query Optimizer being disabled in 2008 sp1 and up. Once we turned this on our large query speeds have gone from 25 minutes to 21 seconds.Here is the article talking about using Trace Flag 4199 to turn on the Query Optimizer.

 

Trace Flag 4199 and the SQL Query Optimizer

We recently had a problem in our production environment where queries against a partitioned view were not utilizing any of the indexes on the underlying tables, even though the indexes were correctly designed. After much research I discovered that this was a problem with the Query Optimizer and that Microsoft had fixed this in SQL Server 2008 SP1. The thing that struck me was that we were on SQL Server 2008 R2, so why was this fix not working properly?

After a bit of research, I was shocked to discover that Query Optimizer fixes are not automatically enabled no matter how many service packs you have installed or how many hotfixes you apply. This means that, by default, you'll always be running the most unpatched and potentially buggy version of the Query Optimizer released for a given SQL Server version!

I can understand Microsoft's thinking around this - if you have highly optimized queries that rely on specific execution plans, changes to the Query Optimizer, even if beneficial, could have unwanted side-effects. To get around this, Microsoft wants you to make a conscious decision to enable Query Optimizer fixes by enabling trace flag 4199.

Where I disagree with Microsoft on this is that I believe the default upgrade behavior should be that all Query Optimizer fixes are automatically turned on and that you should have to turn on trace flag 4199 to DISABLE these optimizer fixes. Most companies I've worked at do not have queries that rely on specific query plans and I can guarantee you 90% of the companies out there don't know about trace flag 4199; consequently, they're not getting any benefit from some pretty important fixes that have been released over the years.

But on these points I digress, turning on trace flag 4199 is actually fairly easy and I wanted to document it in the hopes that it might help out other teams that are running across similar query anomalies. To enable trace flag without any downtime, follow these steps:

      DBCC TRACEON (4199, -1)    -- Turn on the trace flag globally (for all sessions)

      -- Clear the cache because we don’t want any old, inefficient execution plans hanging around.
      CHECKPOINT;
      GO
      DBCC DROPCLEANBUFFERS;
      GO
      DBCC FREEPROCCACHE
      GO 

This will take care of turning the trace flag on for current sessions, but if you restart the SQL Engine this trace flag will turn itself off again!

To ensure this trace flag will always be set, you need to modify the startup properties of your SQL Server Windows Service so that you specify the -T4199 parameter as follows:

traceflag

To confirm that this trace flag is properly set at any time, you can run this query:

    DBCC TRACESTATUS

This will show you which trace flags are enabled for your session and which ones are enabled globally. You'll want to make sure that this command shows that the trace flag is set globally in order for all sessions to take advantage of these query optimizations.

A few points worth noting with regards to what we noticed in setting this up:

  • Just to be safe, we set this up in our dev environment for a few weeks to make sure there were no strange side-effects (which there weren't).
  • When I turned on the trace flag via the TRACEON command, my colleagues could see the trace flag set globally via the TRACESTATUS command, yet their queries were still being run with the buggy query plan at random. Closing their existing session and re-opening it didn't seem to make a difference; however, closing all their sessions by closing down Management Studio did the trick. I'm not sure why this is, but SQL Server seems to cache bad execution plans at the user level rather than the individual session level.
  • If you have this trace flag turned on, it might be worth holding off on upgrading to new service packs as soon as they're released by Microsoft. If there happens to be a query optimizer bug released with a new service pack this would immediately affect your system with this trace flag turned on. Much more testing and rigor is required, however the benefits outweigh the extra testing costs in my opinion.

If you've been running with trace flag 4199 turned on for awhile, I'd love to hear your experiences in the comments.

Statistics
0 Favorited
0 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Related Entries and Links

No Related Resource entered.