Todos os dias, as lojas podem inserir informações de vendas em nosso aplicativo OLTP. O aplicativo chama um procedimento armazenado em SQL para salvar essas informações. Com base na atividade dos usuários, o aplicativo envia um código indicando se o proc deve realizar uma inserção, atualização ou exclusão.
Este procedimento armazenado de salvamento está recebendo concessões de memória de 60 GB para exclusões em uma linha. Para duplicar o problema, executei uma consulta de exclusão ad-hoc entre um begin trane rollbacke capturei o plano real abaixo:
https://www.brentozar.com/pastetheplan/?id=r189liBI4
O esquema é como segue:
Daily_Item_Sales_Headers -- ~100 million rows on this system
========================
DlyItmSlsHdr_Key decimal(15,0) primary key nonclustered,
DlyItmSlsHdr_PaperworkBatch_Key decimal(15,0), -- FK to parent batch of data that contains other types of data
UK_DlyItmSlsHdr_PaperworkBatchKey_Key clustered, unique, unique key located on PRIMARY (DlyItmSlsHdr_PaperworkBatch_Key, DlyItmSlsHdr_Key)
Daily_Item_Sales -- ~790 million rows on this system
================
DlyItmSls_Key decimal(15,0) primary key nonclustered,
DlyItmSls_DlyItmSlsHdr_Key decimal(15,0), -- FK to header table, cascade delete,
UK_DlyItmSls_DlyItmSlsHdrKey_Key clustered unique constraint on (DlyItmSls_DlyItmSlsHdr_Key, DlyItmSls_Key)
[columns about sales data]
A consulta que fiz é simples:
delete Daily_Item_Sales_Headers where DlyItmSlsHdr_Key = 1
O plano mostra que a exclusão do cabeçalho está em cascata corretamente para as linhas de vendas filhas. O plano também mostra uma busca de índice no índice clusterizado da tabela filha. No entanto, essa busca de índice clusterizado para a tabela filho tem uma estimativa de 790 milhões de linhas. O número real de linhas é ~100. As linhas estimadas altas estão causando uma concessão de memória de aproximadamente 60 GB.
Usando dbcc show_statisticsos índices da tabela filho, pude ver que as estatísticas foram atualizadas ontem à noite com um tamanho de amostra de 2%. O histograma mostra entre 1 e ~33.000 linhas estimadas por chave pai. Portanto, as estatísticas parecem mostrar que a estimativa deve ser muito menor.
Por que essa consulta de exclusão está gerando uma concessão de memória tão grande?
Eu vi esta pergunta sobre concessões excessivas de memória de classificação que parecem ser causadas por um bug, mas parece diferente para mim, porque não há classificações neste plano. Talvez seja o mesmo bug se aplicando aos carretéis da tabela ao cascatear para a tabela filho?
Concessão de memória de classificação excessiva
Por causa da cascata de chave estrangeira, não acho que possa contornar a concessão de memória excluindo as linhas filhas primeiro antes de excluir as linhas pai. Este é um sistema OLTP com até 10.000 lojas trabalhando ao mesmo tempo, então não posso descartar as chaves estrangeiras sob demanda para uma única exclusão.
EDITAR 28/02/2019 13:13 CST
A instância SQL tem cerca de 400 GB de memória alocada para ela.
O aplicativo tem os seguintes sinalizadores de rastreamento habilitados:
- 1222: rastreamento de impasse
- 4199: correções do processador de consultas
- 2312: use o estimador de cardinalidade de 2014
- 2453: cardinalidade da variável @Table
A desativação dos sinalizadores de rastreamento gera estimativas diferentes para a exclusão em cascata da tabela filha:
Trace Flag 2312 Trace Flag 4199 Row Estimate
=============== =============== =============
on on 790 million rows
on off 608 rows (very accurate)
off on 1 row
off off 1 row
Adicionar um querytraceonpara o sinalizador 9130 mencionado na pergunta vinculada não faz diferença.
Um colega de trabalho encontrou este artigo interessante sobre um bug de memória no SQL 2014. A resolução vinculada era adicionar option (MAX_GRANT_PERCENT = 1)à consulta.
https://www.thereregister.co.uk/2016/02/09/microsoft_sql_server_2014_bug/
EDITAR
A versão exata do SQL Server é SQL Server 2014 SP2 CU 12.
O banco de dados é o nível de compatibilidade 110 - SQL Server 2012. Não podemos alterar o nível de compatibilidade por um tempo.
Acredito que você esteja encontrando o mesmo bug no novo Estimador de Cardinalidade que eu encontrei. Eu escrevi sobre o problema aqui, com uma visão mais detalhada aqui.
Resumindo, o novo Estimador de Cardinalidade tem um bug ao estimar linhas a serem excluídas em uma exclusão em cascata. Se o valor de condução na tabela pai estiver longe o suficiente de seu histograma, o CE assumirá que todas as linhas da tabela filho serão excluídas.
Sorte sua, porém, que meu servidor pegou as estimativas altas e decidiu começar a escanear :(
Também para sua sorte, você está lidando com um proc em vez de uma loucura ORM, então existem soluções viáveis. Uma seria adicionar
DBCC TRACEOFF( 4199)ao seu procedimento (potencialmente causando problemas de permissões). Isso não o protege do futuro, pois em 2016+ o status 4199 não importa para o bug CE. Outra opção pode ser adicionarOPTION(OPTIMIZE FOR (@1 = <some middle value>)).Como você não está recebendo um plano de varredura, também pode fortalecer a concessão de memória com MAX_GRANT_PERCENT.