Implémenter une architecture de référence “Fast Track SQL Server” pour les entrepôts de données…

Le document

• Implementing a SQL Server Fast Track Data Warehouse

constitue la référence en terme de définition et de planification d'une architecture de référence “Fast Track SQL Server” pour les entrepôts de données. Nous allons tenter ici d’en faire une synthèse en français.

 

1. Philosophie

Les principes généraux utilisés dans cette démarche sont :

• Recherche systématique de la performance via l’utilisation d’un schéma d’accès aux données de type "E/S séquentielles” (meilleur que le schéma plus classique d’accès aléatoire aux données, plus lent).
• Approche holistique (c’est à dire prenant en compte tous les aspects de performances aux niveaux matériel, système et SGBD) afin de préserver le schéma d’accès sous forme d’E/S séquentielles
• Maintien de la séquentialité des données pour maintenir les performances.

2. Principes généraux d’architecture

Les caractéristiques de la charge cible sont les suivantes :

• Tables de faits très grandes ( > 500 Go), trop grand en tout cas pour être entièrement stockées  en mémoire ou dans les caches disques.
• Majorité de requêtes en lecture; la mise à jour des tables de faits est comparativement moins fréquentes et souvent exécutée sous forme de traitements par lots.
• Un niveau de concurrence multi-utilisateurs plus faible que sur un système transactionnel.
• Requêtes complexes nécessitant des jointures et des aggregations de larges volumes de données.

Les principes d’architectures retenus sont :

• Architecture matérielle de type SMP
• Utilisation de configurations équilibrées (entre processeurs et E/S)
• Maximisation des E/S séquentielles et minimisation des E/S aléatoires afin de pouvoir alimenter les processeurs à leur capacité maximale sans utiliser le cache
• Eviter les schémas et tables et les plans d’exécution qui produisent des E/S aléatoires
• Contrôler et minimer la fragmentation des fichiers, des tables et des index pour préserver les E/S séquentielles

Le but premier de la démarche est de définir une configuration équilibrée dans tous ces composants que l’on peut voir représenté sur ce schéma :

3. Blocs de base

Deux blocs de bases sont proposés :

• Quadri-coeurs HP ou Dell + EMC CX4-240 (configuré avec 4 ports,  4 LUNs et 17 disques 300 Go @ 15 000 rpm en RAID1)
• Quadri-coeurs HP + MS 2000 (configuré avec 4 ports,  4 LUNs et 11 disques 300 Go @ 15 000 rpm en RAID1)

La méthodologie pour définir d’autres blocs de base est complètement détaillée.

Concernant la mémoire, les recommandations se basent sur un point de départ à 4Go par coeur. Plus peut être nécessaire en fonction de la charge utilisateurs notamment. Dans le cas d’une charge utilisateurs importante, un monitoring mémoire devra être mis en place.

 

Attention : les recommandations qui suivent sont prévues pour être appliquées toutes ensembles et ne sont efficaces que dans le cadre d’un entrepôt de données avec des accès en lecture séquentiel majoritaires. Les appliquer partiellement ou dans d’autres cas de figure peut avoir des effets contraires sur les performances.

4. Configuration SQL Server

• mode de récupération simple
• ajouter –E aux paramètres de démarrage de l’instance SQL Server, ce qui permet au moteur d’utiliser des extents de 2Mo (cf. détails ici : https://technet.microsoft.com/fr-fr/library/ms190737.aspx  )

5. Configuration Base de données

Tous les fichiers de données sont positionnées sur des LUNs configurées en RAID-1. A un coeur au niveau processeur correspond une LUN et 2 fichiers de données.

Afin de prévenir la fragmentation des espaces de stockage primaires, on créera des groupes de fichiers de type '”staging” pour les opérations de chargement.

Pour la même raison, on dimensionnera les fichiers de données à la taille complète requise. On évitera absolument le mode de croissement automatique (“AUTOGROW”).

Le mode de croissance automatique ne sera actif que pour Tempdb et les fichiers de staging en mode incrément de 4 Mo.

Les fichiers de journaux seront stockés sur une LUN séparée.

Autres recomandations :

Les grosses tables de faits seront partionnées sur le champs date.

Sur ces grosses tables de faits, on créera un index cluster sur le champs date.

6. Configuration des tables de la base de données

On utilisera les critères suivants pour choisir la configuration des tables :

1°) Si les données contiennent un un attribut date utilisé majoritairement dans les requêtes, on utilisera une table triée physiquement sur ce champ (clustered table by date column)

2°) Si la majorité des requêtes au contraire utilise une autre colonne, on utilisera une table triée physiquement sur ce champ (clustered table on this column)

3°) Si aucun des 2 cas de figures ci-dessus ne s’applique, et que la majorité des requêtes balayent l’ensemble des données, on utilisera une table standard (ie non triée – heap table)

 

Par ailleurs la fragmentation à tous les niveaux (Système de fichiers, base de données et index) sera évitée en appliquant les stratégies clasiques qui sont rappelées.

 

7. Procédures de chargement des données

Conserver les données en ordre séquentiel et prévenir la fragmentation lors du chargement des données est très important pour le maintien des performances.

La méthode générale utilisée est la suivante :

1. Créer un table non-triée (heap) dans la zone de staging

2. Charger cette table avec les nouvelles données

3. Utiliser l’ordre INSERT SELECT pour déplacer les données vers leur table définitive

2 approches peuvent être retenues :

soit celle d’un groupe de fichiers de staging

soit celle d’une base de données de staging

les scripts correspondants sont fournis en attachement du livre blanc.

A) Considérations sur le chargement en parallèle

il est déconseillé sur des chargements inférieurs à 500 Go.

En effet, l’amélioration de 2 à 4 du temps de chargement est à contre-balancé par une pénalité de 20 à 30% de performances sur les requêtes suivantes.

B) Chargement d’une table simple

Si la table finale est de type “heap”, il est possible d’utiliser directement des chargements de type “BULK INSERT”.

C) Chargement d’une table triée pysiquement (cluster index)

Trois méthodes, à base d’ordres BULK INSERT, sont proposées de la plus simple à la plus complexe :

1. Chargement simple et sérialisé
2. Chargement optimisé et sérialisé
3. Chargement optimisé et parallélisé

 

Au final, avec l’application de toutes ces recommandations, on obtiendra un entrepôt de données capable de fournir des performances optimales et prédictibles.