Mi è capitato di parlare nei giorni scorsi dei benefici introdotti dal partzionanemnto in SQL 2005.
Dalla mia esperienza devo dire che in porgetti dove la mole di dati è grande poter dividere il lavoro su più filegroup porta notevoli vantaggi sia in interrogazione sia in amministrazione.
Se da un lato il partizionamento porta notevoli benefici dall'altro ci obbliga ad aggiungere una logica di gestione che può non essere banale.
Tornando al caso pratico, in questo progetto dove la quantità di righe per mese è davvero alta (circa 40 Mln) ci siamo posti il problema della finstra temporale di dati da tenere in linea.
Diciamo che fin qui tutto bene, fino a quando non ci siamo detti "bene, abbiamo tutte le nostre partizioni popolate, dobbiamo aggiungere una nuova partizione ed eliminare l'ultima, come facciamo?". SQL 2005 non ha comandi nativi per potre gestire in modo molto granulare le partizioni.
La prima operazione che mi sono trovato a costruire è stata una TRUNCATE PARTITION che nativamente non esiste ammeno di non cancellare le righe con una DELETE ma diventerebbe sconveniente come performance.
Con questo script possiamo andare a cancellare velocemente tutti i dati contenuti con alcune limitazioni. Infatti non possiamo andare a fare una operazione di SWITCH tra partizioni che non risiedano nello stesso filegroup oppure che la destinazione non sia vuota. Per questo ho dovuto crearmi un piccolo script che mi generi una tabella temporanea con la stessa struttura sullo stesso file group. Dobbiamo porre attenzione anche agli indici, che se sono partizionati, devono essere ricreati anche nella tabella di appoggio.
Script Creazione Tabella Appoggio
DECLARE S_TABLE CURSOR FOR
SELECT COL.NAME,
CASE TP.NAME
WHEN 'varchar' THEN CAST(TP.NAME AS VARCHAR(20)) + '(' + CAST(COL.MAX_LENGTH AS VARCHAR(4)) + ')'
WHEN 'char' THEN CAST(TP.NAME AS VARCHAR(20)) + '(' + CAST(COL.MAX_LENGTH AS VARCHAR(4)) + ')'
WHEN 'nvarchar' THEN CAST(TP.NAME AS VARCHAR(20)) + '(' + CAST(COL.MAX_LENGTH AS VARCHAR(4)) + ')'
WHEN 'nchar' THEN CAST(TP.NAME AS VARCHAR(20)) + '(' + CAST(COL.MAX_LENGTH AS VARCHAR(4)) + ')'
WHEN 'decimal' THEN CAST(TP.NAME AS VARCHAR(20)) + '(' + CAST(COL.PRECISION AS VARCHAR(6)) + ',' + CAST(COL.SCALE AS VARCHAR(6)) + ')'
WHEN 'numeric' THEN CAST(TP.NAME AS VARCHAR(20)) + '(' + CAST(COL.PRECISION AS VARCHAR(6)) + ',' + CAST(COL.SCALE AS VARCHAR(6)) + ')'
ELSE TP.NAME
END AS DIM,
CASE WHEN COL.IS_NULLABLE = 1 THEN 'NULL' ELSE 'NOT NULL' END
FROM
SYS.COLUMNS AS COL
INNER JOIN sys.tables AS TB
ON COL.OBJECT_ID = TB.OBJECT_ID
INNER JOIN SYS.TYPES AS TP
ON COL.SYSTEM_TYPE_ID = TP.SYSTEM_TYPE_ID
WHERE TB.NAME = @SRC_TABLE_NAME
Dove SRC_TABLE_NAME è il nome della tabella che dobbiamo "clonare" e con un cursore costruisco lo script:
OPEN S_TABLE
FETCH NEXT FROM S_TABLE INTO @COL_NAME, @COL_DIM, @COL_NULL
SET @CREATE_SC = 'CREATE TABLE ' + @DST_TABLE_NAME + ' ( '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CREATE_SC = @CREATE_SC + @COL_NAME + ' ' + @COL_DIM + ' ' + @COL_NULL
FETCH NEXT FROM S_TABLE INTO @COL_NAME, @COL_DIM, @COL_NULL
IF @@FETCH_STATUS = 0
SET @CREATE_SC = @CREATE_SC + ','
END
CLOSE S_TABLE
DEALLOCATE S_TABLE
Al termine aggiungo le parti più importanti per la funzione e la colonna di PARTIZIONAMENTO ed eseguo
SET @CREATE_SC = @CREATE_SC + ') ON ' + @PARTITION_FUNCTION + '(' + @PARTITION_COLUMN + ')'
EXEC(@CREATE_SC)
Non ci sono in questo script controlli di possibili errori e il tutto si basa sulla conoscenza della colonna di partizionamento, la tabella e la funzione.
Dunque fino ad ora il nostro script ha generato una tabella temporanea con la struttura della tabella sorgente e l'ha creata nello stesso file group della PARTIZIONE che vogliamo svuotare, questo è il punto cruciale, infatti ora possiamo fare lo switch e poi cancellare il contenuto della nostra temporanea oppure sopostarlo su un database di storico.
SELECT
@FILE_GROUP = FG.NAME,
@FILE_PATH = FIL.PHYSICAL_NAME,
@FILE_SIZE = FIL.SIZE,
@PARTITION_NUMBER = DDS.DESTINATION_ID,
@PARTITION_FUNCTION = PS.NAME
FROM sys.destination_data_spaces AS dds
INNER JOIN sys.partition_schemes AS ps
ON DDS.PARTITION_SCHEME_ID = PS.DATA_SPACE_ID
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN SYS.DATABASE_FILES AS FIL
ON FG.DATA_SPACE_ID = FIL.DATA_SPACE_ID
WHERE DDS.DESTINATION_ID = (SELECT $PARTITION.FNPARTBYYYYYMM (@PARTITION_RANGE))
Cuore della TRUNCATE è questo statement che recupera tutto ciò che serve per costruire dinamicamente il file e dove posizionarlo. Importante notare come il parametro sia la funzione di partizionamento ed il suo range. In questa versione la funzione è fissa SELECT $PARTITION.FNPARTBYYYYYMM ma nulla ci vieta di generare dinamicamente lo script e rendere variabeile anche il nome della funzione.
A questo punto possiamo fare lo switch vero e proprio e poi pulire i vari data file, e procediamo con la creazione di un file nello setto file group:
SET @ALTER_DB_SC = N'ALTER DATABASE DbTest
ADD FILE
(
NAME = N''' + @FILE_NAME + ''',
FILENAME = N''' + @FILE_PATH + @FILE_NAME +''',
SIZE = ' + @FILE_SIZE_CH +'
) TO FILEGROUP ' + @FILE_GROUP
Con il file creato e la tabella sul file group procedo con lo switch
SET @ALTER_DB_SC = 'ALTER TABLE ' + @TABLE_NAME + ' SWITCH PARTITION ' + CAST(@PARTITION_NUMBER AS VARCHAR(3)) + ' TO ' + @DST_TABLE_NAME + ' PARTITION ' + CAST(@PARTITION_NUMBER AS VARCHAR(3))
Poi posso cancellare il contenuto della tabella temporanea e deallocare il file dov'erano presenti i dati.
Con questa tecnica messa all'interno di una store e chiamata in modo iterativo per tutte le partizioni in un file group (es. tutti i dati del mese di gennaio dell'anno 2008 -> 200801 come valore di partizionamento) possiamo svuotare completamente una partizione e togliere dalla nostra funzione di partizionamento il range svuotato. Per farlo possiamo procedere con l'altr della funzione
@ALTER_DB_SC = N'ALTER PARTITION FUNCTION [' + @PARTITION_FUNCTION + ']()
MERGE RANGE(' + @PARTITION_RANGE + ')'
Cosa molto importante da ricordare è che tutte le tabelle che hanno dati nella partizione siano vuote altrimenti l'oprazione di MERGE potrebbe impiegare molto tempo ma soprattutto molte risorse.
Con l'introduzione del partizionamento in 2005 abbiamo fatto un passo avanti nella gestione/efficienza del dato c'è ancora un po' di lavoro da fare ma la strada è tracciata e con un po' di T-SQL possiamo sfruttare a pieno questa funzionalità che in ambienti dove i volumi sono importanti possono fare la differenza.
Spero che possa essere utile a tutti almeno come spunto.
Mattia