DI job causing high levels of I/O on database server
We have a DI job that is loading a sql server 2005 database. When the facts are loaded itu2019s causingu2019s a high level of I/O on the database server causing the DI job to slow down. No more than 5 facts are loaded concurrently. The fact dataflows all have a sql transform to run the select query against the DB, a few query transforms to do lookups to get dimension keys, and all do inserts to the target. The DBA says there are too many DB connections open and DI is not closing them. My thinking was DI would manage the open connections for lookup, etc and would close then properly when the dataflow is complete.
Any thoughts on what else would cause high levels of DB I/O?
- Run the DI job, source and target tables are in SQL Server, and it takes 5 hours.
- Run the same DI job again, on the same data set, and it takes 12+ hours. This run will have high levels on DB I/O.
- But if SQL Server is stopped and restart, the job will again take 5 hours the first time it runs.
Edited by: Chris Sam on Apr 15, 2009 3:43 PM