Início / dba / Perguntas / 106246
Accepted
Dave
Asked: 2015-07-08 13:14:58 +0800 CST

Uso excessivo de CPU no banco de dados SQL Server do fornecedor

  • 772

Contexto: Eu tenho um banco de dados SQL Server 2012 fornecido por um fornecedor, portanto, a modificação de consultas e tabelas é limitada. Nós possuímos o banco de dados, portanto, podemos adicionar e manter índices.

Os índices não foram mantidos ou reconstruídos, então há centenas com mais de 30% de fragmentação... essa é minha suspeita inicial de uso massivo e constante da CPU, mas enquanto trabalhamos para corrigir isso, estou investigando outros problemas.

Não estou vendo nenhuma pressão significativa de memória ou E/S de disco. Este é um sistema OLTP relativamente pouco usado e foi bem provisionado para recursos... realmente não deveria ter nenhum problema, ou pelo menos deveria ter apenas picos perceptíveis, sem uso constante de CPU.

Duas questões:

  1. As estatísticas desatualizadas e os índices altamente fragmentados em todo o banco de dados podem causar o uso excessivo da CPU?

  2. A combinação de estatísticas de espera listadas abaixo deste sistema desacredita a explicação da fragmentação do índice?

Em formação:

WaitType                                    Wait_S
---------------------------------           -----------    
CXPACKET                                    773345.21
PAGELATCH_UP                                737295.83
SOS_SCHEDULER_YIELD                         140425.24
LATCH_EX                                    69877.95
RESOURCE_SEMAPHORE_QUERY_COMPILE            60985.48
LCK_M_SCH_S                                 39488.17

Consulta de origem para os resultados da espera:

WITH [Waits] AS
(
 SELECT
     [wait_type],
     [wait_time_ms] / 1000.0 AS [WaitS],
     ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
     [signal_wait_time_ms] / 1000.0 AS [SignalS],
     [waiting_tasks_count] AS [WaitCount],
     100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
     ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
 FROM 
     sys.dm_os_wait_stats
 WHERE 
     [wait_type] NOT IN (... common waits )
     AND [waiting_tasks_count] > 0)
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95;
sql-server performance

1 respostas

  1. Best Answer

    Os índices não foram mantidos ou reconstruídos, então existem centenas com mais de 30% de fragmentação ... esta é minha suspeita inicial de uso massivo e constante da CPU ...

    e

    As estatísticas desatualizadas e os índices altamente fragmentados em todo o banco de dados podem causar o uso excessivo da CPU?

    Isso é parcialmente verdade. A fragmentação do índice não causará CPU ALTA. A fragmentação interna significa que você tem muito espaço livre nas páginas e levará mais tempo para verificar o índice. Isso incorrerá em mais E/S de disco e exigirá mais memória para armazenar o índice (devido aos espaços livres nas páginas de índice), o que significa mais espaço desperdiçado no buffer pool.

    Estatísticas incorretas farão com que o otimizador de consulta gere planos ineficientes (ruins), causando um desempenho degradado, por exemplo , consultas que levaram 2 segundos para serem concluídas levarão 2 minutos ou 2 horas, etc, pois o servidor SQL fará uma estimativa incorreta (por exemplo, estimará 1 linha como oposto a 2 milhões de linhas reais) e pode escolher uma junção inadequada realizando um alto número de leituras ou pode escolher uma junção ruim, por exemplo, loop aninhado em que um hash ou junção de mesclagem teria sido uma escolha melhor. Estatísticas ruins (desatualizadas ou antigas) colocarão sua CPU em um nível muito mais alto.

    Portanto, manter suas estatísticas e índices desfragmentados definitivamente ajudará. Em vez de criar sua própria solução, eu recomendaria usar a solução de manutenção de índice da Ola .

    Consulte a excelente postagem de Kendra: Por que fragmentação de índice e estatísticas ruins nem sempre são o problema (vídeo)?

    Remus Rusanu tem uma postagem de blog muito boa sobre: ​​The Bizzaro Guide to SQL Server Performance (cuidado: não siga!)

    A combinação de estatísticas de espera listadas abaixo deste sistema desacredita a explicação da fragmentação do índice?

    Acredito que pode haver mais coisas a serem abordadas no nível de configuração do servidor SQL do que apenas se preocupar com a fragmentação do índice. Além disso, a espera do CXPACKET em si não é um problema.

    Coisas para verificar:

    Você pode usar as consultas de diagnóstico de Glenn Berry - versão 2012

    -- Signal Waits for instance  (Query 27) (Signal Waits)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) 
AS [% Signal (CPU) Waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) 
AS [% Resource Waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE);

-- Signal Waits above 15-20% is usually a sign of CPU pressure

    e

    -- Top Cached SPs By Total Worker time (SQL Server 2012). Worker time relates to CPU cost  (Query 44) (SP Worker Time)
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], 
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

    Além disso, Joe Sack fala sobre a metodologia de solução de problemas para problemas de desempenho da CPU do SQL Server

    • 3

relate perguntas