Most of the time DBA will fire the “sp_who2 active” command to find out the active session on the SQL Server instance and quickly kill the dead lock as soon as he/she find it blocking one another. While, there’s always other way to find out more detail about it before you pull your trigger to kill the running transaction. Thus, you can avoid possible lost of data after you kill the running transaction.
Although you can look at wait statistics to understand specifically what is causing the server to slow down. It’s a great metrics for understanding the system as a whole. You could run a query against sys.dm_os_wait_stats and other by the number of currently waiting task, the cumulative wait time or the max wait time to see what, in general is causing the server to run slow.
To see what’s currently running on the server, you could run a query against the Dynamic Management Object (DMO) sys.dm_exec_requests.
SELECT der.session_id , --internal identifier for the running session der.status , --determines if the query is active or waiting der.start_time , --gives you an idea when the query started der.command , --the type of command involved der.database_id , --which database you're connected to der.user_id , --which login is running the command der.blocking_session_id , --session id of blocking session der.wait_type , -- what is the waiting session it waiting on der.wait_time , --how long has it been waiting der.last_wait_type , --what caused it to last wait der.cpu_time , --how much of the CPU has been used der.total_elapsed_time , --how long has the command been running der.reads , --has the command hit the disk for information der.writes , --how much information was written to the disk der.logical_reads --how many reads came out of memory FROM sys.dm_exec_requests AS der;
After you capture the long running transaction that probably use plenty of the resources. Now you need to figure out what is the query this session was running and its execution plan. The query below will help you to pull all this information together in a hurry.
SELECT SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1, (der.statement_end_offset - der.statement_start_offset ) / 2 + 1) FROM sys.dm_exec_requests AS der CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest WHERE der.session_id = 442;