How to Get historical data of blocked process in SQL


 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]  
 ,[WaitingUserConnectionLogin]  
 ,[BlockingSessionConnectionLogin]  
 ,[WaitDuration]  
 ,[WaitType]  
 ,[WaitRequestMode]  
 ,[WaitingProcessStatus]  
 ,[BlockingSessionStatus]  
 ,[WaitResource]  
 ,[WaitResourceType]  
 ,[WaitResourceDatabaseID]  
 ,[WaitResourceDatabaseName]  
 ,[WaitResourceDescription]  
 ,[WaitingSessionProgramName]  
 ,[BlockingSessionProgramName]  
 ,[WaitingHost]  
 ,[BlockingHost]  
 ,[WaitingCommandType]  
 ,[WaitingCommandText]  
 ,[WaitingCommandRowCount]  
 ,[WaitingCommandPercentComplete]  
 ,[WaitingCommandCPUTime]  
 ,[WaitingCommandTotalElapsedTime]  
 ,[WaitingCommandReads]  
 ,[WaitingCommandWrites]  
 ,[WaitingCommandLogicalReads]  
 ,[WaitingCommandQueryPlan]  
 ,[WaitingCommandPlanHandle])  
 SELECT b.[session_id] AS [WaitingSessionID]  
 ,b.[blocking_session_id] AS [BlockingSessionID]  
 ,b.[login_name] AS [WaitingUserSessionLogin]  
 ,s1.[login_name] AS [BlockingUserSessionLogin]  
 ,b.scheduler_id  
 ,b.[original_login_name] AS [WaitingUserConnectionLogin]   
 ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]  
 ,b.[wait_duration_ms] AS [WaitDuration]  
 ,b.[wait_type] AS [WaitType]  
 ,t.[request_mode] AS [WaitRequestMode]  
 ,UPPER(b.[status]) AS [WaitingProcessStatus]  
 ,UPPER(s1.[status]) AS [BlockingSessionStatus]  
 ,b.[wait_resource] AS [WaitResource]  
 ,t.[resource_type] AS [WaitResourceType]  
 ,t.[resource_database_id] AS [WaitResourceDatabaseID]  
 ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]  
 ,b.[resource_description] AS [WaitResourceDescription]  
 ,b.[program_name] AS [WaitingSessionProgramName]  
 ,s1.[program_name] AS [BlockingSessionProgramName]  
 ,b.[host_name] AS [WaitingHost]  
 ,s1.[host_name] AS [BlockingHost]  
 ,b.[command] AS [WaitingCommandType]  
 ,b.[text] AS [WaitingCommandText]  
 ,b.[row_count] AS [WaitingCommandRowCount]  
 ,b.[percent_complete] AS [WaitingCommandPercentComplete]  
 ,b.[cpu_time] AS [WaitingCommandCPUTime]  
 ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]  
 ,b.[reads] AS [WaitingCommandReads]  
 ,b.[writes] AS [WaitingCommandWrites]  
 ,b.[logical_reads] AS [WaitingCommandLogicalReads]  
 ,b.[query_plan] AS [WaitingCommandQueryPlan]  
 ,b.[plan_handle] AS [WaitingCommandPlanHandle]  
 FROM [Blocking] b  
 INNER JOIN [sys].[dm_exec_sessions] s1  
 ON b.[blocking_session_id] = s1.[session_id]  
 INNER JOIN [sys].[dm_tran_locks] t  
 ON t.[request_session_id] = b.[session_id]  
 WHERE t.[request_status] = 'WAIT'  
 GO