February 12, 2010 at 7:07 am
Hi,
I'm very new to SSIS. We are facing a problem.
scenario 1: We have created a package which is taking 1 Lakh rows from a DB and doing a sampling and inserting into the database. The package is executed as a windows service. It is taking around 5hrs to 8hrs to execute.
scenario 2:If i execute any other package which is pulling 1 Lakh rows and inserting into a DB, it is executing very fast.
I feel the scenario 1 is taking more time because of Rowsampling. Can any one suggest me any solution. Even if you are suggesting me any tool (preferably freeware) to monitor the performance of the package or of the application server it will be of great help.
February 12, 2010 at 7:21 am
I doubt the RowSampling Transformation is the culprit. You'll have to give us some more information.
What is your source DB, what provider are you using, what does the query look like etc.
Have you tried running the query from SSMS? How does it perform there?
Can you post a copy of your package?
February 12, 2010 at 3:31 pm
try some of the following practice and re-monitor ur package:-
1) dataflow property-> Engine Thread Value-> increase
-> RunInOptimizedMode-> True
2) controlflow property
MaxConcurrentExecutables (by default -1)
reduce this value if ur machine is NOT dedicated server i.e. other applications are also running
increase this value if ur machine is dedicated server
3) Optimize ur data flow either by:
- minimum use of blocking transformations (i.e. sort, aggregate, union all)
- pre sort ur data source as an alternative to SORT transformation, using ORDERBY clause in ur query and then source transformation->right click->property-> isSorted=True and SortKeyPosition=1
-optimize ur Lookup (use Full precaching)
4) while using source/destination transformation ->consider the property-> Max.Rows per Batch (when you are doing bulk insert of data- i.e., batching inserts)
5) Optimize ur destination as well (fast load property, batchin inserts etc.)
- consider database growth and dropping/recreating indexes
-consider the compatibility of data for destination (i.e., quantity and types of data)
6)make use of SSIS logging, for monitoring performances (but be aware- don't turn this functionality on fully as it might be expensive)
7)SSIS profiler, at last...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply