Wednesday, April 15, 2009

Troubleshooting Performance Problems in SQL Server 2008 white paper

Microsoft made available the Troubleshooting Performance Problems in SQL Server 2008 white paper on their site. This white paper is 102 pages long, the scope of this white paper is limited to the problems commonly seen by Microsoft Customer Service and Support (CSS).

Here is is what is covered

Introduction
Goals
Methodology
Resource Bottlenecks

Tools for Resolving Resource Bottlenecks


CPU Bottlenecks
Excessive Query Compilation and Optimization
Detection
Resolution
Unnecessary Recompilation
Detection
Resolution
Inefficient Query Plan
Detection
Resolution
Intraquery Parallelism
Detection
Resolution
Poor Cursor Usage
Detection
Resolution

Memory Bottlenecks
Background
Virtual Address Space and Physical Memory
AWE, Locked Pages, and SQL Server
Memory Pressures
Detecting Memory Pressures
Tools for Memory Diagnostics
New DMVs in SQL Server 2008
Resource Governor in SQL Server 2008
External Physical Memory Pressure
External Virtual Memory Pressure
Internal Physical Memory Pressure
Caches and Memory Pressure
Ring Buffers
Internal Virtual Memory Pressure
General Troubleshooting Steps in Case of Memory Errors
Memory Errors

I/O Bottlenecks
Resolution

tempdb
Monitoring tempdb Space
Troubleshooting Space Issues
User Objects
Version Store
Internal Objects
Excessive DDL and Allocation Operations
Resolution

Slow-Running Queries
Blocking
Locking Granularity and Lock Escalation
Identifying Long Blocks
Blocking per Object with sys.dm_db_index_operational_stats
Overall Performance Effect of Blocking Using Waits
Monitoring Index Usage
Extended Events
Data Collector and the MDW
Appendix A: DBCC MEMORYSTATUS Description
Appendix B: MDW Data Collection




Download it here: http://msdn.microsoft.com/en-us/library/dd672789.aspx