28.11.2017

Truncate Statement: In 3 Schritten alte Partitionen in einer Datenbank leeren

Technical Value

In diesem Blogbeitrag zeige ich euch einen einfachen Weg, wie man alle alten Partitionen einer Faktentabelle dynamisch und ohne viel Aufwand mit dem Truncate Statement bereinigen kann. Dies schafft entweder Platz für zukünftige Daten oder ist der erste Schritt zum Verkleinern der Datenbank. 

Ausgangssituation
Ein typischer Ansatz bei der Modellierung eines Data Warehouse ist die zeitliche Unterteilung der Faktentabellen in Partitionen. Dafür bietet sich in vielen Fällen die Monats ID als geeignete Value an. Im Laufe der Zeit gelangt man nun an einen Punkt, an dem die physische Größe des Data Warehouse zum Thema werden kann. Eventuell ist die Speicherkapazität der Festplatte erreicht, alte Partitionen müssen auf der produktiven Umgebung nicht länger vorgehalten werden oder man möchte einfach die Entwicklungsumgebungen der verschiedenen Entwickler klein halten. Je nach SQL Server-Version gibt es nun verschiedene Vorgehensweisen, um das gewünschte Ziel zu erreichen. Bei älteren Versionen ist es beispielsweise nicht möglich, eine Partition gezielt mit dem Truncate Statement anzusprechen. In diesem Fall muss mit einem Partition Switch gearbeitet werden. Ab der Version 2016 gibt es eine neue Erweiterung für die Truncate-Table Funktion, mit welcher sich eine Partition direkt anhand ihrer Partitionsfunktion und der Value ansprechen lässt. Dies erleichtert den gesamten Prozess und ist wesentlich performanter als der Partition Switch. Die Stored Procedure, welche ich in diesem Beitrag erläutern werde, lässt sich mittels geringfügiger Änderungen jedoch mit beiden Vorgehensweisen verbinden.

Nehmen wir also an, man hat mehrere partitionierte Faktentabellen in einer Datenbank. Diese beinhalten verschiedene Kennzahlen, die Dimensionsschlüssel und die Monats ID im Format YYYYMM.
Truncate Statement Fakentabelle

Schritt 1
Als erstes benötigt man eine Liste von allen in der Datenbank vorhandenen Partitionen, inklusive der notwendigen Informationen. Diese sind der Tabellenname, der Schemaname, der Name der Partitionsfunktion und der hinterlegten Value. Folgendes Statement greift dabei auf die Systemtabellen zurück und liefert das gewünschte Ergebnis: 
 

  1. select
  2.                 t.name as TableName
  3.                 ,cast(prv.value as Int) as PartitionValue
  4.                 ,s.name as SchemaName
  5.                 ,pf.name as FunctionName
  6.                 from sys.tables t
  7.                         inner join sys.schemas s
  8.                 on t.schema_id = s.schema_id
  9.                         inner join sys.indexes i
  10.                 on t.object_id = i.object_id
  11.                         inner join sys.partition_schemes ps
  12.                 on i.data_space_id = ps.data_space_id
  13.                         inner join sys.partition_functions pf
  14.                 on ps.function_id = pf.function_id
  15.                         inner join sys.partition_range_values prv
  16.                 on ps.function_id = prv.function_id


Die Ausgabe sollte wie folgt aussehen:

Truncate Statement Partitionen

  1. Truncate table dbo.Fact_Quantity
  2. With (partitions ($partition.[dbo.FactPF](201001)));

Schritt 2
Um den ganzen Vorgang zu automatisieren, wird als nächstes ein Cursor implementiert, welcher durch die Ausgabe iteriert und das Truncate Statement für jeden Eintrag ausführt. Dabei gibt es jedoch eine Kleinigkeit zu beachten, denn dem Funktionsaufruf können innerhalb des Cursors keine Parameter direkt übergeben werden. Deshalb muss zusätzlich eine Variable angelegt und dieser der entsprechende String zusammen mit den benötigten Parametern übergeben werden. Der Inhalt dieser Variable wird anschließend per Exec(@run) ausgeführt.
Das Statement sieht nun wie folgt aus:
 

  1. declare         @TableName varchar(255),
  2.                         @PartitionValue int,
  3.                         @SchemaName varchar(255),
  4.                         @FunctionName varchar(255),
  5.                        
  6.        
  7.         declare CurPartition Cursor For
  8.  
  9.                          select
  10.                                 t.name as TableName
  11.                                 ,cast(prv.value as Int) as PartitionValue
  12.                                 ,s.name as SchemaName
  13.                                 ,pf.name as FunctionName
  14.                                 from sys.tables t
  15.                                         inner join sys.schemas s
  16.                                 on t.schema_id = s.schema_id
  17.                                         inner join sys.indexes i
  18.                                 on t.object_id = i.object_id
  19.                                         inner join sys.partition_schemes ps
  20.                                 on i.data_space_id = ps.data_space_id
  21.                                         inner join sys.partition_functions pf
  22.                                 on ps.function_id = pf.function_id
  23.                                         inner join sys.partition_range_values prv
  24.                                 on ps.function_id = prv.function_id
  25.                          
  26.  
  27.                 Open CurPartition
  28.                                 Fetch Next From CurPartition
  29.                                 Into @TableName, @PartitionValue, @SchemaName,
  30.                                 @FunctionName
  31.  
  32.                         While @@FETCH_STATUS = 0 Begin
  33.  
  34.                                 declare @run nvarchar(200)
  35.                                 set @run='truncate table ' +  @SchemaName+ '.' + @TableName
  36.                                 + ' with (partitions ($partition.['+ @FunctionName +'](' +
  37.                                 cast(@PartitionValue as varchar) + ')))';
  38.                                 exec(@run)
  39.                         Fetch Next from CurPartition
  40.                         Into @TableName, @PartitionValue, @SchemaName, @FunctionName
  41.                         End
  42.                 Close CurPartition
  43.         Deallocate CurPartition

