Dynamic Management Views (DMVs)

Information about some of the DMVs availabile in SQL Server, and their function

Sessions

sys.dm_exec_sessions
Information about successful and unsuccessful logins

sys.dm_exec_connections
Provides information about connections established to the Database Engine instance

sys.dm_exec_requests
Information about each request executing on the SQL instance

sys.dm_exec_cursors
Information about cursors open

Audits

sys.dm_audit_actions
Information about every audit action that can be reported in the audit log as well as every audit action group that you are able to configure as part of SQL Server Audit

sys.dm_server_audit_status

Information about the current state of auditing

sys.dm_audit_class_type_map

Information about the class_type field in the audit output

fn_get_audit_file

Information from an audit output file that has already been generated by a server audit

Events

sys.server_event_sessions 

Lists all the event session definitions configured for the Database Engine instance

sys.server_event_session_actions

View actions on each event on an event session

sys.server_event_session_events 

View each event in an event session

sys.server_event_session_fields 

View each customizable column set on events and targets

sys.server_event_session_targets

View each event target for a specific event session

I/O

sys.dm_io_pending_io_requests

Provides information on unfulfilled I/O requests

sys.dm_io_backup_tapes 

Information on tape devices and mounts requests for backups

sys.dm_io_cluster_shared_drives

Information on shared drives if the host server is a member of a failover cluster

sys.dm_io_virtual_file_stats

Information on I/O statistics for data and log files

Deadlocks

sys.dm_tran_locks

Information on active locks

sys.dm_os_waiting_tasks

Information on tasks waiting on resources

sys.dm_exec_requests

Requests that are executing within SQL Server