LK optimization by job execution duration

New to a company, need to toss some recognition out. And then began to work on the optimization of the bitter force. Temporarily do not spit slot permissions problem!!!
Optimization effect
Job history prior to optimization (Top 30)
P Figure
CPU usage prior to optimization

There are a few jobs average time 2.5~3.5 hours, there are many hours in more than half an hour of work, basic to 11-12 points to complete the job statistics. The CPU 7:00-11:00 is maintained at a higher value every day.
Post-optimization, job history (Top 30)
P Figure
CPU conditions for the last three days

CPU conditions for the most recent day

After optimization, the job execution time is significantly reduced, from the last 3 days of implementation, the statistical work can be completed before 9:00. The server CPU peak time is reduced from the previous 7:00-11:00 to the present 7:00-8:30.
Optimization steps
Because the problem is clear, reduce the job execution duration! 3 hours, 4 hours too long, lower! How low is the low down! So just go straight to the topic and optimize for jobs, jobs and statements/stored procedures! So the first step here is not to fetch worker_time or logical_reads high-consumption statements/stored procedures.
1, extract the job history, according to the average execution time in reverse order, priority to deal with the longest execution time of the job
The first job often can only view missing indexes from the execution plan, as well as high-consumption operations, combined with SET statistics IO on to get the reads of each operation, can roughly locate the high-consumption statement. Most of these are missing indexes and scanning the table, in which case an appropriate index can be created. In another case, you need to modify the stored procedure, add/delete index hints after the table, append a recompile option after the statement, specify the join method for the associated query, and save the data for multiple uses with the temporary table to improve the execution plan selection.
Often after optimizing a job, you will find that some other jobs have also been promoted. First, they use the same table, and the query conditions are similar, and can benefit from the previous index. This is the case, and the subsequent optimizations need to be adjusted to create a relatively generic index, taking into account the relevant operations. Second, the previous job optimization, consumption decreased. The system has more resources to handle the subsequent jobs, and the performance will be improved appropriately.

--Job Execution History update 2016/04/29Select Top  +A.name,IsNull(B.jobruntimes,0) Jobruntimes,Convert(varchar,IsNull(B.firstrundatetime,0), -) Firstrundatetime,Convert(varchar,IsNull(B.lastrundatetime,a.lastrundatetime), -) Lastrundatetime,IsNull( Right('xx'+Convert(varchar, b.avgrunduration/3600),2)+':'+right('xx'+Convert(varchar, b.avgrunduration%3600/ -),2)+':'+right('xx'+Convert(varchar, b.avgrunduration% -),2), a.lastrunduration) avgrunduration,a.lastrunduration,IsNull(B.jobruntimes,0)*(A.stepcount+1) Historyrecords from (SelectSj.name,Count(sjs.step_id) stepcount,msdb.dbo.agent_datetime (sjv.last_run_date,sjv.last_run_time) LastRunDateTime,Convert(varchar, Msdb.dbo.agent_datetime ('20151201', sjv.last_run_duration),108) Lastrunduration fromMsdb.dbo.sysjobs SJInner Joinmsdb.dbo.sysjobsteps SjS onsj.job_id=sjs.job_idInner Joinmsdb.dbo.sysjobservers sjv onsj.job_id=sjv.job_idwhereSj.enabled=1Group  bysj.name,sjv.last_run_date,sjv.last_run_time,sjv.last_run_duration) A Left Join (SELECTSj.name,Count(Sj.name) jobruntimes,min(Msdb.dbo.agent_datetime (Run_date,run_time)) Firstrundatetime--version 05 and above can be called directly msdb.dbo.agent_datetime,Max(Msdb.dbo.agent_datetime (run_date,run_time)) Lastrundatetime,avg( Left( Right('000000'+Convert(varchar, sjh.run_duration),6),2)*3600+substring( Right('000000'+Convert(varchar, sjh.run_duration),6),3,2)* -+right( Right('000000'+Convert(varchar, sjh.run_duration),6),2)) Avgrunduration fromMsdb.dbo.sysjobhistory SJH with(NOLOCK)INNER JOINMsdb.dbo.sysjobs SJ onsjh.job_id=sj.job_idWHEREsjh.step_id=0  andSj.enabled=1 and Convert(Date,msdb.dbo.agent_datetime (Run_date,run_time))>CONVERT(Date,GETDATE()-7)--limit viewing records for the last 7 daysGROUP  bysj.name,sj.job_id) b onA.name=B.nameOrder  byA.lastrundurationdesc, A.name

View Code

2, combined with CPU usage, focus on checking the CPU suddenly rising time of the job
After the long execution time (for example, more than 30 minutes) of the job optimization, check the CPU suddenly rising time of the job. The execution time of these jobs may not be long, but the CPU as they begin to rise, the completion will be reduced, at this time, it is necessary to check the job corresponding to the stored procedure reads is relatively high, if relatively high, they should be optimized. There is also an easy to ignore, such as in the statistical library found daily 8:10-8:30 between the CPU continues to reach 70-80%, through the job Activity monitor, according to the last run time sorting, find out that period of time to perform the job, one by one to analyze. However, this makes it easy to ignore high-frequency jobs (such as every 10 minutes). These jobs were executed several times during that time, but the last execution time was not in that interval. So don't forget to check the consumption of this type of job.
3. Operation Disabled, adjustment plan
Disable jobs that are no longer in use according to business needs, and stagger job execution time appropriately for jobs that are too focused on execution time. As the previous two steps have been optimized for high-consumption jobs, the job execution time can be adjusted to a larger size. Sometimes in order to determine the impact of a job, it may be possible to move the job itself or the interval of its execution.
This can be combined to obtain the operation of the specified interval job and the above job execution history, the first execution time within the job interval, the last execution time within the interval, the possible run time, the number of executions in the interval, the cycle interval, the frequency of daily execution, and other information

 UsedbnameGo--Note Adjust the stored procedure return column, and order by fieldCreate Table#JobSchedules (namevarchar( -), Firstruntimeinrangedatetime, Lastruntimeinrangedatetime, Runtimesinrangeint, intercyclevarchar( -), Execution_intervalvarchar( +), Active_start_datevarchar(Ten), active_end_datevarchar(Ten), date_createddatetime, date_modifieddatetime)Declare @sql varchar( $)Set @sql='Dba_pro_getjobschedules"'20160423 00:00:00.000"',"'20160423 23:59:59.997" "Insert#JobSchedulesexec(@sql)SelectA.*, B.lastrunduration possiblerunduration from#JobSchedules a Left Join (--job execution history. SQL) b onA.name=B.name

View Code

4. Other influences
After the above operation is adjusted, found that the CPU of the statistical library is still too high between the 8:10-8:30, suspect the statistical library in addition to its own job execution, there should be other high-consumption statements in the execution. Then turn on the trace filter for larger CPU statements. Discover the main library during that time to get a large amount of data from the statistical library through a linked server. The actual operation can be avoided.

Reserved
Reserved

LK optimization by job execution duration