全部产品
阿里云办公
    RDS数据库SQL Server数据库CPU负载

RDS数据库SQL Server数据库CPU负载

更新时间:2017-10-17 14:53:08

提示:这篇文档是由阿里云售后支持团队针对特定或紧急问题提供的“快速发布”文档。文档的内容以原稿呈现,未进行编辑及审核。因此,阿里云对于文档内容不做任何承诺, 并且,我们有权在未经通知您的情形下对文档内容做出编辑、修改或提供补充信息。

问题症状

CPU使用率高image.png | center | 2352x864

问题原因

RDS for SQL Server CPU使用率高的因素有很多,其中最常见的是应用的负载高,查询语句的成本高,或者是实例的并行度设置不合理,使得CXPACKET等待特别多,造成CPU使用率高,还有些时候,应用语句产生严重的阻塞,导致CPU使用情况加重。

  1. 应用负载高
  2. 查询语句的读写高
  3. 阻塞加重CPU使用情况
  4. Max degree of Parallelism 设置不合理

问题排查和解决

RDS for SQL Server 实例在日常使用中,有时会出现 CPU使用达到 100% 的情况。RDS控制台提供“监控与报警”功能,以便实时监控实例CPU使用情况。

1.应用负载高

特征:实例的 QPS(Query Per Second 每秒执行的查询数)高,查询比较简单、执行效率高、优化余地小。表现:没有出现慢查询(或者慢查询不是问题主要原因),QPS 和 CPU 使用率曲线变化吻合。常见于应用优化过的在线事务交易系统(比如订单系统)、高读取率的热门Web网站应用等。

解决方法: 这种情况 SQL 查询优化的余地不大,建议考虑从应用架构、实例规格等方面来解决:1) 升级实例规格,增加 CPU 资源。2) 尽量优化查询,减少查询的执行成本(逻辑IO,执行需要访问的表数据行数),提高应用可扩展性。

**2. 查询语句的读写高

特征:**实例的 QPS(每秒执行的查询次数)不高;查询执行效率低、执行需要扫描大量表中数据、优化余地大。

表现:存在慢查询,QPS 和 CPU 使用率曲线变化不吻合,通过上述语句查询耗用CPU的语句查询看,存在I/O较大的语句。

查询执行效率低,为了获得预期的结果集需要访问大量的数据(平均逻辑IO高),在 QPS 并不高的情况下(例如网站访问量不大),也导致实例的 CPU 使用率高。

解决方法: 1) 缺失索引检查。对于大表查询,检查是否有合适的索引。检查实际执行计划,针对全表扫描操作进行优化。 此外,执行计划中也会给出缺失索引的建议。日常定期索引维护工作可以参考https://yq.aliyun.com/articles/99856?spm=5176.100239.blogrightarea69162.17.maB7Lrimage.png | left | 554x1192) 通过DMS连接实例,创建诊断报告,结合诊断报告的建议进行调优。image.png | left | 554x93

3. 阻塞加重CPU使用情况

当RDS的CPU使用率升高时,需要检查此时SQL Server中是否有阻塞发生,阻塞和CPU高这两个问题会互相影响,加重CPU使用问题。

循环监控sys.dm_tran_locks,sys.dm_os_waiting_tasks等视图,可以得到阻塞图谱。Request_session_id是被阻塞的session_id,RequestingText是被阻塞的语句,blocking_session_id是阻塞头session_id,BlockingText是阻塞头语句。

  1. while 1=1
  2. Begin
  3. SELECT
  4. db.name DBName,
  5. tl.request_session_id, --被阻塞session
  6. wt.blocking_session_id, --阻塞头session
  7. OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
  8. tl.resource_type,
  9. h1.TEXT AS RequestingText,
  10. h2.TEXT AS BlockingText,
  11. tl.request_mode
  12. FROM sys.dm_tran_locks AS tl
  13. INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
  14. INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
  15. INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
  16. INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
  17. INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
  18. CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
  19. CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
  20. waitfor delay '00:00:01' --循环间隔时间可以自定义
  21. End

解决方法:1) 关闭阻塞头连接,可以帮助快速解除阻塞。2) 查看是否有长时间未提交的事务,及时提交事务。3) 如果有S锁参与阻塞,并且应用允许脏读,可以使用with nolock这个查询hint,让select语句避免申请锁,从而避免阻塞。例: select * from table with(nolock)4) 检查应用程序逻辑,按顺序访问某个资源。

**4. Max degree of Parallelism 设置不合理

