Estouro de tamanho de linha de mesclagem no SQL Server - "Não é possível criar uma linha de tamanho .."

Por que a segunda vez que tentei mesclar a mesma linha que já foi inserida resultou em um erro. Se esta linha excedesse o tamanho máximo da linha, esperaria que não fosse possível inseri-la em primeiro lugar.

Primeiramente, obrigado pelo script de reprodução.

O problema não é que o SQL Server não pode inserir ou atualizar uma determinada linha visível ao usuário . Como você observou, uma linha que já foi inserida em uma tabela certamente não pode ser fundamentalmente muito grande para o SQL Server manipular.

O problema ocorre porque a implementação do SQL Server MERGEadiciona informações computadas (como colunas extras) durante as etapas intermediárias no plano de execução. Essas informações extras são necessárias por motivos técnicos, para controlar se cada linha deve resultar em uma inserção, atualização ou exclusão; e também relacionado à maneira como o SQL Server evita genericamente violações de chaves transitórias durante alterações em índices.

O mecanismo de armazenamento do SQL Server exige que os índices sejam exclusivos (internamente, incluindo qualquer unificador oculto) em todos os momentos - à medida que cada linha é processada - em vez de no início e no final da transação completa. Em cenários mais complexos MERGE, isso requer um Dividir (converter uma atualização em uma exclusão e inserção separadas), Classificar e um Colapso opcional (transformar inserções e atualizações adjacentes na mesma chave em uma atualização). Mais informações .

_{Como um aparte, observe que o problema não ocorre se a tabela de destino for um heap (solte o índice clusterizado para ver isso). Não estou recomendando isso como uma correção, apenas mencioná-lo para destacar a conexão entre a manutenção da exclusividade do índice em todos os momentos (agrupado no presente caso) e o Split-Sort-Collapse.}

Em consultas simples , com índices exclusivos adequados e um relacionamento direto entre as linhas de origem e destino (normalmente correspondendo usando uma cláusula que apresenta todas as colunas-chave), o otimizador de consulta pode simplificar muito da lógica genérica, resultando em planos comparativamente simples que não não requer um projeto Split-Sort-Collapse ou Segment-Sequence para verificar se as linhas de destino são tocadas apenas uma vez.MERGEON

Em consultas complexas MERGE , com lógica mais opaca, o otimizador geralmente é incapaz de aplicar essas simplificações, expondo muito mais da lógica fundamentalmente complexa necessária para o processamento correto (apesar dos bugs do produto, e houve muitos ).

Sua consulta certamente se qualifica como complexa. A ONcláusula não corresponde às chaves de índice (e eu entendo o porquê), e a 'tabela de origem' é uma junção automática envolvendo uma função de janela de classificação (novamente, com razões):

MERGE MERGE_REPRO_TARGET AS targetTable
USING
(
    SELECT * FROM 
    (
        SELECT 
            *, 
            ROW_NUMBER() OVER (
                PARTITION BY ww,id, tenant 
                ORDER BY 
                (
                    SELECT COUNT(1) 
                    FROM MERGE_REPRO_SOURCE AS targetTable
                    WHERE 
                        targetTable.[ibi_bulk_id] = sourceTable.[ibi_bulk_id] 
                        AND targetTable.[ibi_row_id] <> sourceTable.[ibi_row_id] 
                        AND 
                        (
                            (targetTable.[ww] = sourceTable.[ww]) 
                            AND (targetTable.[id] = sourceTable.[id]) 
                            AND (targetTable.[tenant] = sourceTable.[tenant])
                        ) 
                        AND NOT ((targetTable.[sampletime] <= sourceTable.[sampletime]))
                ),
                sourceTable.ibi_row_id DESC
            ) AS idx
        FROM MERGE_REPRO_SOURCE sourceTable 
        WHERE [ibi_bulk_id] in (20150803110418887)
    ) AS bulkData
    where idx = 1
) AS sourceTable 
ON 
    (targetTable.[ww] = sourceTable.[ww]) 
    AND (targetTable.[id] = sourceTable.[id]) 
    AND (targetTable.[tenant] = sourceTable.[tenant])
...

Isso resulta em muitas colunas computadas extras, principalmente associadas à Divisão e aos dados necessários quando uma atualização é convertida em um par de inserção/atualização. Essas colunas extras resultam em uma linha intermediária excedendo os 8.060 bytes permitidos em uma classificação anterior - aquela logo após um filtro:

Observe que o Filtro possui 1.319 colunas (expressões e colunas base) em sua Lista de Saída. Anexar um depurador mostra a pilha de chamadas no ponto em que a exceção fatal é gerada:

Observe de passagem que o problema não está no Spool - a exceção é convertida em um aviso sobre o potencial de uma linha ser muito grande.

Por que a atualização usando a mesclagem não está sendo bem-sucedida, enquanto a inserção e a atualização direta também?

Uma atualização direta não tem a mesma complexidade interna que o MERGE. É uma operação fundamentalmente mais simples que tende a simplificar e otimizar melhor. A remoção da NOT MATCHEDcláusula também pode remover o suficiente da complexidade para que o erro não seja gerado em alguns casos. Isso não acontece com o repro, no entanto.

Por fim, meu conselho é evitar MERGEtarefas maiores ou mais complexas. Minha experiência é que as instruções de inserção/atualização/exclusão separadas tendem a otimizar melhor, são mais simples de entender e também costumam ter um desempenho geral melhor, em comparação com MERGE.