Gestion dynamique des partitions SQL Server

Si vous travaillez sur un entrepôt à haute volumétrie, vous serez certainement confronté à des problèmes de performances pour alimenter et requêter les tables les plus volumineuses. Pour résoudre ces problèmes vous devrez avoir recours au partitionnement (fonctionnalité disponible depuis SQL Server 2005, je le précise car il y a quelques irréductibles qui utilise « encore » du 2000).

La mise en œuvre du partitionnement n’est pas la plus complexe qui soit ; seulement le faire une fois c’est bien, mais réinventer la roue sur chaque projet ça use. Voyons comment nous pouvons industrialiser pour tous nos projets BI la création et la gestion des partitions sous SQL Server.

Le partitionnement en quelques mots

Le partitionnement permet de répartir les données d’une ou plusieurs tables et indexes dans plusieurs groupes de fichiers. Pour ventiler les données dans les différentes partitions, SQL Server va s’appuyer sur une clé de partitionnement que vous devrez définir. En BI, la clé de partitionnement la plus communément utilisée est l’axe temps dans ces différentes granularités (année, mois, semaine …)

Les avantages

1. Les données étant stockées dans différents fichiers, les opérations de I/O vont pouvoir être parallélisées
2. Vous pouvez requêter la table en interrogeant directement une ou plusieurs partitions. Vous pouvez donc pilotez la restriction des données vous-même.
3. Côté SSIS ou TSQL, vous pouvez optimiser l’insertion de données et la gestion des indexes en utilisant si vous faites du « Partition Switching ». J’exposerai cette méthode dans un post ultérieur
4. Côté SSAS, si vous partitionner correctement vos groupes de mesures en indiquant la ou les partitions mises en jeux, vous optimiser les temps de process pour les partitions en MOLAP et temps de querying pour les partitions en ROLAP.

Les inconvénients

0, aucun, que dalle, keutchi, nada, bref allez-y foncer, mais qu’est-ce que vous attendez ? Il faut tout de même bien penser et concevoir son plan de partitionnement.

Mise en oeuvre

Vous devez connaître les quelques objets de « base » de SQL Server que vous allez manipuler, à savoir :

• les groupes de fichiers
• les fichiers de données
• les fonctions de partitionnement
• les schémas de partitionnement

Pour plus de détails techniques et de considérations sur les stratégies de partitionnement je vous renvoie à ces quelques liens :

• http://msdn.microsoft.com/fr-fr/library/ms190787.aspx
• http://msdn.microsoft.com/fr-fr/library/ms162136.aspx
• http://msdn.microsoft.com/en-us/library/cc966380.aspx

Voyons désormais comment mettre en place une solution permettant de créer et de maintenir le(s) plan(s) de partitionnement de tous nos projets BI…

La base test utilisée pour ce post est AdventureWorksDW2012.

Définition des règles de nommage

La convention de nommage utilisée dans les exemples de ce post est la suivante :

• pour les groupes de fichiers : <Nom de la base>_FG_<Granularité>_<Valeur du grain> (exemple pour un grain annuel: AdventureWorksDW2012_FG_YEAR_2010)
• les fichiers de données : <Nom de la base>_FN_<Granularité>_<Valeur du grain> (exemple pour un grain annuel: AdventureWorksDW2012_FN_YEAR_2010)
• les fonctions de partitionnement : PartitionFunctionBy<Granularité> (exemple pour un grain annuel: PartitionFunctionByYear)
• les schémas de partitionnement : PartitionSchemeBy<Granularité> (exemple pour un grain annuel: PartitionSchemeByYear)

Les règles de gestion suivies sont les suivantes :

• Le plan de partitionnement ne prend en compte que des granularités temporelles
• La dimension DimDate présente dans AdventureWorks ne me satisfaisant pas (elle ne permet pas de gérer les semaines ISO), j’utilise la dimension DimCalendar décrite dans un précédent post http://wp.me/p2yhHc-4
• Pour les bornes de partitionnement, nous utiliserons le grain jour pour les identifier; ce dernier doit être de type entier au format YYYYMMDD (exemple pour un grain annuel : 20100101,20110101,20120101…)
• Chaque partition est liée à un groupe de fichier lui-même lié à un fichier de données

Création de la table d’administration des partitions

