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 is improperly configured for the workload can cause the slowdowns in SQL Server. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective action. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, Performance Monitor, dynamic management views, and SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.


Copyright

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be 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 is 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 is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval 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 have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any 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, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.


© 2009 Microsoft Corporation. All rights reserved.


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


All other trademarks are 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 that 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 system and Windows Server 2008), also known as Perfmon; dynamic management views (sometimes referred to as DMVs); and SQL Server Extended Events (Extended Events) and the data collector, which are 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 an exhaustive analysis of all possible problems is not feasible.
Goals
The primary goal of this paper is to provide a general methodology for diagnosing and troubleshooting SQL Server performance problems in common customer scenarios by using publicly available tools.
SQL Server 2008 has made great strides in supportability. New dynamic management views (DMVs) have been added, like sys.dm_os_memory_brokers, sys.dm_os_memory_nodes, and sys.dm_exec_procedure_stats. Existing DMVs such as sys._dm_os_sys_info, sys.dm_exec_requests, and sys.dm_exec_requests have been enriched with additional 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 is to introduce new troubleshooting tools and features in SQL Server 2008, including Extended Events and the data collector.
Methodology
There can be 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 are 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 lead 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 space 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 longer than expected. There can be many reasons for this. For example:
o Changes in statistical information can lead to a poor query plan for an 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 an improper isolation level for the transaction.
The causes of these symptoms are not necessarily independent of each 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 not to use 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 2008 introduced new features and tools that you can use to monitor and troubleshoot performance problems. We’ll discuss two 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 are 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 lead to excessive paging, which can ultimately impact performance.
Before you can determine whether you have a resource bottleneck, you need to know how resources are used under normal circumstances. You can use the methods outlined in this paper to collect baseline information about the use of the resource (at a time when you are not having performance problems).
You might find that the problem is a resource that is running near capacity and that SQL Server cannot support the workload in its current configuration. To address this issue, you may need to add more processing power or memory, or you may need to increase the bandwidth of your I/O or network channel. However, before you take that step, it is useful to understand some common causes of resource bottlenecks. Some solutions, such as reconfiguration, do not require the addition of more resources.
Tools for Resolving Resource Bottlenecks
One or more of the following tools can be 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 2008 program group. For more information, see SQL Server 2008 Books Online.
• DBCC commands: For more information, see SQL Server 2008 Books Online and Appendix A.
• DMVs: For more information, see SQL Server 2008 Books Online.
• Extended Events: For more information, see Extended Events later in this paper and SQL Server 2008 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 2008 Books Online.