由定期作业引起的CPU峰值
来源：互联网 发布：淘宝芝麻信用分怎么看 编辑：程序博客网 时间：2024/05/16 01:01
这是一篇转文, 原文参考http://www.sqlservercentral.com/articles/Jobs+configuration/149036/
如下图,可以看到一个周期性每几分钟出现的峰值.
经过筛查,我发现这些峰值可能与几个固定的作业有关.下面是查证作业周期时间的Query.
SELECT [JobName] = [jobs].[name] , [Category] = [categories].[name] , [Owner] = SUSER_SNAME([jobs].[owner_sid]) , [Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END , [Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END , [Description] = [jobs].[description] , [Occurs] = CASE [schedule].[freq_type] WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQL Server Agent starts' WHEN 128 THEN 'Start whenever the CPU(s) become idle' ELSE '' END , [Occurs_detail] = CASE [schedule].[freq_type] WHEN 1 THEN 'O' WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)' WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + LEFT(CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END, LEN(CASE WHEN [schedule].[freq_interval] & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, ' ELSE '' END) - 1) WHEN 16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' WHEN 32 THEN 'The ' + CASE [schedule].[freq_relative_interval] WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' END + CASE [schedule].[freq_interval] WHEN 1 THEN ' Sunday' WHEN 2 THEN ' Monday' WHEN 3 THEN ' Tuesday' WHEN 4 THEN ' Wednesday' WHEN 5 THEN ' Thursday' WHEN 6 THEN ' Friday' WHEN 7 THEN ' Saturday' WHEN 8 THEN ' Day' WHEN 9 THEN ' Weekday' WHEN 10 THEN ' Weekend Day' END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' ELSE '' END , [Frequency] = CASE [schedule].[freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') WHEN 2 THEN 'Occurs every ' + CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':') WHEN 4 THEN 'Occurs every ' + CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':') WHEN 8 THEN 'Occurs every ' + CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':') ELSE '' END , [AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration]) , [Next_Run_Date] = CASE [jobschedule].[next_run_date] WHEN 0 THEN CONVERT(DATETIME, '1900/1/1') ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':')) END FROM [msdb].[dbo].[sysjobs] AS [jobs] WITH ( NOLOCK ) LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITH ( NOLOCK ) ON [jobs].[job_id] = [jobschedule].[job_id] LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITH ( NOLOCK ) ON [jobschedule].[schedule_id] = [schedule].[schedule_id] INNER JOIN [msdb].[dbo].[syscategories] [categories] WITH ( NOLOCK ) ON [jobs].[category_id] = [categories].[category_id] LEFT OUTER JOIN ( SELECT [job_id] , [AvgDuration] = ( SUM(( ( [run_duration] / 10000 * 3600 ) + ( ( [run_duration] % 10000 ) / 100 * 60 ) + ( [run_duration] % 10000 ) % 100 )) * 1.0 ) / COUNT([job_id]) FROM [msdb].[dbo].[sysjobhistory] WITH ( NOLOCK ) WHERE [step_id] = 0 GROUP BY [job_id] ) AS [jobhistory] ON [jobhistory].[job_id] = [jobs].[job_id];GO
使用上面的脚本将会列出作业的时间表。我发现有一组定期作业每分钟安排一次（图2），在每分钟00秒。还有另一组工作以五分钟的间隔运行。
这些作业正在数据库中进行定期更新。有些更新会影响许多行，致使峰值会更高和更宽。另外在每分钟的00秒有每隔5分钟运行一次的作业.这些作业都在同一时间开始.也会带来峰值的产生.
为消除这样的问题, 我对时间进行了调整.
可以看到在已经将作业时间进行了重新分配.
几分钟之后, CPU使用状况就得到了如下的情况:
继续修改其他相关作业的时间分配. CPU情况如下:
总结:
对于CPU使用情况,作业的调度会是一个重要的因素. 有相当高的峰值是由于错误的时间调度产生的.
希望大家在调度作业时, 考虑此情况.
0 0
- 由定期作业引起的CPU峰值
- dedecms导致CPU峰值现象的解决办法
- 由printf引起的
- 电脑CPU的温度急剧上升，是由什么原因引起的？
- 由文件下载引起的
- 由踢球引起的连锁反应
- 由个性签名引起的。。。
- 由 TypeInitializationException 引起的问题
- 由云端引起的蓝屏
- 由北爱引起的胡思乱想
- 由DevOps引起的思考
- 由Ftp引起的问题
- 由发票引起的争吵
- mysqld服务器CPU/IOWAIT瞬间出现峰值的问题
- 由程序语言引起的我的思考
- 由“Indexing Service”服务引起的问题
- 由杀毒引起的……
- 由显示器问题引起的死机
- MySQL存储引擎总结
- xml学习
- Rancher 1.5全面发布!
- 1101: [POI2007]Zap(莫比乌斯反演)
- 安卓Ble低功耗蓝牙快速开发框架H-ble
- 由定期作业引起的CPU峰值
- 本站VIP福利介绍
- C# 传统遍历与迭代器
- jumpserver
- MyEclipse+Tomcat+MAVEN+SVN项目完整环境搭建
- shell脚本unexpected operator
- C++ 引用计数类模板
- 菱形继承 虚继承
- 新浪博客，记事本搬家了。
