I have a job that references an SSIS package which inserts, from a file, a bunch of data into a table with over 100 million rows. I've performance tuned the insert statement and watched it take less than 2 seconds to run for each file (sometimes it takes as much as 10 seconds for a file with a large amount of data).
I run the job at 1:00 AM and for the past week I come in the next day and the job is still running. But when I just run the job manually DURING THE DAY WHEN DB USE IS HIGHER, I watch it complete in about 1-2 hours.
I don't really have any other jobs running at night (that aren't also running during the day) and none of them are hitting that table. The only exception to that I can think of is I back up the transaction logs every 2 hours. I also have a scheduled task that moves a back up of the DB and all the transaction log backups to an external drive at 2AM. Perhaps all the disk I/O is preventing the job from running successfully and then it just gets bogged down until I manually restart it?
Question 1: should I stop the transaction log backups at night?
Question 2: where would I begin to try to figure out what could be happening at night to this job. Is there any history stored somewhere that would show what delays it seems to face at night?
1. Don't touch the backup jobs until you wasted all any other options
2. Check physical location of your input file for the job, database T-log, mdf/ndf files, and tempdb location. Ideally they should be all different for best I/O
3. Check the batch size of your import - should be somewhere in the SSIS step properties. Sometimes lower batch size can make it faster sometimes higher but do not go beyond 10.000 rows per batch. All these depend on the constraints/clustered indexes on the destination table.
4. You should add multiple files to tempdb - 1 per socket but no more than 4 to improve SQL performance - I'll try find the Microsoft link to support this statement.
5. Check make sure no viruscan is running against your SQL files and input file ASSUMING all data going into your DB is scanned at the app level and the input file was already checked.