SQLAuthority News – Troubleshooting Performance Problems in SQL Server 2008

Troubleshooting Performance Problems in SQL Server 2008
SQL Server Technical Article
Writers: Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng, Burzin Patel

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 (https://blog.sqlauthority.com)