このドキュメントでは、Cloud SQL for SQL Server インスタンスが、アンダープロビジョニングされたインスタンスの Recommender によって 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)でクエリプランを表示すると、暗黙的なコンバージョンを簡単に識別できます。