Eu tenho uma tarefa para atualizar 5 milhões de linhas em uma tabela de produção, sem bloquear a tabela inteira por tempo prolongado
Então, eu usei uma abordagem que me ajudou muitas vezes - atualizando as linhas (N) superiores de cada vez com intervalo de 1 N segundo entre os pedaços
Desta vez, começou com a atualização das primeiras (1.000) linhas de cada vez, monitorando a sessão de Eventos Estendidos para lock_escalation
eventos no processo
lock_escalation
aparecia durante cada operação de atualização, então comecei a diminuir a contagem de linhas por 1000 -> 500 -> 200 -> 100 -> 50
linhas de bloco e assim por diante até 1
Antes (não com esta tabela e para operações de exclusão - não atualização), diminuir a contagem de linhas para 200 ou 100 ajudava a se livrar de lock_escalation
eventos
Mas desta vez, mesmo com 1 linha por 1 operação de atualização, a tabela lock_escalation
ainda aparece. A duração de cada operação de atualização é aproximadamente a mesma, independentemente de ser 1 linha ou 1.000 linhas por vez
Como me livrar de escalações de bloqueio de tabela no meu caso?
@@TRANCOUNT é zero
Evento estendido:
Código :
set nocount on
declare
@ChunkSize int = 1000, -- count rows to remove in 1 chunk
@TimeBetweenChunks char(8) = '00:00:01', -- interval between chunks
@Start datetime,
@End datetime,
@Diff int,
@MessageText varchar(500),
@counter int = 1,
@RowCount int = 1,
@TotalRowsToUpdate bigint,
@TotalRowsLeft bigint
-- total row count to update
set @TotalRowsToUpdate = (select count(*)
from [Table1]
join [Table2] on
btid = tBtID
where btStatusID = 81)
set @TotalRowsLeft = @TotalRowsToUpdate
set @MessageText = 'Total Rows to Update = ' + cast(@TotalRowsLeft as varchar) raiserror (@MessageText,0,1) with nowait
print ''
-- begin cycle
while @RowCount > 0 begin
set @Start = getdate()
-- update packages
update top (@ChunkSize) bti
set btstatusid = 154,
btType = 1
from [Table1] bti
join [Table2] on
btid = tBtID
where btStatusID = 81
set @RowCount = @@ROWCOUNT
-- measure time
set @End = getdate()
set @Diff = datediff(ms,@Start,@End)
set @TotalRowsLeft = @TotalRowsLeft - @RowCount
set @MessageText = cast(@counter as varchar) + ' - Updated ' + cast(@RowCount as varchar) + ' rows in ' + cast(@Diff as varchar) + ' milliseconds - total ' + cast(@TotalRowsLeft as varchar) + ' rows left...'
-- print progress message
raiserror (@MessageText,0,1) with nowait
set @counter += 1
WAITFOR DELAY @TimeBetweenChunks
end
Plano:
Se observarmos o plano real, a consulta atual está lendo muitos dados da tabela para serem atualizados. Isso é do índice search em
BoxTrackInfo
:Esta é uma busca de índice
btid
para cada linha que sai da varredura deBlueTrackEvents
. Os bloqueios de atualização são adquiridos conformebtStatusID
verificado para ver se a linha se qualifica para a atualização. Apenas 1.401 linhas se qualificam para a atualização, mas muitos outros bloqueios são obtidos no processo - resultando em escalação de bloqueio para o nível da tabela.Você realmente quer uma forma de plano diferente - para procurar na
BoxTrackInfo
tabelabtStatusID
e, em seguida, juntar-se aBlueTrackEvents
, o que deve obter muito menos bloqueios. Para esse fim, adicionar um índice como este deve ajudar:Isso deve localizar com mais eficiência as linhas de qualificação, permitindo que a atualização seja concluída sem o escalonamento de bloqueio.
Como observação lateral, o plano de execução atual valida a restrição de chave estrangeira ao
btStatusID
usar uma semijunção de mesclagem:Isso provavelmente não é grande coisa no seu caso, pois há apenas 267 linhas na
LBoxTrackStatus
tabela. Se essa tabela for maior, você pode considerar adicionar umaLOOP JOIN
ouFAST 1
dica à consulta para obter a validação FK de loops aninhados. Veja este post para mais detalhes:Por que estou recebendo um problema de isolamento de instantâneo em INSERT?