Schritt 3
Da aber selbstverständlich noch Daten benötigt werden und das Truncate Statement somit nicht auf alle Partitionen angewendet werden soll, wird als nächstes die Monats ID implementiert. Dafür muss zuerst ein neuer Eingabeparameter deklariert werden, welcher die Anzahl der X neusten Partitionen repräsentiert. Für diese Partitionen soll das Truncate Statement nicht ausgeführt werden. Im nächsten Schritt lässt sich daraus mit wenigen Datumsfunktionen die älteste Partition berechnen, für welche später noch Daten vorliegen sollen. Dieser Monatsparameter ergänzt nun zusammen mit der Where Klausel das Select Statement. Zum Abschluss wird der gesamte Code zur einfachen Wiederverwendbarkeit in einer Stored Procedure verpackt.

Ergebnis

  1. CREATE procedure dbo.TruncateAllOldPartitions
  2.  (@CountMonth int)
  3. as
  4. begin
  5.         set nocount on
  6.  
  7.         declare @TableName varchar(255),
  8.                         @PartitionValue int,
  9.                         @SchemaName varchar(255),
  10.                         @FunctionName varchar(255),
  11.                         @LastMonthID date,
  12.                         @ParamLastMonthID int
  13.  
  14.         select @LastMonthID = (dateadd(month,-@CountMonth +1, current_timestamp))
  15.         Select @ParamLastMonthID = replace(left(@LastMonthID,7),'-','')
  16.  
  17.         declare CurPartition Cursor For
  18.  
  19.                          select
  20.                                 t.name as TableName
  21.                                 ,cast(prv.value as Int) as PartitionValue
  22.                                 ,s.name as SchemaName
  23.                                 ,pf.name as FunctionName
  24.                                 from sys.tables t
  25.                                         inner join sys.schemas s
  26.                                 on t.schema_id = s.schema_id
  27.                                         inner join sys.indexes i
  28.                                 on t.object_id = i.object_id
  29.                                         inner join sys.partition_schemes ps
  30.                                 on i.data_space_id = ps.data_space_id
  31.                                         inner join sys.partition_functions pf
  32.                                 on ps.function_id = pf.function_id
  33.                                         inner join sys.partition_range_values prv
  34.                                 on ps.function_id = prv.function_id
  35.                         where
  36.                                 cast(prv.value as int) < @ParamLastMonthID  
  37.  
  38.  
  39.                 Open CurPartition
  40.                                 Fetch Next From CurPartition
  41.                                 Into @TableName, @PartitionValue, @SchemaName,
  42.                                 @FunctionName
  43.  
  44.                         While @@FETCH_STATUS = 0 Begin
  45.  
  46.                                 declare @run nvarchar(200)
  47.                                 set @run='truncate table ' +  @SchemaName+ '.' + @TableName
  48.                                 + ' with (partitions ($partition.['+ @FunctionName +'](' +
  49.                                 cast(@PartitionValue as varchar) + ')))';
  50.                                 exec(@run)
  51.                         Fetch Next from CurPartition
  52.                         Into @TableName, @PartitionValue, @SchemaName, @FunctionName
  53.                         End
  54.                 Close CurPartition
  55.         Deallocate CurPartition
  56.  
  57. End

Ergebnis mit Partition Switch:
Bei einer SQL Server-Version vor 2016 kann nicht auf die Truncate-Table Funktion zurückgegriffen werden und die While Schleife ist dementsprechend anzupassen. Dafür ruft man die Stored Procedure für den Partition Switch auf und übergibt ihr mit jedem Aufruf die entsprechenden Parameter aus dem Cursor. Die Partitionsfunktion wird dabei nicht benötigt und die Schleife sollte wie folgt aussehen:
 

  1. Open CurPartition
  2.                 Fetch Next From CurPartition
  3.                 Into @TableName, @PartitionValue, @SchemaName
  4.  
  5.                 While @@FETCH_STATUS = 0 Begin
  6.                                 EXECUTE [dbo].[PartitionSwitsch]
  7.                                          @TableName
  8.                                         ,@PartitionValue
  9.                                         ,@SchemaName;
  10.                 Fetch Next from CurPartition Into @TableName, @PartitionValue,
  11. @SchemaName

Fazit
Durch den Einsatz der Stored Procedure lässt sich bei einem großen Data Warehouse viel Zeit und Arbeit einsparen. Voraussetzung hierfür ist natürlich, dass die Faktentabellen zeitlich partitioniert sind. Da das Select Statement auf die System Tabellen zugreift und der gesamte Code in einer Stored Procedure hinterlegt ist, lässt er sich ohne weiteres auf verschiedene Datenbanken anwenden. 

Die benötigte Stored Procedure für den Partition Switch gibt es hier: Dynamically Truncate a Partitioned Table

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden