Implement Best Practice and Performance tuning tips in SSIS package

We need to follow set of rules and check list to implement the best practice that can be improved the performance in SSIS development.

SQL Queries in SSIS Development

Avoid (*) in SELECT query

The SELECT statement is retrieving specific columns to perform the current operation otherwise it takes another round for the source to gather meta-data about the columns when you are using (*) in select statement.

ANSI format in SQL Statement

• The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand.
• There are a few circumstances where the older outer join syntax (using +) is ambiguous and the query results are hence implementation dependent - or the query cannot be resolved at all. These do not occur with ANSI-92.
• It does tend to avoid accidental cross joins.

sp_executesql (also known as “Forced Statement Caching”)

We should call dynamic queries in sp_executesql instead of EXEC. The sp_executesql will create the execution plan at first time and the plan will be used every time on the execution.

Avoided Functions on the Left Hand-Side of the Operator

Functions are a handy way to provide complex tasks and they can be used both in the SELECT clause and in the WHERE clause.

Using joins instead of Correlated sub queries

A correlated sub query is a sub query which depends on the outer query. It uses the data obtained from the outer query in its WHERE clause. Suppose you want to list all users who have made a donation.

Not Using column numbers in Order by Clause

In ORDER BY clause, use column names instead of numbers which would be good performance.
Use Stored Procedures in frequent execution
Create separate stored procedures and use it wherever the package has SQL queries. The stored procedure name starts with “USP_” instead “SP_” since it’s a system defined name.

BEGIN TRY …. AND BEGIN CATCH

Doing the error handling in SQL Server has not always been the easiest thing, so this option definitely makes it much easier to code for and handle errors. All the stored procedures SQL Statements are within a TRY CATCH block and the errors handled using RAISE ERROR method.

Best Practice in SSIS Development

Data type Conversion in Query Level

When we are writing SQL queries in source editor, convert the field data type in SQL query instead of using separate data conversion task.

Data Access Mode

There are two different data access modes available in source editor and reading few columns in source then we can use SQL command instead of table or view option. If we are reading all the columns from the source then we can go for table or view option.

Data Access Mode Destination Properties

Fast Load

This setting provides the 'fast load' option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other options.

Table Lock

This option will be locked destination table for enforcing the data load on the table and other process would not be allowed up to complete the current process. 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.

Constraint Check

This setting specifies that the data flow pipeline engine will validate the incoming data against the constraints of target table. This option is unchecked on the destination table, it will improve the performance of the data load.

Rows per Batch

The default value for this setting is -1 which specifies all incoming rows will be treated as a single batch.

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.
• If "Maximum insert commit size" is specified then don't bother setting "Rows per Batch" as it will be ignored anyway
• If "Maximum insert commit size" is not specified i.e. left as 0, then set "Rows Per Batch" to your best estimate of the "total number of rows" that will loaded in order to enable SQL server to select the most efficient way to perform the operation.

Staging database constraints

The staging database does not have any constraints and used fast load method in ETL package for the performance.

Parallel Execution

Implement the default values for MAXCONCURRENTEXECUTABLES and engine threads because this may vary based server configurations.

MAXCONCURRENTEXECUTABLES properties

MAXCONCURRENTEXECUTABLES is the package level property and has a default value of -1, which means the maximum number of tasks that can be executed is equal to the total number of processors on the machine plus two.

Engine Threads

Engine Threads is a data flow task level property and has a default value of 10, which specifies the total number of threads that can be created for executing the data flow task.

Avoid Blocking Transformations

SSIS package has some blocking transformations like as Sort, Aggregate, Fuzzy Group and try to avoid to using these transformation in ETL package.

Also see below articles

Implement Best Practice and Performance tuning tips in SSIS and ETL Development
Top 15 Power BI interview Questions with detailed Answers