Optimierung der Unit of Work in SSIS mit T-SQL und Biml

Optimierung der Unit of Work in SSIS mit T-SQL und Biml

This blog post has also been published in English.

Have you ever run into the situation, where you logically could run all of your load tasks in SSIS in parallel but the system (source or target) would not support it physically?
Then you probably ended up organizing your dataloads into multiple groups, aiming for ideally equally sized containers.
Doing that manually is a time consuming issue – and it never ends as the timings of your data loads will constantly change.

This post is about showing you, how you can easily you can automate the assignment of your tables to containers using simple T-SQL as well as about automating the load package using Biml.

It is not about the difference in performance between load patterns or how to build staging packages with Biml – there are many other posts and tutorials out there about that, so we’ll strictly focus on how we can optimize our SSIS workload.

First, create a new database (ours is called "Performance", but you can choose any name that suits your needs; just be sure to adjust the connection string in the Biml code later on).

Within that database, we’ll need some meta tables which we will store in a new schema:

CREATE SCHEMA meta
GO
CREATE TABLE [meta].[Container]
([ContainerID] [INT] NULL,
 [TotalCost]   [FLOAT] NULL,
 [Tables]      [INT] NULL
)
ON [PRIMARY]
GO
ALTER TABLE [meta].[Container]
ADD CONSTRAINT [DF_Container_TotalCost] DEFAULT((0)) FOR [TotalCost]
GO

ALTER TABLE [meta].[Container]
ADD CONSTRAINT [DF_Container_Tables] DEFAULT((0)) FOR [Tables]
GO
CREATE TABLE [meta].[Tables]
([TableName] [NVARCHAR](50) NULL,
 [Cost]      [FLOAT] NULL,
 [Container] [INT] NULL
)
ON [PRIMARY]

Die Tabelle meta.tables befüllen wir auch mit einigen fiktiven Beispieldaten:

INSERT INTO [meta].[Tables] ([TableName] ,[Cost] ,[Container]) VALUES ('Person.Person',1,0)
INSERT INTO [meta].[Tables] ([TableName] ,[Cost] ,[Container]) VALUES ('Person.PersonPhone',1,0)
INSERT INTO [meta].[Tables] ([TableName] ,[Cost] ,[Container]) VALUES ('Sales.SalesOrderHeader',10,0)
INSERT INTO [meta].[Tables] ([TableName] ,[Cost] ,[Container]) VALUES ('Sales.SalesOrderDetail',20,0)

Die Tabelle meta.containers könnte ebenso eine temporäre Tabelle sein. Wir haben uns dagegen entschieden, um sichtbar zu machen, was hinter den Kulissen passiert.

Konzentrieren wir uns jedoch auf meta.Tables und die drei darin enthaltenen Spalten:

– TableName – Der Name der Tabelle
– Container – In welchem Container soll die Tabelle geladen werden. Diese Information wird im nächsten Schritt durch eine Stored Procedure errechnet und kann daher leer bleiben.
– Cost – Diese Spalte enthält die Kosten zum Laden der Tabelle. Idealerweise ist dies zum Beispiel die durchschnittliche Laufzeit der letzten Durchläufe. Wenn Ihnen diese Information nicht vorliegt könnte es auch die Größe in GB oder notfalls nur die Zeilenanzahl sein. Je akurater die Kosten der Beladung vorliegen, desto besser wird das Ergebnis der Optimierung vorliegen.

Wie bereits erwähnt, wird die eigentliche Arbeit durch eine (bzw. mehrere) Stored Procedures erledigt.

SP_CreateContainers generiert in der Tabelle meta.Container lediglich einen Eintrag je Container:

CREATE PROCEDURE [dbo].[SP_CreateContainers](@MaxContainers INT)
AS
	BEGIN
	    DECLARE @CurrentContainer INT= 1
	    TRUNCATE TABLE Meta.Container
	    WHILE @CurrentContainer <= @MaxContainers
		   BEGIN
			  INSERT INTO Meta.Container
			  (ContainerId)
			  VALUES
			  (@CurrentContainer)
			   SET @CurrentContainer = @CurrentContainer + 1
		   END
	END

SP_UpdateContainerStats aktualisiert die Statistiken jedes Containers (wieviele Tabellen sind enthalten und was sind die Gesamtkosten):

CREATE PROCEDURE [dbo].[SP_UpdateContainerStats]
AS
	BEGIN
	    UPDATE a SET tables = cnt, TotalCost = Cost  FROM meta.container a
	    INNER JOIN
	    (SELECT container, COUNT(*) AS CNT, SUM(Cost) AS Cost FROM meta.tables GROUP BY container) b
	    ON a.containerid = b.container
	END
GO

SP_SetContainers ist für die eigentliche Arbeit zuständig, sie

