Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Tuning options for SQL Server when running in high performance workloads
Article ID: 920093 - View products that this article applies to.
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:
Note The information in this article also applies to the CTP versions of SQL Server 2008 R2.
Trace flag 652: Disable page pre-fetching scansTrace 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 processTrace 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 poolTrace 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:
Trace flag 836: Use the max server memory option for the buffer poolTrace 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 optimizationsTrace 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 buffersA 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 MonitorTrace 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 schedulersSQL Server records an event in the schedule ring buffer every time that one of the following events occurs:
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 bufferThe 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.
|kbexpertiseadvanced kbsql2005engine kbsql2005tsql kbinfo KB920093|
- Brasil (Português)
- Česká republika (Čeština)
- Deutschland (Deutsch)
- España, Latinoamérica (Español)
- France (Français)
- Indonesia (Bahasa Indonesia)
- Italia (Italiano)
- Portugal (Português)
- România (Română)
- Slovenská Republika (Slovenčina)
- Türkiye (Türkçe)
- Việt Nam (Tiếng Việt)
- Россия (Русский)
- Україна (Україньска)
- भारत (हिंदी)
- ไทย (ไทย)
- 대한민국 (한국어)
- 中国 (简体中文)
- 台灣 (繁體中文)
- 日本 (日本語)