Afin d’avoir un gestion dynamique, il faut avant tout créer une table nous permettant de lister et d’enregistrer le paramétrage de nos partitions à savoir :

• le nom du schéma de partitionnement
• le nom de la fonction de partitionnement
• le type de
• la borne de début
• le nom de la dimension temps utilisée
• le type de granularité temporelle utilisé
• le nom de la colonne relative au grain temporel
• la nom de la colonne de clé primaire de la dimension temps
• le dossier dans lequel doivent être stockés les fichiers de données
• la taille initiale d’un fichier de données
• la taille maximale d’un fichier de données
• l’autoextend d’un fichier de données
• un flag indiquant si le plan de partitionnement est actif ou non.

CREATE TABLE [adm].[PartitionManagement]
(
[PartitionManagementID] [tinyint] IDENTITY(1,1) NOT NULL,
[SchemePartitionName] [nvarchar](255) NOT NULL,
[FunctionPartitionName] [nvarchar](255) NOT NULL,
[FunctionRangeType] [tinyint] NOT NULL, --1: LEFT, 2: RIGHT
[FunctionRangeStartBound] [int] NOT NULL,
[CalendarTable] [nvarchar](255) NOT NULL,
[CalendarGranularityType] [nvarchar](20) NOT NULL,
[CalendarGranularityColumnName] [nvarchar](255) NOT NULL,
[CalendarLeafLevel] [nvarchar](255) NOT NULL,
[DbFilePath] [nvarchar](255) NOT NULL,
[DbFileSizeIni] [nvarchar](20) NOT NULL,
[DbFileSizeMax] [nvarchar](20) NOT NULL,
[DbFileGrowth] [nvarchar](20) NOT NULL,
[FlagEnable] [bit] NOT NULL,
CONSTRAINT [PK_PartitionManagement] PRIMARY KEY CLUSTERED (  [PartitionManagementID] ASC )
)
GO

On paramètre ensuite les plans de partitionnement. Nous allons dans notre exemple en décrire 3 : un annuel, un mensuel et un hebdomadaire.

Mise en place du package SSIS permettant d’administrer les partitions au fil de l’eau

La mise en œuvre du package SSIS est trivial, il suffit d’implémenter une simple boucle, nous permettant de récupérer séquentiellement le paramétrage de chaque plan de partitionnement est d’enchainer quelques scripts SQL :

• un script de création de groupe(s) de fichier(s)
• un script de création de création de fichier(s) de données
• un script de création/modification de la fonction et du schéma de partitionnement

Step 1 : Implémentation de la boucle de lecture de la table d’administration

Déclarons dans un premier temps les variables qui nous seront utiles pour lire notre table d’administration. Il nous faut donc une variable de type objet pour récupérer le contenu de la table et une variable par propriété:

Pour récupérer l’ensemble des plans de partitionnement actifs, il suffit d’utiliser un SQL Task et de stocker le ResultSet dans la variable @partitionManagementList :

Il faut ensuite parcourir le ResultSet, afin de pouvoir gérer les partitions une à une à l’aide d’un For Each Loop Container de type ADO.NET. Ce dernier permet de parcourir les enregistrements contenus dans la variable @partitionManagementList ligne à ligne, et de mapper le contenu de chaque colonne d’une occurrence dans une variable :

Step 2 : Passons désormais à l’implémentation des scripts SQL de gestion des partitions

La première étape consiste à créer les groupes de fichiers spécifiques pour le partitionnement. Le principe est simple, il suffit de lister les bornes temporelles en fonction du grain paramétré et de vérifier pour chaque occurrence si le filegroup existe ou non. Le script SQL qui nous permet d’effectuer cette opération est le suivant :

DECLARE @calendarTable NVARCHAR(255)
DECLARE @calendarGranularityType NVARCHAR(255)
DECLARE @calendarGranularityColumnName NVARCHAR(255)
DECLARE @calendarLeafLevel NVARCHAR(255)
DECLARE @functionRangeStartBound INT
DECLARE @sqlStmt NVARCHAR(MAX)
DECLARE @functionRangeEndBound INT = CAST(CONVERT(NVARCHAR(8),GETDATE()-1,112) AS INT)

SET @functionRangeStartBound = ?
SET @calendarTable = ?
SET @calendarGranularityType = ?
SET @calendarGranularityColumnName = ?
SET @calendarLeafLevel = ?

