Encontrei novamente um problema com o SQL Server e a instrução MERGE e preciso de alguma confirmação.
Posso reproduzir meu problema constantemente em um banco de dados do Azure (mas não em um SQL Server 2017/2019 local).
Por favor, execute os seguintes passos (passo a passo, não em uma execução de comando)!
1) Script para Schema:
CREATE TABLE [dbo].[ImpactValueHistory]
(
[Rn] BIGINT NOT NULL,
[ImpactId] UNIQUEIDENTIFIER NOT NULL,
[ImpactValueTypeId] INT NOT NULL,
[Date] DATE NOT NULL,
[Value] DECIMAL(38, 10) NOT NULL,
[ValidFrom] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
[ValidTo] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
[ImpactPeriodId] INT NOT NULL,
[NormalizedValue] DECIMAL(38, 10) NOT NULL,
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX [COLIX_ImpactValueHistory]
ON [dbo].[ImpactValueHistory];
GO
CREATE NONCLUSTERED INDEX [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId]
ON [dbo].[ImpactValueHistory] ([ValidFrom], [ValidTo], [ImpactId], [ImpactValueTypeId], [Date]);
GO
CREATE TABLE [dbo].[ImpactValue]
(
[Rn] BIGINT NOT NULL IDENTITY(1,1),
[ImpactId] UNIQUEIDENTIFIER NOT NULL,
[ImpactValueTypeId] INT NOT NULL,
[Date] DATE NOT NULL,
[Value] DECIMAL(38, 10) NOT NULL,
[ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT [DF_ImpactValue_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
[ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT [DF_ImpactValue_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
[ImpactPeriodId] INT NOT NULL,
[NormalizedValue] DECIMAL(38, 10) NOT NULL,
PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),
CONSTRAINT [PK_ImpactValue] PRIMARY KEY NONCLUSTERED ([ImpactId], [ImpactValueTypeId], [Date], [ImpactPeriodId])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ImpactValueHistory]))
GO
CREATE UNIQUE CLUSTERED INDEX [IX_ImpactValue_Id] ON [dbo].[ImpactValue]([Rn])
GO
CREATE COLUMNSTORE INDEX [CIX_ImpactValue] ON [dbo].[ImpactValue] ([ImpactId], [ImpactValueTypeId], [Date], [Value], [NormalizedValue])
GO
2) Script para inserir alguns dados aleatórios
DECLARE @inserted0 TABLE ([Date] DATE, [ImpactId] uniqueidentifier, [ImpactPeriodId] int, [ImpactValueTypeId] int);
MERGE [dbo].[ImpactValue] USING (
SELECT TOP 278 -- <-- this number is critical
DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY [Name]) - 1, '2000-01-01') AS [Date],
NEWID() AS [ImpactId],
1 AS [ImpactPeriodId],
1 AS [ImpactValueTypeId],
99 AS [Value],
99 AS [NormalizedValue]
FROM [sys].[all_columns]
) AS i ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue]) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue])
VALUES (i.[Date], i.[ImpactId], i.[ImpactPeriodId], i.[ImpactValueTypeId], i.[Value], i.[NormalizedValue])
OUTPUT INSERTED.[Date], INSERTED.[ImpactId], INSERTED.[ImpactPeriodId], INSERTED.[ImpactValueTypeId]
INTO @inserted0;
SELECT * FROM @inserted0
Esta etapa deve retornar como resultado todas as linhas inseridas!
3) Excluir dados da Etapa 2) Esta etapa está preenchendo a tabela de histórico configurada
DELETE [dbo].[ImpactValue]
4) Insira novamente alguns dados aleatórios Você pode usar o script da etapa 2)
Devo observar que as etapas 1) - 4) devem ser executadas separadamente e não em arquivos GO.
Novamente esta etapa deve retornar como resultado todas as linhas inseridas! Mas não! Do meu lado sempre recebo um resultado vazio. Isso pode ser reproduzido em três de nossos bancos de dados de produção :(
A instrução MERGE é gerada pelo EF Core e atualmente estou resolvendo isso definindo um Max Batch Size. Mas esta não poderia ser a solução final.
Deve ter algo a ver com Tabelas Temporais com um índice não clusterizado configurado na tabela temporal.
Veja também:
No passado eu já tropecei neste problema:
- https://stackoverflow.com/questions/70734060/why-does-a-merge-into-a-temporal-table-with-a-nonclustered-index-in-the-history .
- https://github.com/dotnet/efcore/issues/22852
Mas meu problema atual só pode ser reproduzido em um Banco de Dados SQL do Azure e não gera nenhum erro.
Notas laterais interessantes:
- Se eu desabilitar temporariamente a tabela temporal -> está funcionando
- Se eu excluir o índice não clusterizado [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId] -> está funcionando
- Se eu usar SELECT TOP (@BatchSize) na etapa 2) --> está funcionando
- Se eu usar apenas OUTPUT em vez de OUTPUT INTO @inserted0 --> está funcionando
Sem o índice COLUMNSTORE na tabela de histórico está funcionando. Ao remover apenas o índice COLUMNSTORE na tabela principal, vejo o mesmo problema.
Plano de execução real para um caso que (a) reproduz o problema com TOP 278e (b) não reproduz TOP (@BatchSize)disponível em https://1drv.ms/u/s!AsOa6e9ukBWQlIRg9_9eySDFp5hvEA?e=KBQBsP . Também adicionei os planos de execução reais para o tamanho do lote de 277. Ambos estão trabalhando com esse tamanho!
O Banco de Dados SQL do Azure às vezes cria um plano de execução inválido para sua inserção de mesclagem.
Quando ele decide manter a tabela de histórico de armazenamento de colunas usando um único operador (um plano estreito), tudo está bem. Isso inclui trivialmente o caso em que a tabela de histórico não possui índices secundários.
Quando ele decide manter a tabela de histórico usando operadores separados para a tabela base e índices secundários (um plano amplo), as coisas dão errado ao usar a
OUTPUT INTOopção. A escolha do plano é sensível às estimativas de cardinalidade.Por exemplo, o plano
OUTPUTsomente para (não gravando em uma variável de tabela) inclui um spool de tabela. O spool salva as linhas antes do Filtro que remove todas as linhas do fluxo em que ValidTo é nulo. O spool então reproduz as linhas (não filtradas) para retornar ao cliente:Quando
OUTPUT INTOé usado, o mesmo fluxo é usado para manter o índice secundário na tabela de histórico e fornecer linhas para a tabela de saída. Isso cria um problema porque uma inserção pura resulta em nenhuma linha adicionada ao histórico, portanto, todas as linhas são filtradas.Um índice de armazenamento de colunas não é necessário na tabela de histórico para que esse problema se manifeste.
Este é um defeito do produto que você deve relatar diretamente ao Suporte da Microsoft criando uma solicitação de suporte no Portal do Azure.
Nota lateral: as linhas que chegam à tabela de histórico Index Insert não são realmente inseridas porque a coluna de ação informa que não. Esses detalhes não são expostos no showplan infelizmente. Uma possível correção seria testar a ação , bem como ValidTo no filtro.
O filtro extra não aparece no SQL Server 2019 CU16-GDR:
Isso parece uma correção de bug para problemas de nulidade implícitos que foram aplicados ao Banco de Dados SQL do Azure antes do produto de caixa. Nesse caso, é um pouco surpreendente que não reaja às dicas de nível de compatibilidade de QO.
Daniel C .: