이 문서에서는 SQL Server용 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
쿼리 계획에서 암시적 변환 확인
이 작업은 비용이 많이 들고 일반적으로 쿼리 실행 계획에서 경고로 표시됩니다. 일반적으로 메시지에는 쿼리 계획 선택 시 CardinalityEst에 영향을 줄 수 있다는 경고가 표시됩니다. SQL Server Management Studio (SSMS)에서 쿼리 계획을 보면 암시적 전환을 쉽게 식별할 수 있습니다.