MS SQL - Analisi delle query in esecuzione e bloccate

Questo è uno script per l'analisi delle query in esecuzione e bloccate in un database di Microsoft SQL Server:

 SELECT DTL.resource_type ,  
     CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )  
        THEN DTL.resource_type  
        WHEN DTL.resource_type = 'OBJECT'  
        THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])  
        WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' )  
        THEN ( SELECT OBJECT_NAME([object_id])  
           FROM  sys.partitions  
           WHERE  sys.partitions.hobt_id = DTL.resource_associated_entity_id  
          )  
        ELSE 'Unidentified'  
     END AS requested_object_name ,  
     DTL.request_mode ,  
     DTL.request_status ,  
     DEST.TEXT ,  
     SP.spid ,  
     SP.blocked ,  
     SP.STATUS ,  
     SP.loginame  
 FROM  sys.dm_tran_locks DTL  
     INNER JOIN sys.sysprocesses SP ON DTL.request_session_id = SP.spid    
     --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]   
     CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST  
 WHERE  SP.dbid = DB_ID()  
     AND DTL.[resource_type] <> 'DATABASE'  
 ORDER BY SP.blocked DESC ,  
     DTL.[request_session_id] ;