SSIS - Performance issues, is there a way to disable transactions?
Posted on 2014-12-02
I have a SSIS project which, on my development VM running SQL 2012 Standard, is taking over an hour to run. I stopped the job but the SSIS service did not release the 2.4GB or memory it was using.
I then restarted the SQL Server service and that is taking a very long time; in excess of 15 minutes. The only thing running on this server is the SSIS task. What I'm wondering is if the SQL Server are doing some monstrous rollbacks. If this is the case is there a way to turn off the transaction functionality to speed things up?
Also, as the DB is not used by anyone while the SSIS operation completes would there be any benefit to running the DB in single user mode?
I'm pretty desperate to speed this SSIS project up so any tips on debugging, identifying bottlenecks would be greatly appreciated.