Como encontrar a consulta que ainda está mantendo um bloqueio?

O SQL Server não mantém um histórico dos comandos que foram executados ^1,2 . Você pode determinar quais objetos têm bloqueios, mas não pode necessariamente ver qual instrução causou esses bloqueios.

Por exemplo, se você executar esta instrução:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

E olhe para o Texto SQL através do identificador sql mais recente, você verá que a instrução aparece. No entanto, se a sessão fez isso:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

Você só veria a SELECT * FROM dbo.TestLock;instrução, mesmo que a transação não tenha sido confirmada e a INSERTinstrução esteja bloqueando os leitores na dbo.TestLocktabela.

Eu uso isso para procurar transações não confirmadas que estão bloqueando outras sessões:

/*
    This query shows sessions that are blocking other sessions, including sessions that are 
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Hannah Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS 
(
    SELECT v.*
    FROM (VALUES 
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS 
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst 
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS 
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS 
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0 
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.QueryText
    , agg.CountOfBlockedSessions
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg 
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY 
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;

Se configurarmos um banco de teste simples no SSMS com algumas janelas de consulta, podemos ver que só podemos ver a instrução ativa mais recente.

Na primeira janela de consulta, execute isto:

CREATE TABLE dbo.TestLock
(
    id int NOT NULL IDENTITY(1,1)
);
BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

Na segunda janela, execute isto:

SELECT *
FROM  dbo.TestLock

Agora, se executarmos a consulta de transações de bloqueio não confirmadas acima, veremos a seguinte saída:

╔═══════════╦═══════════════════════════════╦═════ ═══════════════╦══════════════════════════════════ ═══════╗
║ SessionID ║ ItemType ║ BlockedBySessionID ║ QueryText ║
╠═══════════╬═══════════════════════════════╬═════ ═══════════════╬══════════════════════════════════ ═══════╣
║ 67 ║ Transação ║ 0 ║ INICIAR TRANSAÇÃO ║
║ ║ ║ ║ INSERT INTO dbo.TestLock VALORES PADRÃO ║
║ 68 ║ Solicitação de Sessão, Tarefa em Espera ║ 67 ║ SELECT * ║
║ ║ ║ ║ FROM dbo.TestLock ║
╚═══════════╩═══════════════════════════════╩═════ ═══════════════╩══════════════════════════════════ ═══════╝

(Removi algumas colunas irrelevantes do final dos resultados).

Agora, se alterarmos a primeira janela de consulta para isso:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

e execute novamente a 2ª janela de consulta:

SELECT *
FROM  dbo.TestLock

Veremos esta saída da consulta de transações de bloqueio:

╔═══════════╦═══════════════════════════════╦═════ ═══════════════╦════════════════════╗
║ SessionID ║ ItemType ║ BlockedBySessionID ║ QueryText ║
╠═══════════╬═══════════════════════════════╬═════ ═══════════════╬════════════════════╣
║ 67 ║ Transação ║ 0 ║ SELECT * ║
║ ║ ║ ║ FROM dbo.TestLock; ║
║ 68 ║ Solicitação de Sessão, Tarefa em Espera ║ 67 ║ SELECT * ║
║ ║ ║ ║ FROM dbo.TestLock ║
╚═══════════╩═══════════════════════════════╩═════ ═══════════════╩════════════════════╝

^{1 - não inteiramente verdade. Existe o cache de procedimento, que pode conter a instrução responsável pelo bloqueio. No entanto, pode não ser fácil determinar qual instrução é a causa real do bloqueio, pois pode haver muitas consultas no cache que tocam no recurso em questão.}

A consulta abaixo mostra o plano de consulta para as consultas de teste acima, pois meu cache de procedimento não está muito ocupado.

SELECT TOP(30) t.text
    , p.query_plan
    , deqs.execution_count
    , deqs.total_elapsed_time
    , deqs.total_logical_reads
    , deqs.total_logical_writes
    , deqs.total_logical_writes
    , deqs.total_rows
    , deqs.total_worker_time
    , deqs.*
FROM sys.dm_exec_query_stats deqs
OUTER APPLY sys.dm_exec_sql_text(deqs.sql_handle) t 
OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) p
WHERE t.text LIKE '%dbo.TestLock%'  --change this to suit your needs
    AND t.text NOT LIKE '/\/\/\/\/EXCLUDE ME/\/\/\/\/\'
ORDER BY 
    deqs.total_worker_time DESC;

Os resultados desta consulta podem permitir que você encontre o culpado, mas esteja ciente, inspecionar o cache de procedimento como este pode ser bastante exigente em um sistema ocupado.

² SQL Server 2016 e superior oferecem o Query Store , que retém o histórico completo das consultas executadas.

Para complementar a outra resposta , achei os utilitários abaixo extremamente úteis:

• beta_lockinfo - por Erland Sommarskog

Eu uso o beta_lockinfo quando quero me aprofundar no bloqueio e analisar o que e como o bloqueio surgiu - o que é extremamente útil.

beta_lockinfo é um procedimento armazenado que fornece informações sobre processos e os bloqueios que eles mantêm, bem como suas transações ativas. beta_lockinfo foi projetado para reunir o máximo de informações possível sobre uma situação de bloqueio, para que você possa encontrar instantaneamente o culpado e matar o processo de bloqueio se a situação for desesperadora. Em seguida, você pode sentar e analisar a saída do beta_lockinfo para entender como a situação de bloqueio surgiu e descobrir quais ações tomar para evitar que a situação ocorra novamente. A saída de beta_lockinfo mostra todos os processos ativos e passivos com bloqueios, quais objetos eles bloqueiam, qual comando eles enviaram pela última vez e qual instrução eles estão executando. Você também obtém os planos de consulta para as instruções atuais.

• Processos bloqueados em execução - pela equipe MS Tiger