Data Source Tuning

Relational data sources, whether SQL Server or any other database product, rely on server optimization for peak performance. There are several SSIS features that make this process as efficient as possible, but there is only so much you can do within Integration Services to make queries run as fast as possible. Apply standard database optimization techniques, which might include:

• Designing tables with indexes to support sorting and query joins.

• Creating database objects such as views, stored procedures, and user-defined functions to encapsulate complex queries.

• Minimizing the use of multi-table joins, outer joins, unions, and subqueries where possible.

• Saving index statistics and execute queries ahead of time to cache execution plans.

• Using nondefault locking options to optimize queries when concurrent user access isn't required.