Sql Server. Пересечение джобов (jobs)

добавлено: 01 фев 12
понравилось:0
просмотров: 2440
комментов: 0

теги:

Автор: gds

Около 2-3-х месяцев назад к мне стали обращаться с проблемой долгого выполнения одного скрипта. Я поинтересовался, что случилось, в чем проблема? Т.к. этот скрипт я за две недели до этого обращения оптимизировал и он работал отлично.

Проблема заключалась в том, что при нормальных условиях это скрипт выполнялся от 15-35 сек. в зависимотсти от кол-ва обрабатываемых данных, а при выполнении из джоба он отрабатывал около получаса (~30 мин). После анализа полученной информации я решил первым делом проверить, а не пересекается ли этот джоб с какими-нибудь другими джобами? Для поиска пересекающихся джобов я написал следующий скрипт.
use [msdb]
GO
with ste as(
select 
	jh.job_id,
	jh.step_id,
	convert(datetime,cast(jh.run_date as varchar(8)),112)+CONVERT(datetime,Tfull.T,108) as run_datetime,	
	convert(datetime,cast(jh.run_date as varchar(8)),112)+CONVERT(datetime,Tfull.T,108)+CONVERT(datetime,Tdfull.T,108) as end_datetime,
	CONVERT(datetime,Tdfull.T,108) as duration
from dbo.sysjobhistory as jh
cross apply(select T = right('00000'+cast(jh.run_time as varchar(6)),6))T
cross apply(select T = substring(T.T,1,2))Th
cross apply(select T = substring(T.T,3,2))Tm
cross apply(select T = substring(T.T,5,2))Ts
cross apply(select T = Th.T+':'+Tm.T+':'+Ts.T)Tfull
cross apply(select T = right('00000'+cast(jh.run_duration as varchar(6)),6))Td
cross apply(select T = substring(Td.T,1,2))Tdh
cross apply(select T = substring(Td.T,3,2))Tdm
cross apply(select T = substring(Td.T,5,2))Tds
cross apply(select T = Tdh.T+':'+Tdm.T+':'+Tds.T)Tdfull

where  jh.run_status = 1
and jh.step_id = 0 
)
SELECT ROW_NUMBER() over (partition by convert(varchar,a.run_datetime,112) order by a.run_datetime) as row_num,
	j.name as job_a,	
	a.step_id as job_a_step_id,
	a.run_datetime as job_a_runtime,
	a.end_datetime as job_a_endtime,
	a.duration as job_a_duration,	
	j0.name as job_b,
	a0.step_id as job_b_step_id,
	a0.run_datetime as job_b_runtime,
	a0.end_datetime as job_b_endtime,
	a0.duration as job_b_duration,
	a.job_id as job_a_id,
	a0.job_id as job_b_id
  FROM ste as a
  JOIN ste as a0    ON a.job_id <> a0.job_id
  join dbo.sysjobs as j on j.job_id = a.job_id
  join dbo.sysjobs as j0 on j0.job_id = a0.job_id
   AND a0.end_datetime >= a.run_datetime
   AND a.end_datetime >= a0.run_datetime
/* Не учитывать перечисленные джобы при сравнении.
   where a.job_id not in ('2FB67516-3465-49FC-B133-09B722459ECB')
   and a0.job_id not in ('2FB67516-3465-49FC-B133-09B722459ECB')
*/

GO

И в действительности джоб пересекался с двумя другими "тяжелыми" джобами одним из которых, была мерждевая репликация.
Теперь давайте пройдемся немного по скрипту.
1. jh.run_status = 1 - при данном условии учитываются только успешно выполненные джобы. Перечень статусов можно найти здесь.
2. jh.step_id = 0 учитывается выполнение джоба в целом. Например джоб А пересекался с джобом Б. Если использовать jh.step_id > 0, то учитывается выполнение каждого шага джоба. Например джоб А на 2-ом шаге пересекался с джобом Б на 4-ом шаге.
В данном примере интервал выполнения выводиться в datetime. Можете преобразовать в time или использовать ниже приведенную функцию для преобразования в удобочитаемый вид (dd:hh:mi:ss).
+ GetDiffTime

IF OBJECT_ID('dbo.uf_getDiffTime') IS NOT NULL
	DROP FUNCTION dbo.uf_getDiffTime
GO
create function dbo.uf_getDiffTime (
	@date_start datetime,
	@date_end datetime
)
 returns varchar(16)
as 
begin
declare @res varchar(16)

select @res =	replace(str(datediff(dd, '19000101', diff),7),' ','')	+':'+
				replace(str(datepart(hh, diff),2),' ','0') +':'+ 
				replace(str(datepart(mi, diff),2),' ','0') +':'+
				replace(str(datepart(ss, diff),2),' ','0') 	
from (select @date_end - @date_start [diff]) t
return @res
end

-- Example
declare @dateStart datetime
declare @dateEnd datetime
select @dateEnd = getdate(),
		  @dateStart = dateadd(hh,-4,getdate())
select dbo.uf_getDiffTime(@dateStart,@dateEnd)


С помощью данного скрипта я не только решил поставленную задачу, но и разнес по времени наиболее критичные к ресурсам джобы. Таким образом разпределил нагрузку более равномерно.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии