Till some time ago, I used to direct my customers to http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx for obtaining scripts which use the DMVs in SQL 2005/2008 to retrieve very commonly used performance troubleshooting related information. Those scripts were contributed by Microsoft’s SQL CAT team.

Unfortunately that link no longer works correctly. Luckily though the scripts are still available at different places on Script Center, and here is quick compilation of those while the original link is fixed.

SQLOS / Execution Model

List SQLOS Execution Model Information

List Runnable Queues

List Scheduler Wait List Information

Wait Statistics

List Top Wait Types for a Workload

Retrieve Waitstat Snapshots

Compare Signal Waits and Resource Waits

List Statements from a Specified Waiter List

 

Query Compilation

Determine CPU Resources Required for Optimization

List Recompiled Statements

Compare Single-Use and Re-Used Plans

Query Statistics

Retrieve Statements with the Highest Plan Re-Use Counts

Retrieve Statements with the Lowest Plan Re-Use Counts

List Statements with the Highest Execution Counts

List Statements By Input/Output Usage

List Statements With the Highest Average CPU Time

Query Execution

Retrieve a SQL Statement with a Specified .SQL_Handle

Retrieve SQL Text and XML Plans

List Currently-Executing Statements

List Real Time Tempdb Task Usage

List Real-Time Tempdb Statements

List Currently-Executing Parallel Plans (the script at this link is actually the script for the next item :-))

List Cached Plans Where Worker Time Exceeds Elapsed Time (the script at this link is actually the script for the previous item :-))

I/O and Buffer cache

Calculate Average Stalls

Retrieve Buffer Counts by Object and Index

Indexes

List Indexes With the Most Contention

Retrieve Tables, Indexes, Files, and File Groups Information

Determine Index Cost Benefits

Retrieve Object and Index Fragmentation Information

Analyze Index Statistics

Identify Missing Indexes

Retrieve Index Usage Statistics

Retrieve Indexes Not Used Since the Last Recycle Time

List Rarely-Used Indexes

Locking and Blocking

Report Blocker and Waiter SQL Statements (sp_block_info)

 

I hope this compilation is useful to you!