SQL Server Monitoring Nuggets

https://www.mssqltips.com/sqlservertip/5502/return-sql-server-connections-information-using-sysdmexecconnections/   Return last SQL command: SELECT DEC . session_id , T . text AS 'SQLQuery' FROM sys . dm_exec_connections AS DEC CROSS APPLY sys . dm_exec_sql_text ( DEC . most_recent_sql_handle ) AS T

https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/   Find blocking / waiting: SELECT session_id , blocking_session_id , start_time , wait_type , wait_type FROM sys . dm_exec_requests WHERE blocking_session_id > 0 ; Active query plan: . SELECT DER . session_id , DEQP . query_plan FROM sys . dm_exec_requests AS DER CROSS APPLY sys . dm_exec_query_plan ( DER . plan_handle ) AS DEQP WHERE NOT DER . status IN ( 'background' , 'sleeping' ); Get % complete SELECT session_id , start_time , status , database_id , percent_complete FROM sys . dm_exec_requests WHERE command = 'DBCC TABLE CHECK' ; , .

  https://www.mssqltips.com/sqlservertip/5507/understanding-and-using-sysdmexecsessions-in-sql-server/ Check for open transactions: SELECT session_id , open_transaction_count FROM sys . dm_exec_sessions WHERE open_transaction_count > 0 ;