Início / dba / Perguntas / 306979
Accepted
J.D.
Asked: 2022-02-04 11:15:04 +0800 CST

O relatório de Consumo Geral de Recursos do Repositório de Consultas está me dizendo que meu banco de dados está tendo um desempenho terrível ou os números estão apenas errados?

  • 772

Recentemente, atualizamos um de nossos servidores de produção do SQL Server 2016 para o SQL Server 2019 (CU 15). Essa foi uma ótima oportunidade para habilitar o Query Store em nosso banco de dados de aplicativos principal. Ele está em execução há alguns dias e é isso que o Relatório geral de consumo de recursos está mostrando:

Relatório geral de consumo de recursos

Na captura de tela, selecionei alguns números que pareciam insanos (para o primeiro dia em que o Query Store foi ativado) e os normalizei para unidades de medida mais fáceis de serem discutidas. Coisas como ~183 TB de dados consumidos por leituras lógicas ou ~5 TB de dados consumidos pela memória, em um único dia, parecem quase impossíveis neste servidor.

Esse banco de dados é o John Smith dos bancos de dados, com apenas 100 GB de tamanho para o arquivo de dados e 200 GB para o arquivo de log. No máximo, talvez 100 usuários diferentes se conectam a ele ao longo do dia, e não há uma tonelada de transações sendo criadas em um único dia. O próprio servidor possui apenas 32 GB de memória provisionada para ele. Para que 5 TB de memória sejam consumidos, a memória alocada precisaria ser preenchida mais de 150 vezes ao longo do dia.

A única outra informação potencialmente relevante que posso pensar em adicionar é que, após a atualização, definimos imediatamente o "Nível de compatibilidade" desse banco de dados para 150 (SQL Server 2019) e deixamos a configuração "Estimativa de cardinalidade herdada" desativada. Eu sei que não é o ideal e é melhor deixar a poeira baixar enquanto coletamos as métricas básicas, mas parte do motivo da atualização foi corrigir alguns problemas urgentes de desempenho para os quais essa combinação de configurações funcionou melhor em nossos testes (e ainda parece estar funcionando muito bem).

Alguns dos problemas de desempenho anteriores que estávamos tendo eram devido a estimativas de cardinalidade insanas, que se o Query Store estivesse usando os pontos de dados estimados , então eu poderia ver os números deste relatório sendo correlativos, mas eu teria que imaginar que o relatório é usando pontos de dados reais ? Embora fosse interessante se isso fosse outro sinal de algo fundamentalmente errado com a forma como meu servidor / banco de dados de produção está configurado em minha conquista contínua para eliminar problemas de estimativa de cardinalidade.

Estou lendo esses números errado, o Query Store está bugando ou meu servidor está travando?

sql-server performance

2 respostas

  1. Best Answer

    Minha estação de trabalho pode fazer cerca de 100K-150K LIO/CPU Second. Um Logical IOs (LIO) está lendo uma única página de 8 KB do cache de página. Ou seja, ao executar uma grande varredura paralela de uma tabela em cache com um plano de consulta trivial, recebo estatísticas de E/S como:

    Tabela 'frs_big'. Contagem de varredura 9, leituras lógicas 519631 , leituras físicas 0, servidor de página lê 0, leitura antecipada lê 0, leitura antecipada do servidor de página lê 0, leitura lógica lob 0, leitura física lob 0, servidor de página lob lê 0, leitura lob ahead lê 0, lob page server read-ahead lê 0.

    (1 linha afetada)

    Tempos de execução do SQL Server: tempo de CPU = 5109 ms, tempo decorrido = 655 ms.

    E com MAXDOP 1 a mesma varredura leva apenas 3625 ms de CPU. E está rodando Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz.

    Durante o período, você tem 22.902.891.616 KB de E/S Lógica, cada LIO tem 8 KB, então 22902891616/8LIO e 20.393.171 ms de tempo de CPU. Então

    with q as
(
  select 22902891616/8 lio, 20393171 cpu 
)
select 1000*lio/cpu lio_per_cpu_sec, lio/(24*60*60) lio_per_sec
from q

    ou

    lio_per_cpu_sec                         lio_per_sec
