TheSQLGuru (3/27/2013)

---

There are at least 2 things you should do before even considering that trace flag.

1) look for unnecessary temp object creations. There could be lots of causes for these, from missing indexes leading to hash joins to unnecessary sorts (VERY popular with devs it seems) to just really poor coding (I once had a client that had at least one table variable in about 80% of their 2000+ sprocs!!)

2) after the above if you are still having temp object allocation issues move to more files in tempdb. Start with 1/4 number of physical cores and work up from there to 1/2, 3/4 and 1:1. Note that you can cause IO to actually be SLOWER in some scenarios by doing this.