ITKeyword,专注技术干货聚合推荐

注册 | 登录

Tuning options for SQL Server when running in high performance workloads

wstoneh01 分享于 2016-01-29

推荐:Versatile High Performance Hierarchies in SQL Server

Versatile High Performance Hierarchies in SQL Server Page 1 of 3 - by Dennis W. Forbes Introduction Figure 1 : Inverted Tree Figure 2 : Corporate Repo

2020阿里云最低价产品入口,含代金券(新老用户有优惠),
地址https://www.aliyun.com/minisite/goods

原文链接:https://support.microsoft.com/en-us/kb/920093 Contents Contents INTRODUCTION More information 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 flags that disable various ring buffers 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 References Properties 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

原文链接:https://support.microsoft.com/en-us/kb/920093 Contents Contents INTRODUCTION More information Trace flag 652 Disable page pre-fetching scans Trace flag 661 Disable the gh

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。