CREATE TABLE #tmp (granularityValue INT NOT NULL)
SET @sqlstmt =
'
INSERT INTO #tmp
SELECT Calendar.' + @calendarGranularityColumnName + '
FROM ' + @calendarTable + ' AS Calendar
WHERE Calendar.' + @calendarLeafLevel + ' BETWEEN ' + CAST(@functionRangeStartBound as nvarchar(8)) + ' AND ' + CAST(@functionRangeEndBound AS NVARCHAR(8)) + '
GROUP BY Calendar.' + @calendarGranularityColumnName
EXECUTE sp_executesql @sqlstmt

SET @sqlStmt =
(
SELECT DISTINCT sqlStmt as 'data()'
FROM
(
SELECT N'ALTER DATABASE ' + DB_NAME() + ' ADD FILEGROUP ' + REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) + N';' as sqlStmt
FROM
#tmp
LEFT OUTER JOIN
sys.filegroups
ON filegroups.name = REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8))
WHERE filegroups.name is null
) tmp
FOR XML PATH('')
)

EXECUTE sp_executesql @sqlStmt

DROP TABLE #tmp

Le mapping des paramètres du SQL Task de gestion des groupes de fichiers est le suivant :

Une fois les groupes de fichiers créés, il faut s’occuper des fichiers de données. Je vous rappelle que nous sommes parti sur le principe (un groupe de fichier = 1 fichier de données). Pour créer les fichiers, nous utilisons le même principe que pour les groupes de fichiers

DECLARE @calendarTable NVARCHAR(255)
DECLARE @calendarGranularityType NVARCHAR(255)
DECLARE @calendarGranularityColumnName NVARCHAR(255)
DECLARE @calendarLeafLevel NVARCHAR(255)
DECLARE @sqlStmt NVARCHAR(MAX);
DECLARE @dbFilePath NVARCHAR(255)
DECLARE @dbFileSizeIni NVARCHAR(20)
DECLARE @dbFileSizeMax NVARCHAR(20)
DECLARE @dbFileGrowth NVARCHAR(20)
DECLARE @functionRangeStartBound INT
DECLARE @functionRangeEndBound INT = CAST(CONVERT(NVARCHAR(8),GETDATE()-1,112) AS INT)

SET @functionRangeStartBound = ?
SET @calendarTable = ?
SET @calendarGranularityType = ?
SET @calendarGranularityColumnName = ?
SET @calendarLeafLevel = ?
SET @dbFilePath = ?
SET @dbFileSizeIni = ?
SET @dbFileSizeMax = ?
SET @dbFileGrowth = ?

CREATE TABLE #tmp (granularityValue INT not null)
SET @sqlstmt =
'
INSERT INTO #tmp
SELECT Calendar.' + @calendarGranularityColumnName + '
FROM ' + @calendarTable + ' AS Calendar
WHERE Calendar.' + @calendarLeafLevel + ' BETWEEN ' + CAST(@functionRangeStartBound as nvarchar(8)) + ' AND ' + CAST(@functionRangeEndBound AS NVARCHAR(8)) + '
GROUP BY Calendar.' + @calendarGranularityColumnName

EXECUTE sp_executesql @sqlstmt

