How to: Performance Tunining on Long Wait Transaction

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.

  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;

Reference : – The Top 5 Hard-earned Lessons of a DBA