인스턴스의 높은 CPU 사용률 최적화

이 문서에서는 SQL 서버용 Cloud SQL 인스턴스가 과소 프로비저닝된 인스턴스 추천자에 의해 CPU 사용량이 높은 것으로 식별된 경우, 해당 인스턴스를 검토하여 최적화하는 방법에 대해 설명합니다.

인스턴스 vCPU의 크기가 적절하지 않으면 경합의 원인이 될 수 있습니다. CPU가 프로비저닝되지 않았거나 병목 현상이 있는지 확인하려면 이 문서의 쿼리를 사용하세요.

평균 작업 수를 확인합니다.

이 쿼리를 몇 번 실행하여 평균 작업 수를 확인합니다. 평균 태스크 수가 일관되게 높으면 인스턴스에 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);

vCPU를 더 추가해야 하는지 확인

특정 조건에서는 vCPU를 늘리는 것이 좋습니다. 이 쿼리를 사용하여 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

누락된 색인 확인

다음 쿼리를 사용하여 누락된 색인이 있는지 확인합니다. 비프로덕션 인스턴스에서 이러한 색인이 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

오버헤드가 너무 많은 색인 확인

색인을 추가하면 최적화에 도움이 될 수 있지만 성능 오버헤드가 추가되어 CPU 및 메모리 사용량이 늘어날 수 있습니다. 읽기 수가 매우 적은 반면 쓰기 수가 매우 많은 반환된 색인을 검토하고 삭제하는 것이 좋습니다.


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

CPU를 가장 많이 사용하는 상위 쿼리 찾기

CPU 사용량 또는 작업자 시간별 상위 20개 쿼리를 검토합니다. CPU를 가장 많이 사용하는 쿼리 실행 통계를 기반으로 하는 쿼리입니다. 이러한 통계는 시간이 지남에 따라 집계되며 캐시의 계획에 연결됩니다.


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

쿼리 계획에서 암시적 변환 확인

이 작업은 비용이 많이 들며 일반적으로 쿼리 실행 계획에 경고로 표시됩니다. 메시지에는 일반적으로 쿼리 계획 선택의 CardinalityEstimate에 영향을 줄 수 있다는 경고가 포함됩니다. SQL Server Management Studio(SSMS)에서 쿼리 계획을 볼 때 암시적 전환을 쉽게 식별할 수 있습니다.