Início / dba / Perguntas / 124949
Accepted
Yosi Dahari
Asked: 2016-01-01 10:16:25 +0800 CST

Erro de estouro de linha de 8k ao atualizar linha de tamanho 5k

  • 772

Estou tentando atualizar uma tabela de destino que tem uma linha de tamanho 5k para uma linha de tamanho 5k também.

Como é uma linha, é fácil saber o tamanho real da linha:

select *
from sys.dm_db_index_physical_stats(DB_ID('RODS_HSD_ES'), 
OBJECT_ID(N'TBL_BM_HSD_SUBJECT_AN_148_REPRO'), NULL, NULL, 'DETAILED')

Reproduzir

A tabela não foi alterada desde a criação. não vejo nenhuma razão para que ele falhe. Ideias?

sql-server sql-server-2012

2 respostas

  1. A atualização falha por razões muito semelhantes àquelas que expliquei em resposta à sua pergunta anterior .

    Nesse caso, como você está potencialmente atualizando várias linhas em que uma coluna de chave de um índice exclusivo é alterada* , o SQL Server cria um plano que inclui os operadores Dividir, Classificar e Recolher para evitar violações intermediárias de chave exclusiva (consulte este artigo para obter detalhes) .

    O operador Sort assim introduzido encontra uma linha intermediária (incluindo despesas gerais internas) de uma largura que excede o limite, portanto, um erro é gerado. Adicionar uma OPTION (ROBUST PLAN)dica à consulta de atualização mostra que isso é inevitável:

    Msg 8619, Nível 16, Estado 2, Linha 681
    O processador de consulta não pôde produzir um plano de consulta porque uma tabela de trabalho é necessária e seu tamanho mínimo de linha excede o máximo permitido de 8060 bytes. Uma razão típica pela qual uma tabela de trabalho é necessária é uma cláusula GROUP BY ou ORDER BY na consulta. Reenvie sua consulta sem a dica ROBUST PLAN.

    As relações de dados de origem/destino não são claras para mim em uma breve olhada, mas se você puder garantir que cada operação de atualização afetará no máximo uma linha, poderá evitar a necessidade de Dividir/Classificar/Recolher adicionando TOP (1)à instrução de atualização:

    UPDATE TOP (1) [TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET] 
SET ...

    Este é um pouco de um hack, no entanto. Idealmente, a construção da instrução de atualização e os índices devem fornecer informações suficientes ao otimizador para que ele possa ver que no máximo uma linha será atualizada. Em particular, é uma prática recomendada escrever instruções de atualização que sejam determinísticas .

    Dado o design estranho e a falta de clareza na pergunta, nem vou tentar decifrar os relacionamentos de dados ou as alterações de consulta e índice que seriam necessárias para alcançar isso em detalhes.

    * Como Martin Smith apontou em um comentário, isso não seria um problema nessa situação específica se a tabela não fosse particionada. Onde a atualização define a chave com o mesmo valor determinístico em cada linha, Dividir/Classificar/Recolher não é necessário, a menos que a tabela também seja particionada nessa chave. Portanto, uma solução alternativa para essa consulta é não particionar a tabela em sampletime .

    • 10
  2. Best Answer

    O problema está relacionado ao fato de você estar atualizando a chave de clustering e a tabela de destino ter um esquema de particionamento 1 . Quando o SQL Server é solicitado a atualizar qualquer componente da chave de clustering, ele deve executar um UPDATEe DELETEou uma atualização híbrida em que algumas das linhas são atualizadas no local e outras não.

    Se você remover o índice clusterizado da tabela de destino, verá que a atualização funciona.

    A mensagem de erro, embora talvez um pouco enganosa, é precisa, pois o tamanho da linha resultante durante a atualização excede o comprimento máximo.

    Sugiro que você considere alterar a estrutura da tabela para:

    • não use VARCHAR(MAX)para todas essas colunas. Se você realmente não precisa de 2 GB de caracteres em uma única coluna, por que definir a coluna dessa maneira? Defina a coluna para ter o tamanho máximo que será encontrado de forma realista.
    • talvez divida esta tabela em várias tabelas em que o tamanho máximo da linha resultante seja inferior a 8060 bytes. Parece que você tem vários clusters lógicos de colunas, como V_MAX_xxx, V_64_xxxe V_512_xxxcolunas, etc.

    Para simplificar sua reprodução, você pode querer eliminar o cursor e fazer apenas a seguinte operação DML:

    UPDATE dbo.TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET
SET [sampletime]  = '2015-12-29 01:11:26.687';

    A coluna acima é um dos componentes da chave de agrupamento e também da chave de particionamento (atualizar outras colunas de chave CI funciona bem).

    Com o índice clusterizado no lugar, você obtém este erro:

    Msg 511, Nível 16, Estado 1, Linha 1

    Não é possível criar uma linha de tamanho 8287 que seja maior que o tamanho máximo de linha permitido de 8060.

    A instrução foi encerrada.

    Sem o índice clusterizado no lugar, a instrução é bem-sucedida.

    1 Curiosamente, se eliminarmos o particionamento da reprodução, descobriremos que a atualização foi bem-sucedida, mesmo com o índice clusterizado instalado.

    • 9

relate perguntas