Code CSS

Tuesday, June 12, 2012

SQL Server Trace Flag 4199

You know, sometimes as a software developer I'm forced to do things that feel wrong but necessary, like cobble together some kludge that barely works to meet an insane schedule that someone else came up with. I work at a fairly small-time operation as software companies go, though, so I thought that might be just a side effect of our small team and underdeveloped processes. Surely the big guys do it better, right?

Well I came across something a while back in SQL Server that makes me wonder: trace flag 4199. This obscure flag is set to "off" by default (as are all trace flags) but by turning it on, you enable numerous bug fixes to in the query optimizer. If I understand it correctly, this means no matter what patch or service pack you have added to SQL Server 2008 R2, you still don't get any bug fixes in the query optimizer without turning on trace flag 4199. Presumably this is so that a patch will not start producing different query plans for an established and highly-tuned system, thus causing a nasty performance problem. I get why they did this, but it sure seems like it would be more widely advertised. Frankly, I would have made the default to enable all fixes and provide a flag to turn off the fixes if you found that they affect performance. It seems like the vast majority of SQL Server users and administrators would want all the bug fixes they can get, right?

Anyway, check out the details on trace flag 4199 here, and if you are having trouble with some query plans, it might make sense for you to turn it on:
http://support.microsoft.com/kb/974006

In my case it didn't really help or hurt for most test cases, but it certainly DID create different query plans in some cases, and most of those that were different were better than the old ones.

UPDATE 1/2/2013:
Trace flag 4199 continues to exist in SQL Server 2012, but since I published this post I have encountered a query that actually won't run when it is turned on. One of my larger queries would simple run all day filling up tempdb (over 100GB before I cancelled it) when this flag was turned on in SQL 2012. It wouldn't even generate an estimated query plan in SSMS! Applying SP1 allowed me to view the query plan again, but the query still would fill up tempdb. I had to turn off trace flag 4199 to make the query run again.

There is certainly a lot of room for improvement in my query but this doesn't give me a lot of faith in the "bug fixes" that trace flag 4199 is supposed to include. It seems it probably creates as many bugs as it fixes. It honestly gives me the feeling that SQL Server query optimizer has turned into such a beast that the developers at Microsoft aren't confident in their ability to fix problems in it without creating massive new problems.

1 comment:

  1. More info can be found here on 4199
    http://www.sqlservercentral.com/articles/trace+flags/70131/

    ReplyDelete