**并行的执行计划,多线程处理时,由于每个线程处理的数据量不一致,会出现CXPACKET等待情况,CXPACKET等待发生比较多的话,造成CPU使用率高。可以通过SQL Server Management Studio的活动监视器或者下面语句(多次执行取差值),监控是否存在大量CXPACKET等待。

  1. WITH [Waits] AS
  2. (SELECT
  3. [wait_type],
  4. [wait_time_ms] / 1000.0 AS [WaitS],
  5. ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
  6. [signal_wait_time_ms] / 1000.0 AS [SignalS],
  7. [waiting_tasks_count] AS [WaitCount],
  8. 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
  9. ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
  10. FROM sys.dm_os_wait_stats
  11. WHERE [wait_type] NOT IN (
  12. N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
  13. N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
  14. N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
  15. N'CHKPT', N'CLR_AUTO_EVENT',
  16. N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
  17. -- Maybe uncomment these four if you have mirroring issues
  18. N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
  19. N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
  20. N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
  21. N'EXECSYNC', N'FSAGENT',
  22. N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
  23. -- Maybe uncomment these six if you have AG issues
  24. N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
  25. N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
  26. N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
  27. N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
  28. N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
  29. N'ONDEMAND_TASK_QUEUE',
  30. N'PREEMPTIVE_XE_GETTARGETSTATE',
  31. N'PWAIT_ALL_COMPONENTS_INITIALIZED',
  32. N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
  33. N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
  34. N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
  35. N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
  36. N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
  37. N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
  38. N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
  39. N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
  40. N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
  41. N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
  42. N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
  43. N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
  44. N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
  45. N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
  46. N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
  47. N'WAIT_XTP_RECOVERY',
  48. N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
  49. N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
  50. N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
  51. AND [waiting_tasks_count] > 0
  52. )
  53. SELECT
  54. MAX ([W1].[wait_type]) AS [WaitType],
  55. CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
  56. CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
  57. CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
  58. MAX ([W1].[WaitCount]) AS [WaitCount],
  59. CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
  60. CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
  61. CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
  62. CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
  63. FROM [Waits] AS [W1]
  64. INNER JOIN [Waits] AS [W2]
  65. ON [W2].[RowNum] <= [W1].[RowNum]
  66. GROUP BY [W1].[RowNum]
  67. HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
  68. GO

如果出现CXPACKET等待较多的情况,可以通过调整Max Degree of Parallelism(MAXDOP)来解决。MAXDOP的推荐设置值参考https://support.microsoft.com/zh-cn/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-configuration-option-in-sql-server

解决方法:1) 从语句级别进行设置

  • 通过查询语句寻找耗CPU的语句。
  1. SELECT TOP 50
  2. [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
  3. [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
  4. [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
  5. [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
  6. qs.execution_count,
  7. [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
  8. [Total I/O] = total_logical_reads + total_logical_writes,
  9. Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
  10. (
  11. (
  12. CASE qs.statement_end_offset
  13. WHEN -1 THEN DATALENGTH(qt.[text])
  14. ELSE qs.statement_end_offset
  15. END - qs.statement_start_offset
  16. ) / 2
  17. ) + 1
  18. ),
  19. Batch = qt.[text],
  20. [DB] = DB_NAME(qt.[dbid]),
  21. qs.last_execution_time,
  22. qp.query_plan
  23. FROM sys.dm_exec_query_stats AS qs
  24. CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
  25. CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  26. where qs.execution_count > 5 --more than 5 occurences
  27. ORDER BY [Total MultiCore/CPU time(sec)] DESC
  • RDS for SQL Server 2008 R2,提供“慢日志统计”功能,也可以从“慢日志统计”中来查找这些语句。

01.png | center | 1781x514找到语句之后,查看其执行计划,对于并行度较高的语句,例如下面语句的并行度为4。02.png | center | 923x333可以在语句级别添加查询hint,限制语句并行度。

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

2)从实例级别进行设置首先,可以通过下面语句查看当前实例的MAXDOP值,SQL Server系统默认值为0。

  1. select * from sys.configurations where name like '%max%'

03.png | center | 1132x192在实例级别设置该参数,对所有查询均生效。

对于RDS for SQL Server 2008 R2,可以在RDS管理控制台的“参数设置”中进行手动设置,需提交参数生效。04.png | center | 1711x426对于RDS for SQL Server 2012,已经设置默认的MAXDOP为2,不可自行修改该值。

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

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

  1. 应用设计和开发过程中,要考虑查询的优化,遵守SQL优化的一般优化原则,降低查询的逻辑 IO,提高应用可扩展性。
  2. 新功能、新模块上线前,要使用生产环境数据进行压力测试。
  3. 建议经常关注和使用 DMS 中的诊断报告。关于如何访问 DMS 中的诊断报告,请参考: RDS 如何访问诊断报告: https://help.aliyun.com/knowledge_detail/41814.html

相关文档

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