Найти причины блокировок SQL

SELECT session_id, wait_type, blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0

SELECT TOP 10
    s.session_id, s.program_name,
    r.wait_type, r.wait_time/1000 AS очікування_сек,
    r.blocking_session_id,
    t.text AS запит
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.program_name LIKE '%1CV8%'
ORDER BY r.wait_time DESC

SELECT 
    r.session_id AS жертва,
    r.wait_type,
    r.wait_time/1000 AS очікування_сек,
    r.blocking_session_id AS винуватець,
    s_block.program_name AS програма_винуватця,
    s_block.host_name AS хост_винуватця,
    t.text AS запит_жертви
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s_block 
    ON r.blocking_session_id = s_block.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC

-- Транзакції, відкриті більше 30 секунд
SELECT
    s.session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    s.status,
    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS open_sec,
    at.transaction_begin_time,
    t.text AS last_query
FROM sys.dm_tran_active_transactions    at
JOIN sys.dm_tran_session_transactions   st ON st.transaction_id = at.transaction_id
JOIN sys.dm_exec_sessions               s  ON s.session_id      = st.session_id
LEFT JOIN sys.dm_exec_requests          r  ON r.session_id      = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) > 30
ORDER BY open_sec DESC;

SELECT r.session_id, r.command, r.percent_complete,
       r.estimated_completion_time/1000/60 AS хвилин_залишилось,
       t.text AS запит
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = 62

KILL 58

SELECT 
    DB_NAME(vfs.database_id) AS база,
    mf.physical_name AS файл,
    vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) 
        AS середня_затримка_читання_мс,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) 
        AS середня_затримка_запису_мс,
    vfs.num_of_reads AS читань,
    vfs.num_of_writes AS записів,
    vfs.io_stall AS загальна_затримка_мс
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf 
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
WHERE DB_NAME(vfs.database_id) IN ('ViatecUT', 'tempdb')
ORDER BY vfs.io_stall DESC