Microsoft knowledge base article 920093 discusses various trace flags as tuning options in Microsoft SQL Server 2005. From the article:

INTRODUCTION

This article describes various trace flags as tuning options in Microsoft SQL Server 2005. You can use these trace flags to improve the performance of SQL Server 2005. Typically, you use these trace flags when SQL Server 2005 is running in high performance workloads.

Note The trace flags that this article describes are advanced tuning techniques. You should consider using these trace flags only after you do more basic and routine optimizations. For example, you should consider using these trace flags after you do the following optimizations:

• Index analysis

• I/O balancing

• SQL query profiling

• System statistics monitoring

Additionally, these trace flags are mainly helpful on high-end servers that have the following characteristics:

• Many CPUs, for example, more than eight CPUs

• Lots of main memory, for example, more than 8 gigabytes (GB) of memory

• High I/O rates, for example, more than 10,000 physical I/O per second or more than 500 megabytes (MB) per second

The trace flags that this article describes may decrease performance under some workloads. We recommend that you evaluate the effects on your workload on a test system before you deploy any changes in a production environment.

Read the rest of the article here.