SQL Server: Win some, learn some... try a whole buncha

***Update 20140625***
Amit Banerjee (twitter: @banerjeeamit) indicated that T345, listed below, no longer applies to current SQL Server builds.  Thanks, Amit!
***End update***

It isn't that I am opposed to query tuning... far from it!  However, I'm in a rather unusual position where changing the SQL text of any given query may require waiting for changes in two separate products from two different vendors.  Then waiting for adoption of those versions.  There are only a few things I'm good at - waiting isn't one of them.  Until then index can be added (or removed), stats and index maintenance strategies can be implemented and modified... but not too much in terms of tuning individual queries lest the customizations make future package upgrades more precarious.

So I do everything I can to tune the underlying hardware/driver/filesystem/OS system.  The idea is to deliver as much reliability and performance capacity as possible, and coax the database into leveraging those attributes as much as possible.

This is why I spend a lot of time thinking and writing about NUMA, memory management, disk IO optimization and the like.

Its also the reason I spend so much time learning about SQL Server trace flags.  Sometimes, the database can be encouraged to make better use of the system performance capacity for my workloads with a trace flag.  That is certainly the case with T8048, which removes a significant bottleneck in stealing query memory when there are multiple queries per NUMA node (or scheduler group) that are stealing lots of query memory.  There are other trace flags that have the effect of 'tuning' sets of queries, all at the same time.  For example, the enhanced join ordering available with trace flag 4101.  That one really helped me out of a jam - I saw some memory grants drop in size from ten GB or more to 1 mb with no other change than adding that trace flag.  (Tested benefits and looked for problems first with querytraceon, then promoted it to instance-wide enabled.)

So this year, here are some trace flags that I'll be evaluating with my workloads.   Not a lot of info about them.  As I test with them in SQL Server 2012 and 2014 I hope to provide some details about what I see - especially if I see no discernible difference at all.

T342
T345   http://support.microsoft.com/kb/625072/it
T2328 http://blogs.msdn.com/b/ianjo/archive/2006/03/28/563419.aspx
T4138 http://support.microsoft.com/kb/2667211
T9082 http://support.microsoft.com/kb/942906

For some information on these and many other trace flags, you can check out this post, and the accompanying pdf.
http://sqlcrossjoin.wordpress.com/2013/10/28/a-topical-collection-of-sql-server-flags/

Here's my disclaimer: trace flags are not to be trifled with.  Test them in isolation if possible first on a nonprod system.  Measure the effects, compare to expected/desired behavior and baseline.  When possible, test in full context (full scale workload at full concurrency) in nonproduction before promoting to production.