Acabei de depurar um problema que me deixou completamente confuso.
Um processo de transformação ETL em nosso data warehouse de desenvolvimento acabou de falhar depois de trabalhar com sucesso todos os dias por meses. O mesmo trabalho do SSIS chamando o mesmo proc armazenado, com o mesmo esquema de tabela, índices e dados funciona bem na produção.
Esta etapa normalmente levaria menos de 2 min. Hoje, depois de 4 horas, o trabalho não foi concluído, mas também não falhou. Não há erros relatados. Nada no log do SQL e sp_who2não mostra nada bloqueando.
- Aqui está um link para o plano estimado quando a consulta tiver um bom desempenho.
- Aqui está um link para o plano estimado quando a consulta não for concluída.
O trabalho trunca uma tabela de preparo e insere cerca de 600.000 linhas de dados. O processo ETL tem acesso exclusivo à tabela. Quando verifiquei, tudo o que pude ver foi espera CXPACKET.
Eu rastreei a falha para um índice clusterizado exclusivo.
A tabela tem uma chave primária não clusterizada em uma coluna de identidade (veja abaixo)
CREATE TABLE [dbo].[Transform_JobCosting_Transaction](
[ETL_TransformKey] [int] IDENTITY(1,1) NOT NULL,
[TransactionId] [varchar](255) NOT NULL,
[KeyType] [varchar](255) NOT NULL,
[FinancialYear] [varchar](255) NOT NULL,
[Job] [varchar](255) NOT NULL,
[Subjob] [varchar](255) NOT NULL,
[AnalysisCode] [varchar](255) NULL,
[etc] [varchar](255) NOT NULL,
[etc] [varchar](255) NOT NULL,
[etc] [varchar](255) NOT NULL
CONSTRAINT [PK_Transform_JobCosting_Transaction] PRIMARY KEY NONCLUSTERED
(
[ETL_TransformKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
O índice clusterizado do problema é:
CREATE UNIQUE CLUSTERED INDEX [IDX_Unique] ON [dbo].[Transform_JobCosting_Transaction]
( [FinancialYear] ASC,
[KeyType] ASC,
[TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Há um terceiro índice não agrupado que não afeta a inserção.
Dois de nós estão trabalhando nisso há 4 horas. Eu deixei cair e re-adicionei o Índice 20-30 vezes tentando opções em diferentes combinações.
Resumo: O índice clusterizado bloqueia inserções. Não agrupado funciona bem.
Nós tentamos:
- A reinicialização do servidor não fez diferença.
- Tabelas truncadas e sp são executadas manualmente a partir do SSMS sem diferenças. (Com privs SA)
- Reconstruir o índice não ajudou.
- Se eu descartar o índice clusterizado, a inserção funciona.
- Após inserir acima, posso adicionar o índice clusterizado sem erro.
- Se descartar e adicionar novamente o índice como não agrupado, ele funcionará.
- Eu verifiquei os dados e é único agrupado por esses 3 campos.
- Alterar o índice para que não seja exclusivo não fez diferença.
- Adicionar/remover
with tablockdica não ajudou. - Tentei classificar os dados antes de inserir e não fez diferença.
Em execução: Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Developer Edition (64 bits) no Windows Server 2012 R2 Standard 6.3
Quaisquer idéias ou sugestões seriam muito apreciadas.
Vamos dar um passo atrás e esquecer toda a solução de problemas em torno do índice clusterizado. Você tem uma
INSERTconsulta que costumava terminar em um período de tempo razoável, mas agora não termina depois de horas. Por que essa consulta agora pode ser lenta? Vamos dar uma olhada no plano estimado:Lendo da direita para a esquerda, o plano é primeiro escanear a única linha de
Extract_DW_Control_Finance, fazer um loop join com um scan ofExtract_JCS_Transno lado interno, classificar os dados de acordo com a chave clusterizada da tabela de destino e fazer outro loop join com um digitalização doExtract_GL_Jnl_Translado interno. A primeira junção provavelmente não é o problema. O plano não pode realmente se beneficiar do paralelismo, mas com uma única linha no conjunto de resultados externo, a verificaçãoExtract_JCS_Transdeve ocorrer apenas uma vez. No entanto, o otimizador estima que uma única linha sairá dessa junção. Se essa estimativa de linha estiver errada, você poderá acabar fazendo centenas de milhares de varreduras de índice clusterizado noExtract_GL_Jnl_Trans.O plano de consulta para a consulta com bom desempenho usa uma estratégia diferente. As estimativas de linha são significativamente diferentes e executa uma junção de hash:
Suspeito que o otimizador escolherá um plano diferente para a consulta com baixo desempenho se você corrigir as estimativas de linha. Se a
Extract_DW_Control_Financetabela sempre tiver uma linha, considere movê-la para uma variável local e possivelmente usar umaRECOMPILEdica. Isso poderia resultar em uma estimativa muito melhor.Em termos de por que a remoção do índice clusterizado causa o problema, suspeito que o otimizador faça uma junção de hash
Extract_GL_Jnl_Transsem o índice clusterizado. Uma junção de hash não preserva a ordem da entrada externa, mas uma junção de loop preserva a ordem. O otimizador pode ter custado fazer a classificação em uma única linha e executar uma junção de loop menor do que fazer uma junção de hash e executar a classificação posteriormente em 356.566 linhas. No entanto, se a classificação não for necessária, fazer a junção de hash pode ter um custo menor do que a junção de loop. Provavelmente tudo se resume a corrigir suas estimativas de cardinalidade.Se você precisar solucionar mais problemas enquanto a consulta lenta estiver em execução, considere o sinalizador de rastreamento 7412 se estiver no SQL Server 2016 SP1. Isso deve fornecer pistas sobre onde o SQL Server está "preso" no plano de consulta. Se você puder solicitar um plano real ou executar a consulta diretamente no SSMS, poderá usar sys.dm_exec_query_profiles ou o recurso de estatísticas de consulta ao vivo.