najti_prichiny_blokirovok_sql [wiki.hostingcloud.com.ua]

Эта страница только для чтения. Вы можете посмотреть её исходный текст, но не можете его изменить. Сообщите администратору, если считаете, что это неправильно.
====== Найти причины блокировок SQL ======

===== Перевірка на блокування =====
==== Розширена аналітика поточних блокувань + статистичні данні ====
<code>
SELECT
    s.session_id,
    s.program_name,
    s.host_name,
    DB_NAME(s.database_id)                          AS база,
    r.command,
    r.status,
    r.wait_type,
    r.wait_time/1000                                AS очікування_сек,

    -- ЧАС
    r.total_elapsed_time/1000                       AS виконується_сек,
    r.total_elapsed_time/1000/60                    AS виконується_хв,

    -- CPU
    r.cpu_time                                      AS cpu_мс,
    CAST(r.cpu_time * 100.0 
        / NULLIF(r.total_elapsed_time,0) AS DECIMAL(5,1)) AS cpu_відсоток,

    -- IO
    r.logical_reads                                 AS логічних_читань,
    r.reads                                         AS фізичних_читань,
    r.writes                                        AS записів,
    -- скільки МБ прочитано з диску
    r.reads * 8 / 1024                              AS прочитано_МБ,

    -- БЛОКУВАННЯ
    r.blocking_session_id                           AS блокує_сесія,
    r.open_transaction_count                        AS відкритих_транзакцій,

    -- ПРОГРЕС (для BACKUP, DBCC, ALTER INDEX)
    r.percent_complete                              AS прогрес_відсоток,
    r.estimated_completion_time/1000/60             AS залишилось_хв,

    -- ПАМ'ЯТЬ
    mg.granted_memory_kb/1024                       AS виділено_памяті_МБ,
    mg.used_memory_kb/1024                          AS використано_памяті_МБ,
    mg.requested_memory_kb/1024                     AS запитано_памяті_МБ,

    -- ПЛАН ВИКОНАННЯ (посилання)
    r.plan_handle,

    -- ТЕКСТ ЗАПИТУ
    SUBSTRING(t.text, 
        (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset
          END - r.statement_start_offset)/2)+1
    )                                               AS поточний_оператор,
    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
LEFT JOIN sys.dm_exec_query_memory_grants mg 
    ON r.session_id = mg.session_id
WHERE s.is_user_process = 1
ORDER BY r.cpu_time DESC, r.logical_reads DESC
</code>

==== Показує всі сесії що зараз заблоковані кимось іншим ====
<code>
SELECT session_id, wait_type, blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
</code>
===== Перевірка на блокування програмою 1С =====
<code>
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
</code>
==== Перевірка Хто кого блокує + деталі винуватця ====
<code>
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
</code>

=====  Довгі відкриті транзакції (типова причина в 1С) =====
<code>
-- Транзакції, відкриті більше 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;
</code>
===== Пошук що саме робить проблемна сесія (в прикладі номер 62) =====
<code>
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
</code>
===== Вбити проблемний процес =====
<code>
KILL 58
</code>
===== Перевірка навантаження дискової підсистеми =====
<code>
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
</code>