Início / dba / Perguntas / 194679
Accepted
Steffen Mangold
Asked: 2018-01-05 15:14:24 +0800 CST

100% CPU com plano de execução ruim

  • 772

Eu tenho um grande problema com picos de CPU de 100% devido a um plano de execução ruim usado por uma consulta específica. Passo semanas agora resolver sozinho.

Meu banco de dados

Meu banco de dados de exemplo contém 3 tabelas simplificadas.

[Data logger]

CREATE TABLE [model].[DataLogger](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ProjectID] [bigint] NULL,
CONSTRAINT [PK_DataLogger] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[Inversor]

CREATE TABLE [model].[Inverter](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SerialNumber] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Inverter] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UK_Inverter] UNIQUE NONCLUSTERED 
(
    [DataLoggerID] ASC,
    [SerialNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [model].[Inverter] WITH CHECK
ADD CONSTRAINT [FK_Inverter_DataLogger]
FOREIGN KEY([DataLoggerID])
REFERENCES [model].[DataLogger] ([ID])

[Dados do Inversor]

CREATE TABLE [data].[InverterData](
    [InverterID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [DayYield] [decimal](18, 2) NULL,
 CONSTRAINT [PK_InverterData] PRIMARY KEY CLUSTERED 
(
    [InverterID] ASC,
    [Timestamp] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

Estatísticas e manutenção

A [InverterData]tabela contém vários milhões de linhas (difere em várias instâncias PaaS) particionadas em lixos mensais.

Todos os indexadores são desfragmentados e todas as estatísticas são recompiladas/reorganizadas conforme necessário em um turno diário/semanal.

Minha consulta

A consulta é gerada pelo Entity Framework e também simples. Mas eu corro 1.000 vezes por minuto e o desempenho é essencial.

SELECT
[Extent1].[InverterID] AS [InverterID],
[Extent1].[DayYield] AS [DayYield]
FROM [data].[InverterDayData] AS [Extent1]
INNER JOIN [model].[Inverter] AS [Extent2] ON [Extent1].[InverterID] = [Extent2].[ID]
INNER JOIN [model].[DataLogger] AS [Extent3] ON [Extent2].[DataLoggerID] = [Extent3].[ID]
WHERE ([Extent3].[ProjectID] = @p__linq__0)
AND ([Extent1].[Date] = @p__linq__1) OPTION (MAXDOP 1)

A MAXDOP 1dica é para outro problema com um plano paralelo lento.

O "bom" plano

Durante os 90% do tempo, o plano usado é extremamente rápido e se parece com isso:

plano rápido

O problema

Ao longo do dia, o plano bom mudou aleatoriamente para um plano ruim e lento.

O plano "ruim" é usado por 10 a 60 minutos e depois alterado de volta para o plano "bom". O plano "ruim" aumenta a CPU para 100% permanente.

Isto é o que parece:

plano lento

O que eu tentei até agora

Meu primeiro pensamento foi o Hash Matché o bad boy. Então eu modifiquei a consulta com uma nova dica.

...Extent1].[Date] = @p__linq__1) OPTION (MAXDOP 1, LOOP JOIN)

O LOOP JOINdeve forçar a usar Nested Loopo instante de Hash Match.

O resultado é que o plano de 90% se parece com antes. Mas o plano também mudou aleatoriamente para um plano ruim.

O plano "ruim" agora se parece com isso (ordem do loop de tabela alterada):

também plano lento

A CPU também atinge 100% durante o plano "novo ruim".

Solução?

Me vem à mente forçar o "bom" plano. Mas não sei se isso é uma boa ideia.

Dentro do plano há um índice recomendado que inclui todas as colunas. Mas isso dobrará a tabela completa e diminuirá a velocidade das inserções que são muito frequentes.

Por favor me ajude!

Atualização 1 - relacionada ao comentário @James

Aqui estão os dois planos (alguns campos extras mostrados no plano porque são da tabela real):

Bom plano

Plano ruim 1 (Hash Match)

Plano ruim 2 (Loop aninhado)

Atualização 2 - relacionada à resposta do @David Fowler

O plano ruim está entrando em ação no valor do parâmetro aleatório. Então normalmente eu @p__linq__1 ='2016-11-26 00:00:00.0000000' @p__linq__0 =20825o dia inteiro e depois o plano ruim vem no mesmo valor.

Eu conheço o problema de sniffing de parâmetros de procedimentos armazenados e como evitá-los dentro do SP. Você tem uma dica para mim como evitar esse problema para minha consulta?

A criação do índice recomendado incluirá todas as colunas. Isso dobrará a tabela completa e diminuirá a velocidade das inserções, que são muito frequentes. Isso não "parece" certo para construir um índice que simplesmente clona a tabela. Também pretendo dobrar o tamanho dos dados desta grande tabela.

Atualização 3 - relacionada ao comentário de @David Fowler

Também não funcionou e acho que não poderia. Para uma melhor compreensão vou explicar como a consulta é chamada.

Vamos supor que eu tenha 3 entidades na [DataLogger]tabela. Ao longo do dia, eu chamo as mesmas 3 consultas em uma ida e volta:

Consulta básica: ...WHERE ([Extent3].[ProjectID] = @p__linq__0) AND ([Extent1].[Date] = @p__linq__1)

Parâmetro:

  1. @p__linq__0 = 1; @p__linq__1 = '2018-01-05 00:00:00.0000000'
  2. @p__linq__0 = 2; @p__linq__1 = '2018-01-05 00:00:00.0000000'
  3. @p__linq__0 = 3; @p__linq__1 = '2018-01-05 00:00:00.0000000'

O parâmetro @p__linq__1é sempre a mesma data. Mas ele escolhe o plano ruim aleatoriamente em uma consulta que é executada milhares de vezes com um plano bom antes. Com o mesmo parâmetro!

Atualização 4 - relacionada ao comentário @Nic

A manutenção é executada todas as noites e se parece com isso.

Índice

Se um Índice for fragmentado em mais de 5%, ele será reorganizado...

ALTER INDEX [{index}] ON [{table}] REORGANIZE

Se um índice for fragmentado em mais de 30%, ele será reconstruído...

ALTER INDEX [{index}] ON [{table}] REBUILD WITH (ONLINE=ON, MAXDOP=1)

Se o Índice for particionado, ele será verificado quanto à fragmentação e alterado por partição...

ALTER INDEX [{index}] ON [{table}] REBUILD PARTITION = {partitionNr} WITH (ONLINE=ON, MAXDOP=1)

Estatisticas

Todas as estatísticas serão atualizadas se modification_counterfor maior que 0...

UPDATE STATISTICS [{schema}].[{object}] ([{stats}]) WITH FULLSCAN

ou em particionado..

UPDATE STATISTICS [{schema}].[{object}] ([{stats}]) WITH RESAMPLE ON PARTITIONS({partitionNr})

A manutenção inclui todas as estatísticas, também a gerada automaticamente.

Exemplo

performance sql-server-2016

1 respostas

  1. Best Answer

    Olhe para os planos, existem algumas diferenças entre o bom e os ruins. A primeira coisa a notar é que o bom plano realiza uma busca em InverterDayData, onde ambos os planos ruins realizam uma varredura. Por que isso, se você verificar as linhas estimadas, verá que o bom plano está esperando 1 linha, enquanto os planos ruins estão esperando 6661 e cerca de 7000 linhas.

    Agora dê uma olhada nos valores dos parâmetros compilados,

    Bom plano @p__linq__1 ='2016-11-26 00:00:00.0000000' @p__linq__0 =20825

    Planos ruins @p__linq__1 ='2018-01-03 00:00:00.0000000' @p__linq__0 = 20686

    então está me parecendo um problema de sniffing de parâmetros, quais valores de parâmetro você está passando para essa consulta quando ela está com um desempenho ruim?

    Há uma recomendação de índice nos planos ruins em InverterDayData que parece sensato, eu tentaria executar isso e ver se isso ajuda você. Pode permitir que o SQL execute uma varredura na tabela.

    • 3

relate perguntas