SET @sqlStmt =
(
SELECT DISTINCT sqlStmt as 'data()'
FROM
(
SELECT
N'ALTER DATABASE ' + DB_NAME() + +
' ADD FILE (NAME=' + REPLACE(DB_NAME(),' ','_') + '_FN_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) +
' , FILENAME=''' + @dbFilePath + REPLACE(DB_NAME(),' ','_') + '_FN_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) + '.ndf''' +
' , SIZE=' + @dbFileSizeIni +', MAXSIZE=' + @dbFileSizeMax + ', FILEGROWTH=' + @dbFileGrowth + ')' +
' TO FILEGROUP ' + REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8)) + N';' as sqlStmt
FROM
#tmp
left outer join
(
SELECT master_files.name filename
FROM
master.sys.master_files
INNER JOIN
sys.databases
ON databases.database_id = master_files.database_id
WHERE databases.name = DB_NAME()
) files
ON files.filename = REPLACE(DB_NAME(),' ','_') + '_FN_' + UPPER(@CalendarGranularityType) + '_' + cast(#tmp.granularityValue as nvarchar(8))
WHERE files.filename is null
) tmp
FOR XML PATH('')
)

EXECUTE sp_executesql @sqlStmt

DROP TABLE #tmp

Le mapping des paramètres du SQL Task de gestion des fichiers est le suivant :

Il faut ensuite gérer la fonction et le schéma de partitionnement :

• Pour créer la fonction de partitionnement, il est nécessaire de récupérer l’ensemble de bornes de la partition en fonction du paramétrage spécifié dans la table d’administration. Il suffit donc de prendre le premier jour de chaque occurrence de grain temporel à utiliser. La requête en elle-même est basique,  mais elle doit être construite dynamiquement pour respecter le paramétrage de chaque plan de partitionnement. Exemple pour une granularité annuelle :

SELECT Calendar.YearCode,CAST(MIN(Calendar.CalendarID) AS NVARCHAR) AS CalendarID
FROM common.DimCalendar AS Calendar
WHERE  Calendar.CalendarID BETWEEN 2010 AND CONVERT( GROUP BY Calendar.YearCode

Le but du jeu étant de générer un seul ordre SQL pour créer la fonction de partitionnement. Il me faut donc « linéariser » l’ensemble de valeurs afin d’obtenir un liste de bornes séparées par des virgules. Exemple pour une granularité annuelle : 2010,2011,2012. Pour se faire, j’utilise la fonctionnalité SQL « FOR XML PATH ». Il ne reste plus qu’à encapsuler cette liste de valeurs dans une ordre CREATE PARTITION FUNCTION

On effectue le même type d’opération pour créer le schéma de partitionnement.

• Si la fonction de partitionnement existe, je n’ai qu’à la modifier en fonction de la borne correspondant au grain temporel associé à la date courante. Il faut toutefois s’assurer que la borne n’est pas déjà déclarée et modifier au préalable le schéma de partitionnement pour que les données de la nouvelle borne s’insère correctement dans le dernier filegroup

Le script SQL permettant de réaliser ces opérations est le suivant :

</pre>
DECLARE @functionPartitionName NVARCHAR(255)
DECLARE @functionRangeType INT
DECLARE @functionRangeStartBound INT
DECLARE @calendarTable NVARCHAR(255)
DECLARE @calendarGranularityType NVARCHAR(255)
DECLARE @calendarGranularityColumnName NVARCHAR(255)
DECLARE @calendarLeafLevel NVARCHAR(255)
DECLARE @schemePartitionName NVARCHAR(255)
DECLARE @schemeFileGroupLst nvarchar(max)
DECLARE @paramDefinition NVARCHAR(MAX)
DECLARE @sqlStmt NVARCHAR(MAX);
DECLARE @lstPartitionID AS NVARCHAR(MAX)
DECLARE @functionRangeEndBound INT = CAST(CONVERT(NVARCHAR(8),GETDATE()-1,112) AS INT)
DECLARE @currentPartitionID as INT
DECLARE @currentSchemeID as NVARCHAR(max)
DECLARE @isBoundExists BIT

SET @functionPartitionName = ?
SET @functionRangeType = ?
SET @functionRangeStartBound = ?
SET @calendarTable = ?
SET @calendarGranularityType = ?
SET @calendarGranularityColumnName = ?
SET @calendarLeafLevel = ?
SET @schemePartitionName = ?

--Teste si la fonction de partition existe
IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = @functionPartitionName)
BEGIN
-- Préparation de la récupération des bornes de partitionnement
SET @sqlStmt =
'SELECT
@paramLstPartitionID =
CAST(
STUFF(
(
SELECT DISTINCT '',''+' + @calendarLeafLevel + ' AS ''data()''
FROM
(
SELECT Calendar.' + @calendarGranularityColumnName + ',CAST(MIN(Calendar.' + @calendarLeafLevel +') AS NVARCHAR) AS ' + @calendarLeafLevel + '
FROM ' + @calendarTable + ' AS Calendar
WHERE Calendar.' + @calendarLeafLevel + ' BETWEEN ' + CAST(@functionRangeStartBound as nvarchar(8)) + ' AND ' + CAST(@functionRangeEndBound AS NVARCHAR(8)) + '
GROUP BY Calendar.' + @calendarGranularityColumnName + '
) tmp
ORDER BY 1
FOR XML PATH('''')
)
,1,1,''''
) AS NVARCHAR(MAX)
)
'
-- récupération des bornes de partitionnement
SET @paramDefinition = N'@paramLstPartitionID nvarchar(max) OUTPUT'
EXECUTE sp_executesql @sqlStmt,@paramDefinition,@paramLstPartitionID=@lstPartitionID OUTPUT
-- création de la fonction de partitionnement
SET @sqlStmt = 'create partition function ' + @functionPartitionName + ' (int) as range ' + CASE WHEN @functionRangeType=1 THEN 'LEFT' ELSE 'RIGHT' END + ' for values (' + @lstPartitionID +')'

EXECUTE sp_executesql @sqlStmt

-- récupère la liste des groupe de fichier à associer au schéma de partitionnement
SELECT @schemeFileGroupLst =
CAST(
STUFF(
(
SELECT DISTINCT ',' + filegroups.name AS 'data()'
FROM sys.filegroups
WHERE filegroups.name like REPLACE(DB_NAME(),' ','_') + '_FG_' + UPPER(@CalendarGranularityType) + '_%'
ORDER BY 1 FOR XML PATH('')
),
1,
1,
''
) AS NVARCHAR(MAX)
)

-- création de schema de partitionnement
SET @sqlStmt = N' CREATE PARTITION SCHEME ' + @schemePartitionName + N' AS PARTITION ' + @functionPartitionName + ' TO ' +N'(' + CASE WHEN @functionRangeType=2 THEN N'[PRIMARY],' ELSE '' END + @schemeFileGroupLst + CASE WHEN @functionRangeType=1 THEN N'[PRIMARY]' ELSE '' END + N')'
EXECUTE sp_executesql @sqlStmt
END
ELSE
BEGIN
-- Préparation de la requête permettant de récupérer la borne courante
SET @sqlStmt =
'
SELECT @paramPartitionID=min(Calendar.' + @calendarLeafLevel + '),@paramSchemeID=REPLACE(DB_NAME(),'' '',''_'') + ''_FG_' + UPPER(@CalendarGranularityType) + '_'' + cast(min(Calendar.' + @calendarGranularityColumnName + ') as nvarchar(10))
FROM ' + @calendarTable + ' AS Calendar
WHERE Calendar.' + @calendarGranularityColumnName + ' =
CASE ''' + @calendarGranularityType + '''
WHEN ''year'' THEN LEFT(@functionRangeEndBound,4)
WHEN ''month'' THEN LEFT(@functionRangeEndBound,6)
WHEN ''week'' THEN common.ufn_ISOWeek(cast(@functionRangeEndBound as nvarchar(8)))
ELSE NULL
END
'
-- récupération de la borne en cours
SET @paramDefinition = N'@functionRangeEndBound INT,@paramPartitionID INT OUTPUT,@paramSchemeID NVARCHAR(MAX) OUTPUT'
EXECUTE sp_executesql @sqlStmt,@paramDefinition,@functionRangeEndBound,@paramPartitionID=@currentPartitionID OUTPUT,@paramSchemeID=@currentSchemeID OUTPUT
-- Vérification de l'existance de la partition pour la borne concernée
SELECT @isBoundExists = cast(COUNT(1) as bit)
FROM
sys.partition_range_values
inner join sys.partition_functions on partition_functions.function_id = partition_range_values.function_id
WHERE partition_functions.name = @functionPartitionName
and partition_range_values.value = @currentPartitionID
-- si la partition n'existe pas on la crée

IF @isBoundExists=0
BEGIN
SET @sqlStmt = 'alter partition scheme ' + @schemePartitionName + ' next used ' + @currentSchemeID
EXECUTE sp_executesql @sqlStmt;
SET @sqlStmt = N'alter partition function ' + @functionPartitionName + '() split range (' + cast(@currentPartitionID as nvarchar(8)) + N');'
EXECUTE sp_executesql @sqlStmt;
END

END

La création du SQL Task est très simple, il suffit de s’assurer que le bon passage des paramètres :

L’implémentation du package est désormais terminée, et nous avons au final un lot SSIS très simple (comme d’habitude :)) :

Il ne reste plus qu’à partitionner les tables voulues et à planifier l’exécution de votre package.

Retrouvez les sources de cet article sur SkyDrive http://sdrv.ms/Uhhbvl

• Architecture, Modélisation
• Partition
• SQL Server
• SSIS