Trace flags can be used to change the behavior of how SQL server schedules/does its tasks. In high workload systems, these settings can be tweaked to squeeze more performance out of the SQL server. Always use caution when using trace flags and test these settings thoroughly before using them.
MS KB link to the details of the TFs. Some of the things that can be done:
- Trace flag 652: Disable page pre-fetching scans.
- Trace flag 661: Disable the ghost record removal process
- Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool
- Trace flag 836: Use the max server memory option for the buffer pool
- Trace flag 2301: Enable advanced decision support optimizations
- Trace flag 8011: Disable the ring buffer for Resource Monitor
- Trace flag 8012: Disable the ring buffer for schedulers
- Trace flag 8018: Disable the exception ring buffer
- Trace flag 8019: Disable stack collection for the exception ring buffer
- Trace flag 8020: Disable working set monitoring
- Trace flag 8744: Disable pre-fetching for ranges
- Trace flag 610: (SQL 2008) Controls minimally logged inserts into indexed tables.
- There is also a start-up parameter that can be used. The –x flag on starting SQL Server reduces the time SQL Server spends collecting performance statistics at run-time.
- Trace flag 1117: keeps data files the same size in case DB needs to auto grow. This is more for the tempdb, when there are multiple data files. Note that this is a server wide setting.