DTS completion time jump

Hi,
I have a DTS package that runs 4 other packages on a nightly basis. This
package usually takes about 2 hours to run, but ocasionally it jumps to
around 5 hours to complete. There have not been any changes to the
parameters or coding. It is the same process that causes this each time
which is a cursor run strored procedure. There have not been any huge jumps
in the number of records in the table or changes to the source data that
would cause the cursor to take longer to run through. This seems to be
intermittent, I can not easily check to see if something else in the network
on that server is happening since it is a customer site. Would you think
that something outside of the package is affecting this since it only happen
s
once in a while (about once a w or so), like I mentioned - backups, serve
r
processes, other jobs etc.? I just don't know where to look if I have not
changed anything within my package.
Thanks,
PatriceThe are many reasons why performance can change over time. One is job
contention, where multiple jobs are running at the same time. The following
will dump a listing of jobs, when they started and ended out of the
msdb.dbo.sysjobhistory system table, and store it in a temporary table:
select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
'1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
INTO #jobsched
FROM (
select t1.[name],
CAST(
start_date + ' ' + LEFT(t1.start_time,2) + ':' +
SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
) AS "job_start",
LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
RIGHT(t1.job_duration,2) AS job_duration
FROM
(
select
j.[name],
LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date",
REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
"start_time",
REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6))
"job_duration"
from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
ON h.job_id = j.job_id
WHERE h.step_id = 0
) t1
) t2
SELECT * FROM #jobsched
You could take a look at the output of the above and "eyeball" it for job
conflicts, or you could try (*untested*)
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
RIGHT JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
to get a listing of jobs that are conflicting with the job named 'Job to
analyze for conflicts' .
It's also possible that the additional time for the DTS task to process is
due to locking. You could try adding another stop to the DTS job that dumbs
the output of sp_who2 into a table for further analyses.
Hope that the above helps.
--
"Patrice" wrote:

> Hi,
> I have a DTS package that runs 4 other packages on a nightly basis. This
> package usually takes about 2 hours to run, but ocasionally it jumps to
> around 5 hours to complete. There have not been any changes to the
> parameters or coding. It is the same process that causes this each time
> which is a cursor run strored procedure. There have not been any huge jum
ps
> in the number of records in the table or changes to the source data that
> would cause the cursor to take longer to run through. This seems to be
> intermittent, I can not easily check to see if something else in the netwo
rk
> on that server is happening since it is a customer site. Would you think
> that something outside of the package is affecting this since it only happ
ens
> once in a while (about once a w or so), like I mentioned - backups, ser
ver
> processes, other jobs etc.? I just don't know where to look if I have not
> changed anything within my package.
> Thanks,
> Patrice|||Sorry, that last query should have been
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
INNER JOIN --INNER replaced RIGHT for the JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
"Mark Williams" wrote:
> The are many reasons why performance can change over time. One is job
> contention, where multiple jobs are running at the same time. The followin
g
> will dump a listing of jobs, when they started and ended out of the
> msdb.dbo.sysjobhistory system table, and store it in a temporary table:
>
> select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
> '1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
> INTO #jobsched
> FROM (
> select t1.[name],
> CAST(
> start_date + ' ' + LEFT(t1.start_time,2) + ':' +
> SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
> ) AS "job_start",
> LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
> RIGHT(t1.job_duration,2) AS job_duration
> FROM
> (
> select
> j.[name],
> LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
> char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date
",
> REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
> "start_time",
> REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6
))
> "job_duration"
> from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
> ON h.job_id = j.job_id
> WHERE h.step_id = 0
> ) t1
> ) t2
> SELECT * FROM #jobsched
> You could take a look at the output of the above and "eyeball" it for job
> conflicts, or you could try (*untested*)
> SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
> t2.job_start, t2.job_end
> FROM
> ( SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] = 'Job to analyze for conflicts'
> ) t1
> RIGHT JOIN
> (
> SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] <> 'Job to analyze for conflicts'
> ) t2
> ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
> OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
> to get a listing of jobs that are conflicting with the job named 'Job to
> analyze for conflicts' .
> It's also possible that the additional time for the DTS task to process is
> due to locking. You could try adding another stop to the DTS job that dumb
s
> the output of sp_who2 into a table for further analyses.
> Hope that the above helps.
> --
> "Patrice" wrote:
>