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
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)
- SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.
White paper contains following subjects:
Introduction
Goals
Methodology
Resource Bottlenecks
Tools for Resolving Resource Bottlenecks
CPU Bottlenecks
Memory Bottlenecks
I/O Bottlenecks
tempdb
Slow-Running Queries
Extended Events 80
Data Collector and the MDW
Appendix A: DBCC MEMORYSTATUS Description
Appendix B: MDW Data Collection
Download “Troubleshooting Performance Problems in SQL Server 2008“
Abstract courtesy : Microsoft
Reference : Pinal Dave (http://blog.SQLAuthority.com)
