DMVs

From SQLServerPedia

What Are Dynamic Management Views (DMVs)?

SQL Server 2005 introduced Dynamic Management Views (DMV's) and Dynamic Management Functions (DMF's) to expose much internal data that was very difficult or impossible to retrieve in SQL Server 2000. DMV's and DMF's are collectively referred to as dynamic management objects; these provide valuable insight into inner workings of software and can be used for examining the state of SQL Server instance, troubleshooting and performance tuning. In addition to providing a handy way of examining indexes, memory and I/O, transactions, locking and waits; dynamic objects also offer detailed information regarding various components of SQL Server.

SQLServerPedia Editor Tim Ford's Favorite DMV Queries

sys.dm_db_index_physical_stats

Provides information about index fragmentation. Sample queries:

Index Defragmentation Script - shows how to detect fragmentation and correct it.
Tim Ford's Top 5 Indexing Best Practices

sys.dm_db_missing_index_details

SQL Server constantly tracks what queries would have benefited from additional indexes. You can query this DMV to find out what indexes you might need to add. Before adding them blindly, though, carefully consider the performance impact. Sample queries:

Find Missing Indexes - queries this DMV and includes an "Impact" estimate to indicate which indexes are more important.

sys.dm_exec_sql_text

This takes a SQL handle or plan handle, and returns the underlying database, object, and text of the query. This dynamic management object is most useful when combined with other DMVs. Sample queries:

Activity Monitor Replacement - T-SQL query that shows what's going on in your server right now, and updates itself every few seconds. Helps DBAs to find out what's going on quickly.

sys.dm_exec_query_plan

Like sys.dm_exec_sql_text, this Dynamic Management Object takes a plan handle, but this one returns the XML query plan. Useful for performance tuning queries that reside in the plan cache. Sample queries:

Find Queries in the Plan Cache That Are Missing an Index - lists queries in the cache, and then the user can click on each line to see the full-blown XML query plan.

sys.dm_exec_connections

When sys.dm_exec_connections, sys.dm_exec_requests, and sys.dm_exec_sessions are used together, the DBA can find out what sessions are open and who's holding transactions open.

Books Online for sys.dm_exec_connections - scroll to the bottom of the page, and there's several examples including one that finds all open transactions that are sitting idle.

sys.dm_os_performance_counters

Just like using Perfmon to access the SQL Server Performance Monitor counters, but it's available through T-SQL too. Only the SQL Server counters are available, though, not the Windows counters.

sys.dm_db_file_space_usage

The name of this DMV is a little misleading - it only gives file space usage for TempDB, not for all user databases. Sample queries:

TempDB file usage query from Louis Davidson

sys.dm_tran_locks

This view returns one row per active lock. SQL Server 2000 users can get locking information with the stored procedure sp_lock. Sample queries:

Show Blocked and Blocking Queries by Grant Fritchey - see the full T-SQL of both the bad query and the one waiting for it to finish. Helps troubleshooting blocking issues.
Examining Current Lock Requests - explains how sys.dm_tran_locks works.

sys.dm_os_waiting_tasks

Sample queries:

sys.dm_exec_cached_plans

This DMV returns a list of all cached query plans. Sample queries:

Find Query Plans that May Utilize Parallelism by Jason Strate

sys.dm_os_memory_objects