Performance problems will raise due to bottlenecks .
Situation to find bottle neck
1. To create a package with optimization .
2. There is a package which is running such a long time .
This bottle neck can be at any many levels .
1. Package Level
2. Source Level
3.Destination Level
4.Transformation Level
5.Data Flow Task Level
6.System Level
We can identify bottlenecks by using progress tab information or by using log providers.
Package Level Tuning
1. Implement check points to have better restart ability of components in the package .
2. Disable Event Handler: EH decrease package performance so unnecessary event handlers should be removed or disabled .
3. Maximum Concurrent Executable : Increasing the nof executable will increase the parallelism of package and concurrantly execute in less time .
4. Maximum Error Count : Default '1' means it fails for single error in the package . If you increase the error count it doesn't fails the package until it reaches the count.
Data Flow Task Level Tuning Tips
1. Delay Validation (T/F) : True means the validation of component is delayed until the execution of other component finished.
2. Default buffer max rows and size : Increase or decrease according to the volume of data loading i.e for more volume increase rows and buffer size . For less volume decrease rows and buffer size .
3. Engine Threads : Default it takes 10 if we increase more threads it runs more parallel and uses more processes to finish the data flow operations .
Note : Thread is a part of process to do some task . More threads less data and less threads with more data decreases performance .
4. Run in Optimized Mode : If it is true then data flow avoids unnecessary transformations , conversions etc operations during package execution .
Source Level Tuning
In case of Flat File
1. Try to take the flat file local to the system .
2. Use the property "FastParse=True" so that the column uses faster , local neutral processing routines and avoids unnecessary conversions .
We can find fast parse option by right clicking on advanced editor --> Input and Output properties --> O/P columns --> Fast Parse
If the source is table or view
1. Create index on source table so that it retrieves the data faster.
2. Instead of taking a table , take a SQL Query or SQL Command as data access mode to get the required columns and rows of data .
3. At connection manager level click on ALL and change the packet size : 32767 instead of 0 . If packet size is zero means 4 KB of data will be transferred at a time . If packet size 32767 means 32 KB of data to be transferred at a time .
Destination Level Tuning Tips
1. In case of flat file try to take the file local to the system .
2. In case of relational db table or view use data access method as SQL Command to load required data.
3. Use data access mode as fast load to load the data much faster .
4. If the table contains constraints , indexes and triggers then loading will be slow so we need to disable or drop them once the loading finished recreate or enable them.
To implement this there are many ways
Another way is event handling
on pre execute of data flow disable / drop functionality.
on post execute of data flow enable / recreate .
Transformation Level Tuning Tips
We need to identify either the transformation is synchronous or asynchronous .
Asynchronous transformation always takes time to run than synchronous.
Synchronous Transformation
Here rows are processed as it is getting from the source .ex: derived column , data conversion etc.
Synchronous transformation occupies less memory and process less rows.
Asynchronous Transformation
In Asynchronous transformation until the retrieved rows system collects it doesn't perform any operation. It retrieves more memory and less rows. These transformations either blocks partially or fully so these transformations also called as partial blocking or fully blocking transformations ex sort,merge,aggregate etc.
if there are no bottle necks in the above all levels then we are having bottle necks at system level.
I hope the article was useful! and I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .
Situation to find bottle neck
1. To create a package with optimization .
2. There is a package which is running such a long time .
This bottle neck can be at any many levels .
1. Package Level
2. Source Level
3.Destination Level
4.Transformation Level
5.Data Flow Task Level
6.System Level
We can identify bottlenecks by using progress tab information or by using log providers.
Package Level Tuning
1. Implement check points to have better restart ability of components in the package .
2. Disable Event Handler: EH decrease package performance so unnecessary event handlers should be removed or disabled .
3. Maximum Concurrent Executable : Increasing the nof executable will increase the parallelism of package and concurrantly execute in less time .
4. Maximum Error Count : Default '1' means it fails for single error in the package . If you increase the error count it doesn't fails the package until it reaches the count.
Data Flow Task Level Tuning Tips
1. Delay Validation (T/F) : True means the validation of component is delayed until the execution of other component finished.
2. Default buffer max rows and size : Increase or decrease according to the volume of data loading i.e for more volume increase rows and buffer size . For less volume decrease rows and buffer size .
3. Engine Threads : Default it takes 10 if we increase more threads it runs more parallel and uses more processes to finish the data flow operations .
Note : Thread is a part of process to do some task . More threads less data and less threads with more data decreases performance .
4. Run in Optimized Mode : If it is true then data flow avoids unnecessary transformations , conversions etc operations during package execution .
Source Level Tuning
In case of Flat File
1. Try to take the flat file local to the system .
2. Use the property "FastParse=True" so that the column uses faster , local neutral processing routines and avoids unnecessary conversions .
We can find fast parse option by right clicking on advanced editor --> Input and Output properties --> O/P columns --> Fast Parse
If the source is table or view
1. Create index on source table so that it retrieves the data faster.
2. Instead of taking a table , take a SQL Query or SQL Command as data access mode to get the required columns and rows of data .
3. At connection manager level click on ALL and change the packet size : 32767 instead of 0 . If packet size is zero means 4 KB of data will be transferred at a time . If packet size 32767 means 32 KB of data to be transferred at a time .
Destination Level Tuning Tips
1. In case of flat file try to take the file local to the system .
2. In case of relational db table or view use data access method as SQL Command to load required data.
3. Use data access mode as fast load to load the data much faster .
4. If the table contains constraints , indexes and triggers then loading will be slow so we need to disable or drop them once the loading finished recreate or enable them.
To implement this there are many ways
Another way is event handling
on pre execute of data flow disable / drop functionality.
on post execute of data flow enable / recreate .
Transformation Level Tuning Tips
We need to identify either the transformation is synchronous or asynchronous .
Asynchronous transformation always takes time to run than synchronous.
Synchronous Transformation
Here rows are processed as it is getting from the source .ex: derived column , data conversion etc.
Synchronous transformation occupies less memory and process less rows.
Asynchronous Transformation
In Asynchronous transformation until the retrieved rows system collects it doesn't perform any operation. It retrieves more memory and less rows. These transformations either blocks partially or fully so these transformations also called as partial blocking or fully blocking transformations ex sort,merge,aggregate etc.
if there are no bottle necks in the above all levels then we are having bottle necks at system level.
I hope the article was useful! and I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .
It helped me lot to tune my existing packages. Thank you.
ReplyDelete