Analysis of Data Flow

Analysis of Data Flow

 

Applies To: SQL Server 2016 Preview

You can use the catalog.execution_data_statistics SSISDB database view to analyze the data flow of packages. This view displays a row each time a data flow component sends data to a downstream component. The information can be used to gain a deeper understanding of the rows that are sent to each component.

System_CAPS_noteNote

The logging level must be set to Verbose in order to capture information with the catalog.execution_data_statistics view.

The following example displays the number of rows sent between components of a package.

use SSISDB
select package_name, task_name, source_component_name, destination_component_name, rows_sent
from catalog.execution_data_statistics
where execution_id = 132
order by source_component_name, destination_component_name

The following example calculates the number of rows per millisecond sent by each component for a specific execution. The calculated values are:

  • total_rows - the sum of all the rows sent by the component

  • wall_clock_time_ms – the total elapsed execution time, in milliseconds, for each component

  • num_rows_per_millisecond – the number of rows per millisecond sent by each component

The HAVING clause is used to prevent a divide-by-zero error in the calculations.

use SSISDB
select source_component_name, destination_component_name,
    sum(rows_sent) as total_rows,
    DATEDIFF(ms,min(created_time),max(created_time)) as wall_clock_time_ms,
    ((0.0+sum(rows_sent)) / (datediff(ms,min(created_time),max(created_time)))) as [num_rows_per_millisecond]
from [catalog].[execution_data_statistics]
where execution_id = 132
group by source_component_name, destination_component_name
having (datediff(ms,min(created_time),max(created_time))) > 0
order by source_component_name desc

Community Additions

ADD
Show:
© 2016 Microsoft