question

SSG-2301 avatar image
0 Votes"
SSG-2301 asked Cathyji-msft commented

SSIS job taking more time than expected

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-generalsql-server-integration-services
job-history.png (152.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft commented

Hi @SSG-2301,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SSG-2301,

Could you share us the detail query that the SSIS package run? Did the numbers of data is same when running this SSIS package ?
If there is no any useful output from above query. You can run PSSDIAG on 7:35am to collect the information. Then check which query waste more time, and check wait stats to see what your SQL Server is waiting on.


0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.