SSIS Master Package Analysis based on Performance Counter and Wait Stats

In order to achieve effective and goal-oriented performance tuning it is mandatory to know your system’s bottlenecks. In most cases the root causes for those bottlenecks lie deep in the system’s architecture and are not visible at first sight.  For example the following image shows one of the complex master package design.

Performance Monitor

Performance Monitor allows you to analyze all performance counters in a single window. Depending on the number of counters this can be very confusing.

SSIS Package Instances: Total number of simultaneous SSIS Packages running.

The above image illustrates that we can go up to 26 Maximum Parallel Processing (Number of Logical Processing + 2 = 24 + 2= 26).

This information will help to re organize sub packages in proper order in the master packages and parallel processing design.

Buffer Spooled: "Buffers spooled" counter to determine whether data buffers are being written to disk temporarily while a package is running. This swapping reduces performance and indicates that the computer has insufficient memory.

Total SQL memory vs SSIS memory utilization (Only SSIS packages):

From this output we can come to the conclusion that there are no any Memory Bottlenecks.

SQL Server I/O Bottlenecks:

Disk sec/Transfer –> Time taken to perform the I/O operation

Ideal value for Disk sec/Transfer is 0.005-0.015 sec. If you consistently notice this counter is beyond 0.015 then there is a serious I/O bottleneck. Look for Disk Bytes /sec immediately. If it is below 150 MB for SAN disk and Below 50 MB for Single disk then the problem is with I/O subsystem engage hardware vendor.

From this output we can come to the conclusion that there is a series bottleneck with I/O operation in the server. However this is not applicable for production environment or other environment because in the server all file groups are pointed to one disk.

% Processor Time (Total): %Processor Time Indicated how much the processor actually spends on productive threads. Seek to understand how much CPU is being used by Integration Services and how much CPU is being used overall by SQL Server while Integration Services is running.

This outcome will help us for future re organize sub packages/tasks in packages and parallel processing design.

Network: SSIS moves data as fast as your network is able to handle it. Because of this, it is important to understand your network topology and ensure that the path between your source and target have both low latency and high throughput.

From this outcome we can come to conclusion that there are no any Network bottle neck in the server.

 
Wait Stats When SQL Server is executing any task, and if for any reason it has to wait for resources to execute the task, this wait is recorded by SQL Server with the for the delay.

CXPACKET: When a parallel operation is created for a SQL query, multiple threads for a single query are used. Each query usually deals with a different set of data or rows. Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat.

Depending on the server workload type, there are several ways to reduce or avoid this wait type. Each way uses the properties “Maximum Degree of Parallelism” and “Cost Threshold for Parallelism”. 

IO_COMPLETION: The IO_COMPLETION wait type occurs while SQL Server is waiting for I/O operations to complete. This wait type generally represents non-data page I/Os.

This outcome clearly illustrate that parallel execution should be handle optimized way, otherwise unnecessary waiting happen in SQL Server. This information will help to re organize sub packages and task.

Cheers!
Uma

How to find DefaultMaxBufferRows for SSIS Package performancetuning

One of the common recommended step to improve the performance of an SSIS package is Buffer Size tuning. In this blog, I share the method that I have used for my projects, however there may be some other alternative ways you would find in other blogs.

When SSIS caches data in memory as it’s going through the ETL process, it uses something called buffers to control how much data in can pick up and transform/modify at once.

DefaultMaxBufferSize – the default is 10 MB. The maximum size is 100MB, which SSIS stores as MaxBufferSize

DefaultMaxBufferRows – The default is 10,000 rows.

DefaultMaxBufferRows * (times) single row size <= DefaultMaxBufferSize 

You can find it using calculation. However, this appropriate SSIS Package Buffer Size can be identified using SYSSSISLOG log data. Enable the following events in the package as shown below.

BufferSizeTunning – The data-flow engine may change the size of the buffer away from the default values. This event gives details of the new sizes with the reasons as given below.

PipelineInitialization – This event gives the various initialization details of the data flow task as given below.

Set very larger number for DefaultMaxBufferRows such as 1,000,000 and keep it default buffer size as 10MB. Run the packages with log.

If you run the query as shown below, log data will show the appropriate buffer size. Even though we set big value for DefaultMaxBufferRows,  SSIS engine will adejust based on its own optimization technique.

 

Same time, if you set low value for DefaultMaxBufferRows,  the DefaultMaxBufferRows will be adjusted by SSIS Engine.

For example, rows in buffer type 11 would cause a buffer size less than allocation minimum, which is 65536 bytes. There will be 1638 rows in buffers of this type.

Cheers!
Uma