Este documento explica como rever e otimizar uma instância do Cloud SQL para SQL Server se essa instância for identificada pelo recomendador de instâncias com aprovisionamento insuficiente como tendo uma utilização elevada da CPU.
Se uma vCPU da instância não tiver o tamanho adequado, pode tornar-se uma fonte de contenda. Para verificar se a CPU é um gargalo ou está subaprovisionada, use as consultas neste documento.
Use as estatísticas de consultas para identificar consultas com um elevado consumo da CPU
As estatísticas de consultas ajudam a detetar, diagnosticar e evitar problemas de desempenho de consultas para bases de dados do Cloud SQL.
Verifique a quantidade média de tarefas
Execute esta consulta algumas vezes para verificar a quantidade média de tarefas. Se a contagem média de tarefas for consistentemente elevada, a instância pode estar a enfrentar pressão da CPU.
SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count],
GETDATE() AS [System Time]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);
Determine se é necessário adicionar mais vCPUs
Em determinadas condições, pode querer aumentar a vCPU. Use esta consulta para determinar se é necessário adicionar mais vCPUs.
-- Shows queries where max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
qs.max_worker_time/1000 max_cpu_time_ms,
(qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
qs.execution_count,
q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle)
AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
OR qs.max_worker_time > @cputime_threshold_microsec )
AND execution_count > @execution_count
ORDER BY qs.total_worker_time DESC
Verifique se existem índices em falta
Verifique se existem índices em falta através da seguinte consulta. Teste estes índices numa instância de não produção para ver como afetam o desempenho da CPU.
SELECT
CONVERT(
decimal(18, 2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01))
AS [index_advantage],
CONVERT(nvarchar(25), migs.last_user_seek, 20) AS [last_user_seek],
mid.[statement] AS [Database.Schema.Table],
COUNT(1) OVER (PARTITION BY mid.[statement]) AS [missing_indexes_for_table],
COUNT(1)
OVER (PARTITION BY mid.[statement], mid.equality_columns)
AS [similar_missing_indexes_for_table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.user_seeks,
CONVERT(decimal(18, 2), migs.avg_total_user_cost) AS [avg_total_user_cost],
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH(NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH(NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH(NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC
Verifique se existem índices com demasiada sobrecarga
A adição de um índice pode ajudar na otimização, mas também pode adicionar uma sobrecarga de desempenho que contribui para uma maior utilização da CPU e da memória. Reveja os índices devolvidos que têm um número muito elevado de escritas em comparação com um número muito baixo de leituras e considere removê-los.
SELECT
SCHEMA_NAME(o.[schema_id]) AS [Schema Name],
OBJECT_NAME(s.[object_id]) AS [TABLE Name],
i.name AS [Index Name],
i.index_id,
i.is_disabled,
i.is_hypothetical,
i.has_filter,
i.fill_factor,
s.user_updates AS [Total Writes],
s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH(NOLOCK)
INNER JOIN sys.indexes AS i WITH(NOLOCK) ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN sys.objects AS o WITH(NOLOCK) ON i.[object_id] = o.[object_id]
WHERE
OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
AND i.index_id > 1
AND i.[type_desc] = N'NONCLUSTERED'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND i.is_unique = 0
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC
Encontre as principais consultas que consomem a maior parte da CPU
Reveja as 20 principais consultas por utilização da CPU ou tempo de processamento. Estas são as consultas com base nas estatísticas de execução de consultas que usam a maior parte da CPU. Estas estatísticas são agregadas ao longo do tempo e estão associadas aos planos na cache.
SELECT
top 20
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time
Verifique se existem conversões implícitas nos planos de consulta
Esta operação é dispendiosa e, normalmente, é apresentada como um aviso no plano de execução da consulta.
Normalmente, a mensagem tem um aviso que indica que pode afetar CardinalityEstimate
na escolha do plano de consulta. Pode identificar conversões implícitas quando vê os planos de consulta no SQL Server Management Studio (SSMS).