What dynamic management views (DMV) or dynamic management functions (DMF).
There are well over 150 DMV/DMFs spread across 20 some categories in 2012 now
Execution Related
· sys.dm_exec_connections
· sys.dm_exec_sessions
· sys.dm_exec_requests
· sys.dm_exec_cached_plans
· sys.dm_exec_query_plans
· sys.dm_exec_sql_text
· sys.dm_exec_query_stats
Index Related
· sys.dm_db_index_physical_stats --> Returns size and fragmentation information for the data and indexes of the specified table or view.
· sys.dm_db_index_usage_stats --> Returns counts of different types of index operations and the time each type of operation was last performed.
· sys.dm_db_index_operational_stats--> Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
sys.dm_db_missing_index_columns --> Returns information about database table columns that are missing an index, excluding spatial indexes.
sys.dm_db_missing_index_details --> Returns detailed information about missing indexes, excluding spatial indexes.
sys.dm_db_missing_index_groups --> Returns information about what missing indexes are contained in a specific missing index group, excluding spatial indexes
SQL Server Operating System
· sys.dm_os_performance_counters
· sys.dm_os_schedulers
· sys.dm_os_nodes
· sys.dm_os_waiting_tasks
· sys.dm_os_wait_stats
I/O Related
· sys.dm_io_virtual_file_stats
Get a Listing of All Connections:
SELECT * FROM sys.dm_exec_connections
Get a Listing of All Sessions
select * FROM sys.dm_exec_sessions
Get a listing of all user connections:
SELECT c.session_id, c.auth_scheme, c.node_affinity
, s.login_name
, db_name(s.database_id) AS database_name
, CASE s.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncomitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
, s.status
, c.most_recent_sql_handle
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
http://sqlcan.wordpress.com/2012/05/25/a-microsoft-sql-server-dmvdmf-cheat-sheet/
sys.dm_exec_requests
You can use the sys.dm_exec_requests dynamic management view to obtain detailed information about the requests currently executing on SQL Server.The columns you are most likely to use when troubleshooting a block or deadlock are as follow:
blocking_session_id - The SPID of the blocking session.
wait_type - Type of wait.
wait_time - Length of time request has been waiting (in milliseconds).
last_wait_type - If a wait has ended, its type is listed here.
wait_resource - Name of resource the request is waiting for.
transaction_isolation_level - Isolation level for the transaction.
lock_timeout - Length of time a lock can exist before timing out
To view blocked processes execute the following query:
USE [master]
GO
SELECT session_id ,blocking_session_id ,wait_time ,wait_type ,last_wait_type ,wait_resource ,transaction_isolation_level ,lock_timeout
FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
GO
sys.dm_tran_locks
You can view information about current locks and the processes blocking them using the sys.dm_tran_locks dynamic management view. This column has one of three values: GRANT, WAIT or CONVERT. The value of CONVERT means that the requestor has been granted a request but is waiting to upgrade to the initial request to be granted. To locate information about all locks with a request status of CONVERT, you execute the following:
USE [master]
GO
SELECT * from sys.dm_tran_locks WHERE request_status = 'CONVERT'
GO
Check Table Locks
select object_name(resource_associated_entity_id) as 'TableName' ,*
from sys.dm_tran_locks where resource_type = 'OBJECT' and resource_database_id = DB_ID()
GO
exec sp_who 'active'
exec sp_lock
The request_session_id column contains the Process ID for the process. To view locking in the particular database, execute the following query that joins sys.dm_tran_locks with sys.partitions:
USE [master]
GO
SELECT tl.resource_type,tl.resource_associated_entity_id ,OBJECT_NAME(p.object_id) AS object_name ,tl.request_status
,tl.request_mode ,tl.request_session_id ,tl.resource_description
FROM sys.dm_tran_locks tl LEFT JOIN sys.partitions p
ON p.hobt_id = tl.resource_associated_entity_id
WHERE tl.resource_database_id = DB_ID()
GO
sys.dm_os_waiting_tasks
The sys.dm_os_waiting_tasks dynamic management view reports information about the blocked and blocking processes. The blocked process is listed in the session_id column. The blocking is listed in the blocking_session_id column.
Execute the following to view wait stats for all block processes on SQL Server:
USE [master]
GO
SELECT w.session_id,w.wait_duration_ms,w.wait_type,w.blocking_session_id,w.resource_description,s.program_name,t.text,t.dbid,s.cpu_time,
s.memory_usage FROM sys.dm_os_waiting_tasks w INNER JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1
GO
This detail is good for a big picture, or to get a quick idea of the types of waits occurring, but most of the real diagnostics and tuning will occur at a statement level.