SSIS job taking more time than expected

SSG 1 Reputation point
2021-11-16T02:34:47.273+00:00

Hi ,

We have a SQL job configured to run SSIS package every 2 minutes between 7:35am and 7:30pm.
The job usually takes less than a minute. The job takes more than 40 minutes to run at 7:35am some days.

From SQL error logs, could see the below message when the job took more time.

last target outstanding: 2, avgWriteLatency 19
average writes per second: 18.53 writes/sec
average throughput: 2.50 MB/sec, I/O saturation: 2939, context switches 2764
FlushCache: cleaned up 26072 bufs with 1507 writes in 81315 ms (avoided 16538 new dirty bufs) for db 10:0

last target outstanding: 16, avgWriteLatency 35
average writes per second: 31.31 writes/sec
average throughput: 4.03 MB/sec, I/O saturation: 5344, context switches 4730
FlushCache: cleaned up 63868 bufs with 3873 writes in 123715 ms (avoided 39804 new dirty bufs) for db 10:0

There are other SSIS jobs configured to run on this sever, but I dont see any schedule conflict among them.

Not able to figure out, why only at that particular time(7:35am)149593-job-history.png the job is taking more time to run and that too only on certain days.
Could someone help me out?

Thanks.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,640 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,450 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-11-16T06:30:58.98+00:00

    Hi @SSG ,

    > why only at that particular time(7:35am)149593-job-history.png the job is taking more time to run and that too only on certain days.

    Check to see if there is any blocking when issued occurred.

    select * from sys.sysprocesses where blocked<>0

    Then check wait stats to see what your SQL Server is waiting on.

  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-11-16T22:41:19.023+00:00

    So what does the job actually do? You say that the job runs from 7:35 to 19:30 (I did get the am/pm correct, or did I? I'm a 24-hour person and get a headache when people use am/pm), and the long run is one at 7:35, or the first in the morning. So it seems to me that a possible reason is that there is a lot more data to process now when then job has not been running for 12 hours.

    Or is this assumption to simple-minded?

    0 comments