Atualizações de índice exclusivo e contadores de modificação de linha de estatísticas

O SQL Server sempre usa a combinação de operadores Dividir, Classificar e Recolher ao manter um índice exclusivo como parte de uma atualização que afeta (ou pode afetar) mais de uma linha.

Trabalhando com o exemplo da pergunta, poderíamos escrever a atualização como uma atualização de linha única separada para cada uma das quatro linhas presentes:

-- Per row updates
UPDATE dbo.Banana SET pk = 2 WHERE pk = 1;
UPDATE dbo.Banana SET pk = 3 WHERE pk = 2;
UPDATE dbo.Banana SET pk = 4 WHERE pk = 3;
UPDATE dbo.Banana SET pk = 5 WHERE pk = 4;

O problema é que a primeira instrução falharia, pois muda pkde 1 para 2 e já existe uma linha onde pk= 2. O mecanismo de armazenamento do SQL Server exige que os índices exclusivos permaneçam exclusivos em todas as etapas do processamento, mesmo dentro de uma única instrução . Este é o problema resolvido por Dividir, Classificar e Recolher.

Dividir

A primeira etapa é dividir cada instrução de atualização em uma exclusão seguida por uma inserção:

DELETE dbo.Banana WHERE pk = 1;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');

DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');

DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');

DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

O operador Split adiciona uma coluna de código de ação ao fluxo (aqui rotulado como Act1007):

O código de ação é 1 para uma atualização, 3 para uma exclusão e 4 para uma inserção.

Ordenar

As instruções de divisão acima ainda produziriam uma violação de chave única transitória falsa, portanto, a próxima etapa é classificar as instruções pelas chaves do índice exclusivo que está sendo atualizado ( pkneste caso) e, em seguida, pelo código de ação. Para este exemplo, isso significa simplesmente que as exclusões (3) na mesma chave são ordenadas antes das inserções (4). A ordem resultante é:

-- Sort (pk, action)
DELETE dbo.Banana WHERE pk = 1;
DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');
DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');
DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

Colapso

O estágio anterior é suficiente para garantir a prevenção de falsas violações de exclusividade em todos os casos. Como uma otimização, Collapse combina exclusões e inserções adjacentes no mesmo valor de chave em uma atualização:

-- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

Os pares delete/insert para pkos valores 2, 3 e 4 foram combinados em uma atualização, deixando uma única exclusão em pk= 1 e uma inserção para pk= 5.

O operador Collapse agrupa as linhas pelas colunas-chave e atualiza o código de ação para refletir o resultado do colapso:

Atualização do índice clusterizado

Este operador é rotulado como Update, mas é capaz de inserções, atualizações e exclusões. Qual ação é executada pela atualização de índice clusterizado por linha é determinada pelo valor do código de ação nessa linha. O operador tem uma propriedade Action para refletir este modo de operação:

Contadores de modificação de linha

Observe que as três atualizações acima não modificam a(s) chave(s) do índice exclusivo que está sendo mantido. Na verdade, transformamos as atualizações das colunas- chave no índice em atualizações das colunas não-chave ( c1e c2), além de uma exclusão e uma inserção. Nem uma exclusão nem uma inserção podem causar uma violação de chave exclusiva falsa.

Uma inserção ou exclusão afeta cada coluna na linha, portanto, as estatísticas associadas a cada coluna terão seus contadores de modificação incrementados. Para atualização(ões), apenas as estatísticas com qualquer uma das colunas atualizadas como a coluna principal têm seus contadores de modificação incrementados (mesmo que o valor não seja alterado).

Os contadores de modificação de linha de estatísticas, portanto, mostram 2 alterações para pk, e 5 para c1e c2:

-- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;                         -- All columns modified
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;  -- c1 and c2 modified
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');    -- All columns modified

Nota: Apenas as alterações aplicadas ao objeto base (heap ou índice clusterizado) afetam os contadores de modificação de linha de estatísticas. Índices não clusterizados são estruturas secundárias, refletindo as alterações já feitas no objeto base. Eles não afetam os contadores de modificação de linha de estatísticas.

Se um objeto tiver vários índices exclusivos, uma combinação separada de Dividir, Classificar e Recolher é usada para organizar as atualizações de cada um. O SQL Server otimiza esse caso para índices não clusterizados salvando o resultado da divisão em um spool de tabela ansioso e, em seguida, reproduzindo esse conjunto para cada índice exclusivo (que terá suas próprias chaves de classificação por índice + código de ação e recolhimento).

Efeito nas atualizações de estatísticas

As atualizações automáticas de estatísticas (se habilitadas) ocorrem quando o otimizador de consulta precisa de informações estatísticas e percebe que as estatísticas existentes estão desatualizadas (ou inválidas devido a uma alteração de esquema). As estatísticas são consideradas desatualizadas quando o número de modificações registradas excede um limite.

O arranjo Dividir/Classificar/Recolher resulta na gravação de diferentes modificações de linha do que seria esperado. Isso, por sua vez, significa que uma atualização de estatísticas pode ser acionada mais cedo ou mais tarde do que seria o caso de outra forma.

No exemplo acima, as modificações de linha para a coluna-chave aumentam em 2 (a alteração líquida) em vez de 4 (uma para cada linha da tabela afetada) ou 5 (uma para cada exclusão/atualização/inserção produzida pelo Collapse).

Além disso, as colunas não-chave que não foram alteradas logicamente pela consulta original acumulam modificações de linha, que podem numerar até o dobro das linhas da tabela atualizadas (uma para cada exclusão e uma para cada inserção).

O número de alterações registradas depende do grau de sobreposição entre os valores de coluna de chave antigos e novos (e, portanto, o grau em que as exclusões e inserções separadas podem ser recolhidas). Redefinindo a tabela entre cada execução, as consultas a seguir demonstram o efeito nos contadores de modificação de linha com diferentes sobreposições:

UPDATE dbo.Banana SET pk = pk + 0; -- Full overlap

UPDATE dbo.Banana SET pk = pk + 1;

UPDATE dbo.Banana SET pk = pk + 2;

UPDATE dbo.Banana SET pk = pk + 3;

UPDATE dbo.Banana SET pk = pk + 4; -- No overlap