System Dynamic Management Views
Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server, sys.dm_db_index_physical_stats.
Returns information about all the waits encountered by threads that executed, sys.dm_os_wait_stats.
Returns information about the wait queue of tasks that are waiting on some resource., sys.dm_os_waiting_tasks
Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function, sys.dm_io_virtual_file_stats.
Returns information about the connections established to this instance of SQL Server and the details of each connection, sys.dm_exec_connections
Returns a row per performance counter maintained by the server. For information about each performance counter, sys.dm_os_performance_counters.
Returns information about each request that is executing in SQL Server. For more information about requests, sys.dm_exec_requests.
Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks, sys.dm_exec_sessions.
SELECT T.[text], P.[query_plan], S.[program_name], S.[host_name], S.[client_interface_name], S.[login_name], R.* FROM sys.dm_exec_requests R INNER
JOIN sys.dm_exec_sessions S ON S.session_id = R.session_id CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS T CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P