--------------------------------------- ---------------------------------------
140,383                                 33,134

    140.383 LIO/CPU Segundo. Portanto, o número de E/S lógicos corresponde aproximadamente ao uso total da CPU e você tem uma média de 33.000 LIO/seg no período de 24 horas.

    Dado que você atualizou para 2019 com todo o comportamento do otimizador mais recente, você provavelmente tem alguns planos ruins que estão causando varredura excessiva, mas a velocidade do servidor e o tamanho moderado estão mantendo sua taxa de acertos de cache muito alta e mantendo o desempenho percebido aceitável.

    Não tenho certeza do que essa métrica de memória significa. Esta é a consulta por trás do relatório:

    exec sp_executesql N'WITH DateGenerator AS
(
SELECT CAST(@interval_start_time AS DATETIME) DatePlaceHolder
UNION ALL
SELECT  DATEADD(d, 1, DatePlaceHolder)
FROM    DateGenerator
WHERE   DATEADD(d, 1, DatePlaceHolder) < @interval_end_time
), WaitStats AS
(
SELECT
    ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms))*1,2) total_query_wait_time
FROM sys.query_store_wait_stats ws
    JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE NOT (itvl.start_time > @interval_end_time OR itvl.end_time < @interval_start_time)
GROUP BY DATEDIFF(d, 0, itvl.end_time)
),
UnionAll AS
(
SELECT
    CONVERT(float, SUM(rs.count_executions)) as total_count_executions,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) as total_logical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) as total_logical_io_writes,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) as total_physical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) as total_clr_time,
    ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) as total_dop,
    ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,
    ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) as total_rowcount,
    ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used*rs.count_executions))*0.0009765625,2) as total_log_bytes_used,
    ROUND(CONVERT(float, SUM(rs.avg_tempdb_space_used*rs.count_executions))*8,2) as total_tempdb_space_used,
    DATEADD(d, ((DATEDIFF(d, 0, rs.last_execution_time))),0 ) as bucket_start,
    DATEADD(d, (1 + (DATEDIFF(d, 0, rs.last_execution_time))), 0) as bucket_end
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY DATEDIFF(d, 0, rs.last_execution_time)
)
SELECT 
    total_count_executions,
    total_duration,
    total_cpu_time,
    total_logical_io_reads,
    total_logical_io_writes,
    total_physical_io_reads,
    total_clr_time,
    total_dop,
    total_query_max_used_memory,
    total_rowcount,
    total_log_bytes_used,
    total_tempdb_space_used,
    total_query_wait_time,
    SWITCHOFFSET(bucket_start, DATEPART(tz, @interval_start_time)) , SWITCHOFFSET(bucket_end, DATEPART(tz, @interval_start_time))
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket_start ORDER BY bucket_start, total_duration DESC) AS RowNumber
FROM UnionAll , WaitStats
) as UnionAllResults
WHERE UnionAllResults.RowNumber = 1
OPTION (MAXRECURSION 0)',N'@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@interval_start_time='2022-01-03 15:57:04.2571919 -06:00',@interval_end_time='2022-02-03 15:57:04.2571919 -06:00'

    e

    ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,

    não me parece uma métrica muito útil.

    • 6

  2. Como observação geral, acho que ninguém aqui pode responder à sua pergunta com 100% de confiança. Não temos base. No entanto, aqui estão algumas idéias sobre o que você pode fazer:

    Você tem alguma métrica coletada em ambos os servidores? Você pode compará-los?

    Os números no pop-up parecem ser cumulativos para todo o banco de dados para todas as consultas. Se minha matemática estiver correta, a duração média da consulta é de 400ms. Isso é comparável ao servidor antigo?

    Compare o uso da CPU. Por um dia são 5,5h no novo servidor. Assumindo 1 CPU lógica, é ~ 25% de uso médio da CPU. Assumindo 2 CPUs lógicas é 12,5% etc. Não temos os números para nenhum dos servidores, mas sabendo o uso médio de CPU e a contagem de processador lógico em ambos, você poderá comparar o tempo de CPU desses dois. Assumindo 2 CPUs lógicas, eu diria que o uso é baixo. As estatísticas de espera também são comparativamente baixas (presumo que o tempo de espera na captura de tela significa espera). Então, a menos que você tenha veneno, você está bem na minha opinião.

    Em geral, eu compararia as estatísticas de espera entre o servidor antigo e o novo. Se o novo servidor esperar menos, provavelmente você está bem.

    Finalmente: os usuários estão reclamando que algumas partes do aplicativo estão mais lentas agora? Isso seria um forte indício de que algumas consultas regrediram.

    • 2

relate perguntas