SSIS 2008 Performance and Scalability

Ella Maschiach — Sun, 30 May 2010 19:05:00 GMT

This is continued live blogging from the Donald Farmer seminar from the SQL & BI convention in Tel Aviv of Data Integration in Large Organizations.

SSIS 2008 Performance and Scalability

The importance of measuring to improve performance

Understand and measure hardware:
How many CPU cores?
How much memory?
How fast is the I\O subsystem?

Understand the potential bottlenecks:
Understand the limits of the Source System (usually a bottleneck as it's usually slower than the new system)

Gains from the source system:
Find better drivers
Configure the driver
Optimize I\O and network configuration

Measure Speed per Connection: Row / sec = Row Count / Time of the data flow

Improving the speed of the Source
Use multiple connections (not possible for a text file, but is possible for a DB with a query that takes CustID 1-1000 and then 1000- 2000 etc)
Convert the data from string to the relevant type (like int) already at the source - can create better performance.

FastParse - doesn't have any error handling. We have to guarantee that the integers are indeed integers else we won't get the error and it just won't execute. We gain better speed.

Don't assume you know where performance is best. Test it to see where it will be best - source, transformation or destination.

SSIS will take all the memory possible for it. There is no memory management for it at the current version, though it is planned for future release.

Tuning the Data Flow

Use the NOLOCK hint to remove locking overhead in large table scans
SELECT only columns you need

Network Tuning
Change the network packet size in the connection manager - put in the max of 32767, cut it by half and see how it changed performance.

Make data types as narrow as possible
Casting data types is expensive, especially in dates
Think of money, float and decimal - consider how effective they are.

SQL destination - guarantees great performance if the DB is on the same server as the SSIS.

Use partitions and partitions SWITCH load data in parallel and in great performance and then use SWITCH to connect all the tables together.

Even if you use split, you don't have to union all them. You can load the data in parallel to the DB. This is dependant of having an index on the table.

Simplify the logic as much as possible.

Change the Design
Don't Sort unless you absolutely have to (a time you do need - joining data when one source is text file that needs to be sorted). Usually you don't need to use Sort. Use the capabilities of the Source System (indexes, set based operations).
Sometimes T-SQL is faster - taking advantage of Indexes or set operations: an Update by T-SQL, group by, sum.

Delta detection: if more than 10% of your data has changed - reload it!

Do minimally logged operation if possible.

Designing for parallelism
Partition the problem: partition source data, partitioning the target table.
Design to stay in memory.

Schedule it correctly.
Right now, we're using SQL Agent, suppose to get a scheduler for the next version of SSIS.

Lookup in SSIS gives good performance, close in performance to T-SQL.

SSIS 2008 improvements over SSIS 2005

Ella Maschiach — Sun, 30 May 2010 15:38:00 GMT

This is continued live blogging from the Donald Farmer seminar from the SQL & BI convention in Tel Aviv of Data Integration in Large Organizations.

SSIS 2008 improvements over SSIS 2005

C# scripting can reference all .net assemblies
VSA replaced by VSTA

Pipeline Scale Up

Tuning SSIS through looking at the buffers on the Data Flow.
In SSIS 2005 you would add buffers and break up flows

SSIS Tip:
Tools > Options > BI Designers > IS Designers:
Control Flow Data Connect
Data Flow Data Connect
Check them both to connect the components automatically

Use Row Sampling component to do performance testing.

One buffer structure goes all through the process.
We load data in the beginning but we don't need it to move in memory. The problem is when one buffers slows down the process, it slows down for all the buffers before him as well.
In 2008 we have benchmarks for each buffer and if needed, create buffers artificially (rather than manually as we had to in 2005).

Asynchronous component - the input and output are different. We don't know what have in the middle.
Sort - asynchronous, creates a new buffer as you can exclude a column.
The other components don't need to be held up when an asynchronous component doesn't run quickly, as they don't depend on it.
Advanced Editor for the component: SynchronousInputID exists for synchronous components (and not for asynchronous).

ADO .net support - for improved source UI and added destination adapter (enabling you to use SSIS to load data into providers other than SQL Server).
The performance is still 30% slower because of the use of ADO .net provider, but it enables greater flexibility.
Use OLEDB providers as is, preferably don't use .Net OLEDB providers through ADO .net providers unless you have to.
When you connect using the ADO .net, you will only be getting a list of tables. If the data isn't in shape of tables, you won't see anything in the dropdown list.

Use ADO .net if you have to use ODBC, or if the component you're using requires it, or if you need it for your Script component.
Your default should still be OLEDB.

Import \ Export Wizard is much smarter:
ADO .net support for source and destination
Data types conversion - does only accurate conversions

Starting Import \ Export from the program menu enables straight execution. Starting it in SSIS Package requires execution to be done manually.

Persistent cache usage reducing DB and memory usage.
Cache in Lookup enabling more control over sharing and lifetime of reference data.
Cache potential cons - requiring more disk storage and data in it available only for SSIS.

New to SQL Server 2008 that's useful for SSIS 2008:
SQL Merge Syntax
Change Data Capture CDC (works only against SQL Server or through partner product against other sources)

Enhanced supportability:
In case SSIS crashes, SuperDump collects all the data needed to analyze why it crashed. Enables quicker time to analyze and treat the problem.

Data Profiling which enables you to react to the data quality. Enables analysis of the amount of nulls in a column, the uniqueness of a field etc.

Community Samples on Codeplex - code for you to use freely (SharePoint List adapter for instance).

More connectivity out of the box in SSIS 2008. The Attunity connector which is inside SSIS 2008 moves data into Oracle quicker than with the connector Oracle gives you.

Ella Maschiach's BI Blog : SSIS 2008

SSIS 2008 Performance and Scalability

SSIS 2008 Performance and Scalability

The importance of measuring to improve performance

Tuning the Data Flow

SSIS 2008 improvements over SSIS 2005