Manutenção de índice para varbinary

Se você tiver uma tabela com 1,9 bilhão de linhas, suponho que tenha uma taxa de alteração bastante baixa como porcentagem. Verifique quantas linhas você está realmente inserindo/atualizando diariamente - é provável que seja menos de 1%.

Nesse caso, não faz sentido reorganizar toda a tabela a cada 2-3 dias (especialmente considerando que leva mais de 10 horas). Tamanho.

Comece dando um passo para trás e perguntando: "Qual é o problema que estou tentando resolver fazendo reorganizações de índice?" Se a resposta for instruções de seleção lenta, a manutenção do índice em uma tabela de linhas de 1,9 bilhão não será a resposta.

De acordo com o comentário, minha pergunta sobre o uso foi porque eu esperava que esse índice existisse para verificar os registros existentes, ou seja, pesquisas de singleton, sem comportamento de verificação. Com isso em mente, concordo com @BrentOzar que reorganizar com tanta frequência provavelmente não trará nenhum benefício tangível. Desligue-o e veja se o desempenho do ETL piora, suspeito que não.

Se houver escopo para modificar o processo ETL, eu procuraria despejar o VARBINARYhash e substituí-lo por BIGINT. Eu uso um arranjo semelhante em um processo ETL de datawarehouse:

CAST(HASHBYTES('SHA1', longstring) AS BIGINT)

Neste caso de uso, as colisões resultantes do truncamento não são um problema. O hash é usado para verificar se uma linha já existe por meio de um índice composto por chaves de 8 bytes em vez de um índice de 255 bytes ou verificando uma tabela contendo a coluna de 4000 bytes. Se a verificação não resultar em nenhuma linha, você insere. Se houver uma correspondência no hash, faça a comparação no texto bruto para determinar se você tem uma linha existente ou precisa inserir.

Qual é o FILLFACTORíndice NonClustered? Qual algoritmo de hash você está usando? Esse índice foi PAD_INDEXdefinido como ON? Qual é a definição do índice agrupado (incluindo tipos de dados de coluna)?

Tudo isso nos dará uma imagem mais clara da composição física do índice.

Que outras operações estão ocorrendo? Ou seja, você atualiza a NVARCHAR(2000)coluna? Você exclui muitas linhas? As únicas coisas que devem aumentar a fragmentação são: inserções regulares (já que o hash é efetivamente ordenado "aleatoriamente"), atualizações no NVARCHARcampo, pois isso alterará o valor (mas não o tamanho) do hash e MUITAS exclusões.

As respostas a essas perguntas nos darão uma compreensão mais clara de como/por que a fragmentação está aumentando.

Além disso, fora do nível de fragmentação atingindo o limite padrão para o script de Ola recomendar a desfragmentação, você notou alguma degradação no desempenho do ETL quando não desfragmentou?

Além disso, eu também teria cuidado ao converter o valor de hash para BIGINTdado que BIGINTé apenas 8 bytes, mas todos os algoritmos de hash - mesmo MD5 - são maiores que 8 bytes ( MD5 = 16 bytes, SHA1 = 20, SHA2_256 = 32 e SHA2_512 = 64). E converter valores binários maiores que 8 bytes para BIGINTtruncar silenciosamente os valores, portanto, você perde a precisão e aumenta as ocorrências de falsos positivos. A consulta a seguir mostra esse comportamento:

SELECT CONVERT(BIGINT, 0xFFFFFFFFFFFFFF),      --  7 bytes = 72057594037927935
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFF),    --  8 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFF),  --  9 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFFFF) -- 10 bytes = -1

Obviamente, de acordo com a explicação de uso do @Marks, é possível que esse truncamento apenas aumente a frequência de comparação completa do NVARCHARcampo. Ainda assim, deve-se estar ciente do comportamento, pois é um truncamento silencioso (ou seja, não óbvio).