Compare wait stats snapshots over a chosen window, subtract cumulative counters, remove background waits, and calculate rates and average waits.
Why One Query Has Two Cached Plans: SET Options and plan_attributes
Investigate two cached plans with plan_attributes, decode differing SET options, and match application settings before diagnosing skew.
Seeing What Is Running Right Now
See what is running right now in SQL Server with request DMVs, statement text, plans, waits, and blocking details in one reusable query.
SQL SERVER – 2008 – Find If Index is Being Used in Database
How do you find if an index is being used in your database? My quick script checks sys.dm_db_index_usage_stats, so you can drop unused indexes safely.
Finding the Client IP Address of a Connection
Find the client IP address of a SQL Server connection with DMVs and CONNECTIONPROPERTY, and understand proxy and local transport limits.
Three Scripts for a First Look at Any Server
A first look at any server needs three read-only T-SQL checks: configuration, databases and backups, and current activity.
SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info
While browsing Books Online I found sys.dm_exec_query_optimizer_info, which returns statistics about the query optimizer to help you tune a workload.