– Setzt die Zuordnung zu den Containern in meta.tables zurück
– Ruft SP_CreateContainers auf um die Anzahl an Containern welche mittels des einzigen Parameters "MaxContainers" definiert wurde anzulegen
– Öffnet einen Cursor welche alle Tabellen aus meta.Tables abruft, sortiert von den höchsten zu den niedrigsten Kosten
– Prüft für jede Tabelle, welcher Container derzeit die niedrigsten Gesamtkosten hat, weist sie diesem Container zu und aktualisiert die Statistiken des Containers

Sobald das abgeschlossen ist (für 2 oder 2000 Tabellen), sollten alle Tabellen ziemlich gleichmässig über die Container verteilt sein:

CREATE PROCEDURE [dbo].[SP_SetContainers](@MaxContainers INT)
AS
	BEGIN
	    UPDATE meta.tables
		 SET
			container = 0
	exec [SP_CreateContainers] @maxcontainers
	Declare @TableName nvarchar(50)
	Declare @Cost float
	declare @NextContainer Bigint
	DECLARE tbl_Cursor CURSOR FOR  
SELECT TableName,Cost
FROM meta.tables order by Cost desc

OPEN tbl_Cursor   
FETCH NEXT FROM tbl_Cursor INTO @TableName,@Cost 

WHILE @@FETCH_STATUS = 0   
BEGIN   
       select @NextContainer= min(containerid) from (
SELECT   [ContainerID]
      ,[TotalCost]
      ,[Tables],min([TotalCost]) over() MinCost
  FROM  [meta].[Container]) a where [TotalCost] = MinCost
	   update meta.tables set container = @NextContainer where TableName = @TableName
	   exec SP_UpdateContainerStats
       FETCH NEXT FROM tbl_Cursor INTO  @TableName,@Cost  
END  
 
		CLOSE tbl_Cursor   
DEALLOCATE tbl_Cursor    
  exec SP_UpdateContainerStats

	END

Nun fehlen uns noch die SSIS Pakete um diese Tabellen zu befüllen.
Das wollen wir natürlich mittels Biml automatisieren:

In C#:

<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data" #>
<# int MaxContainers = 3;
string ConnString = "Data source=localhost; Database=Performance; Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(ConnString);
SqlCommand cmd = new SqlCommand("exec [dbo].[SP_SetContainers] "  + MaxContainers, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close(); #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
        <Package Name="Load_Containers" ConstraintMode="Parallel">
            <Tasks>
                <# for (int Container = 1; Container <= MaxContainers; Container++) { #>
                <Container Name="Load Container <#=Container #>" ConstraintMode="Linear"><Tasks>
                <# foreach (DataRow row in ExternalDataAccess.GetDataTable("Provider=SQLNCLI11;" + ConnString,"select * from meta.tables where container = " + Container).Rows) {  #>
                <Container Name="Load <#= row["TableName"] #>" ConstraintMode="Linear">
                    <Tasks>
                           <!-- Your dataload pattern goes here! -->
                     </Tasks>
                </Container>
                 <# } #>
                 </Tasks></Container>
                <# } #>
             </Tasks>
        </Package>
    </Packages>
</Biml>

Oder in VB:

<#@ template language="VB" optionexplicit="False" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data" #>
<# MaxContainers = 3
ConnString = "Data source=localhost; Database=Performance; Integrated Security=SSPI"
Dim conn as new SqlConnection(ConnString)
Dim cmd = new SqlCommand("exec [dbo].[SP_SetContainers] "  & maxcontainers, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close() #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Load_Containers" ConstraintMode="Parallel">
            <Tasks>
                <# for Container = 1 to MaxContainers #>
                <Container Name="Load Container <#=Container #>" ConstraintMode="Linear"><Tasks>
                <# for each row in ExternalDataAccess.GetDataTable("Provider=SQLNCLI11;" + ConnString,"select * from meta.tables where container = " & container).Rows  #>
                <Container Name="Load <#= Row("TableName")#>" ConstraintMode="Linear">
                    <Tasks>
                           <!-- Your dataload pattern goes here! -->
                     </Tasks>
                </Container>
                 <# next #>
                 </Tasks></Container>
                <# next #>
             </Tasks>
        </Package>
    </Packages>
</Biml>
 

Unser Biml code enthält eine Variable, über welche wir die Anzahl der gewünschten Container definieren. Der Code führt dann zunächst unsere Stored Procedure aus.
Danach erstellt er ein Paket welches die gewünschte Anzahl Sequenzcontainer enthält. Jeder Container erhält dann wiederum die ihm von der Stored Procedure zugewiesenen Tabellen.
Wie eingangs erwähnt geht es uns hier nicht um Load patterns, daher ist der eigentliche Datenload hier nur als Kommentar eingebaut.

Das wars auch schon!

Haben Sie hierzu Fragen oder Anmerkungen? Wir freuen uns auf Ihren Input unter biml@solisyon.de!

Weitere Informationen zu Biml, einschließlich Terminen und Blog Beiträgen finden Sie auch auf unserer Biml Seite.

Viel Spaß beim Biml’n!

Facebook