Troubleshooting SQL Server 2008 Performance (i)--Introduction

Original: SQL Server 2008 Performance Troubleshooting (i)--Introduction

Remark: I spent a lot of work time translation, no plagiarism, allow reprint, but please indicate the source. Because of the length, not a post all finished, but also not so fast all translated, so according to the chapter published. Due to my limited level, translation results must be problematic, in order not to mislead, at the end of each attached to the original text for your reference, but also hope to point out my problem, in order to improve. Thank you.

In addition, this article is written to a slightly experienced database developer or DBA who may not be able to read. Please forgive me here.

author : Sunil Agarwal, Boris Baryshnikov, Keithelmore, Juergen Thomas, Kun Cheng, Burzin Patel

Technical Review : Jerome Halmans, Fabricio Voznika,george Reynya

released : March 2009

applies to : SQL Server 2008

Profile:Sometimes poor database design for a workload or incorrect system configuration can cause SQL Server to run slowly. The DBA needs to proactively prevent or minimize the problem, and when the problem occurs, diagnose the cause of the problem and respond correctly. This article provides step-by-steps guidelines for using publicly available tools such as SQL Server Profiler, Performance Monitor, DMV, SQL Server expansion eventsAnd Data CollectorTo diagnose and troubleshoot common performance issues.

Copyright: This section is omitted, please respect other people's labor results can

Brief introduction:The occasional slow run of SQL Server is an uncommon phenomenon. The general reason can be attributed to poor database design or incorrect system configuration for a workload. As a DBA, you need to proactively avoid or minimize problems. When a problem occurs, you need to diagnose the cause and make the right response. This whitepaper provides tools such as SQL Server Profiler, Performance Monitor, DMV, SQL Server expansion events, and data collectors to diagnose and troubleshoot common performance issues. This paper limits the scope of the problem to what some customers often reflect, because it is unrealistic to analyze all possible problems.

Goal:The main purpose of this article is to provide a general approach, primarily a number of publicly available tools for diagnosing and troubleshooting. SQL Server 2008 has a significant boost in support. Added some new dynamic management Views (DMV): such as Sys.dm_os_memory_brokers,sys.dm_os_memory_nodes,sys.dm_exec_procedure_stats. There are already (2005) of the DMV such as: Sys.dm_os_sys_info,sys.dm_exec_requests and sys.dm_exec_requests also added a lot of new information. You can use the DMV and collect performance-related data for analysis using existing tools such as SQL Server Profiler, Performance Monitor.
The second goal is to introduce the New troubleshooter and 2008 features, including the expansion event (Extended events) and the Data Collector (collector)


Methodology:

There may be many reasons why SQL Server is slow, and this article begins with the following 3 main symptoms:

• Resource bottlenecks (Resource bottlenecks): CPU, memory, and I/O bottlenecks are all mentioned in this article. We do not consider network problems. In each resource bottleneck, we describe how to identify the problem and then iterate to check for possible causes. For example, a memory bottleneck can cause too many page transitions to affect performance.
• Tempdb bottleneck: Because in each instance of SQL Server, only one tempdb can be used by each database, so it can become a performance problem and a hard disk space bottleneck. An application may overload tempdb because of excessive DDL or DML operations, and consuming too much resources. This can cause non-correlated applications running on the same server to become slow or even fail to run.
• A slow-running user query: An existing query can affect performance, or a new query consumes more resources than imagined. This is generally caused by the following reasons:

1. A change in the statistics of an existing query causes the optimizer to choose a performance-poor execution plan.
2. Missing indexes will cause the table scan to be forced and the query speed to slow down.
3, the application will also affect performance because of blocking, even if the resource utilization is normal.
4, some bad applications, unreasonable architecture design, or the use of inappropriate transaction isolation level, will lead to excessive blocking.
These reasons should not be analyzed separately, and inefficient execution plans can exacerbate the use of system resources and cause overall performance degradation of workloads. Therefore, if a large table loses a valid index, or the query optimizer does not choose to use the index, the query will be very slow. These conditions also put a lot of pressure on the I/O subsystem's read operations, because I had to read some pages that were not necessary or had already been cached in memory. The excessive compilation of a program that is similar to a regular run will put a strain on the CPU.

• New performance tools in SQL Server 2008: SQLServer2008 provides new tools and features to assist you in monitoring and troubleshooting. Our main discussion is: expansion events and data collectors

Resource bottlenecks (Resource bottlenecks):In the next section, you will discuss CPU, memory, and I/O subsystem resources, and discuss under what circumstances they will become bottlenecks (the network part is not covered in this article). For each resource bottleneck, we will discuss how to identify you by asking him and then iterate over the possible causes. Memory bottlenecks, for example, can result in excessive paging, which can affect performance.
Before you can judge a performance bottleneck, you must know how resources are used under normal circumstances. You can use the methods described in this article to collect performance baselines. That is, performance data before there is no performance problem.
You may find that the resources are working properly, but SQL Server cannot support the corresponding load under the current configuration. To solve this problem, you may have to add more and more powerful resources, such as memory, increase your current I/O or network bandwidth. But before you do that, you need to understand the general causes of resource bottlenecks first. Some solutions, such as reconfiguration, do not necessarily add resources.


Tools to Address resource bottlenecks:
One or more of the following tools can be used to troubleshoot partial performance issues:
 Performance Monitor (Performance Monitor): Available in some Windows operating systems, see the Windows documentation for detailed information.
sqlserver Profiler: You can find it in the Performance Tools group of SQL Server to view Books Online.
DBCC command: You can view Appendix A and Books Online.
dmvs: View Books Online in detail.
 Expansion event (Extended events): You can view the Extended events section and Books Online that are mentioned later.
 Data collector and the Management Data Warehouse (MDW): You can view the information collector and MDW sections mentioned later and Books Online.

Next section: CPU bottlenecks

Original:

Troubleshooting performance Problems in SQL Server 2008
SQL Server Technical article

Writers:sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng, Burzin Patel
Technical reviewers:jerome Halmans, Fabricio Voznika, George Reynya

Published:march 2009
Applies To:sql Server 2008

Summary:sometimes a poorly designed database or a system that's improperly configured for the workload can cause the SLO wdowns in SQL Server. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take correcti ve action. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using public LY available tools such as SQL Server Profiler, performance Monitor, dynamic management views, and SQL Server Extended Eve NTS (Extended Events) and the Data Collector, which is new in SQL Server 2008.


Copyright

The information contained in this document represents the current view of the Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should is interpreted to is a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This white paper are for informational purposes only. MICROSOFT makes NO warranties, EXPRESS, implied, OR statutory, as to the information in this DOCUMENT.

Complying with all applicable copyright laws are the responsibility of the user. Without limiting the rights under copyright, no part of the This document is reproduced, stored in, or introduced into a R Etrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise ), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may be patents, patent applications, trademarks, copyrights, or other intellectual property rights covering SU Bject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not giv E license to these patents, trademarks, copyrights, or other intellectual property.

Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, PLA CES, and events depicted herein is fictitious, and no association with any real company, organization, product, domain NA Me, e-mail address, logo, person, place, or event was intended or should be inferred.


? Microsoft Corporation. All rights reserved.


Microsoft, MSDN, SQL Server, Win32, Windows, Windows Server, and Windows Vista are either registered trademarks or Tradema Rks of Microsoft Corporation in the States and/or and other countries.


All other trademarks is property of their respective owners.



?
Table of Contents
Introduction 1
Goals 1
Methodology 1
Resource Bottlenecks 2
Tools for resolving Resource bottlenecks 2
CPU Bottlenecks 3
Excessive Query compilation and optimization 4
Detection 5
Resolution 7
Unnecessary recompilation 9
Detection 10
Resolution 13
Inefficient Query Plan 14
Detection 15
Resolution 15
Intraquery Parallelism 16
Detection 18
Resolution 21
Poor Cursor Usage 21
Detection 22
Resolution 23
Memory bottlenecks 23
Background 23
Virtual Address Space and physical Memory 23
AWE, Locked Pages, and SQL Server 23
Memory pressures 25
Detecting Memory pressures 26
Tools for Memory Diagnostics 26
New DMVs in SQL Server 2008 27
Resource Governor in SQL Server 2008 27
External Physical Memory Pressure 28
External Virtual Memory Pressure 30
Internal Physical Memory Pressure 30
Caches and Memory pressure 36
Ring Buffers 37
Internal Virtual Memory Pressure 43
General troubleshooting Steps in case of Memory Errors 44
Memory Errors 44
I/O bottlenecks 48
Resolution 52
Tempdb 56
Monitoring tempdb Space 58
Troubleshooting Space Issues 59
User Objects 59
Version Store 60
Internal Objects 62
Excessive DDL and Allocation Operations 65
Resolution 66
Slow-running Queries 66
Blocking 67
Locking granularity and Lock escalation 69
Identifying Long Blocks 71
Blocking per Object with Sys.dm_db_index_operational_stats 74
Overall performance Effect of Blocking Using Waits 75
Monitoring Index Usage 78
Extended Events 80
Data Collector and the MDW 88
Appendix A:DBCC Memorystatus Description 95
Appendix B:mdw Data Collection 96



Introduction
It ' s not uncommon to experience the occasional slowdown of a database running the Microsoft? SQL Server? Database software. The reasons can range from a poorly designed database to a system, which is improperly configured for the workload. As an administrator, you want to proactively prevent or minimize problems; If they occur, you want to diagnose the cause and take corrective actions to fix the problem whenever possible. This white paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using Publicly available tools such as SQL Server Profiler; System Monitor (in the Windows Server. 2003 operating System) or performance Monitor (in the Windows Vista? Operating Syst Em and Windows Server), also known as Perfmon; dynamic management Views (sometimes referred to as DMVs); and SQL Server Extended events (Extended events) and the Data Collector, which is new in SQL Server 2008. We have limited the scope of this white paper to the problems commonly seen by Microsoft Customer Service and support, because a exhaustive analysis of all possible problems are not Feasible.
Goals
The primary goal of this paper are to provide a general methodology for diagnosing and troubleshooting SQL Server Performan Ce problems in common customer scenarios by using publicly available tools.
SQL Server made great strides in supportability. New dynamic management Views (DMVs) has been added, like Sys.dm_os_memory_brokers, Sys.dm_os_memory_nodes, and Sys.dm_exe C_procedure_stats. Existing DMVs such as Sys._dm_os_sys_info, sys.dm_exec_requests, and sys.dm_exec_requests has been enriched with addition Al information. You can use DMVs and existing tools, like SQL Server Profiler and performance Monitor, to collect performance related data for analysis.
The secondary goal of this paper are to introduce new troubleshooting tools and features in SQL Server, including exte Nded Events and the Data collector.
Methodology
There can many reasons for a slowdown in SQL Server. We use the following three key symptoms to start diagnosing problems:
? Resource bottlenecks:cpu, memory, and I/O bottlenecks is covered in this paper. We do not consider network issues. For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes. For example, a memory bottleneck can leads to excessive paging that ultimately impacts performance.
? Tempdb Bottlenecks:because There is only one tempdb for each SQL Server instance, it can be a performance and a disk SPAC E bottleneck. An application can overload tempdb through excessive DDL or DML operations and by taking too much space. This can cause unrelated applications running on the server to slow down or fail.
? A slow-running user query:the performance of an existing query might regress, or a new query might appear to be taking Lo Nger than expected. There can many reasons for this. For example:
o Changes in statistical information can leads to a poor query plan for a existing query.
o Missing indexes can force table scans and slow down the query.
o An application can slow down due to blocking even if resource utilization is normal.
o Excessive blocking can be due to poor application or schema design or the choice of a improper isolation level for the Transaction.
The causes of these symptoms is not necessarily independent of all other. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or if the query optimizer decides does not have it, the query can slow down; These conditions also put heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer Pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently-run query can put pressure on the CPU.
NEW performance Tools in SQL Server 2008
SQL Server introduced new features and tools that you can use to monitor and troubleshoot performance problems. We ' ll discuss the features:extended Events and the Data collector.
Resource bottlenecks
The next sections of this paper discuss CPU, memory, and I/O subsystem resources and how these can become bottlenecks. (Network issues is outside of the scope of this paper.) For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes. For example, a memory bottleneck can leads to excessive paging, which can ultimately impact performance.
Before you can determine whether you had a resource bottleneck, you need to know how resources is used under normal circ Umstances. You can use the methods outlined in this paper to collect baseline information for the use of the resource (at a time wh En you is not having performance problems).
You might find the problem are a resource that's running near capacity and that SQL Server cannot support the Workloa D in its current configuration. To address this issue, if you are need to add more processing power or memory, or if you are need to increase the bandwidth of Y Our I/O or network channel. However, before you take that step, it's useful to understand some common causes of resource bottlenecks. Some solutions, such as reconfiguration, do not require the addition for more resources.
Tools for resolving Resource bottlenecks
One or more of the following tools can is used to resolve a particular resource bottleneck:
? Performance Monitor:this tool is available as part of the Windows? Operating system. For more information, see your Windows documentation.
? SQL Server Profiler:see SQL Server Profiler in the performance Tools group in the SQL Server program group. For more information, see SQL Server Books Online.
? DBCC commands:for more information, see SQL Server Books Online and Appendix A.
? Dmvs:for more information, see SQL Server Books Online.
? Extended events:for more information, see Extended Events later in this paper and SQL Server Books Online.
? Data collector and the Management Data Warehouse (MDW): For more information, see Data Collector and the MDW later in this Paper and SQL Server Books Online.

Troubleshooting SQL Server 2008 Performance (i)--Introduction