Questo documento spiega come esaminare e ottimizzare un'istanza Cloud SQL per SQL Server se viene identificata dal motore per suggerimenti di istanze sottodimensionate come con un utilizzo elevato della CPU.
Se le dimensioni della vCPU di un'istanza non sono adeguate, possono diventare una fonte di contesa. Per verificare se la CPU è un bottleneck o se è sottodimensionata, utilizza le query in questo documento.
Controllare il conteggio medio delle attività
Esegui questa query un paio di volte per controllare il numero medio di attività. Se il numero medio di attività è costantemente elevato, l'istanza potrebbe essere sottoposta a un carico della 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);
Determina se è necessario aggiungere altre vCPU
In determinate condizioni, ti consigliamo di aumentare le vCPU. Utilizza questa query per determinare se è necessario aggiungere altre vCPU.
-- 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
Controllare la presenza di indici mancanti
Verifica la presenza di indici mancanti utilizzando la seguente query. Testa questi indici su un'istanza non di produzione per vedere in che modo influiscono sul rendimento della 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
Controlla se ci sono indici con un overhead eccessivo
L'aggiunta di un indice può contribuire all'ottimizzazione, ma può anche comportare un sovraccarico delle prestazioni che contribuisce a un maggiore utilizzo della CPU e della memoria. Esamina gli indici restituiti con un numero molto elevato di scritture rispetto a un numero molto basso di letture e valuta la possibilità di rimuoverli.
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
Trovare le query principali che consumano la maggior parte della CPU
Esamina le 20 query principali in base all'utilizzo della CPU o al tempo del worker. Si tratta delle query basate sulle statistiche di esecuzione delle query che utilizzano la maggior parte della CPU. Queste statistiche vengono aggregate nel tempo e sono collegate ai piani nella 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
Controllare la presenza di conversioni implicite nei piani di query
Questa operazione è costosa e di solito viene visualizzata come avviso nel piano di esecuzione della query. Di solito il messaggio contiene un avviso che indica che potrebbe influire su CardinalityEstimate nella scelta del piano di query. Puoi identificare facilmente le conversioni implicite quando visualizzi i piani di query in SQL Server Management Studio (SSMS).