colin.Leversuch-Roberts (3/11/2013)
Generally do you run with this on or off?I've always been slightly worried about setting this trace flag to ON as I don't really have any load like production on any other environment; however I'm thinking running without optimiser fixes may not be the best idea, for the system in question there's a lots of very complex and heavy sql and any performance improvements would be helpful < grin >
As you note there are quite a few optimizer "fixes" covered by that TF. The issue as always is what if a particular system or even single query gets regressed by one of those fixes?? That could be devastating for a particular application. You either test fully to KNOW you are good or you put it on and hope for the best. There is no middle ground here IMHO. I will say that I am in agreement with you and WANT those fixes enabled if I don't get those ugly regressions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service