Understanding SQL 2005 DMV and DMF

sys.dm_exec_query_stats -

Returns performance statistics for cached query plans. This contains one row per query plan so if a stored procedure or batch contains two SELECT statements you may get two rows here.

sys.dm_exec_sql_text -

Returns the text of the sql statement based on the SQL handle

sys.dm_exec_query_plan -

Returns the showplan in XML format for a batch or module based on the plan handle.


dm_db_index_usage_stats –

displays how often each index is used, show Read Seeks, Read Scans, Write Seeks and Write Scans

dm_db_index_physical_stats –

replace DBCC SHOWCONTIG used to check index fragmentation.

dm_db_index_operational_stats –

use to check for index contention and blocking

dm_os_wait_stats –

Used to determine the types of waits (signal or resource that are occurring)

dm_os_waiting_tasks –

Used to help identify blocking

dm_tran_locks –

Used to help indentify blocking

dm_os_performance_counters –

Query Access to SQL Server Performance counters