Skip to main content

Posts

Showing posts with the label How to Get historical data of blocked process in SQL

How to Get historical data of blocked process in SQL

By
WITH [Blocking]AS (SELECT w.[session_id] ,s.[original_login_name] ,s.[login_name] ,w.[wait_duration_ms] ,r.[scheduler_id] ,w.[wait_type] ,r.[status] ,r.[wait_resource] ,w.[resource_description] ,s.[program_name] ,w.[blocking_session_id] ,s.[host_name] ,r.[command] ,r.[percent_complete] ,r.[cpu_time] ,r.[total_elapsed_time] ,r.[reads] ,r.[writes] ,r.[logical_reads] ,r.[row_count] ,q.[text] ,q.[dbid] ,p.[query_plan] ,r.[plan_handle] FROM [sys].[dm_os_waiting_tasks] w INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id] INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id] CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p WHERE w.[session_id] > 50 ) Insert into Blocking_v1 ([session_id] ,[blocking_session_id] ,[WaitingUserSessionLogin] ,[BlockingUserSessionLogin] ,[scheduler_id]...
Read more