05/03/2015 01:14
Let’s discuss few necessary steps when we want to optimize the extracting and loading process in the package. Before you make any changes in your package, always use SSIS advance functionality to monitor and log metadata about package execution (SSIS Logging, SSIS Performance Counter, and SQL Server Profiler)
Steps you should check while creating SSIS package:
1. Remove unwanted columns from the source and set data types appropriately; this will help you with buffer management also try to use SQL command to fetch the source data because when you use "Table or view" or "Table name or view name from variable" data access mode in OLEDB source. It behaves like SELECT * and pulls all the columns.
2. Check your system has sufficient memory available that way you can set property to have small number of large buffers also try to tune DefaultBufferMaxSizeand DefaultBufferMaxRows properties of data flow task and test your package.
3. Checkpoint: This allows your package to start from the last point of failure on next execution. By enabling this feature you can save a lot of time for successfully executed tasks and start the package execution from the task which failed in last execution. You can enable this feature for your package by setting three properties (CheckpointFileName, CheckpointUsage and SaveCheckpoints )
4. The SSIS project has the RunInOptimizedMode property, which is applicable at design time only, which if you set to TRUE ensures all the data flow tasks are run in optimized mode irrespective of individual settings at the data flow task level.
5. There are few settings you will have to do with OLEDB destination which can definitely impact the performance of your package process.
· Data Access Mode: Use 'fast load' option for uploading data into the destination table which works as a Bulk Insert Statement.
· Keep Identity: By default this setting is unchecked which means the destination table (if it has an identity column) will create identity values on its own.
· Keep Nulls: unchecked means default value will be inserted during insert into the destination table if NULL value is coming from the source for that particular column. If you check this option then default constraint on the destination table's column will be ignored and preserved NULL of the source column will be inserted into the destination.
· Table Lock: check this option unless the same table is being used by some other process at same time. It specifies a table lock will be acquired on the destination table instead of acquiring multiple row level locks, which could turn into lock escalation problems.
· Check Constraints: un-check it if you are sure that the incoming data is not going to violate constraints of the destination table.
There are two more settings in OLEDB Destination, Which are very important to understand to improve the performance.
a. Rows per batch – The default value for this setting is -1 which specifies all incoming rows will be treated as a single batch. You can change this default behavior and break all incoming rows into multiple batches. The allowed value is only positive integer which specifies the maximum number of rows in a batch.
b. Maximum insert commit size – The default value for this setting is '2147483647' (largest value for 4 byte integer type) which specifies all incoming rows will be committed once on successful completion. You can specify a positive value for this setting to indicate that commit will be done for those number of records.
There are other factors also which impact the performance, one of them is resource availability. So please do proper testing by you before putting these changes into your production environment.
