RDS SQL Server CPU使用率高問題排查 - ApsaraDB RDS

本文介紹RDS SQL Server使用過程中遇到的CPU使用率過高甚至達到100%時，一些常見原因及解決方案。

說明

阿里雲提醒您：

如果您對執行個體或資料有修改、變更等風險操作，務必注意執行個體的容災、容錯能力，確保資料安全。
如果您對執行個體（包括但不限於ECS、RDS）等進行配置與資料修改，建議提前建立快照或開啟RDS記錄備份等功能。
如果您在阿里雲平台授權或者提交過登入帳號、密碼等安全資訊，建議您及時修改。

執行個體的並行度設定不合理

排查步驟

同步多執行緒任務時，由於每個線程處理的資料量不一致，可能會出現CXPACKET等待情況。CXPACKET等待發生較多時，會造成CPU使用率高。您可以通過SSMS的活動監視器、或如下SQL代碼（多次執行取差值），來監控是否存在大量CXPACKET等待。

說明

CXPACKET等待狀態出現在SQL Server的平行處理情境中，指某些線程已完成其分擔負載，而在等待其他並行線程完成，以便所有線程的結果匯合。CXPACKET等待時間如果積累過多，可能影響系統的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語句，通過查詢語句尋找消耗CPU的語句。

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執行個體，可以在RDS控制台查看慢日誌統計，尋找消耗CPU的語句。

查看方法：進入RDS執行個體詳情頁面，在左側導覽列單擊日誌管理，在慢日誌統計頁簽下查看。

識別高並行度SQL語句後，查看其執行計畫，對這類查詢，可在語句末添加並行度控制提示，如OPTION (MAXDOP 1), 來約束並存執行，最佳化資源管理。樣本如下：
```
SELECT column1,column2 
FROM table1 o INNER JOIN table2 d ON (o.d_id = d.d_id) 
OPTION (maxdop 1);
```

執行個體層級進行設定

執行以下SQL語句，查看當前執行個體的MAXDOP（Maximum Degree of Parallelism）值。
```
SELECT * FROM SYS.CONFIGURATIONS
WHERE NAME LIKE '%MAX%';
```
執行以下SQL語句，在執行個體層級設定該參數，對所有查詢均生效。
```
sp_rds_configure 'max degree of parallelism',1;
```
說明
對於RDS SQL Server 2008 R2執行個體，可以在RDS管理主控台對參數進行手動設定。具體操作，請參見使用控制台設定參數。

應用負載高

現象描述

當應用負載較高時，並未觀察到慢查詢問題（或者慢查詢並非導致系統效能的主要原因），且QPS和CPU使用率曲線變化吻合。常見於應用最佳化過的線上事務交易系統（比如訂單系統）、高讀取率的熱門Web網站應用程式等。

特徵概括

執行個體的QPS高，查詢比較簡單、執行效率高、最佳化空間小。

處理方法

建議從應用架構、執行個體規格等方面來解決：

升級執行個體規格，增加CPU資源。更多詳情，請參見變更配置。
盡量最佳化查詢，減少查詢的執行成本（邏輯IO，執行需要訪問的表資料行數），提高應用可擴充性。

查詢語句的讀寫過高

現象描述

存在慢查詢，QPS和CPU使用率曲線變化不吻合，檢查消耗CPU的語句，存在I/O較大的語句。

特徵概括

執行個體的QPS不高，查詢執行效率低、執行需要掃描大量表中資料、最佳化空間大。

處理方法

對於大表查詢，檢查是否有合適的索引。檢查實際執行計畫，針對全表掃描操作進行最佳化，執行計畫中也會給出缺失索引的建議。
通過CloudDBA檢查效能問題。更多詳情，請參見空間管理。

ApsaraDB RDS：RDS SQL Server CPU使用率高問題排查

執行個體的並行度設定不合理

排查步驟

處理方法

語句層級進行設定

執行個體層級進行設定

應用負載高

現象描述

特徵概括

處理方法

查詢語句的讀寫過高

現象描述

特徵概括

處理方法

更多建議

相關文檔