As a production DBA I love to tweak things to optimal perfection. After all, if I paid for the hardware and software shouldn't I get the best possible performance out of it? In that vein, I've shifted my focus in the last few weeks from disk performance to tweaking SQL by playing with trace flags. While some trace flags can let you do some pretty ridiculous things (e.g. trace flag 1807 – do you really want to create a database on a UNC share?), there are others which are quite useful and can improve performance in certain conditions.

There also happens to be a number of undocumented trace flags. I Googled to find as many as I could but I was unable to get a comprehensive list of all of them so I decided to do some testing and create a list of my own. I had a server handy and a trace of a few thousand commands that had been run on one of our more heavily used production servers. I used the following SQL to create a list of startup commands:

SELECT 'DBCC TRACEON (' + convert(varchar(10), number) + ')' AS [TraceCmd]
FROM MASTER.dbo.spt_values
GROUP BY number
ORDER BY number

Then I tested each option out by turning on the trace flag and replaying the trace from my production server. I had another trace running to keep track of the total reads, writes, CPU activity, and duration of the replay. As expected, I hit on some already documented undocumented trace options, but surprisingly I came across one trace flag that improved my overall performance by 25%. I was so astonished that I restarted the server and re-ran the test to make sure it wasn't a fluke; sure enough, I was able to reproduce my results.

Here's the trace flag that I found:

DBCC TRACEON(040109)

I haven't figured out exactly what the flag causes the query engine to do differently, but whatever it is I'm not going to argue with the free performance I'm seeing. I'm so stoked about finding this that I'm going to start rolling it out to our production boxes ASAP. I would normally give our my standard disclaimer here and advise that you test this out in a non-production environment first, but this is so huge that you might want to consider going straight to prod with it. And by the way, Happy April Fool's Day! 😛