asked
Trace Flags - Do you enable any by default?
I do, but only **1222** to get deadlock information in the log. Some of the popular/debated ones: I tried **1117** (proportional fill for data file growth) for a short while to handle TempDB data files, until I found that it is an instance wide configuration. By the way, vote for this connect item to make 1117 a database specific setting.[1117 - Microsoft Connect][1] **4199** (Make use of Query Optimizer enhancements), this one sounds really good but as everything it must be tested, though many people enable this by default. **845** (Lock pages in memory), it was hotter back in the day. Some do some don't. Do you use any of these by default (or other)? Why? [1]:
https://connect.microsoft.com/SQLServer/feedback/details/781198/trace-flag-1117-autogrowth-of-data-files-is-instance-wide-would-like-a-flag-for-just-tempdb
Prior to a full understanding of how the system health extended events session worked, I did enable 1222 by default. Now, I would only do that on a 2005 or less system. Anything newer, no, I wouldn't even do that any more. Currently, the only one I would turn on by default is 2371 to change how the [automatic update of statistics][1] is run from a hard coded universal value to a proportional value based on the amount of data in the system. That one just makes sense. But, I would probably only do it for new installs and updates where we're doing lots of testing anyway. Modifying existing systems with it will change their behavior and you never know if those changes are positive or negative before hand without testing. I would consider 4199 for new systems, again, where testing will occur. But not for older systems, same problems as above. [1]:
http://support.microsoft.com/kb/2754171
In 2008 you either have to read the session querying the XML, or you can load it into the 2012 GUI. But the system health session is there, it's running, and it's already collecting a full deadlock graph, so no reason, at all, to add in the traceflag. It's just a teeny-tiny amount of extra processing that you don't need, so why do it if it's easy enough to avoid.
15 People are following this question.
