Tuning options for SQL Server when running in high performance workloads

原文链接：https://support.microsoft.com/en-us/kb/920093

Contents

INTRODUCTION

This article describes various trace flags as tuning options in Microsoft SQL Server 2005 and SQL Server 2008. You can use these trace flags to improve the performance of SQL Server. Typically, you use these trace flags when SQL Server 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.

Note The information in this article also applies to the CTP versions of SQL Server 2008 R2.

More information

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.

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.

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

Trace flag 834 causes SQL Server 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. 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.

Note If you are using the Column Store Index feature of SQL Server 2012, we do not recommend turning on trace flag 834.

For more information about large-page support in Windows, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/aa366720.aspx

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

Trace flag 836 causes SQL Server 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 that have the AWE allocation enabled. You can turn on trace flag 836 only at startup.

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.

Trace flags that disable various ring buffers

A ring buffer is an internal diagnostic mechanism in SQL Server 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 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 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.

Trace flag 8020: Disable working set monitoring

SQL Server uses the size of the working set when SQL Server 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 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.

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 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.

The following table provides more information about the products or tools that automatically check for the condition that is described in the “Symptoms” section in your instance of SQL Server and in the versions of SQL Server against which the rule is evaluated.

Rule software Rule title Rule description Product versions against which the rule is evaluated System Center Advisor SQL Server with Column store index and Trace Flag 834 might cause instability of Server In this SQL Server instance advisor detected the presence of one or more column store index along with Trace Flag 834 (large pages for buffer pool). This can cause Server instability. We do not recommend enabling this Trace Flag when you are using Column store index. SQL Server 2012

References

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

Properties

Article ID: 920093 - Last Review: 01/02/2014 17:26:00 - Revision: 5.0
Applies to

Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Express EditionMicrosoft SQL Server 2005 Express Edition with Advanced ServicesMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Enterprise Edition for Itanium-based SystemsMicrosoft SQL Server 2005 Enterprise X64 EditionMicrosoft SQL Server 2005 Standard Edition for Itanium-based SystemsMicrosoft SQL Server 2005 Standard X64 EditionMicrosoft SQL Server 2008 StandardMicrosoft SQL Server 2008 EnterpriseMicrosoft SQL Server 2008 DeveloperMicrosoft SQL Server 2008 ExpressMicrosoft SQL Server 2008 Express with Advanced ServicesMicrosoft SQL Server 2008 WorkgroupMicrosoft SQL Server 2008 R2 ExpressMicrosoft SQL Server 2008 R2 EnterpriseMicrosoft SQL Server 2008 R2 DeveloperMicrosoft SQL Server 2008 R2 StandardMicrosoft SQL Server 2012 ExpressMicrosoft SQL Server 2012 EnterpriseMicrosoft SQL Server 2012 DeveloperMicrosoft SQL Server 2012 Standard

Keywords:

kbexpertiseadvanced kbsql2005engine kbsql2005tsql kbinfo KB920093