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.

Back to the top

Trace flag 652: Disable page pre-fetching scans

Trace flag 652 disables page pre-fetching during scans. You can turn on trace flag 652 at startup or in a user session. When you turn on trace flag 652 at startup, the trace flag has global scope. When you turn on trace flag 652 in a user session, the trace flag has session scope. If you turn on trace flag 652, SQL Server no longer brings database pages into the buffer pool before these database pages are consumed by the scans. If you turn on trace flag 652, queries that benefit from the page pre-fetching feature exhibit low performance.

Back to the top

Trace flag 661: Disable the ghost record removal process

Trace flag 661 disables the ghost record removal process. A ghost record is the result of a delete operation. When you delete a record, the deleted record is kept as a ghost record. Later, the deleted record is purged by the ghost record removal process. When you disable this process, the deleted record is not purged. Therefore, the space that the deleted record consumes is not freed. This behavior affects space consumption and the performance of scan operations.

When you turn on trace flag 661 at startup or in a user session, trace flag 661 always applies across the server and has global scope. If you turn off this trace flag, the ghost record removal process works correctly.

Back to the top

Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool

Trace flag 834 causes SQL Server 2005 to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU.

Trace flag 834 applies only to 64-bit versions of SQL Server 2005. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.

Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server 2005.

For more information about large-page support in Windows, visit the following Microsoft Developer Network (MSDN) Web site:

Back to the top

Trace flag 836: Use the max server memory option for the buffer pool

Trace flag 836 causes SQL Server 2005 to size the buffer pool at startup based on the value of the max server memory option instead of based on the total physical memory. You can use trace flag 836 to reduce the number of buffer descriptors that are allocated at startup in 32-bit Address Windowing Extensions (AWE) mode.

Trace flag 836 applies only to 32-bit versions of SQL Server 2005 that have the AWE allocation enabled. You can turn on trace flag 836 only at startup.

Back to the top

Trace flag 2301: Enable advanced decision support optimizations

Trace flag 2301 enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets.


You can turn on trace flag 2301 at startup or in a user session. When you turn on trace flag 2301 at startup, the trace flag has global scope. When you turn on trace flag 2301 in a user session, the trace flag has session scope.

Back to the top

Trace flags that disable various ring buffers

A ring buffer is an internal diagnostic mechanism in SQL Server 2005 that you can use to record additional information about the server. Typically, you use this information to troubleshoot server problems. You can explore the contents of the ring buffers by using the sys.dm_os_ring_buffers dynamic management view.

Disabling a ring buffer generally improves performance. However, disabling a ring buffer eliminates diagnostic information that Microsoft Support uses and may prevent successful troubleshooting.

The following trace flags disable various ring buffers.

Trace flag 8011: Disable the ring buffer for Resource Monitor

Trace flag 8011 disables the collection of additional diagnostic information for Resource Monitor. You can use the information in this ring buffer to diagnose out-of-memory conditions. Trace flag 8011 always applies across the server and has global scope. You can turn on trace flag 8011 at startup or in a user session.

Trace flag 8012: Disable the ring buffer for schedulers

SQL Server 2005 records an event in the schedule ring buffer every time that one of the following events occurs:

•	A scheduler switches context to another worker.
•	A worker is suspended.
•	A worker is resumed.
•	A worker enters the preemptive mode or the non-preemptive mode.

You can use the diagnostic information in this ring buffer to analyze scheduling problems. For example, you can use the information in this ring buffer to troubleshoot problems when SQL Server 2005 stops responding.

Trace flag 8012 disables recording of events for schedulers. You can turn on trace flag 8012 only at startup.

Trace flag 8018: Disable the exception ring buffer
Trace flag 8019: Disable stack collection for the exception ring buffer

The exception ring buffer records the last 256 exceptions that are raised on a node. Each record contains some information about the error and contains a stack trace. A record is added to the ring buffer when an exception is raised.

Trace flag 8018 disables the creation of the ring buffer, and no exception information is recorded. Trace flag 8019 disables stack collection during the record creation. Trace flag 8019 has no effect if trace flag 8018 is turned on. Disabling the exception ring buffer makes it more difficult to diagnose problems that are related to internal server errors. You can turn on trace flag 8018 and trace flag 8019 only at startup.

Back to the top

Trace flag 8020: Disable working set monitoring

SQL Server 2005 uses the size of the working set when SQL Server 2005 interprets the global memory state signals from the operating system. Trace flag 8020 removes the size of the working set from consideration when SQL Server 2005 interprets the global memory state signals. If you use this trace flag incorrectly, heavy paging occurs, and the performance is poor. Therefore, contact Microsoft Support before you turn on trace flag 8020.

You can turn on trace flag 8020 only at startup.

Back to the top

Trace flag 8744: Disable pre-fetching for ranges

Trace flag 8744 disables pre-fetching for the Nested Loops operator. Incorrect use of this trace flag may cause additional physical reads when SQL Server 2005 executes plans that contain the Nested Loops operator. For more information about the Nested Loops operator, see the "Logical and physical operators reference" topic in SQL Server 2005 Books Online.

You can turn on trace flag 8744 at startup or in a user session. When you turn on trace flag 8744 at startup, the trace flag has global scope. When you turn on trace flag 8744 in a user session, the trace flag has session scope.

Back to the top

For more information about how to turn on or to turn off trace flags and about global trace flags and session trace flags, see the following topics in SQL Server 2005 Books Online:

•	DBCC TRACEON (Transact-SQL)
•	Trace flags (Transact-SQL)
•	DBCC TRACESTATUS (Transact-SQL)
•	Breaking changes to Database Engine features in SQL Server 2005

Back to the top