Troubleshooting Performance Problems in SQL Server 2005

It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This article provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005
There is also a word document versionf for download
If you are a SQL Server 2005 developer/administrator then this is a must read

You will be introduced to new views such as sys.dm_exec_sql_text, sys.dm_exec_cached_plan, Sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats

Below is a short list of what is covered in the article

Resource Bottlenecks
Tools for resolving resource bottlenecks

CPU Bottlenecks
Excessive compilation and recompilation
Inefficient query plan
Intra-query parallelism
Poor cursor usage


Memory Bottlenecks
External physical memory pressure
External virtual memory pressure
Internal physical memory pressure
Caches and memory pressure
Ring buffers
Internal virtual memory pressure



Tempdb
Monitoring tempdb space
Troubleshooting space issues
User objects
Version store
Internal Objects
Excessive DDL and allocation operations
Resolution

Slow-Running Queries
Blocking
Identifying long blocks
Blocking per object with sys.dm_db_index_operational_stats
Overall performance effect of blocking using SQL waits
Monitoring index usage


Appendix A: DBCC MEMORYSTATUS Description
Appendix B: Blocking Scripts


Read the whole article here (Troubleshooting Performance Problems in SQL Server 2005)