SSIS Package runs fast through SQL Agent first time only

Question

SSIS Package runs fast through SQL Agent first time only

cpeck

SSC Eights!

Points: 814
More actions
April 7, 2017 at 5:57 am

#329358

We had a consultant come in to try to help us speed up our processes. Every time we implement a suggested change, the SSIS package runs fast that night through SQL Agent. The next night, it slows back down again. Can anyone explain why that would happen? Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Probably not enough information...what, specifically, is the package doing?
i would be looking at performance in the database first and foremost, assuming the package is doing something in the database. The package is n't slow, it's the work that it is doing that we have to dig into.
since you said it called is fast one time and slow another implies something changed in the data, right? a job inserted a bunch of data, but did not update statistics, for example, would show that same symptom, because statistics would be stale, but the original plan that was created is now sub-optimal now.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cpeck SSC Eights! Points: 814 More actions · Answer 2

The project contains a bunch of packages that load tables, but it's pretty straightforward. There are OLE DB Sources and OLE DB Destinations. No lookups, no transformations. The OLE DB Sources execute stored procedures that join all the information together. The stored procedures, source databases and destinations are all on the same server. Two nights ago we started updating statistics nightly on the main database source. It cut down the time it took to run almost in half (it ran in an hour). Last night it took 2 hours again.

Prior to that, I had multiple data flows in one data flow task. I was told it would run more efficiently if I only put one data flow in a data flow task so I redesigned my whole package. The first night it ran (in SQL Agent) and took a little over an hour. Within a few days, it was back to taking 2 hours again.

This seems to be a recurring theme. Anything we do speeds the job up for one night, then the next night or within a few nights it is back to 2 hours again.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

I advise you to check the All Executions report (assuming your package is deployed to SSISDB) to work out the running times for the individual components of the package, in an attempt to narrow down where the problem lies.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 4

I inherited a similar problem with SQL 2008, however it was a unique scenario that may not apply to you. There where approximately 320 tables where data was loaded to each night. As time went on the load get slower and slower. The way it had been designed was as follows: The entire contents of each table from an ODBC database would be copied into an existing table in SQL server. A Boolean field was used to mark the data as new. All previous data in the table would then be deleted. None of these tables had any indexes. When you load and delete data in this fashion, the amount of free space in the table would balloon, which severely affects performance. To prevent this, each table should have had a Clustered Index. The loading times would creep from 4 hours up to 12 hours or more.

It might be worth checking for free space in your tables.