インスタンスでの高い CPU 使用率を最適化する

プレビュー

この機能には、サービス固有の利用規約の「一般的なサービス規約」の「pre-GA サービス規約」が適用されます。 pre-GA の機能は「現状有姿」で利用できますが、サポートが制限される場合があります。 詳しくは、リリース ステージの説明をご覧ください。

このドキュメントでは、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）でクエリプランを表示すると、暗黙的なコンバージョンを簡単に識別できます。