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

本文介绍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;

处理方法

语句级别进行设置

  1. 执行以下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实例详情页面,在左侧导航栏单击日志管理,在慢日志统计页签下查看。

  2. 识别高并行度SQL语句后,查看其执行计划,对这类查询,可在语句末添加并行度控制提示,如OPTION (MAXDOP 1), 来约束并行执行,优化资源管理。示例如下:

    SELECT column1,column2 
    FROM table1 o INNER JOIN table2 d ON (o.d_id = d.d_id) 
    OPTION (maxdop 1);

实例级别进行设置

  1. 执行以下SQL语句,查看当前实例的MAXDOP(Maximum Degree of Parallelism)值。

    SELECT * FROM SYS.CONFIGURATIONS
    WHERE NAME LIKE '%MAX%';

    image

  2. 执行以下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不高,查询执行效率低、执行需要扫描大量表中数据、优化空间大。

处理方法

  • 对于大表查询,检查是否有合适的索引。检查实际执行计划,针对全表扫描操作进行优化,执行计划中也会给出缺失索引的建议。

    image

  • 通过CloudDBA检查性能问题。更多详情,请参见空间管理

更多建议

避免出现CPU使用100%的一般原则如下:

  • 设置CPU使用率告警,实例CPU使用率保证一定的冗余度。

  • 应用设计和开发过程中,要考虑查询的优化,遵守SQL优化的一般优化原则,降低查询的逻辑I/O,提高应用可扩展性。

  • 新功能、新模块上线前,要使用生产环境数据进行压力测试。

  • 经常使用CloudDBA查看实例各项性能,及时发现问题。

相关文档

RDS SQL Server性能优化与诊断