Blog Post

Reblog: SSIS Performance Testing

,

I had to do some performance testing for an MSSQLTips article and I thought I’d share the framework I used in a blog post.

First of all we have to log start and end dates of the package to a table so we can easily calculate the duration a package took to finish. This can probably be calculated from the SSIS catalog as well, but I was a bit too busy lazy to find out how to do this. Anyway, the logging table is created using the following statement:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PackageLogging]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[PackageLogging](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [RunID] [int] NOT NULL,
        [PackageName] [varchar](50) NOT NULL,
        [StartDate] [datetime2](7) NOT NULL,
        [EndDate] [datetime2](7) NULL
    );
END
GO

The RunID column is populated by a package parameter; I will come back to this later on.

TestSSISPerf_parameter

The package starts with an Execute SQL Task to log the start. I use the following SSIS expression to construct the SQL statement on the fly, allowing you to easy copy-paste the logging task between packages.

“INSERT INTO dbo.PackageLogging(RunID, PackageName,StartDate) VALUES (” +  (DT_STR,10,1252)@[$Package::RunID] + “,’” +  @[System::PackageName] + “‘,SYSDATETIME());”

At the end of the control flow, there is an Execute SQL Task that updates the EndDate of the previously inserted row. The expression looks like this:

“UPDATE dbo.PackageLogging SET [EndDate] = SYSDATETIME() WHERE RunID = ” + (DT_STR,10,1252)  @[$Package::RunID] + ” AND PackageName = ‘” +  @[System::PackageName] + “‘;”

The RunID parameter is important to link those two Execute SQL Tasks together. A typical control flow looks like this:

TestSSISPerf_controllflow

Logging is the first step, now we have to run the package of course. I created a stored procedure that allows me to easily start a package in the SSIS catalog.

CREATE PROC [dbo].[RunPackage]
    (@RunID         INT
    ,@PackageName   VARCHAR(50)
    ,@FolderName    VARCHAR(50)
    ,@ProjectName   VARCHAR(50)
    ,@Synchronized  BIT = 1 -- run synchronously by default
    )
AS
DECLARE @execution_id BIGINT;
 
EXEC [SSISDB].[catalog].[create_execution]
     @package_name      = @PackageName
    ,@execution_id      = @execution_id OUTPUT
    ,@folder_name       = @FolderName
    ,@project_name      = @ProjectName
    ,@use32bitruntime   = False
    ,@reference_id      = NULL;
 
--SELECT @execution_id;
 
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
    ,@object_type       = 30
    ,@parameter_name    = N'RunID'
    ,@parameter_value   = @RunID;
 
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
     @execution_id
    ,@object_type       = 50
    ,@parameter_name    = N'SYNCHRONIZED'
    ,@parameter_value   = @Synchronized;
 
EXEC [SSISDB].[catalog].[start_execution] @execution_id;
 
GO

The proc passes the @RunID parameter to the package, as well as other usual suspects, such as the package name, folder name and project name. You can also choose if a package is run synchronously or asynchronously. When run synchronously, the stored procedure doesn’t finish until the package is finished as well.

Using this stored procedure, it is easy to run a package multiple times in a row using a WHILE loop.

DECLARE @RunID INT = 1;
 
WHILE (@RunID <= 10)
BEGIN
    EXEC dbo.RunPackage @RunID, 'myPackage.dtsx', 'myFolder', 'myProject', 1;
    SET @RunID += 1;
END

The package is run synchronously, so that multiple instances do not run at the same time. This eliminates resource contention and gives us a clearer result of the performance of the individual package.

Using the following query, it is easy to build a nice chart in SSRS:

SELECT
     [RunID]
    ,[PackageName]
    ,[Duration] = DATEDIFF(MILLISECOND,StartDate,EndDate) / 1000.0
    ,[Mean] = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF(MILLISECOND,StartDate,EndDate) / 1000.0)
                    OVER (PARTITION BY PackageName)
FROM [dbo].[PackageLogging]
ORDER BY ID;

The result:

TestSSISPerf_chart

I used RunID as category, PackageName as the series and the Duration/Mean as the values. I created a custom palette where I forced the Duration measure and the Mean to have the same color.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating