Início / dba / Perguntas / 262111
Accepted
Danielle Paquette-Harvey
Asked: 2020-03-18 05:58:49 +0800 CST

As estimativas de SQL estão longe da instrução DELETE com Triggers em tabelas enormes

  • 772

Estou trabalhando com o Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) 27 de novembro de 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows Server 2012 R2 Padrão 6.3 (Build 9600: )

Este servidor está em unidades SSD e tem uma memória máxima de 128 GB. CostTheshold para Paralelismo é 70, MaxDegree de Paralelismo é 3.

Eu tenho uma tabela "Trips" que é referenciada por 23 chaves estrangeiras com a opção ON DELETE CASCADE.

Esta tabela por si só não é tão grande (5,3 milhões de linhas, 1,3 gb de dados). Mas das 23 tabelas referenciadas, duas das tabelas são bem grandes (mais de 1 bilhão de linhas, 54 e 69 gb cada).

O problema é que quando tentamos excluir uma pequena quantidade de linhas na tabela "Trips" (digamos 4 linhas), o SQL estima que tantas linhas serão excluídas, ele pede 10 GB de RAM, estima que milhões de linhas serão retornado e bloqueia a tabela. Tudo para e outras consultas são bloqueadas e o aplicativo expira.

Aqui estão as tabelas principais e a contagem de linhas para 1 instrução de exclusão:

  • Viagens (4 linhas)
  • Segmentos (27 linhas, relacionadas a Viagens por SegmentId)
  • Perfis (linhas de 2012, relacionadas a Segmentos por SegmentId)
  • ProfileRanges (2337 linhas, relacionadas a perfis por ProfileId)
  • Eventos (7.750 linhas, relacionadas a segmentos por SegmentId)
  • EventConditions (9230 linhas, relacionadas a eventos por EventId)

As tabelas EventConditions e ProfileRanges têm mais de 1 bilhão de linhas cada.

Aqui está o cache do plano: https://www.brentozar.com/pastetheplan/?id=HJNg5I0BU

Quando olho no explorador de planos SentryOne, posso ver que o SQL está lendo a tabela inteira, mesmo que o "spool de tabela" filtre e mantenha apenas para 2012 linhas ProfileRanges e quase o mesmo para EventConditions.

Intervalos de perfil

Faixas de perfil do TableSpool

Condições do evento

Condições do evento TableSpool

Quando olho para a concessão de memória da consulta com o procedimento sp_blitzCache de Brent Ozar, posso ver que a consulta pede cerca de 10 GB de RAM.

concessão de memória

Depois disso, a consulta está esperando em SOS_SCHEDULER_YIEL (então esperando a vez de usar a CPU após os 4ms) ou MEMORY_ALLOCATION_EXT. O programa expira e falha.

O que posso fazer para que isso funcione?

Uma das coisas que eu estava pensando era remover as chaves estrangeiras nas duas maiores tabelas e excluir suas linhas em um gatilho em vez de. Mas eu não sou um grande fã de impor a consistência do banco de dados com gatilhos em vez de chaves estrangeiras.

Qualquer conselho ou ajuda será apreciado

A chave primária de ProfileRanges é

  • PerfilId int
  • ProfileRangeDefId1 int
  • ProfileRangeDefId2 int

A chave primária de EventConditions é

  • EventId bigint
  • EventConditionDefId int
sql-server query-performance

2 respostas

  1. Best Answer

    Supondo que todas as tabelas relacionadas tenham a indexação correta para os caminhos de exclusão, você pode tentar:

    DELETE [Trips]
WHERE [ISAFileName]='ID_774199_20200311_133117.isa'
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

    Se isso funcionar, tente reduzi-lo ao número mínimo de dicas.

    Esses tipos de planos são muito desafiadores para a estimativa de cardinalidade, e o modelo CE 'padrão' geralmente faz uma bagunça .

    Depois de ter uma forma de plano que funcione bem, você poderá forçar essa forma usando um guia de plano etc., se necessário.

    • 17

  2. Uma varredura de tabela na exclusão em cascata é um sintoma comum de não ter índices adequados em suas tabelas.

    Certifique-se de que todas as tabelas FK tenham índices que suportem as chaves estrangeiras. Ou seja, um índice clusterizado ou não clusterizado com a(s) coluna(s) FK como a(s) coluna(s) líder (es) no índice das outras tabelas.

    POR EXEMPLO

    create index ix_TripId on EventConditions (TripId)

    E considere se a coluna TripID FK não deve ser a coluna principal no Índice clusterizado, por exemplo:

    create table EventConditions
(
  TripId int not null,
  EventId bigint not null,
  EventConditionDefId int not null,
  constraint pk_EventConditions 
     primary key clustered(TripId, EventId, EventConditionDefId),
  ...
)

    Isso otimizaria cada tabela para acesso por TripId.

    Adicionalmente

    Uma das coisas que eu estava pensando era remover as chaves estrangeiras nas duas maiores tabelas e excluir suas linhas em um gatilho em vez de.

    Você não precisa remover os FKs. Basta excluir primeiro as tabelas filhas e, possivelmente, remover ON DELETE CASCADE do FK para exigir a exclusão das tabelas filhas primeiro. O que começaria com o carregamento de tabelas temporárias com os valores de chave a serem excluídos em cada nível e carregá-los de cima para baixo.

    create table #tripIdsToDelete(TripId int primary key)
insert into #tripIdsToDelete ...

create table #EventIdsToDelete(EventId int primary key)
insert into #EventIdsToDelete(EventID)
  select EventId from Events 
  where TripId in (select TripId from #tripIdsToDelete)
...
create table #EventConditionIdsToDelete ...
    • 9

relate perguntas