Package execution time performance
-
Hi
I want to improve the execution time of my SSIS package.
Would any of these help?
i. Turn off replication
ii. Change the logging model - I find the log file grows huge during the transfer, and think it may be slowing the package down.
iii. Turn off or reduce logging (is this possible? how?)
iv. Does running the package in debug mode go any slower?
Can anyone hazard a guess as to the kind of performance increase such measures may produce
All suggestions gratefully received
-
While logging can add a little time to your package, I doubt it's what's dragging down your package that much. I would look at how my package is constructed before I started disabling logging. Logging is something you'll regret turning off one day when you have a problem.
Do you have any kind of baseline you're using to say the package is slow or does it just feel like it should be running faster?
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Thanks for your suggestions. Our DBA would not turn off logging, but did change it to 'simple' - but it didn't make much difference.
Symptoms are: First 1000 items go pretty quickly, then its slower and slower, so the first 1000 may run in 20 minutes, but a run of 6000 items runs in 10 hours! If I split up the run into 6 lots of 1000, I can run it in about 2.5 hours, but it requires much manual intervention, and configuring of the input file.
Looking at the package: -
Its a 2-step process: Export items from source database into a temp
table, including lots of FK linked items - thats the slowest part, then transform/insert into new database from the temp table - which is fairly quick, and doesn't slow down. The package begins by truncating the temp table - I suspected its this emptying of the temp table that improves performance at the start of a run.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply