RDS SQL Server CPU使用率高问题排查

CPU使用率高的因素有很多，其中最常见的因素说明及解决方法如下：

实例的并行度设置不合理 排查步骤 线程并行处理任务时，由于每个线程处理的数据量不一致，会出现CXPACKET等待情况，CXPACKET等待发生比较多的话，就会造成CPU使用率高。可以通过SQL Server Management Studio的活动监视器或者如下所示语句（多次执行取差值），监控是否存在大量CXPACKET等待。

说明：CXPACKET指线程正在等待彼此完成并行处理。当SQL Server发现一条指令复杂时，会决定用多个线程并行来执行，由于某些并行线程已完成工作，在等待其它并行线程来同步，这种等待就叫CXPACKET。

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实例，可以在控制台查看慢日志统计，查找消耗CPU的语句。 控制台慢日志统计 找到语句之后，查看其执行计划，对于并行度较高的语句，可以在语句级别使用hint查询，限制语句并行度，示例如下。 SELECT column1,column2 FROM table1 o INNER JOIN table2 d ON (o.d_id = d.d_id) OPTION (maxdop 1); 从实例级别进行设置。 执行以下SQL语句，查看当前实例的MAXDOP值。 select * from sys.configurations where name like '%max%'; 查询maxdop 执行以下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检查性能问题。 更多信息 避免出现CPU使用100%的一般原则如下：

设置CPU使用率告警，实例CPU使用率保证一定的冗余度。 应用设计和开发过程中，要考虑查询的优化，遵守SQL优化的一般优化原则，降低查询的逻辑 I/O，提高应用可扩展性。 新功能、新模块上线前，要使用生产环境数据进行压力测试。 经常使用CloudDBA查看实例各项性能，及时发现问题。 适用于 云数据库RDS SQL Server版