sql server 查询阻塞sql

SELECT * FROM sys.[sysprocesses]  WHERE   blocked<>0
 -----------------------------------占用CUP最多的SQL语句 -----------------------------------
 SELECT TOP 10 
 dest.[text] AS 'sql语句'
 FROM sys.[dm_exec_requests] AS der 
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC
 
 ----------------------------------造成阻塞的情况 -----------------------------------
  SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 AND blocked<>0
  -- AND DB_NAME([dbid])='DBName' --数据库名称
 
 ------------------------------------------造成阻塞的SQL情况 -----------------------------------
 SELECT
    S.session_id, R.blocking_session_id,
    S.host_name, S.login_name, 
    databaseName=DB_NAME(R.database_id),R.command, R.status,
    current_execute_sql = SUBSTRING(T.text,
                R.statement_start_offset / 2 + 1,
                CASE
                    WHEN statement_end_offset = -1 THEN LEN(T.text)
                    ELSE (R.statement_end_offset - statement_start_offset) / 2+1
                END),
    S.program_name,
    S.status,
    S.cpu_time, memory_usage_kb = S.memory_usage * 8, S.reads, S.writes,
    S.transaction_isolation_level,
    C.connect_time, C.last_read, C.last_write,
    C.net_transport, C.client_net_address, C.client_tcp_port, C.local_tcp_port,
    R.start_time, 
    R.wait_time, R.wait_type, R.last_wait_type, R.wait_resource,
    R.open_transaction_count, R.transaction_id
    
FROM sys.dm_exec_sessions S
    LEFT JOIN sys.dm_exec_connections C
        ON S.session_id = C.session_id
    LEFT JOIN sys.dm_exec_requests R
        ON S.session_id = R.session_id
            AND C.connection_id = R.connection_id
    OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) T
WHERE  S.is_user_process = 1  -- 如果不限制此条件，则查询所有进程（系统和用户进程）
and s.session_id in(


SELECT blocked FROM sys.[sysprocesses] WHERE --[spid]>50 AND blocked<>0
  blocked<>0
  -- AND DB_NAME([dbid])='DBName' --数据库名称


)