Início / dba / Perguntas / 175227
Accepted
Nick.McDermaid
Asked: 2017-06-02 17:57:08 +0800 CST

Query se transformou em um monstro devorador de CPU

  • 772

Eu tenho um SP cujo tempo de execução passou de 5 minutos > 20 minutos > 30 minutos > 53 minutos ao longo de quatro dias

As esperas estavam mostrando CPU crescente e status suspenso

Eu isolei uma única consulta que fixa a CPU

UPDATE thing.table
SET YYYYMM = 
CASE 
  WHEN 
    DAY(SnapshotDate) = 1
    OR 
  SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table) 
  THEN CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
  ELSE NULL
END

Executei novamente, adicionando WITH (RECOMPILE)no final - sem diferença

Eu corri UPDATE STATISTICS thing.table- nenhuma diferença

Seria interessante executá-lo e obter o plano real, mas não quero prender a CPU por uma hora. Verifiquei sys.dm_exec_cached_plans, mas parece ter apenas o plano estimado, não o plano real

Eu reescrevi usando CONVERTem vez de FORMAT(porque desconfio de coisas novas) - nenhuma diferença

Então eu reescrevi assim e levei a execução de volta para alguns segundos:

BEGIN TRAN;

UPDATE thing.table
SET YYYYMM = NULL;

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE 
(
DAY(SnapshotDate) = 1 
OR
SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table) 
);

COMMIT TRAN;

A tabela tem cerca de 150.000 registros. É bem possível que recentemente tenha muito mais registros despejados nele, distorcendo as estatísticas, mas por que consertar isso WITH(RECOMPILE)e não? UPDATE STATISTICSEle tira um instantâneo diário e possivelmente o número de registros aumentou devido ao final do mês.

Então as perguntas são:

  1. O plano de consulta real está armazenado em qualquer lugar?, para me poupar de executá-lo interativamente?
  2. Normalmente, quando uma consulta de repente leva uma eternidade, as estatísticas ficam obsoletas, mas esse não parece ser o caso aqui

Esta é a minha versão do SQL Server

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) 20 de abril de 2015 17:29:27 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows NT 6.3 (Build 9600: ) (Hypervisor)

Aqui estão os planos de consulta lenta e rápida. Não é surpresa que eles sejam diferentes porque estão fazendo coisas diferentes:

Plano lento:

Plano de consulta lenta

Plano Rápido:

Plano de consulta rápida

Percebo que o slowpoke usa uma junção de loop e o fasty usa uma correspondência de hash.

Percebo que a pequena perna da junção do loop tem filtro

[Expr1006]=DB.thing.table.[SnapshotDate]. Talvez isso não fosse mais tão pequeno?

sql-server sql-server-2014

2 respostas

  1. Best Answer

    A primeira consulta é tão lenta porque fará uma varredura completa da tabela thing.tablepara cada linha dessa tabela para a qual o DAY(SnapshotDate) <> 1. Portanto, se você tiver 100 mil linhas na tabela, no pior caso, fará 100 mil varreduras, o que significa ler 10 bilhões de linhas. Se a tabela for pequena o suficiente, ela permanecerá na memória para que sua consulta paralela pareça queimar na CPU.

    Você pode dizer observando o plano de consulta com cuidado. A varredura está no lado interno de uma junção de loop aninhada. Se essa não é sua xícara de chá, você pode tentar estatísticas de consulta ao vivo para ver a consulta enquanto ela é executada. Dessa forma, você pode obter algumas informações do plano real sem precisar concluir a consulta. Não há como economizar em planos reais antigos sem configurar eventos estendidos.

    A segunda consulta é mais rápida porque o otimizador de consultas é mais livre para reorganizar os elementos da consulta devido à falta da CASEexpressão. Em vez de calcular a subconsulta MAX(SnapshotDate)uma vez por linha, o cálculo é feito uma vez por consulta.

    Você definitivamente desejará corrigir essa consulta de alguma forma ou o tempo de execução continuará a crescer quadraticamente com o número de linhas na tabela. Uma solução seria adicionar um índice à SnapshotDatecoluna. A subconsulta ainda será executada uma vez para cada linha, mas obter o valor máximo será uma operação muito barata. Uma maneira melhor é salvar o valor da subconsulta em uma variável local e usá-la em sua UPDATEconsulta. A menos que você tenha que se preocupar com a simultaneidade, isso não deve ser um problema.

    Você também pode manter a correção que encontrou, se quiser. Uma sugestão que pode ajudar em alguns casos (dependendo da estrutura da tabela) seria adicionar uma cláusula where ao seu first UPDATE:

    UPDATE thing.table
SET YYYYMM = NULL
WHERE YYYYMM IS NOT NULL;
    • 4

  2. Eu recomendaria quebrar a consulta da seguinte forma:

    --select MAX(SnapshotDate) only once
DECLARE @MAX_SnapshotDate datetime  --use the appropriate data type
SELECT @MAX_SnapshotDate=MAX(SnapshotDate) FROM thing.table

--Create computed column
ALTER TABLE thing.table
ADD DAY_SnapshotDate as (DAY(SnapshotDate)) PERSISTED

UPDATE thing.table
SET YYYYMM = NULL;

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE 
SnapshotDate = @MAX_SnapshotDate

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE 
DAY_SnapshotDate = 1
    • 0

relate perguntas