jplankenhorn (8/28/2013)
Koen Verbeeck (8/28/2013)
Why ADO.NET for a SQL Server Destination? I normally use OLE DB Destination with Fast Load option.Is the destination database in simple recovery model?
Are the database files correctly sized?
Any transformations in the data flows?
Did you try out different buffer sizes in the data flows?
About how much data are we talking here?
No particular reason. I do have the option for Use Bulk Insert checked.
Yes, simple model.
Not sure I know what you mean by database files being correctly sized.
No transformations.
How do you change buffer sizes? Any recommendations on what to base them off of?
The database is 10 GB. Largest tables have row counts under 10 million.
A typical database has two files: the data file (mdf) and the log file (ldf).
Let's say for example your data file is currently 1GB big and is at 90% capacity (so 100MB free space).
Suddenly you want to pump data in some tables for 2GB worth. This means the data file has to autogrow to at least 1.9GB.
The default autogrow setting for a database is incremental steps of 10MB. This means the database has to grow 190 times. This wastes a lot of time and is thus not really efficient.
If your database file would have a correct size of 3GB, the file wouldn't need to grow at all, so inserts would be faster.
Regarding the data flow buffers; you have two settings in the data flow property: DefaultBufferMaxRows and DefaultBufferSize. Making them larger (so they can handle more rows at the same time) might improve performance. More info: Data Flow Performance Features.
Other performance tips: Top 10 SQL Server Integration Services Best Practices
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP