ALTER INDEX ALL REBUILD usa mais espaço de log de transações com modelo de recuperação simples do que reconstruir cada índice individualmente?

Presumi que, após cada operação de índice executada pela forma "ALL" do comando, os dados do log de transações seriam liberados antes da próxima reconstrução do índice. É assim que realmente funciona ou as recompilações de índice são registradas como se fossem parte de uma única transação?

1) Limpeza de log: o modelo de recuperação SIMPLE não limpa o log após cada transação, mas nos pontos de verificação. ( link para mais informações)

2a) REBUILD ALL: sim, REBUILD ALL funciona como uma única transação. As recompilações de índice têm suas próprias transações, mas a operação geral não é totalmente confirmada até o final. Então, sim, você pode limitar o crescimento do arquivo de log reconstruindo índices individuais (e possivelmente emitindo comandos CHECKPOINT).

2b) Prova! Aqui, tenha um script de demonstração. (Construído em 2016 dev) Primeiro, configure um banco de dados de teste, com tabela e índices:

USE master
GO

CREATE DATABASE Test_RebuildLog
GO

ALTER DATABASE Test_RebuildLog
SET RECOVERY SIMPLE
GO

USE Test_RebuildLog
GO

CREATE TABLE IndexTest
(ID int identity(1,1),
a char(1),
b char(1))

CREATE CLUSTERED INDEX CIX_IndexTest_ID ON IndexTest(ID)
CREATE INDEX IX_IndexTest_a ON IndexTest(a)
CREATE INDEX IX_IndexTest_b ON IndexTest(b)

INSERT IndexTest
(a,b)
VALUES ('a','b'),('z','y'),('s','r')

Agora você pode comparar a atividade de log entre REBUILD ALL e reconstruir individualmente

CHECKPOINT
GO
ALTER INDEX ALL ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

CHECKPOINT
GO
ALTER INDEX CIX_IndexTest_ID ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_a ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_b ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

Observe como a primeira transação aberta (ID de transação 0000:000002fa para mim) não é confirmada até o final do REBUILD ALL, mas para as recompilações índice por índice, elas são confirmadas sucessivamente.

Tal como está, esta é uma transação única.

A questão é trivial para uma reconstrução offline . Claro que é uma única transação. Imagine o estrago que aconteceria se a operação dividisse cada índice em sua própria transação, pois teria que liberar os bloqueios ao confirmar e depois readquiri-los. Enquanto o bloqueio SCH-M da tabela crítica foi liberado, os índices podem ser descartados e novos índices podem ser criados, como a instrução lidaria com esses casos? Sem mencionar que a tabela pode ser descartada e até mesmo recriada entre as duas transações! Incluindo o caso em que a tabela é descartada e uma tabela diferente é criada com o mesmo id de objeto (sim, isso pode acontecer) ...

E se você aumentar a pergunta para dizer o que acontece se a reconstrução do índice for uma reconstrução online ? É uma única transação ou muitas? A resposta é complexa, pois na verdade há várias transações internas envolvidas . No entanto, o ponto-chave é que há uma transação de arqueamento geral que abrange toda a operação (a instrução ALTER) e isso fixa o log no lugar (não pode truncar), portanto, a operação precisa ser planejada de acordo para permitir ~ 1,6x dados size para o modo de recuperação FULL ou 0,2x tamanho dos dados para o modo BULK_LOGGED/SIMPLE. Consulte o artigo vinculado para obter mais detalhes.

Você pode argumentar por que a compilação offline não emprega as mesmas transações internas que o modo online faz e divide a operação? Os problemas que mencionei sobre a tabela ser alterada/descartada entre as operações de índice individuais (ou seja, tabela 'estabilidade de esquema') ainda exigiriam que houvesse uma transação abrangente que mantenha um SCH-S na tabela durante toda a duração da instrução. Como essa transação deve conter o SCH-S também durante a recuperação, ela deve ser registrada e, como tal, haverá um registro de log BEGIN XACT que fixará o log e impedirá o truncamento por toda a duração da instrução. Eu sei que esse problema específico estava sendo resolvido no período de tempo do SQL 2016-2017 (devido a problemas de tamanho de log do SQL Azure), mas não tenho certeza de qual progresso foi feito . Parece que está em pré-visualização agora:A reconstrução de índice online recuperável está em visualização pública para SQL Server 2017 CTP 2.0 .

Sim, eu tive esse mesmo problema com uma mesa muito grande. Sempre que eu emiti ALTER INDEX ALL, o log de transações crescia muito, mas se fosse emitido ALTER INDEX individualmente, o uso do espaço de log seria menor.

A resposta anterior de Remus de que a indexação online requer 1,6x o tamanho do índice no modo de recuperação FULL não está correta. A proporção de espaço de log de transações necessária para reconstruir um índice online em FULL pode ser muito maior e observamos muitas vezes o tamanho do índice, especialmente quando o índice que está sendo reconstruído é compactado, pois o log de transações não é compactado. Isso por si só deve deixar claro que o log de transações durante uma reconstrução online em FULL pode ser pelo menos algumas vezes o tamanho do índice. Adicione a sobrecarga de registro de tlog que não é totalmente documentada pela Microsoft, mas geralmente é estimada em 60 bytes por linha e o tamanho proporcional do log durante uma reconstrução de índice online sob recuperação completa pode ser muitas vezes o tamanho do índice que está sendo recompilado, especialmente se o índice é comprimido

Rdfozz está correto, essa é a melhor maneira de decidir se seu maior índice pode ser reconstruído com base no armazenamento atual. Basta executar dm_exec_requestsenquanto a operação está acontecendo (ou SQL Profiler) para ver se todos os índices estão sendo reconstruídos. Eu também consideraria mudar o modelo de recuperação para log em massa. Isso é o que eu faço e ainda há backups de log de transações durante a janela. Veja abaixo o artigo https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx