由定期作业引起的CPU峰值

这是一篇转文, 原文参考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使用情况,作业的调度会是一个重要的因素. 有相当高的峰值是由于错误的时间调度产生的.

希望大家在调度作业时, 考虑此情况.