このトピックでは、ApsaraDB RDS for SQL ServerインスタンスのCPU使用率が高い、または100% に達するという問題の原因と解決策について説明します。
以下の点にご注意ください。
Alibaba Cloudインスタンスの設定やデータの変更など、リスクの高い操作を実行する前に、インスタンスのディザスタリカバリ機能とフォールトトレランス機能を確認して、データのセキュリティを確保することを推奨します。
ECS (Elastic Compute Service) インスタンスやRDSインスタンスなどのインスタンスの設定またはデータを変更する前に、インスタンスのスナップショットを作成するか、バックアップを有効にすることを推奨します。 たとえば、RDSインスタンスのログバックアップ機能を有効にできます。
Alibaba Cloud管理コンソールで機密情報に対する権限を付与した場合、または機密情報を送信した場合は、できるだけ早い機会に機密情報を変更することを推奨します。 機密情報には、ユーザー名とパスワードが含まれます。
RDSインスタンスの不適切なDOP
トラブルシューティング
複数のスレッドを使用してタスクを並列に処理する場合、各スレッドによって処理されるデータの量は異なり、CXPACKET待機が発生する可能性があります。 CXPACKET待ちの数が多いと、CPU使用率が高くなります。 SQL Server Management Studio (SSMS) でActivity Monitorを使用するか、次のSQL文を複数回実行して、大量のCXPACKET待機が存在するかどうかを確認できます。
SQL Serverでは、CXPACKET待機は並列処理シナリオで発生します。 CXPACKET待ちは、特定のスレッドが処理を完了し、他のスレッドが処理を完了して処理結果を統合するのを待つことを示す。 累積CXPACKET待機時間が長すぎると、RDSインスタンスのCPUパフォーマンスが影響を受けます。
WITH [Waits] AS (
SELECT [wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(
ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER',
N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER',
N'CHECKPOINT_QUEUE',
N'CHKPT',
N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT',
N'CLR_SEMAPHORE', -- Maybe uncomment these four if you have mirroring issues N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', -- Maybe uncomment these six if you have AG issues N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE', N'PREEMPTIVE_XE_GETTARGETSTATE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_RECOVERY', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 )
SELECT MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16, 2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16, 2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16, 2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5, 2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16, 4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16, 4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX([W1].[Percentage]) < 95;ソリューション
SQL文固有のDOP
次のSQL文を実行して、RDSインスタンスのCPUリソースを消費するSQL文を特定します。
SELECT TOP 50 [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count, [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000, [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count, [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000, qs.execution_count, [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count, [Total I/O] = total_logical_reads + total_logical_writes, Query = 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), Batch = qt.[text], [DB] = DB_NAME(qt.[dbid]), qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp where qs.execution_count > 5 --more than 5 occurences ORDER BY [Total MultiCore/CPU time(sec)] DESC;説明RDSインスタンスがSQL Server 2008 R2を実行している場合、ApsaraDB RDSコンソールのRDSインスタンスの低速クエリログにCPUリソースを消費するSQL文を表示できます。
スロークエリログを表示するには、次の手順を実行します。インスタンスの詳細ページに移動します。 左側のナビゲーションウィンドウで、[ログ] をクリックします。 [スローログの概要] タブで、ログを表示します。
高度な並列処理 (DOP) を持つSQL文を特定した後、SQL文の実行計画を表示し、SQL文の最後に
OPTION (MAXDOP 1)などのヒントを追加して、DOPを制限し、リソース管理を改善できます。 例：SELECT column1,column2 FROM table1 o INNER JOIN table2 d ON (o.d_id = d.d_id) OPTION (maxdop 1);
RDSインスタンス固有のDOP
次のSQL文を実行して、RDSインスタンスの最大並列度パラメーターの値を表示します。
SELECT * FROM SYS.CONFIGURATIONS WHERE NAME LIKE '%MAX%';
次のSQL文を実行して、パラメーターを設定します。 この設定は、RDSインスタンスで実行されたすべてのクエリに対して有効になります。
sp_rds_configure 'max degree of parallelism',1;説明RDSインスタンスがSQL Server 2008 R2を実行している場合、ApsaraDB RDSコンソールでパラメーターを変更できます。 詳細については、「ApsaraDB RDSコンソールを使用したApsaraDB RDS For SQL Serverインスタンスのパラメーターの再設定」をご参照ください。
重いアプリケーション負荷
症状
RDSインスタンスの負荷は重いです。 ただし、低速クエリが発生しないか、低速クエリが重い負荷の根本原因ではありません。 1秒あたりのクエリのカーブ (QPS) とCPU使用率が一致しています。 この問題は、注文システムなどの最適化されたオンライン取引システム、および高い読み取りレートを有する一般的なwebアプリケーションで一般的です。
特性
QPSは高い。 クエリ操作は簡単です。 クエリ効率は高いです。 クエリを最適化することは困難です。
ソリューション
アプリケーションアーキテクチャを最適化するか、インスタンスタイプをアップグレードすることを推奨します。
RDSインスタンスのインスタンスタイプをアップグレードして、CPUリソースを増やします。 詳細については、「ApsaraDB RDS For SQL Serverインスタンスの仕様の変更」をご参照ください。
クエリを最適化して実行コストを削減し、アプリケーションのスケーラビリティを向上させます。 ロジックI/O操作を最適化し、クエリがテーブルで読み取る必要のある行数を最小限に抑えることができます。
多数の読み取りおよび書き込み操作
症状
遅いSQLクエリが発生します。 QPSとCPU使用率のカーブが一致しません。 複数のCPUリソースを消費するSQL文の中には、多数のI/O操作を伴うものがある。
特性
QPSは低い。 クエリの効率は低いです。 クエリ中に大量のデータをスキャンする必要があります。 クエリを最適化できます。
ソリューション
クエリに含まれる大きなテーブルに対してインデックスが作成されているかどうかを確認します。 クエリの実行計画を確認し、テーブル全体のスキャンを最適化します。 実行計画で不足しているインデックスの候補を表示できます。
CloudDBAを使用してパフォーマンスの問題を確認します。 詳細については、「ApsaraDB RDS For SQL Serverインスタンスのストレージ情報の表示」をご参照ください。
その他の提案
CPU使用率が100% に達しないようにするには、次の操作を実行します。
CPU使用率の監視とワークロードの計画に役立つように、CPU使用率のアラートルールを構成します。
アプリケーションの設計および開発中に、一般的なルールに基づいてSQL文を最適化し、クエリの論理I/Oを減らし、アプリケーションのスケーラビリティを向上させます。
新しい機能またはモジュールをリリースする前に、本番環境でストレステストを実行するために本番データを使用します。
CloudDBAを使用してRDSインスタンスのパフォーマンスを確認し、できるだけ早い機会に問題を特定します。