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

List of SQL 2005 DMVs

-- OS Related
select * from sys.system_objects
where name like 'DM_OS%'
GO

-- Exec Related
select * from sys.system_objects
where name like 'DM_exec%'
GO

-- Transaction Related
select * from sys.system_objects
where name like 'DM_tran%'
GO

-- Replication related
select * from sys.system_objects
where name like 'DM_repl%'
GO

-- IO Releated
select * from sys.system_objects
where name like 'DM_IO%'
GO

-- clr Releated
select * from sys.system_objects
where name like 'DM_CLR%'
GO

-- db index related

select * from sys.system_objects
where name like 'DM_db%'
GO


-- broker related

select * from sys.system_objects
where name like 'DM_broker%'
GO

-- fts related
select * from sys.system_objects
where name like 'DM_fts%'
GO

-- qn related
select * from sys.system_objects
where name like 'DM_qn%'
GO

SQL 2005

DSQuery

Following command gives OU details of a server
c:\winnt\system32>dsquery computer -name xxxxxxxxx