Otimizando planos com leitores XML

O motivo da diferença de desempenho está em como as expressões escalares são tratadas no mecanismo de execução. Neste caso, a manifestação de interesse é:

[Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)

Esse rótulo de expressão é definido por um operador Compute Scalar (nó 11 no plano serial, nó 13 no plano paralelo). Os operadores Compute Scalar são diferentes de outros operadores (SQL Server 2005 em diante) porque as expressões que eles definem não são necessariamente avaliadas na posição em que aparecem no plano de execução visível; a avaliação pode ser adiada até que o resultado do cálculo seja solicitado por um operador posterior.

Na consulta atual, a target_datastring geralmente é grande, tornando a conversão de string em XMLdispendiosa. Em planos lentos, a string para XMLconversão é executada toda vez que um operador posterior que requer o resultado Expr1000é rebote.

A religação ocorre no lado interno de uma junção de loops aninhados quando um parâmetro correlacionado (referência externa) é alterado. Expr1000é uma referência externa para a maioria das junções de loops aninhados neste plano de execução. A expressão é referenciada várias vezes por vários leitores XML, ambos Stream Aggregates e por um filtro de inicialização. Dependendo do tamanho do XML, o número de vezes que a string é convertida XMLpode facilmente chegar a milhões.

As pilhas de chamadas abaixo mostram exemplos da target_datastring sendo convertida para XML( ConvertStringToXMLForES- onde ES é o Serviço de Expressão ):

Filtro de inicialização

Leitor de XML (TVF Stream internamente)

Fluxo Agregado

A conversão da string para XMLcada vez que qualquer um desses operadores é religado explica a diferença de desempenho observada com os planos de loops aninhados. Isso ocorre independentemente de o paralelismo ser usado ou não. Acontece que o otimizador escolhe um hash join quando a MAXDOP 1dica é especificada. Se MAXDOP 1, LOOP JOINfor especificado, o desempenho será ruim, assim como no plano paralelo padrão (onde o otimizador escolhe loops aninhados).

Quanto o desempenho aumenta com uma junção de hash depende se Expr1000aparece no lado de compilação ou de teste do operador. A consulta a seguir localiza a expressão no lado da sonda:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions s
    INNER HASH JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

Inverti a ordem escrita das junções da versão mostrada na pergunta, porque as dicas de junção ( INNER HASH JOINacima) também forçam a ordem para toda a consulta, como se FORCE ORDERtivesse sido especificada. A inversão é necessária para garantir que Expr1000apareça no lado da sonda. A parte interessante do plano de execução é:

Com a expressão definida no lado da sonda, o valor é armazenado em cache:

A avaliação de Expr1000ainda é adiada até que o primeiro operador precise do valor (o filtro de inicialização no rastreamento de pilha acima), mas o valor calculado é armazenado em cache ( CValHashCachedSwitch) e reutilizado para chamadas posteriores pelos leitores de XML e agregados de fluxo. O rastreamento de pilha abaixo mostra um exemplo do valor armazenado em cache sendo reutilizado por um leitor de XML.

Quando a ordem de junção é forçada de forma que a definição de Expr1000ocorra no lado da construção da junção hash, a situação é diferente:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st 
    INNER HASH JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Uma junção de hash lê sua entrada de compilação completamente para construir uma tabela de hash antes de iniciar a sondagem de correspondências. Como resultado, temos que armazenar todos os valores, não apenas aquele por thread que está sendo trabalhado do lado da sonda do plano. O hash join, portanto, usa uma tempdbmesa de trabalho para armazenar os XMLdados, e todo acesso ao resultado Expr1000por operadores posteriores requer uma viagem cara para tempdb:

O seguinte mostra mais detalhes do caminho de acesso lento:

If a merge join is forced the input rows are sorted (a blocking operation, just like the build input to a hash join) resulting in a similar arrangement where slow access via a tempdb sort-optimized worktable is required because of the size of the data.

Plans that manipulate large data items can be problematic for all sorts of reasons that are not apparent from the execution plan. Using a hash join (with the expression on the correct input) is not a good solution. It relies on undocumented internal behaviour with no guarantees it will work the same way next week, or on a slightly different query.

A mensagem é que a XMLmanipulação pode ser algo complicado de otimizar hoje. Escrever o XMLem uma variável ou tabela temporária antes de triturar é uma solução alternativa muito mais sólida do que qualquer coisa mostrada acima. Uma maneira de fazer isso é:

DECLARE @data xml =
        CONVERT
        (
            xml,
            (
            SELECT TOP (1)
                dxst.target_data
            FROM sys.dm_xe_sessions AS dxs 
            JOIN sys.dm_xe_session_targets AS dxst ON
                dxst.event_session_address = dxs.[address]
            WHERE 
                dxs.name = N'system_health'
                AND dxst.target_name = N'ring_buffer'
            )
        )

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM @data.nodes ('./RingBufferTarget/event[@name eq "xml_deadlock_report"]') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

Finalmente, eu só quero adicionar o gráfico muito bom de Martin dos comentários abaixo:

Esse é o código do meu artigo originalmente postado aqui:

http://www.sqlservercentral.com/articles/deadlock/65658/

Se você ler os comentários, encontrará algumas alternativas que não têm os problemas de desempenho que você está enfrentando, uma usando uma modificação dessa consulta original e a outra usando uma variável para armazenar o XML antes de processá-lo, o que funciona Melhor. (veja meus comentários na página 2) O XML do DMV pode ser lento para processar, assim como a análise do XML do DMF para o destino do arquivo, que geralmente é melhor realizado lendo os dados em uma tabela temporária primeiro e depois processando-os. XML em SQL é lento em comparação com coisas como .NET ou SQLCLR.