Guide de référence pour entrepôt de données Fast Track
publicité
Guide de référence pour entrepôt de données Fast Track pour SQL Server 2012
Article technique SQL Server
Auteurs : Eric Kraemer, Mike Bassett, Eric Lemoine, Dave Withers
Relecteurs techniques : Claude Lorenson, Susan Price, Ralph Kemperdick, Henk van der
Valk, Alexi Khalyako, Oliver Chiu
Date de publication : mars 2012
S'applique à : SQL Server 2012
Résumé : Ce document définit un modèle de configuration de référence (appelé entrepôt de
données Fast Track) à l'aide d'une approche d'équilibrage des ressources pour implémenter
une architecture système de bases de données SQL Server de type multiprocesseur symétrique
aux performances et à l'évolutivité éprouvées pour les charges de travail d'entrepôt de données.
L'objectif d'une architecture de référence d'entrepôt de données Fast Track consiste à obtenir
un équilibre efficace des ressources entre la fonctionnalité de traitement de données SQL Server
et le débit du composant matériel obtenu.
Copyright
Ce document est fourni « en l'état ». Les informations et les opinions exprimées dans ce document,
y compris les adresses URL et les autres références à des sites Internet, peuvent faire l'objet de
modifications sans préavis. Vous assumez tous les risques liés à son utilisation.
Ce document ne vous concède aucun droit de propriété intellectuelle portant sur les produits Microsoft.
Vous pouvez copier et utiliser ce document à titre de référence pour un usage interne.
© 2012 Microsoft. Tous droits réservés.
2
Sommaire
Historique des modifications du Guide de référence pour entrepôt de données Fast Track ...................... 6
Introduction .................................................................................................................................................. 6
Public visé.................................................................................................................................................. 6
Entrepôt de données Fast Track ................................................................................................................... 6
Fast-Track .................................................................................................................................................. 7
Proposition de valeur ................................................................................................................................ 7
Méthodologie................................................................................................................................................ 7
Architecture holistique des composants .................................................................................................. 7
Méthode optimisée par charge de travail ................................................................................................ 8
Configurations de référence SQL Server Fast Track validées.................................................................... 9
Résumé ..................................................................................................................................................... 9
Charge de travail d'entrepôt de données Fast Track .................................................................................... 9
Modèles de charge de travail d'entrepôt de données.............................................................................. 9
Évaluation de la charge de travail ........................................................................................................... 10
Attributs qualitatifs de charge de travail d'entrepôt de données .......................................................... 12
Choix d'une configuration de référence d'entrepôt de données Fast Track................................................. 13
Option 1 : évaluation de base ................................................................................................................. 14
Étape 1 : évaluer le cas d'usage du client ............................................................................................... 14
Étape 2 : choisir une architecture de référence d'entrepôt de données Fast Track publiée ............. 15
Option 2 : évaluation complète .............................................................................................................. 15
Présentation du processus .................................................................................................................. 16
Étape 1 : évaluer le cas d'usage du client ........................................................................................... 16
Étape 2 : établir les mesures d'évaluation .......................................................................................... 17
Étape 3 : choisir une architecture de référence d'entrepôt de données Fast Track .......................... 18
Option 3 : architectures de référence définies par l'utilisateur ............................................................. 18
Étape 1 : définir la charge de travail ................................................................................................... 18
Étape 2 : établir des tests d'évaluation de l'architecture des composants ........................................ 18
Choisir un résumé de l'architecture de référence Fast Track ................................................................. 19
3
Configuration standard d'entrepôt de données Fast Track ........................................................................ 20
Architecture des composants matériels ................................................................................................. 20
Impératifs relatifs aux composants et configuration .......................................................................... 21
Configuration de l'application................................................................................................................. 23
Windows Server 2008 R2 .................................................................................................................... 23
SQL Server 2012 Enterprise ................................................................................................................ 23
Système de stockage........................................................................................................................... 25
Meilleures pratiques SQL Server pour l'entrepôt de données Fast Track .................................................. 30
Architecture des données ....................................................................................................................... 30
Structure de table ............................................................................................................................... 30
Partitionnement de table.................................................................................................................... 32
Indexation ........................................................................................................................................... 33
Index columnstore optimisés en mémoire xVelocity ......................................................................... 33
Statistiques de base de données ........................................................................................................ 35
Compression ....................................................................................................................................... 36
Gestion de la fragmentation des données.............................................................................................. 37
Fragmentation du système de fichiers ............................................................................................... 37
Plusieurs groupes de fichiers .............................................................................................................. 39
Chargement des données ....................................................................................................................... 39
Chargements incrémentiels ................................................................................................................ 40
Migration des données ....................................................................................................................... 42
Tests d'évaluation et validation .................................................................................................................. 45
Effectuer la validation d'entrepôt de données Fast Track de référence ................................................ 46
Tests d'évaluation de référence avec SQLIO....................................................................................... 47
Effectuer un test d'évaluation de base de données Fast Track .............................................................. 50
Calcul du MCR ..................................................................................................................................... 51
Calcul du BCR ...................................................................................................................................... 52
Architectures de référence d'entrepôt de données Fast Track publiées ................................................... 55
Conclusion ................................................................................................................................................... 55
4
Annexe ........................................................................................................................................................ 57
FTDW System Sizing Tool ........................................................................................................................ 57
Validation d'une architecture de référence Fast Track définie par l'utilisateur ..................................... 57
Test d'E/S synthétiques ....................................................................................................................... 57
Génération des fichiers de test avec SQLIO ........................................................................................ 57
Test de la charge de travail ..................................................................................................................... 60
Mesure du MCR pour votre serveur (facultatif) ................................................................................. 60
Mesure du BCR pour votre charge de travail...................................................................................... 60
Facteurs affectant le taux de consommation de requête .................................................................. 65
5
Historique des modifications du Guide de référence pour entrepôt de
données Fast Track
Le tableau suivant dresse la liste des modifications ou mises à jour notables des versions
finales du Guide de référence pour entrepôt de données Fast Track.
Description
Version
Remarque
Nouveautés de
Liens vers d'autres documents de
4.0
SQL Server 2012
meilleures pratiques SQL Server
Nouveautés de
4.0
Tests d'évaluation et validation
SQL Server 2012
Nouveautés de
4.0
Mémoire requise
SQL Server 2012
Nouveautés de
Index columnstore optimisés en
4.0
SQL Server 2012
mémoire xVelocity
Nouveautés de
4.0
Stockage à l'état solide
SQL Server 2012
Nouveautés de
4.0
Validation et index columnstore
SQL Server 2012
Nouveautés de
4.0
Validation d'E/S de base
SQL Server 2012
Tableau 1 : Historique des modifications
Emplacement
Important
Attention
RAM
Index columnstore
État solide
Validation
SQLIO
Introduction
Ce document définit l'architecture des composants et la méthodologie du programme Entrepôt
de données Fast Track SQL Server. Cette approche a pour résultat la validation d'une architecture
système minimale de base de données Microsoft SQL Server, y compris des logiciels et du
matériel, nécessaire pour obtenir et gérer un niveau de base des performances prêtes à l'emploi
de plusieurs charges de travail de stockage des données.
Public visé
Le public visé par ce document comprend les planificateurs, les architectes, les administrateurs
de base de données et les utilisateurs de Business Intelligence (BI) intéressés par la sélection
d'architectures système standard éprouvées pour les charges de travail SQL Server conformes
à l'entrepôt de données Fast Track.
Entrepôt de données Fast Track
L'initiative Entrepôt de données Fast Track SQL Server fournit une méthodologie de base et
des exemples concrets en vue du déploiement d'une configuration matérielle et de base de
données équilibrée pour une charge de travail de stockage des données. Pour plus
d'informations, consultez la section Charge de travail d'entrepôt de données Fast Track de ce
document.
6
L'équilibre est une mesure des composants clés du système dans une installation de SQL
Server ; stockage, serveur, réseau de stockage, base de données et système d'exploitation.
Chacun de ces composants est réglé de façon à obtenir la configuration optimale. L'objectif
consiste à obtenir un équilibre prédéfini efficace entre les ressources de la fonctionnalité
de traitement des données SQL Server et celles des composants matériels. Idéalement,
la configuration inclut le matériel système minimal pour répondre aux exigences en matière
de performances et de stockage d'une charge de travail de stockage des données.
Fast-Track
La marque SQL Server Fast Track identifie une configuration matérielle du composant
conforme aux principes de l'architecture de référence d'entrepôt de données Fast Track.
Chaque architecture de référence Fast Track est définie par une charge de travail et un
ensemble de meilleures pratiques pour la configuration, la validation et la base de données.
Vous trouverez ci-dessous les principes clés du programme Fast Track :
Tests d'évaluation spécifiques à la charge de travail. La conception et la configuration du
système sont basées sur des charges de travail de requêtes simultanées réelles.
Spécifications détaillées et validées des composants matériels.
Équilibre de l'architecture des composants entre la fonction de base de données et les
principales ressources matérielles.
Proposition de valeur
Les principes suivants créent la base de la proposition de valeur d'entrepôt de données Fast Track :
Équilibre prédéterminé entre les principaux composants du système. Cela réduit le
risque de dépassement de budget pour les ressources processeur ou de stockage qui
ne seront jamais utilisées au niveau de l'application.
Performances prêtes à l'emploi prédictibles. Les configurations Fast Track sont
créées à une capacité qui correspond déjà aux fonctions de l'application SQL Server
pour un serveur et une charge de travail sélectionnés.
Centrée sur la charge de travail. Au lieu d'être une approche universelle de
configuration de base de données, l'approche d'entrepôt de données Fast Track s'aligne
sur un cas d'usage d'entrepôt de données.
Méthodologie
Architecture holistique des composants
Les architectures de référence d'entrepôt de données Fast Track SQL Server fournissent une
infrastructure pratique pour équilibrer les relations complexes entre les composants clés de
l'architecture du système de base de données. Désignée sous le nom générique de pile,
l'architecture des composants est présentée dans l'illustration 1.
7
Illustration 1 : Exemple d'architecture de composants de base de données Fast Track
Chaque composant de la pile est un lien dans une chaîne des opérations nécessaires pour
traiter les données dans SQL Server. L'évaluation de la pile en tant que système intégré permet
l'exécution de tests qui établissent la bande passante réelle pour chaque composant. Cela
garantit que les composants fournissent un débit suffisant pour correspondre aux fonctionnalités
de l'application SQL Server de la pile indiquée.
Méthode optimisée par charge de travail
Les différentes charges de travail d'application de base de données peuvent exiger des architectures
de composants très différentes pour obtenir un équilibre optimal des ressources. Un exemple
classique est le contraste entre les charges de travail OLTP (traitement transactionnel en ligne)
basées sur la recherche de requêtes de petite taille et le stockage de données analytiques de
requêtes de grande taille gourmandes en ressources d'analyse. Les cas d'usage d'OLTP sont
fortement indexés, de façon à prendre en charge la récupération à latence faible d'un petit
nombre de lignes de jeux de données qui ont souvent un faible volume de données historiques.
Ces types d'opérations de base de données induisent le déplacement de tête de disque important
et génèrent des modèles d'analyse d'E/S aléatoire classiques. Les cas d'usage analytique, tels
que le stockage des données, peuvent impliquer des demandes de données beaucoup plus
importantes et tirer parti du potentiel de débit total accru des analyses de disque séquentielles.
Pour ces cas de figure contrastants, l'impact de la pile d'un composant équilibrée est significatif.
Les taux moyens d'analyse d'E/S aléatoires par disque pour les lecteurs de disques SAS
récents peuvent être 10 fois plus lents par rapport aux taux d'analyse séquentielle pour le
même matériel. Avec les charges de travail d'entrepôt de données Fast Track, l'accent est mis
sur l'obtention de taux d'analyse d'E/S toujours élevés (mesurés en Mo/s) plutôt que sur les
opérations par seconde (mesurées en IOPS).
8
Les problèmes liés à des charges de travail très différentes sont résolus en définissant clairement
les attributs des charges de travail clientes. Les charges de travail SQL Server Fast Track comportent
une liste qualitative d'attributs qui définissent de façon unique un cas d'usage courant d'application
de base de données. De plus, chaque charge de travail est représentée par des mesures
quantitatives, notamment les requêtes de test d'évaluation standard. Les tests d'évaluation
spécifiques à la charge de travail permettent de valider la configuration de la base de données,
les meilleures pratiques et les recommandations relatives aux composants matériels.
Configurations de référence SQL Server Fast Track validées
Toutes les architectures de référence Fast Track publiées sont validées comme étant
conformes à l'ensemble des règles et des instructions fournies dans le guide de référence.
Vous trouverez des exemples de ce processus plus loin dans ce document.
Résumé
La spécification d'entrepôt de données Fast Track SQL Server décrite dans ce guide de
référence est centrée sur la charge de travail et équilibrée par composant. Cette approche
confirme qu'une configuration universelle peut être inefficace et coûteuse pour plusieurs cas
d'usage de base de données. Les besoins de l'entreprise de plus en plus complexes couplés
à des volumes de données qui évoluent rapidement nécessitent une approche plus réaliste.
En présentant une combinaison des architectures normatives de référence, des tests d'évaluation
des composants matériels et logiciels, ainsi que des charges de travail clairement ciblées,
ce document fournit une méthode pratique pour obtenir des architectures de composants équilibrées.
Charge de travail d'entrepôt de données Fast Track
Modèles de charge de travail d'entrepôt de données
En général, les questions posées aux entrepôts de données requièrent l'accès à de grands volumes
de données. Les entrepôts de données doivent prendre en charge un large éventail de requêtes
d'un large public (par exemple : équipes des secteurs finance, marketing, opérations et recherche).
Pour surmonter les limites des systèmes d'entrepôts de données classiques, les sociétés ont
recours à des techniques d'optimisation traditionnelles de SGBDR, telles que génération des
index, préagrégation des données et restriction de l'accès aux niveaux inférieurs de données.
Les charges de maintenance associées à ces approches surchargent souvent les fenêtres de
commandes les plus conséquentes. À mesure qu'un entrepôt de données gagne en maturité
et que le public augmente, la prise en charge des optimisations spécifiques aux cas d'usage
s'avère de plus en plus difficile, en particulier dans le cas de données ou de corrections des
données qui arrivent en retard.
9
Une solution commune à ce problème consiste à ajouter des lecteurs ; il n'est pas rare de visualiser
des centaines de disques qui prennent en charge un entrepôt de données relativement petit afin
d'essayer de surmonter les limitations de performances d'E/S pour mapper une infrastructure
d'E/S basée sur la recherche à une charge de travail basée sur l'analyse. Cela est souvent
observé dans les environnements SAN qui sont traditionnellement optimisés pour la recherche.
De nombreuses E/S de stockage référencent des modèles et des techniques qui favorisent
l'accès d'E/S aléatoire, en introduisant la latence de disque et en réduisant le débit global du
sous-système de stockage pour une charge de travail d'entrepôt de données qui utilise
beaucoup de ressources d'analyse.
L'entrepôt de données Fast Track constitue une manière différente d'optimiser les charges de
travail d'entrepôt de données. En alignant les fichiers de base de données et la configuration
avec l'accès à l'analyse de disque efficace (plutôt qu'à la recherche), les performances obtenues
à partir des disques peuvent être bien supérieures. Le gain de performance obtenu par disque
permet de réduire le nombre de disques nécessaires pour générer le débit suffisant d'E/S de
façon à satisfaire la capacité de traitement des données SQL Server pour une charge de travail
donnée. En outre, vous évitez certaines techniques d'optimisation basées sur les index,
utilisées pour améliorer la recherche disque.
Évaluation de la charge de travail
Lors de l'analyse des charges de travail pour les systèmes d'entrepôt de données Fast Track,
il est important de déterminer l'adéquation avec les pratiques et la configuration du système
présentées dans ce document. La configuration des entrepôts de données peut varier selon le
client et certaines conditions. Par exemple, la réplication de base de données peut ne pas être
appropriée pour tous les systèmes d'entrepôt de données Fast Track. Les critères clés initiaux
de ce type d'évaluation de charge de travail sont présentés ici.
Analyse gourmande en ressources
Les requêtes d'une charge de travail d'entrepôt de données analysent généralement un grand
nombre de lignes. C'est pourquoi, les performances d'analyse de disque deviennent prioritaires
contrairement aux charges de travail transactionnelles qui agissent sur le temps de recherche
disque. L'architecture de référence d'entrepôt de données Fast Track optimise les composants
matériels et logiciels de base de données avec des performances d'analyse de disque prioritaires.
Il en résulte des lectures séquentielles de disque plus efficaces et une augmentation corrélée
dans le débit d'E/S disque par lecteur.
Absence de volatilité
Une fois les données écrites, elles sont rarement modifiées. Les opérations DML, telles que la
mise à jour de SQL server, qui déplacent des pages associées à la même table de base de
données hors de l'alignement contigu doivent être gérées attentivement. Les charges de travail
qui présentent en général une telle volatilité ne peuvent pas être alignées dans l'entrepôt de
données Fast Track. En cas de volatilité, nous recommandons des opérations de maintenance
périodique pour réduire la fragmentation.
10
Allégée en index
Le fait d'ajouter des index non cluster accentue généralement les performances des recherches
d'un ou plusieurs enregistrements. Si des index non cluster sont appliqués aux tables dans
lesquelles un grand nombre de lignes doivent être récupérées, l'augmentation consécutive des
opérations de recherche disque aléatoires peut altérer les performances globales du système.
La gestion des index peut également ajouter une surcharge significative de gestion des données,
ce qui peut créer un risque pour le contrat de niveau de service (SLA) et la capacité de satisfaire
les fenêtres de charge de base de données.
En revanche, les taux d'analyse séquentielle peuvent être bien supérieurs (au moins 10 fois
plus) aux taux d'accès aléatoire. Un système qui réduit l'utilisation de la recherche aléatoire,
ce qui entraîne des index secondaires, obtient généralement des taux d'E/S soutenus supérieurs
à la moyenne. Cela signifie une utilisation plus efficace des ressources d'E/S de stockage et
des performances plus prédictibles de requêtes de type d'analyse volumineuses.
La méthodologie d'entrepôt de données Fast Track prescrit des techniques d'optimisation de
base de données qui s'alignent sur les caractéristiques de la charge de travail ciblée. L'index
cluster et les partitions par spécification de plages de valeurs sont des exemples de structures
de données qui prennent en charge les E/S disque efficaces basées sur l'analyse. Nous les
recommandons comme principaux outils pour l'optimisation basée sur l'architecture des
données des environnements d'entrepôt de données Fast Track.
Alignée sur la partition
Une caractéristique commune des charges de travail d'entrepôt de données Fast Track est la
possibilité de tirer parti du partitionnement SQL Server. Le partitionnement permet de simplifier
la gestion du cycle de vie des données et de réduire la fragmentation dans le temps. En outre,
les modèles de requêtes d'analyses volumineuses tirent parti de la qualification de partition par
spécification de plages de valeurs et réduisent considérablement la taille des analyses de table
sans compromettre la fragmentation ou le débit d'E/S disque.
Considérations supplémentaires
Les considérations supplémentaires suivantes doivent être prises en compte lors de l'évaluation
d'une charge de travail de base de données :
11
L'implémentation et la gestion d'une stratégie d'optimisation de base de données allégée
en index est une condition fondamentale pour les charges de travail de d'entrepôt de
données Fast Track.
Il est supposé que la fragmentation minimale des données est conservée dans l'entrepôt
de données. Cela signifie que :
o Le type de fragmentation majeur peut être exprimé en termes de taille de
fragment. Un fragment représente des allocations contiguës de pages de base
de données de 8 Ko.
o Développer le serveur en ajoutant du stockage nécessite que toutes les tables
sensibles aux performances soient remplies de manière conforme aux
instructions fournies dans ce document.
o
Implémenter des structures de données volatiles, telles que des tables à activité
régulière de mise à jour au niveau des lignes, peut nécessiter des opérations de
maintenance fréquentes (telles que la défragmentation ou la reconstruction des
index) pour réduire la fragmentation.
o Le chargement de tables d'index cluster avec des lots d'ID de clé de cluster qui
chevauchent les plages existantes est une source fréquente de fragmentation.
Cela doit être soigneusement contrôlé et géré, conformément aux meilleures
pratiques contenues dans le guide de référence.
Le stockage des données englobe beaucoup de choses selon le public ciblé. Il convient
d'être attentif lors de l'évaluation des exigences du client par rapport aux attributs de
charge de travail d'entrepôt de données Fast Track.
Attributs qualitatifs de charge de travail d'entrepôt de données
Définissez la charge de travail d'entrepôt de données Fast Track via les propriétés des zones
de sujet suivantes liées aux opérations de base de données :
Besoins des utilisateurs et modèle d'accès
Modèle de données
Architecture des données
Optimisation de la base de données
Le tableau suivant récapitule les attributs de charge de travail d'entrepôt de données ;
une opposition est fournie via la comparaison avec une charge de travail OLTP ou d'une
banque de données opérationnelles (ODS).
Attribut
Description de
cas d'utilisation
12
Affinité de charge de travail :
Entrepôt de données
Principalement en lecture (90 %-10 %)
Mises à jour généralement limitées
aux exigences de qualité des données
Insertions en bloc haut volume
Simultanéité globale des requêtes
moyenne à basse ; nombre maximal
de requêtes simultanées compris
entre 10 et 30
Débit de requêtes simultanées
caractérisé par les besoins
d'analyse et de rapports
Analyses de plages et/ou
agrégations volumineuses
Requêtes complexes (filtre, jointure,
regroupement, agrégation)
OLTP/ODS
Taux de mise à jour/lecture
équilibré (60 %-40 %)
Débit de requêtes simultanées
caractérisé par les besoins
opérationnels
Insertions et mises à jour
à granularité fine
Débit de transactions élevé
(par exemple, 10 K/s)
Simultanéité globale des
utilisateurs moyenne à élevée ;
nombre maximal de requêtes
simultanées compris entre
50 et 100 ou plus
Transactions généralement très
courtes (par exemple, recherches
de lignes minimales discrètes)
Attribut
Modèle de
données
Architecture
des données
Affinité de charge de travail :
Entrepôt de données
Modèle d'entrepôt de données
centralisé très normalisé
Dénormalisation prenant en
charge les besoins en rapports
généralement traités à partir
d'applications BI, telles que
SQL Server Analysis Services
Structures de données
dimensionnelles hébergées sur la
base de données à requêtes
analytiques haut volume
à simultanéité relativement faible
Les analyses d'un grand nombre
de plages sont courantes
Cas d'usage analytiques ad hoc
Utilisation significative des
structures de table de segments
Tables partitionnées de grande taille
et index cluster qui prennent en charge
les analyses limitées par plage
Tables de faits volumineuses
(par exemple, de centaines de
gigaoctets à plusieurs téraoctets)
Taille des données volumineuse
(par exemple, de centaines de
téraoctets à un pétaoctet)
Utilisation minimale des
structures de table de segments
Structures de table d'index cluster
qui prennent en charge les
recherches d'enregistrement
détaillé (une à plusieurs lignes
par requête)
Plus petites tables de faits
(par exemple, de taille
inférieure à 100 Go)
Taille des données relativement
petite (par exemple, quelques
téraoctets)
Utilisation importante de
l'optimisation des index
secondaires
Utilisation minimale des index
secondaires (décrite précédemment
comme allégée en index)
Le partitionnement est courant
Tableau 2 : Attributs de charge de travail d'entrepôt de données
Optimisation
de la base de
données
OLTP/ODS
Modèle de données
opérationnelles très normalisé
Dénormalisation fréquente pour
l'aide à la décision ; accès
concurrentiel élevé, recherches
discrètes à latence faible
Conservation des données
d'historique limitée
Modèles de données
dénormalisés extraits d'autres
systèmes sources afin de
prendre en charge la prise de
décision opérationnelle
Choix d'une configuration de référence d'entrepôt de données Fast Track
Il existe trois approches générales pour utiliser la méthodologie d'entrepôt de données Fast
Track décrite dans ce document. Les deux premières sont spécifiques à l'utilisation des
architectures de référence Fast Track publiées conformes pour le stockage des données.
Ces approches permettent de sélectionner des systèmes préconçus publiés dans le cadre
du programme d'entrepôt de données Fast Track. La troisième approche traite la principale
méthodologie Fast Track comme des instructions de création d'un système de stockage des
données défini par l'utilisateur. Cette dernière approche nécessite le profilage détaillé de la
charge de travail et l'évaluation du système avant achat ou déploiement. Elle requiert un niveau
élevé de connaissances techniques dans les domaines de la configuration du serveur et du
stockage d'entreprise, ainsi qu'en optimisation de base de données SQL Server.
13
Option 1 : évaluation de base
Dans ce scénario, le client a déjà ciblé une configuration de référence d'entrepôt de données
Fast Track ou dispose d'autres méthodes pour déterminer la configuration requise pour l'UC
et le serveur. Si vous utilisez cette option, vous n'avez pas besoin d'exécuter une évaluation
complète de la plateforme (autrement dit, une preuve de concept).
Étape 1 : évaluer le cas d'usage du client
Les configurations de référence d'entrepôt de données Fast Track ne sont pas des configurations
logicielles et matérielles universelles. En revanche, elles sont définies pour les caractéristiques
d'une charge de travail de stockage des données. La première étape de sélection d'une
configuration consiste à identifier ces caractéristiques ; commencez par examiner les points
clés des exigences de votre client et les modèles d'utilisation.
Charge de travail
Les définitions de charge de travail d'entrepôt de données Fast Track fournissent deux points
clés pour l'évaluation de cas d'usage. Le premier est un jeu des règles qui définissent les
éléments clés de la charge de travail, car elle est liée aux performances de SQL Server.
Ces règles doivent être mesurées soigneusement par rapport à un cas d'usage donné, car des
conflits peuvent indiquer qu'une charge de travail cible ne convient pas pour une architecture
de référence d'entrepôt de données Fast Track.
Le deuxième composant d'une charge de travail est une description générale du cas d'usage
ciblé. Cela fournit une description globale utile du cas d'usage en plus d'un point de départ
pratique pour évaluer l'adéquation de la charge de travail.
Évaluation de la charge de travail
La liste suivante présente un processus de base d'évaluation de la charge de travail du client.
Il s'agit d'une estimation qualitative et elle doit être considérée à titre indicatif :
1. Définissez les conditions de la charge de travail ciblée. Comparez avec les attributs de
charge de travail d'entrepôt de données Fast Track. Pour plus d'informations, consultez
la section Charge de travail d'entrepôt de données Fast Track de ce document.
2. Évaluez les meilleures pratiques pour entrepôt de données Fast Track. Les pratiques
relatives à l'optimisation de la gestion des bases de données, de l'architecture et du
système de données doivent être évaluées par rapport au cas d'usage cible et
à l'environnement de production.
Prise de décision
L'objectif de cette estimation de charge de travail vise à garantir qu'une décision en toute
connaissance de cause peut être prise lorsqu'une architecture de référence d'entrepôt de
données Fast Track validée est sélectionnée. En réalité la plupart des scénarios de stockage
des données représentent un mélange des attributs conformes et en conflit par rapport à la
charge de travail d'entrepôt de données Fast Track. Les attributs de charge de travail prioritaires
avec une affinité forte pour les configurations de référence Fast Track sont répertoriés ici ;
les principaux cas d'usage des clients qui sont directement en conflit avec l'un de ces attributs
doivent être évalués, car ils peuvent rendre la méthodologie non valide pour le cas d'usage.
14
Charge de travail
Les attributs suivants de charge de travail sont prioritaires :
Les charges de travail critiques incluent des modèles d'accès aux données gourmands
en ressources d'analyse (autrement dit, celles qui tirent parti du placement de données
séquentielles). En général, les requêtes d'interrogation impliquent la lecture de dizaines
de milliers voire de millions (ou plus) de lignes.
Capacité de données élevée, faible simultanéité par rapport aux charges de travail
OLTP courantes.
Faible volatilité des données. L'activité DML de mise à jour/suppression fréquentes doit
être limitée à un faible pourcentage de l'encombrement global de l'entrepôt de données.
Gestion de base de données
Cela inclut les pratiques en matière d'administration de base de données, d'architecture des
données (modèle de données et structure de la table) et d'intégration des données :
Architecture de données partitionnée, allégée en index.
Gestion rigoureuse de la fragmentation de base de données, via des stratégies de
chargement et ETL appropriées et une maintenance périodique.
Conditions de croissance des données prédictibles. Les systèmes d'entrepôt de
données Fast Track sont préconçus à une capacité équilibrée. L'expansion du stockage
requiert la migration des données.
Étape 2 : choisir une architecture de référence d'entrepôt de données Fast Track publiée
Un client peut avoir un serveur à l'esprit lors d'une évaluation simple basée sur le budget ou
l'expérience. Ou bien, il peut déjà avoir une idée de la capacité de la charge de travail ou d'un
système existant sur lequel baser l'analyse des besoins en bande passante. Dans tous les cas,
vous n'effectuez pas une évaluation de plateforme complète lors d'une évaluation de base.
En revanche, vous sélectionnez une configuration d'entrepôt de données Fast Track conforme
qui correspond aux conditions attendues par le client.
Option 2 : évaluation complète
Les architectures de référence conformes à Fast Track fournissent des configurations de
composants matériels couplées avec des charges de travail définies du client. La méthodologie
suivante permet une approche rationalisée de sélection d'une architecture des composants de
base de données qui garantit un meilleur équilibre entre les conditions, les performances et
l'évolutivité de cas d'usage. Cette approche suppose un niveau élevé de compétences en
matière d'architecture de système de base de données et de déploiement d'entrepôt de
données. Les ressources des partenaires Fast Track et du support technique Microsoft sont en
général impliquées dans ce processus.
15
Présentation du processus
Le flux de traitement suivant résume le processus de sélection d'évaluation complète d'entrepôt
de données Fast Track :
1. Évaluer les attributs de charge de travail Fast Track par rapport au scénario d'utilisation
cible.
2. Identifier le serveur et/ou la bande passante nécessaires pour le cas d'usage du client.
Une configuration de référence d'entrepôt de données Fast Track publiée doit être
sélectionnée avant de démarrer une évaluation.
3. Identifier une requête représentative des conditions de charge de travail du client.
4. Calculer le taux de consommation de test d'évaluation (BCR, Benchmark Consumption
Rate) de SQL Server pour la requête.
5. Calculer la capacité requise pour les données utilisateur (UDC, Required User Data Capacity).
6. Comparer les estimations de BCR et UDC par rapport aux évaluations de capacité et de
taux de consommation d'UC maximal (MCR) publiées pour les architectures de référence
Fast Track conformes.
La section suivante décrit en détail les points du flux de traitement de l'évaluation complète.
Étape 1 : évaluer le cas d'usage du client
Évaluation de la charge de travail
Ce processus est identique à celui de l'option 1 : évaluation de base.
Sélectionner le matériel d'évaluation d'entrepôt de données Fast Track
Avant de démarrer une évaluation complète du système, vous devez sélectionner et déployer
une configuration de référence d'entrepôt de données Fast Track pour le test. Vous avez le
choix entre plusieurs méthodes pour identifier une configuration de référence appropriée.
Les approches courantes sont les suivantes :
16
Budget. Le client choisit d'acheter le système doté de la plus grande capacité et/ou le
système aux performances les plus élevées pour le budget disponible.
Performances. Le client choisit d'acheter le système aux performances les plus élevées
disponible.
Analyse interne. La décision repose sur l'analyse de la charge de travail effectuée par le
client sur le matériel existant.
Analyse ad hoc. L'outil de dimensionnement d'entrepôt de données Fast Track propose
une approche de base pour calculer les configurations du système d'entrepôt de
données Fast Track requises selon des hypothèses de base sur la charge de travail de
base de données ciblée. Cet outil de feuille de calcul peut être téléchargé à l'adresse
http://download.microsoft.com/download/D/F/A/DFAAD98F-0F1B-4F8B-988F22C3F94B08E0/Fast%20Track%20Core%20Calculator%20v1.2.xlsx.
Étape 2 : établir les mesures d'évaluation
Les trois mesures suivantes sont importantes pour une évaluation complète d'entrepôt de données
Fast Track. Elles intègrent les principaux critères de décision pour l'évaluation du matériel :
Taux de consommation de cœur de processeur maximal (MCR)
Taux de consommation de test d'évaluation (BCR)
Capacité requise pour les données utilisateur (UDC)
Pour plus d'informations sur le calcul de ces mesures, consultez la section Tests d'évaluation et
validation de ce document.
MCR
Cette métrique mesure le taux de traitement des données SQL Server maximal d'une requête
et d'un jeu de données standard pour une combinaison d'UC et de serveur spécifique. Elle est
fournie en tant que taux par cœur et est exprimée en tant qu'analyse basée sur les requêtes
du cache mémoire. MCR est le point de départ initial de la création du système Fast Track.
Cette mesure est une estimation de la bande passante d'E/S maximale pour le serveur, l'UC et
la charge de travail. La mesure MCR est utile en tant que guide de création initial, car elle ne
nécessite qu'un stockage local et minimal et un schéma de base de données pour estimer le
débit potentiel d'une UC spécifique. Il est important de renforcer l'idée que la mesure MCR est
utilisée comme point de départ pour la création du système ; il ne s'agit pas d'une mesure des
performances du système.
BCR
BCR est mesuré par un ensemble de requêtes considérées comme définitives dans la charge
de travail d'entrepôt de données Fast Track. BCR est calculé en termes de bande passante
totale de lecture de disque et du cache, plutôt qu'uniquement du cache comme dans le calcul
de MCR. Le calcul BCR permet la personnalisation de l'infrastructure pour un cas d'usage
spécifique du client en évaluant un ensemble de requêtes correspondant aux modèles de
charge de travail du client. Ou, dans le cas d'une architecture de référence Fast Track validée
par le partenaire, un jeu de requêtes de test d'évaluation sont utilisées pour veiller à ce que le
système soit conçu pour les charges de travail à forte contrainte. En résumé, BCR est vraie une
mesure de traitement des données utilisant plusieurs requêtes de charge de travail simultanée
sur des volumes importants de données.
Capacité pour les données utilisateur
Il s'agit de la capacité anticipée de la base de données SQL Server. La capacité des données
utilisateur Fast Track entre dans la compression de la base de données postchargement et
représente une estimation de la quantité de fichiers de données utilisateur non compressés ou
flux qui peuvent être chargés dans le système Fast Track. Le taux de compression standard
utilisé pour l'entrepôt de données Fast Track est 3,5:1.
Notez que toute expansion de stockage au-delà du déploiement initial requiert la migration des
données qui distribuerait efficacement les données existantes entre les nouveaux emplacements
des fichiers de base de données. C'est pourquoi, il est important de prendre en compte la
croissance de la base de données prévue et l'espérance de vie du système lorsque vous
choisissez une architecture de référence.
17
Étape 3 : choisir une architecture de référence d'entrepôt de données Fast Track
Une fois le BCR calculé, il peut être comparé aux évaluations de capacité et MCR publiées
fournies par les partenaires Fast Track pour chaque architecture de référence Fast Track publiée.
Pour plus d'informations sur nos partenaires, consultez Entreposage de données Fast Track
(http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/fasttrack.aspx).
Utilisez les métriques BCR comme point de référence commun pour évaluer les résultats du
système de test/d'évaluation par rapport aux configurations publiées. À partir des données
BCR, le client peut choisir l'option Fast Track qui s'aligne le mieux sur le résultat des tests.
Option 3 : architectures de référence définies par l'utilisateur
Cette approche exploite la méthodologie d'entrepôt de données Fast Track pour personnaliser
un système selon une charge de travail spécifique ou un ensemble de matériel. Elle exige une
compréhension approfondie de SQL Server et des composants matériels sur lesquels il s'exécute.
Les étapes suivantes présentent l'approche générale pour développer une architecture de
référence définie par l'utilisateur conforme aux principes d'entrepôt de données Fast Frack.
Étape 1 : définir la charge de travail
La compréhension du cas d'usage de la base de données cible est au centre de la configuration
d'entrepôt de données Fast Track et cela s'applique également à toute application personnalisée
de l'aide fournie dans ce document. L'aide sur l'architecture de référence Fast Track, en particulier
la rubrique sur les charges de travail, peut être utilisée en tant que modèle de référence pour
intégrer l'évaluation de charge de travail dans la création de l'architecture des composants.
Étape 2 : établir des tests d'évaluation de l'architecture des composants
L'infrastructure de base suivante est destinée au développement d'une architecture de référence
pour une charge de travail prédéfinie :
1. Établissez le MCR pour le serveur et l'UC choisis. Utilisez la méthode présentée dans la
section Tests d'évaluation et validation de ce document pour calculer le MCR. Utilisez
également les estimations MCR publiées pour les configurations d'entrepôt de données
Fast Track. En général, les unités centrales de la même famille possèdent des taux
similaires de consommation de cœur processeur pour la base de données SQL Server.
2. Utilisez la valeur MCR pour estimer la configuration requise pour le stockage et le
réseau de stockage et créer un système initial.
3. Mettez à disposition un système de test en fonction de la création initiale du système.
Idéalement ce sera la configuration complète spécifiée.
4. Créez un taux de consommation de tests d'évaluation (BCR). Selon l'évaluation de la
charge de travail, identifiez une requête ou dans le cas idéal un ensemble de requêtes
représentatives. Suivez les méthodes décrites dans la section Mesure du BCR de votre
charge de travail de ce document.
5. Ajustez la création du système en fonction des résultats.
6. Créez la configuration définitive du serveur et du stockage.
18
Étape 3 : Validation du système
L'objectif du test d'évaluation du système doit être la configuration et la validation du débit de la
configuration des composants matériels spécifiés à l'étape 2. Pour plus d'informations sur ce
processus, consultez la section Validation d'une architecture de référence Fast Track définie
par l'utilisateur de ce document. Pour valider votre système, procédez comme suit :
1. Évaluez le débit des composants par rapport aux exigences établies en matière de
performances. Cela garantit que le débit système réel correspond aux attentes.
2. Validez le débit du système en reconstruisant la configuration définitive et en exécutant
les tests d'évaluation finaux. En règle générale, le BCR final doit atteindre 80 % ou plus
du MCR système.
Choisir un résumé de l'architecture de référence Fast Track
Le tableau suivant récapitule les trois options de sélection d'architecture de référence Fast Track.
Option
Évaluation de base
Évaluation complète
Architecture de référence
définie par l'utilisateur
Avantages
Configuration du système et
disponibilité très rapides
(de quelques jours à quelques
semaines)
Coûts réduits de création et
d'évaluation
Les compétences requises en
infrastructure sont moindres
Architecture de référence
prédéfinie personnalisée pour
la charge de travail attendue
Potentiel d'économie sur le
matériel
Confiance accrue dans
la solution
Inconvénients
Possibilité de stockage
surdimensionné ou d'UC
sous-dimensionnée
Possibilité de réutiliser le
matériel existant
Possibilité d'incorporer le
matériel le plus récent
Système hautement
personnalisé pour votre cas
d'utilisation
Tableau 3 : Comparaison des différentes options d'évaluation
19
L'évaluation nécessite
des efforts et du temps
(de quelques semaines
à quelques mois)
Nécessite une
compréhension
approfondie de la charge
de travail cible
Le processus prend
plusieurs mois
Requiert des compétences
significatives en
infrastructure
Requiert des compétences
significatives en SQL Server
Configuration standard d'entrepôt de données Fast Track
Architecture des composants matériels
Les architectures de référence actuelles d'entrepôt de données Fast Track reposent sur des
configurations de stockage dédiées. Les options actuellement publiées comportent le réseau
SAN commuté, l'attachement direct SAN, l'attachement direct SAS, SAS-RBOD et iSCSI. Le
débit des E/S disque est atteint au moyen de l'utilisation de boîtiers et processeurs de stockage
dédiés indépendants. Des informations et configurations supplémentaires sont publiées par les
fournisseurs Fast Track. L'illustration 2 montre les blocs de construction au niveau des composants
qui comprennent une architecture de référence d'entrepôt de données Fast Track basée sur le
stockage SAN.
Illustration 2 : Exemple de configuration de stockage pour un serveur à 12 cœurs et 2 sockets
20
Impératifs relatifs aux composants et configuration
Mémoire serveur
RAM totale : L'allocation de mémoire vive (RAM) pour les architectures de référence Fast
Track repose sur les résultats de test d'évaluation ayant pour objectif l'équilibrage du débit
logique maximal (nombre total de pages lues sur le disque et dans la mémoire tampon dans
le temps) avec l'utilisation du processeur. Le tableau 4 répertorie les allocations de mémoire
recommandées pour les architectures de référence SQL Server 2012. Les valeurs maximales
de mémoire fournies ne sont pas des limites inconditionnelles, mais représentent les valeurs
moyennes pour les systèmes validés avec succès.
Taille du serveur
Mémoire minimale
Mémoire maximale
1 socket
64 Go
128 Go
2 sockets
128 Go
256 Go
4 sockets
256 Go
512 Go
8 sockets
512 Go
768 Go
Tableau 4 : Allocations de mémoire recommandées pour SQL Server 2012
Les considérations suivantes sont aussi importantes à prendre en compte lorsque vous évaluez
la configuration requise pour la mémoire système :
Requête du cache : Les charges de travail qui servent un pourcentage élevé de
requêtes à partir du cache peuvent tirer parti des allocations accrues de RAM à mesure
que la charge de travail augmente.
Jointures de hachage et tris : Les requêtes qui reposent sur des jointures de hachage
à grande échelle ou effectuent des opérations de tri à grande échelle tireront parti d'une
grande quantité de mémoire physique. Avec une quantité de mémoire inférieure,
ces opérations débordent sur le disque et utilisent souvent tempdb, ce qui introduit un
modèle d'E/S aléatoires sur les lecteurs de données du serveur.
Chargements : Les insertions en bloc peuvent également introduire des opérations de
tri qui utilisent tempdb si elles ne peuvent pas être traitées dans la mémoire disponible.
Index columnstore optimisés en mémoire xVelocity : Les charges de travail qui
favorisent fortement les plans de requête d'index columnstore s'exécutent plus
efficacement avec des pools de mémoire au niveau le plus élevé des plages
répertoriées dans le tableau 4.
SAN Fibre Channel
ADAPTATEUR HBA – SAN : Les composants réseau de l'adaptateur HBA et SAN diffèrent par
marque et par modèle. En outre, le débit du boîtier de stockage peut être sensible à la configuration
SAN et aux fonctions de bus PCIe. Cette recommandation est générale et est compatible avec
l'évaluation effectuée pendant le développement de la configuration de référence d'entrepôt de
données Fast Track.
21
Si la segmentation est utilisée, seuls les ports utilisés pour Fast Track doivent exister dans le ou
les segments. La topologie et la configuration du réseau FC détaillées sont documentées dans
le Guide technique de configuration fourni par chaque partenaire Fast Track et spécifique
à chaque architecture de référence Fast Track publiée.
MPIO (Multipath I/O) : MPIO doit être configuré. Chaque volume hébergé dans des baies de
stockage dédiées doit avoir au moins un chemin d'accès actif.
La méthode « tourniquet » (round robin) avec sous-ensemble est la stratégie par défaut utilisée
pour les configurations Fast Track. Cependant, elle est rarement utilisée pour les architectures
de référence de serveur partenaire, car des configurations plus optimales sont identifiées par
les équipes d'ingénierie des partenaires Entrepôt de données Fast Track. Les DSM et/ou
documents spécifiques au partenaire prescrivent souvent des paramètres différents et doivent
être examinés avant la configuration.
Stockage
Disque local : 2 disques d'une pile de disque de niveau RAID1 constituent l'allocation minimale
pour l'installation de Windows Server et de SQL Server. L'espace disque suffisant doit être alloué
pour les spécifications de RAM virtuelle et de pagination. Généralement, 250 Go de RAM ou
1,5 fois la RAM système doivent être disponibles dans l'espace disque. La configuration restante
du disque dépend de la préférence du client et du cas d'usage.
Système de fichiers logique : Il est conseillé de monter des numéros d'unité logique dans les
chemins d'accès aux dossiers de points de montage dans Windows, plutôt que des lettres de
lecteurs, en raison du nombre de volumes dans la plupart des systèmes Fast Track.
Il peut également être utile de comprendre quel lecteur du système d'exploitation Windows
représente un numéro d'unité logique (volume), un groupe de disques de niveau RAID et un
point de montage Windows Server dans les boîtiers de stockage. Adoptez un schéma
d'affectation de noms pour les points de montage et les volumes lorsque vous montez des
numéros d'unité logique dans des dossiers Windows. Pour plus d'informations sur les schémas
d'affectation de noms, consultez l'aide sur la configuration fournie par le partenaire Fast Track.
Utilisez les outils spécifiques au fournisseur pour obtenir le schéma d'affectations de noms
recommandé. Si l'outil approprié n'existe pas, rendez un disque à la fois disponible dans
Windows à partir des baies de stockage lors de l'affectation des noms de lecteur pour garantir
la topologie physique-logique correcte.
Système de fichiers physique : Pour plus d'informations, notamment des instructions
détaillées, consultez la section Configuration de l'application de ce document.
22
Configuration des boîtiers de stockage : Tous les paramètres des boîtiers restent à leurs
valeurs par défaut, sauf en cas d'indication contraire dans la documentation technique de
référence du partenaire Fast Track. Les caractéristiques d'entrepôt de données Fast Track pour
la configuration du système de fichiers nécessitent des boîtiers de stockage qui permettent la
configuration de regroupements RAID et d'affectations de numéro d'unité logique. Cela doit être
pris en compte pour tout remplacement de matériel dans la configuration de référence
d'entrepôt de données Fast Track ou toute évaluation de matériel personnalisée.
Configuration de l'application
Windows Server 2008 R2
Sauf indication contraire, les paramètres par défaut doivent être utilisés pour le système
d'exploitation Windows Server 2008 R2 Enterprise. Vérifiez que les derniers Service Pack et les
mises à jour critiques sont appliqués. La fonctionnalité Multipath I/O est nécessaire pour de
nombreuses architectures de référence. Pour plus d'informations sur la configuration détaillée
de MPIO, consultez le guide technique de configuration du partenaire Fast Track pour l'architecture
de référence donnée. Vérifiez que Windows Server 2008 R2 est installé en tant que rôle Serveur
d'applications pour garantir l'installation correcte du .NET Framework et des valeurs par défaut.
SQL Server 2012 Enterprise
Options de démarrage
Vous devez ajouter - E aux options de démarrage. Cela augmente le nombre d'extensions
contiguës dans chaque fichier, qui sont allouées à une table de base de données à mesure
qu'elle se développe. Cela améliore l'accès séquentiel au disque. Pour plus d'informations
sur cette option, consultez l'article 329526 de la Base de connaissances Microsoft
(http://support.microsoft.com/kb/329526). Il est important de s'assurer que l'option -E a pris effet
au démarrage de la base de données. Cette option respecte la casse et le format. L'espace
avant ou après l'option peut empêcher l'initialisation.
Vous devez également ajouter -T1117 aux options de démarrage. Cet indicateur de trace
permet même la croissance des fichiers d'un groupe de fichiers, si la croissance automatique
est activée. La recommandation standard pour les entrepôts de données Fast Track en vue de
la croissance des bases de données consiste à définir la préallocation plutôt que la croissance
automatique (à l'exception de tempdb). Pour plus d'informations, consultez la section Détails de
la configuration du stockage de ce document.
Activez l'option Verrouiller les pages en mémoire. Pour plus d'informations, consultez
Procédure : activer l'option Verrouiller les pages en mémoire (http://go.microsoft.com/fwlink/
?LinkId=141863).
-T834 doit être évalué au cas par cas. Cet indicateur de trace peut améliorer les débits de
nombreuses charges de travail de stockage des données. Il permet les allocations de pages
volumineuses en mémoire pour le pool de mémoires tampons SQL Server. Pour plus
d'informations à ce sujet et sur d'autres indicateurs de trace, consultez l'article 920093 de la
Base de connaissances Microsoft (http://support.microsoft.com/kb/920093).
23
Remarque : À ce stade, SQL Server 2012 ne prend pas en charge l'utilisation de –T834, si des
index columnstore sont en cours d'utilisation sur la base de données. Si vous envisagez
d'utiliser des index columnstore, n'utilisez pas cet indicateur de trace.
Mémoire maximale SQL
Pour SQL Server 2012, au plus 92 % de la mémoire vive (RAM) totale du serveur doivent être
alloués à SQL Server. Si des applications supplémentaires doivent partager le serveur, la
quantité de RAM disponible pour le système d'exploitation doit être modifiée en conséquence.
Ce paramètre est contrôlé par l'option max server memory. Pour plus d'informations sur les
paramètres de mémoire pour les architectures de référence validées, consultez la documentation
du partenaire Entrepôt de données Fast Track.
Gouverneur de ressources
Les charges de travail de stockage des données comprennent généralement des requêtes
complexes qui opèrent sur de grands volumes de données. Ces requêtes consomment une
grande quantité de mémoire, et elles peuvent déborder sur le disque si la mémoire est limitée.
Ce comportement a des conséquences spécifiques en termes de gestion des ressources.
Utilisez la technologie du Gouverneur de ressources dans SQL Server 2012 pour gérer
l'utilisation des ressources.
Dans les paramètres par défaut de SQL Server, le Gouverneur de ressources fournit au plus
25 % des ressources mémoire de SQL Server à chaque session. Au pire, cela signifie que trois
requêtes suffisamment volumineuses pour consommer au moins 25 % de la mémoire disponible
vont bloquer les autres requêtes nécessitant beaucoup de mémoire. Dans cet état, toutes les
requêtes supplémentaires qui requièrent une grande allocation de mémoire pour s'exécuter
seront mises en file d'attente jusqu'à ce que des ressources soient disponibles.
Utilisez le Gouverneur de ressources pour réduire la mémoire maximale consommée par
requête. Toutefois, par conséquent, les requêtes simultanées susceptibles de consommer sinon
de grandes quantités de mémoire utilisent plutôt tempdb, ce qui introduit des E/S plus aléatoires
et réduit le débit global. Bien qu'il puisse être bénéfique pour de nombreuses charges de travail
d'entrepôt de données de limiter la quantité de ressources système disponibles pour une
session individuelle, cela peut être mesuré par l'analyse des charges de travail de requêtes
simultanées. Pour plus d'informations sur l'utilisation du Gouverneur de ressources,
consultez Gestion des charges de travail SQL Server avec le Gouverneur de ressources
(http://msdn.microsoft.com/fr-fr/library/bb933866.aspx).
Vous devez également consulter l'aide et les pratiques spécifiques du fournisseur de solutions
Fast Track. En particulier, les plus grandes solutions Fast Track à 4 et 8 sockets peuvent
reposer sur des paramètres spécifiques du Gouverneur de ressources pour obtenir des
performances optimales.
En résumé, il est nécessaire de trouver un compromis entre la réduction des contraintes
qui offrent de hautes performances des requêtes et des contraintes plus rigoureuses qui
garantissent le nombre de requêtes pouvant s'exécuter simultanément.
24
Pour plus d'informations sur les meilleures pratiques et les scénarios courants pour le
Gouverneur de ressources, consultez le livre blanc Utilisation du Gouverneur de ressources
(http://msdn.microsoft.com/fr-fr/library/ee151608.aspx).
Système de stockage
La gestion de la fragmentation s'avère indispensable pour les performances du système dans le
temps des architectures de référence d'entrepôt de données Fast Track qui placent le stockage
de base de données primaire sur des lecteurs de disque dur (HDD). Pour cette raison, une
configuration du système de fichiers et de stockage détaillée est spécifiée.
Composants du système de stockage
L'illustration 3 fournit une vue qui combine trois couches principales de configuration de stockage
pour la pile de base de données intégrée. Celle-ci doit être considérée comme un cas de référence,
car la topologie diffère considérablement par partenaire Fast Track. La pile de base de données
par défaut contient les éléments suivants :
25
Baie de disques physiques : 4 sous-unités RAID 1+0 correspondent à l'approche
standard représentée sur l'illustration 3. Des volumes RAID 5 et RAID 6 ont également
été utilisés dans certaines architectures de référence des partenaires SQL Server 2008
R2 et SQL Server 2012.
Affectation du volume du système d'exploitation (numéro d'unité logique)
Bases de données : utilisateur, temporaire du système, des journaux du système
Illustration 3 : Exemple d'architecture complète de stockage pour un système d'entrepôt de
base de données Fast Track basé sur trois boîtiers de stockage avec un numéro d'unité logique
(LUN) par groupe de disques
26
Détails de la configuration du stockage
Pour chaque boîtier de stockage, procédez comme suit.
1. Créez des groupes de disques de quatre disques chacun, à l'aide de RAID 1+0
(RAID 10). Le nombre exact de groupes de disques pour chaque boîtier de stockage
varie selon le fournisseur. Pour plus d'informations, consultez la documentation
spécifique au fournisseur. Généralement, le nombre est de (2) groupes de disques
RAID10 et (1) groupe de disques RAID1 pour les boîtiers de grande taille et (5) groupes
de disques RAID10 pour les boîtiers de petite taille.
Le nombre total de volumes utilisés comme emplacements de groupes de fichiers de
données primaires ne doit pas excéder 32. Si le nombre total de numéros d'unité logique
du système de stockage atteint ce seuil, des groupes de disques de plus grande taille
peuvent être utilisés de façon à réduire le nombre d'unités logiques tout en conservant
un débit d'E/S comparable. Par exemple, utilisez un groupe de huit disques RAID 10 avec
un numéro d'unité logique plutôt qu'un groupe de quatre disques RAID 10 avec un numéro
d'unité logique. Les groupes de disques de plus grande taille entraînent une certaine
diminution du débit et de l'efficacité. Cela varie en fonction de la technologie de
stockage.
2. Dédiez tous les groupes de disques à l'exception d'un aux données primaires utilisateur.
Les emplacements de données primaires utilisateur désignent les emplacements de
groupes de fichiers de base de données SQL Server.
Toutes les architectures de référence Fast Track appellent un ou deux LUN par groupe
de disques de données primaires utilisateur. Reportez-vous à l'aide spécifique au
fournisseur de l'architecture de référence sélectionnée. Ces numéros d'unité logique
permettent de stocker les fichiers de base de données SQL Server (fichiers .mdf et .ndf).
3. Vérifiez que l'affectation du processeur de stockage principal pour chaque volume disque
alloué aux données primaires d'un boîtier de stockage est équilibrée. Par exemple,
un boîtier de stockage avec quatre volumes disque alloués aux données primaires aura
deux volumes affectés au processeur de stockage « A » et deux affectés au processeur
de stockage « B ».
4. Créez un numéro d'unité logique dans l'autre groupe de disques pour héberger les journaux
des transactions de la base de données. Pour les configurations Fast Track de plus
grande taille, les allocations de journaux sont limitées aux premiers boîtiers de stockage
du système. Dans ce cas, les groupes de disques supplémentaires sont utilisés pour la
mise en lots autre que celle des bases de données ou laissés vides pour réduire le coût.
Pour chaque base de données, procédez comme suit :
1. Créez au moins un groupe de fichiers contenant un fichier de données par numéro
d'unité logique de données primaires utilisateur. Veillez à créer tous les fichiers de la
même taille. Si vous envisagez d'utiliser plusieurs groupes de fichiers dans une base de
données pour isoler des objets (par exemple, une base de données de mise en lots pour
prendre en charge le chargement), veillez à inclure tous les numéros d'unité logique de
données primaires utilisateur comme emplacements de chaque groupe de fichiers.
27
2. Lorsque vous créez les fichiers de chaque groupe de fichiers, préallouez-leur la plus
grande taille anticipée, avec une taille assez importante pour contenir les objets anticipés.
3. Désactivez l'option de croissance automatique des fichiers de données, et augmentez
manuellement la taille de tous les fichiers de données lorsque la limite de taille actuelle
est atteinte.
4. Pour plus d'informations sur les recommandations pour les bases de données utilisateur
et les groupes de fichiers, consultez la section Gestion de la fragmentation des données
de ce document.
Pour tempdb, procédez comme suit :
1. Préallouez l'espace, puis ajoutez un seul fichier de données par numéro d'unité logique.
Veillez à créer tous les fichiers de la même taille.
2. Affectez des fichiers journaux temporaires sur un des numéros d'unité logique dédiés
aux fichiers journaux.
3. Activez la croissance automatique ; généralement l'utilisation d'un incrément de
croissance espacé est adaptée aux charges de travail d'entrepôt de données.
Une valeur équivalente à 10 % de la taille du fichier d'origine est un point de départ
raisonnable.
4. Suivez les meilleures pratiques SQL Server standard pour les considérations concernant
la taille de la base de données et de tempdb. Une allocation d'espace supérieure peut
être nécessaire pendant la phase de migration ou pendant le chargement initial des
données de l'entrepôt. Pour plus d'informations, consultez Planification des capacités de
tempdb (http://msdn.microsoft.com/fr-fr/library/ms345368.aspx) dans la Documentation
en ligne de SQL Server.
Pour le journal des transactions, procédez comme suit :
1. Créez un seul fichier journal des transactions par base de données sur un des numéros
d'unité logique affectés à l'espace du journal des transactions. Répartissez les fichiers
journaux des différentes bases de données entre les numéros d'unité logique disponibles
ou utilisez plusieurs fichiers journaux pour la croissance du journal selon les besoins.
2. Activez l'option de croissance automatique des fichiers journaux.
3. Vérifiez que la capacité du journal est conforme aux conditions préalables spécifiées
dans le tableau 5. Un écart est acceptable selon les fonctionnalités de création
spécifiques au système.
RAM système (Go)
Capacité évaluée Fast Track
(téraoctets)
<= 96
<=10
<= 128
>10
<=40
Tableau 5 : Recommandations d'allocation du journal
28
Allocation minimale
recommandée des journaux
Espace libre en miroir (Go)
300 Go x 1 volume
300 Go x 2 volume
ou
600 Go x 1 volume
Consultez les meilleures pratiques existantes pour l'allocation et la gestion du journal des
transactions SQL Server.
Stockage à l'état solide
Les architectures de référence d'entrepôt de données Fast Track qui utilisent le stockage à l'état
solide des données primaires (PRI) présentent de nombreux avantages, notamment gestion
simplifiée, coûts d'exploitation moindres et maintenance prédictible.
Gestion simplifiée : Le stockage à l'état solide ne nécessite pas de gestion de la
fragmentation. L'option de démarrage SQL Server –E doit encore être utilisée, mais aucune
optimisation ou gestion supplémentaire d'allocation de pages n'est requise. Cette simplification
facilite la gestion à long terme des environnements d'entrepôt de données Fast Track. En outre,
des groupes de disques de plus grande taille et un nombre inférieur de volumes/numéros d'unité
logique peuvent être utilisés sans impact négatif sur les performances. Cette modification
simplifie la création et la maintenance des groupes de fichiers.
Résilience d'E/S : Le stockage à l'état solide a un impact minimal sur les performances lorsque
la fragmentation des pages ou la simultanéité est élevée. En outre, la charge de travail de lecture
aléatoire mixte (recherche) n'a aucun impact négatif sur les modèles d'E/S de requête (analyse)
volumineuse.
Maintenance prédictible : De nombreuses options de stockage à l'état solide fournissent
l'analyse de la durée d'écriture logicielle à une fréquence inférieure de défaillances physiques
difficiles à prédire.
Coûts d'exploitation moindres : Bien que le tarif du stockage à l'état solide soit plus onéreux,
il offre un équilibre plus efficace de débit d'E/S à la capacité par unité. Le taux effectif d'E/S de
charge de travail d'entrepôt de données Fast Track pour HDD SAS 300 Go 10k avoisine les
50 Mo. Un disque SSD Enterprise MLC atteint 150 et 200 Mo à une capacité de 600 Go.
En outre, le stockage à l'état solide consomme beaucoup moins, génère moins de chaleur et
prend souvent en charge les solutions à plus haute densité.
Configuration du stockage à l'état solide
Les modifications suivantes peuvent être apportées aux instructions standard de configuration
du stockage d'entrepôt de données Fast Track si le stockage à l'état solide est utilisé pour les
volumes de données primaires utilisateur.
29
Si la mise en miroir est nécessaire, RAID1+0 ou RAID5 peut être utilisé. RAID5 fournit la
meilleure capacité sans impact sur les performances des charges de travail d'entrepôt
de données Fast Track sur semi-conducteur.
Le nombre de LUN et de volumes peut être réduit au minimum à un volume de données
primaires utilisateur par unité de stockage. Il est utile dans certains cas que le nombre
de volumes de données primaires utilisateur soit un multiple du nombre de cœurs d'UC.
Le nombre minimal de volumes de données primaires est deux.
Le journal des transactions peut également être placé sur semi-conducteur, mais les
charges de travail d'entrepôt de données Fast Track ne sont généralement pas liées au
journal. Le coût peut être réduit en plaçant le journal sur HDD traditionnel. Il en va de
même pour le stockage local de l'installation de Windows Server et de SQL Server.
Les recommandations pour la gestion de la fragmentation des pages et le chargement
en parallèle d'index de cluster peuvent être ignorées, car la fragmentation de base de
données logique n'a aucune incidence sur les performances d'E/S à l'état solide.
Meilleures pratiques SQL Server pour l'entrepôt de données Fast Track
Les pratiques pour les charges de travail Fast Track sont validées et documentées dans deux
cas. Le premier se produit si une pratique Fast Track diffère de façon significative des meilleures
pratiques SQL Server établies. Le deuxième cas se produit dans les scénarios où les pratiques
sont manquantes ou difficilement accessibles. Les pratiques fournies ici ne sont pas exhaustives,
car il existe un ensemble complet de la documentation existante pour le déploiement de base
de données SQL Server. La documentation technique et les meilleures pratiques pour SQL Server
doivent être référencées dans de nombreuses rubriques liées à un déploiement d'entrepôt de
données Fast Track.
Important : Il existe plusieurs liens vers la documentation rédigée pour SQL Server 2008 R2
dans ce guide. Nous pensons que la majorité de cette aide est toujours précieuse pour SQL
Server 2012. Vous devez rechercher les versions mises à jour de ces documents dès qu'elles
sont disponibles. Les versions ultérieures de ce guide de référence mettront à jour les liens dès
qu'elles seront disponibles.
Architecture des données
Structure de table
Le type de table utilisée pour stocker des données dans la base de données a un impact
significatif sur les performances de l'accès séquentiel. Il est essentiel de concevoir le schéma
physique en gardant cela à l'esprit, de façon à permettre aux plans de requête de générer des
E/S séquentielles dans la mesure du possible.
Le choix d'un type de table se fait la plupart du temps en fonction de la façon dont les données
de la table seront accessibles. Les informations suivantes peuvent être utilisées pour déterminer
quel type de table doit être pris en compte en fonction des détails des données stockées.
Tables de segments de mémoire
Les tables de segments de mémoire fournissent des E/S séquentielles propres pour les
analyses de table et ont généralement un coût moindre en ce qui concerne la fragmentation des
tables. Elles n'autorisent pas intrinsèquement les analyses basées sur des plages (accès direct)
optimisées comme dans une table d'index cluster. Dans une situation d'analyse de plages, une
table de segments mémoire analyse la table entière (ou la partition par spécification de plages
de valeurs appropriée, si le partitionnement s'applique).
L'analyse de tables de segments de mémoire atteint un débit maximal à 32 fichiers. Par
conséquent, l'utilisation de segments de mémoire pour les tables de faits volumineuses sur des
systèmes ayant un grand nombre de LUN (plus de 32) ou de cœurs (plus de 16) peut nécessiter
l'utilisation du Gouverneur de ressources, de contraintes de degré de parallélisme ou des
modifications de l'allocation de fichiers de base de données Fast Track standard.
30
31
Il est préférable d'utiliser des tables de segments de mémoire lorsque :
La majorité des requêtes à priorité élevée dans la table de référence contiennent des
attributs qui référencent plusieurs colonnes disparates ou n'ont aucun prédicat de colonne.
Les requêtes exécutent généralement des analyses volumineuses par opposition aux
analyses limitées par plage, telles que les tables utilisées exclusivement pour remplir les
cubes Analysis Services. (Dans ce cas, la table de segments de mémoire doit être
partitionnée avec la même granularité que le cube Analysis Services rempli.)
Les conditions de charge de travail de requête sont remplies sans surcharge
incrémentielle de gestion des index ou les performances de charge revêtent une
importance capitale : les tables de segments de mémoire sont plus rapides à charger.
Tables d'index cluster
Dans l'environnement d'entrepôt de données, un index cluster est plus efficace lorsque la
clé est une colonne qualifiée par plage (telle que la date), qui est souvent utilisée dans les
restrictions de la charge de travail de requête concernée. Dans ce cas, l'index peut être utilisé
pour limiter et optimiser de manière significative les données à analyser.
Il est préférable d'utiliser des tables d'index cluster, dans les cas suivants :
Il existe des colonnes qualifiées par plage dans la table, qui sont utilisées dans les
restrictions de requête de la majorité des scénarios de charge de travail haute priorité
dans la table. Pour les configurations d'entrepôt de données Fast Track, la colonne de
date partitionnée d'un index cluster doit également être la clé d'index cluster.
Remarque : dans certains, cas, il peut être avantageux de choisir une clé d'index cluster
qui n'est pas la colonne de partition de date pour une table d'index cluster. Toutefois,
cela risque de donner lieu à une fragmentation, sauf si les partitions complètes sont
chargées, car les nouvelles données qui chevauchent les plages existantes de clé
d'index cluster créent des fractionnements de pages.
Les requêtes dans la table sont normalement des recherches granulaires ou par plages
contraintes, et non pas des analyses de table entière ou de plusieurs plages
volumineuses.
Partitionnement de table
Le partitionnement de table peut être un outil important pour gérer la fragmentation dans les
bases de données d'entrepôt de données Fast Track. Par exemple, le partitionnement peut être
utilisé pour mettre à jour ou supprimer des blocs volumineux de données utilisateur basées sur
des plages d'une table sans traiter les autres parties de la table. En revanche, la suppression
ligne par ligne d'un index cluster peut induire une fragmentation significative de l'étendue.
Un scénario classique consiste à reconstruire plusieurs partitions après leur arrivée à expiration
et la fréquence des opérations DML pour la plage de données diminue. La partition est maintenant
stable par rapport aux opérations DML et possède une fragmentation minimale de l'étendue.
32
En outre, les tables volumineuses utilisées principalement pour le remplissage de cubes SQL
Server Analysis Services peuvent être créées en tant que tables de segments de mémoire
partitionnées, avec le partitionnement de table aligné sur le partitionnement du cube. Lors de
l'accès, seules les partitions concernées de la table de grande taille sont analysées. (Les
partitions qui prennent en charge le mode ROLAP Analysis Services peuvent être mieux
structurées en tant qu'index cluster.)
Pour plus d'informations sur le partitionnement de table, consultez le livre blanc Stratégies de
tables et d'index partitionnés avec SQL Server 2008 (http://msdn.microsoft.com/fr-fr/library/
dd578580(v=SQL.100).aspx).
Indexation
Pour la création d'index d'entrepôt de données Fast Track, tenez compte des instructions
suivantes :
Utilisez un index cluster pour les restrictions par plages de dates ou usuelles.
Utilisez un index columnstore dans la mesure du possible. La section suivante présente
les meilleures pratiques recommandées pour l'utilisation des index columnstore dans les
environnements d'entrepôt de données Fast Track.
Réservez l'indexation non cluster pour les situations où la recherche granulaire est
nécessaire et le partitionnement de table ne fournit pas les performances suffisantes.
Si possible, utilisez un index columnstore à la place d'un index non cluster.
Les index explicatifs non cluster peuvent fournir une valeur pour certaines charges de
travail d'entrepôt de données. Ils doivent être évalués au cas par cas et comparés par
rapport à l'index columnstore.
Index columnstore optimisés en mémoire xVelocity
SQL Server 2012 introduit une nouvelle fonctionnalité d'accélération des requêtes d'entrepôt de
données basée sur la technologie des colonnes : les index columnstore. Ces nouveaux index,
associés aux fonctionnalités avancées de traitement des requêtes, améliorent les performances
des requêtes d'entrepôt de données pour un large éventail de requêtes analytiques.
Les index columnstore optimisés en mémoire xVelocity sont de « purs » index columnstore
(et non pas hybrides), car ils stockent toutes les données des colonnes incluses sur des pages
distinctes. Les index columnstore améliorent les performances d'analyse d'E/S et le taux d'accès
à la mémoire tampon, et ils sont bien alignés avec la méthodologie de conception d'entrepôt de
données Fast Track.
Meilleures pratiques recommandées
Les objets d'index columnstore résident avec les tables et sont créés de la même façon que les
index non cluster. Ces faits impliquent qu'une capacité de stockage incrémentielle est nécessaire.
Il n'est pas nécessaire de créer des index columnstore dans des groupes de fichiers distincts
à moins que des modifications fréquentes dans la table ciblée par l'index soient prévues. Le fait
de conserver les index columnstore dans des groupes de fichiers distincts peut vous aider
à gérer la fragmentation des pages dans le temps dans des environnements très volatiles.
33
Création d'index columnstore pour les modèles de données normalisés
Les modèles de données standard (autrement dit, 3NF) déclenchent souvent des jointures entre
au moins deux tables (de faits) volumineuses. Ces types de jointures ne sont pas appropriés
pour le traitement des index columnstore et peuvent entraîner une régression des performances
par rapport aux plans de requête d'index non columnstore. Les méthodes suivantes peuvent
vous aider à éviter ce problème avec les modèles de données standard :
Utilisez les indicateurs de niveau de requête pour bloquer l'utilisation du traitement des
index columnstore.
Utilisez OPTION(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
Réécrivez les requêtes. Pour plus d'informations, consultez les ressources répertoriées
dans la section Meilleures pratiques générales pour les index columnstore de ce document.
Essayez d'omettre les clés de jointure communes d'une table impliquée dans une ou
plusieurs jointures SQL qui affichent des régressions de performances des plans de
requête d'index non columnstore. En omettant la clé de jointure de l'index columnstore
sur une table, l'index columnstore risque de ne pas être utilisé pour les requêtes qui
joignent la colonne omise. Cette approche peut être utile dans les environnements où
les options au niveau de la requête ne peuvent pas être appliquées. Sachez que le fait
d'omettre une colonne de l'index columnstore ne garantit pas un meilleur plan de requête
et peut affecter d'autres requêtes pour lesquelles l'index columnstore fournirait de meilleures
performances. Si vous choisissez d'utiliser cette option, le fait de sélectionner une
colonne dans la plus petite des tables impliquées peut réduire l'impact sur les performances
d'autres requêtes. Notez que les clés primaires (DDL) déclarées doivent être incluses dans
l'index columnstore, ce qui peut limiter les colonnes de jointure disponibles. Même si vous
omettez une colonne clé primaire de la définition d'index columnstore, toutes les colonnes
clés primaires sont automatiquement ajoutées à l'index columnstore lors de sa création.
Alors que les modèles de données standard ne sont pas parfaitement optimisés pour les index
columnstore dans la version actuelle, il est important de noter que les tests d'entrepôt de
données Fast Track sont basés sur une version modifiée de TPC-H, qui est un modèle normalisé.
Des gains importants ont été mesurés pour les charges de travail simultanées qui combinent
à la fois les plans de requête d'index columnstore et non columnstore, notamment le débit
d'entrepôt de données Fast Track qui est près de deux fois supérieur à celui des performances
globales de charge de travail dans certains cas.
Création d'index columnstore pour les modèles de données dimensionnels
Suivez les meilleures pratiques pour les index columnstore des modèles dimensionnels,
tels que les schémas en étoile. Cela peut être considéré comme le meilleur des cas pour le
traitement des index columnstore.
34
Gestion de la mémoire pour les index columnstore
Les architectures de référence Fast Track validées pour SQL Server 2012 sont généralement
dotées d'une RAM système totale supérieure à celle des configurations similaires pour SQL
Server 2008 R2. La raison principale découle du fait que les charges de travail améliorées par
cet index columnstore s'exécutent plus efficacement avec de plus grands pools de mémoires.
Le Gouverneur de ressources doit toujours être utilisé pour définir la quantité maximale de
mémoire par session pour les environnements d'entrepôt de données Fast Track dans lesquels
vous projetez de tirer parti des index columnstore. Les architectures de référence Fast Track
validées documentent les paramètres du Gouverneur de ressources utilisés pour obtenir les
performances Fast Track, et ces valeurs peuvent être considérées comme point de départ pour
les charges de travail clientes. Idéalement, le paramètre est évalué et modifié spécifiquement
pour une charge de travail du client après installation du système.
La commande SQL suivante configure le Gouverneur de ressources SQL Server en fonction de ces
recommandations. Dans ce cas, la quantité maximale de mémoire par session est définie sur 19 %.
ALTER RESOURCE GOVERNOR RECONFIGURE;
ALTER WORKLOAD GROUP [default] WITH(request_max_memory_grant_percent=19);
Meilleures pratiques générales pour un index columnstore optimisé en mémoire xVelocity
L'aide de référence pour entrepôt de données Fast Track couvre uniquement les méthodes
propres à Fast Track. Pour plus d'informations sur les index columnstore, consultez le Guide
Paramétrage des index columnstore SQL Server 2012 (http://social.technet.microsoft.com/wiki/
contents/articles/sql-server-columnstore-performance-tuning.aspx) et les Questions fréquentes
sur les index columnstore SQL Server 2012 (http://social.technet.microsoft.com/wiki/contents/
articles/sql-server-columnstore-index-faq.aspx).
Statistiques de base de données
Votre choix quant au moment d'exécuter des statistiques et la fréquence à laquelle elles doivent
être mises à jour ne dépend pas d'un seul facteur. La fenêtre de maintenance disponible et des
performances système insuffisantes constituent généralement les deux principales raisons pour
lesquelles des problèmes de statistiques de base de données sont traités.
Pour plus d'informations, consultez Statistiques pour SQL Server 2008
(http://msdn.microsoft.com/fr-fr/library/dd535534.aspx).
35
Meilleures pratiques recommandées
Nous recommandons d'appliquer les recommandations suivantes pour les statistiques de la
base de données :
Utilisez les options AUTO CREATE et AUTO UPDATE (synchrones ou asynchrones)
pour les statistiques (valeurs par défaut du système dans SQL Server). L'utilisation de
cette technique réduit au minimum le besoin d'exécuter manuellement des statistiques.
Si vous devez collecter des statistiques manuellement, elles doivent idéalement être
collectées pour toutes les colonnes d'une table. S'il n'est pas possible d'exécuter des
statistiques pour toutes les colonnes, vous devez au moins collecter les statistiques sur
toutes les colonnes utilisées dans la clause WHERE ou HAVING et sur les clés de
jointure. La création d'index crée des statistiques sur la clé d'index, vous n'avez pas à le
faire explicitement.
Les statistiques (à plusieurs colonnes) composites sont essentielles pour de nombreux
scénarios de jointure. Les jointures de dimensions de faits qui impliquent des clés de
jointure composites peuvent générer des plans d'optimisation de boucle imbriquée non
optimaux en l'absence de statistiques composites. Les statistiques automatiques
ne créent pas, n'actualisent pas ou ne remplacent pas de statistiques composites.
Les statistiques qui impliquent une valeur de clé croissante (telle qu'une date dans une
table de faits) doivent être mises à jour manuellement après chaque opération de
chargement incrémentiel. Dans tous les autres cas, les statistiques peuvent être mises
à jour moins souvent. Si vous constatez que l'option AUTO_UPDATE_STATISTICS
n'est pas suffisante pour vous, exécutez les statistiques suivant une planification.
Compression
Les configurations d'entrepôt de données Fast Track sont conçues avec la compression de page
activée. Nous vous recommandons d'utiliser la compression de page sur toutes les tables de faits.
La compression des tables de dimension de petite taille (autrement dit, celles comportant moins
d'un million de lignes) est facultative. Avec des tables de dimension de plus grande taille, il est
souvent judicieux d'utiliser la compression de page. Dans les deux cas, la compression de tables
de dimension doit être évaluée suivant un cas d'usage. La compression de ligne est une option
supplémentaire qui indique les taux de compression raisonnables pour certains types de
données.
La compression de page SQL Server réduit les données dans les tables, les index et les
partitions. Cela réduit la quantité d'espace physique nécessaire pour stocker les tables utilisateur,
et permet à davantage de données de tenir dans le pool de mémoires tampons (la mémoire) SQL
Server. Un des avantages tient dans la réduction du nombre de demandes d'E/S servies à partir
du stockage physique.
Le niveau de compression réelle qui peut être obtenu varie en fonction des données stockées
et de la fréquence de champs de données en double dans les données. Si vos données sont
très aléatoires, les avantages liés à la compression sont très limités. Même dans les meilleures
conditions, l'utilisation de la compression sollicite davantage l'UC pour la compression et la
décompression des données, mais diminue également l'espace disque physique nécessaire
et dans la plupart des cas améliore le temps de réponse aux requêtes en traitant les requêtes
d'E/S dans la mémoire tampon. Habituellement, la compression de page dispose d'un taux de
36
compression (taille d'origine/taille compressée) compris entre 2 et 7:1, 3:1 étant une évaluation
pessimiste classique. Les résultats varient en fonction des caractéristiques de vos données.
Gestion de la fragmentation des données
La fragmentation peut se produire à plusieurs niveaux, tous devant être contrôlés pour conserver
des E/S séquentielles. L'objectif principal d'un entrepôt de données Fast Track est de maintenir
vos données dans l'ordre le plus séquentiel possible tout en limitant la fragmentation sous-jacente.
Si la fragmentation est autorisée, les performances globales du système sont altérées.
La défragmentation périodique est nécessaire. Cependant, les instructions suivantes peuvent
vous aider à réduire le nombre de processus longs de défragmentation.
Fragmentation du système de fichiers
Les blocs de disque par fichier de base de données doivent être maintenus contigus sur le
plateau physique dans le système de fichiers NTFS. La fragmentation à ce niveau peut être
empêchée par préallocation de la taille maximale des fichiers prévue lors de la création.
Les outils de défragmentation du système de fichiers NTFS doivent être évités. Ces outils sont
conçus pour fonctionner au niveau du système d'exploitation et ne tiennent pas compte des
structures des fichiers de données SQL Server internes.
Fragmentation de l'étendue
Dans SQL Server, toutes les pages d'un fichier, quelle que soit l'association de table, peuvent
devenir entrelacées dans la limite de l'ensemble de blocs contigus (2 Mo) ou du niveau de page
(8 Ko) Cela se produit généralement en raison d'opérations DML simultanées, de mises à jour
au niveau des lignes excessives ou de suppressions au niveau des lignes excessives.
Réécrire entièrement la ou les tables en question est la seule méthode permettant de garantir
l'allocation optimale de pages dans un fichier. Il n'existe aucune autre méthode pour résoudre
ce type de fragmentation de base de données. Pour cette raison, il est important de suivre les
instructions de configuration SQL Server et les meilleures pratiques pour le chargement des
données et la gestion des instructions DML.
La requête suivante fournit des informations clés pour évaluer la fragmentation logique d'une
table d'entrepôt de données Fast Track. La mesure la plus élevée est celle de la taille moyenne
des fragments. Cette valeur est un entier qui représente le nombre moyen de pages SQL
Server qui sont regroupées dans des blocs contigus.
SELECT db_name(ps.database_id) as database_name
,object_name(ps.object_id) as table_name
,ps.index_id
,i.name
,cast (ps.avg_fragmentation_in_percent as int) as [Logical Fragmentation]
,cast (ps.avg_page_space_used_in_percent as int) as [Avg Page Space Used]
37
,cast (ps.avg_fragment_size_in_pages as int) as [Avg Fragment Size In Pages]
,ps.fragment_count as [Fragment Count]
,ps.page_count
,(ps.page_count * 8)/1024/1024 as [Size in GB]
FROM sys.dm_db_index_physical_stats (DB_ID() --NULL = All Databases
, OBJECT_ID('$(TABLENAME)')
,1
, NULL
, 'SAMPLED') AS ps
--DETAILED, SAMPLED, NULL = LIMITED
INNER JOIN sys.indexes AS i
on (ps.object_id = i.object_id AND ps.index_id = i.index_id)
WHERE ps.database_id = db_id()
and ps.index_level = 0;
Le tableau suivant fournit des instructions générales pour interpréter les valeurs de taille
moyenne des fragments.
Taille moyenne des
fragments
>400
Statut
Il s'agit d'une valeur idéale et elle peut être
difficile à conserver pour certaines structures
de données.
300-399
Vert
La table fournit de bonnes performances
d'E/S et ne requiert pas la gestion d'une
fragmentation logique.
150-299
Jaune
La fragmentation logique est susceptible
d'altérer l'efficacité des E/S. Une opération
de maintenance est recommandée pour
améliorer le nombre de fragments.
10-149
Rouge
Fragmentation logique sévère. Les requêtes
d'E/S volumineuses sur cette structure vont
provoquer le déplacement de tête de disque
important et réduire l'efficacité globale des
E/S système.
<10
Rouge
Des valeurs de taille moyenne des fragments
aussi faibles indiquent en général que l'option
de démarrage SQL Server –E n'a pas été
définie ou n'est pas reconnue au démarrage.
Tableau 6 : Valeurs de taille moyenne des fragments
38
Idéal
Action
Enfin, il est important de noter que les résultats de taille moyenne des fragments ne doivent pas
être évalués pour les tables ou les partitions de taille inférieure à 500 Mo. Les petites structures
de données n'ont pas assez de pages au total pour obtenir un nombre de fragments très
efficace. En outre, les petites structures de données représentent en général des demandes
relativement petites de données et ont un impact limité sur l'efficacité globale des E/S système.
Les meilleurs résultats sont souvent visibles en gérant uniquement les tables les plus grandes
et les plus fréquemment utilisées dans un environnement d'entrepôt de données.
Fragmentation d'index
Un index peut être dans un ordre physique (page) et logique (index) différent.
N'utilisez pas la commande ALTER INDEX REORGANIZE pour résoudre ce type de fragmentation,
car vous risquez d'inverser les avantages des allocations. Une reconstruction d'index ou
l'utilisation d'INSERT… SELECT pour insérer des données dans une copie de l'index (ce qui
évite un nouveau tri) peut résoudre ce problème. Tout processus ALTER INDEX REBUILD doit
spécifier SORT_IN_TEMPDB=TRUE pour éviter la fragmentation du groupe de fichiers de
destination. La valeur 1 affectée à MAXDOP est idéale, mais peut provoquer des taux de
chargement très lents. Il est possible d'affecter la valeur maximale 8 à MAXDOP dans certains
cas. Pour plus d'informations, consultez la section Chargement des données de ce document.
Plusieurs groupes de fichiers
Des groupes de fichiers distincts peuvent être créés pour réduire la fragmentation logique des
cas d'usage de données volatiles, par exemple :
Des tables ou des index qui sont fréquemment supprimés et recréés (ce qui laisse des
vides dans la structure de stockage, qui sont remplis par d'autres objets).
Des index pour lesquels il n'existe aucun autre choix que de les prendre en charge
comme étant très fragmentés en raison des fractionnements de page, comme dans les
cas où des données incrémentielles qui chevauchent principalement la plage existante
de clé d'index cluster sont souvent chargées.
Des tables de plus petite taille (telles que les tables de dimension) chargées par
incréments relativement rapprochés, qui peuvent être placées dans un groupe de
fichiers volatile pour empêcher les lignes d'être entrelacées avec de grandes transactions
ou tables de faits.
Des bases de données temporaires dont les données sont insérées dans la table de
destination finale.
D'autres tables peuvent être placées dans un groupe de fichiers non volatile. En outre, les tables
de faits volumineuses peuvent également être placées dans des groupes de fichiers distincts.
Chargement des données
L'architecture des composants Fast Track est équilibrée pour les taux d'analyse supérieurs à la
moyenne obtenus avec un accès disque séquentiel. Pour conserver ces taux d'analyse, prenez
soin de vérifier la structure contiguë des données dans le système de fichiers SQL Server.
39
Cette section est divisée en deux approches générales, chargement incrémentiel et migration
des données. Cette aide est spécifique, mais pas réservée, au stockage des données Fast Track.
Pour plus d'informations sur le chargement en masse SQL Server, consultez le Guide des
performances du chargement de données (http://msdn.microsoft.com/frfr/library/dd425070.aspx).
Une autre ressource utile est le guide des meilleures pratiques de chargement de données Fast
Track 3.0. Cette présentation PowerPoint Microsoft est disponible sur le Portail d'entrepôt de
données Fast Track SQL Server (http://msdn.microsoft.com/fr-fr/library/dd425070.aspx). Bien
qu'initialement basé sur SQL Server 2008 R2, ce document reste applicable à SQL
Server 2012.
Chargements incrémentiels
Cette section aborde des scénarios de chargement quotidien courants d'un environnement
d'entrepôt de données. Elle présente les scénarios de chargement d'un ou plusieurs des
attributs suivants :
Petite taille par rapport à la mémoire système disponible
Les opérations de tri de chargement tiennent dans la mémoire disponible
Petite taille par rapport au nombre total de lignes de l'objet de chargement cible
Tenez compte des instructions suivantes lorsque vous chargez des tables d'index cluster et de
segments de mémoire.
Processus de chargement des tables de segments de mémoire
Les insertions en bloc pour les tables de segments de mémoire peuvent être implémentées en
tant que processus en série ou parallèle. Utilisez les conseils suivants :
Pour exécuter le déplacement des données dans la table de segments de mémoire de
destination, utilisez BULK INSERT avec l'option TABLOCK. Si la table permanente finale
est partitionnée, utilisez l'option BATCHSIZE, car le chargement dans une table
partitionnée provoque un tri dans tempdb.
Pour améliorer les performances de chargement lorsque vous importez des jeux de
données volumineux, effectuez plusieurs opérations d'insertion en bloc simultanément
de façon à utiliser le parallélisme dans le processus en bloc.
Processus de chargement d'index cluster
Il existe deux approches générales pour charger des tables d'index cluster avec une
fragmentation minimale des tables.
Option 1
Utilisez BULK INSERT pour charger des données directement dans la table de destination.
Pour de meilleures performances, le jeu complet de données chargées doit tenir dans un tri
en mémoire. Toutes les données chargées peuvent être traitées par une seule opération de
validation à l'aide de la valeur 0 affectée à BATCHSIZE. Ce paramètre empêche les données
40
de plusieurs traitements d'être entrelacées et de générer des fractionnements de pages.
Si vous utilisez cette option, le chargement doit être monothread.
Option 2
Créez une table intermédiaire qui correspond à la structure (partitionnement compris) de la
table de destination :
Exécutez des insertions en bloc multithread ou en série dans la table intermédiaire vide
d'index cluster en utilisant des valeurs de taille modérées et différentes de zéro de façon
à éviter que les tris débordent dans tempdb. Les meilleures performances seront
obtenues avec un certain niveau de parallélisme. L'objectif de cette étape est d'obtenir
des performances ; par conséquent, les fractionnements de pages et la fragmentation
logique induite par les insertions parallèles et/ou simultanées ne sont pas un problème.
Insérez à partir de la table intermédiaire dans la table d'index cluster de destination
à l'aide d'une seule instruction SELECT INSERT… avec la valeur 1 affectée à
MAXDOP. Cette valeur garantit une fragmentation minimale de l'étendue, mais sacrifie
souvent les performances. Les paramètres MAXDOP ayant une valeur maximale de 8
peuvent être utilisés pour augmenter les performances de chargement, mais entraînent une
augmentation de la fragmentation de l'étendue à mesure que le parallélisme augmente.
L'équilibre efficace de ce compromis est évalué au cas par cas.
Option 3
Cette option requiert l'utilisation de deux groupes de fichiers et deux tables ou plus. L'approche
requiert une table d'index cluster partitionnée et convient tout particulièrement pour les tables
qui affichent des niveaux élevés de fragmentation logique dans les partitions les plus récentes
sans activité de changement notable dans les partitions plus anciennes. L'objectif global est de
placer les partitions volatiles dans un groupe de fichiers dédié et faire expirer ou restaurer ces
partitions dans le groupe de fichiers statique lorsqu'elles cessent de recevoir de nouveaux
enregistrements ou des modifications apportées aux enregistrements existants :
41
Créez deux groupes de fichiers, selon l'aide sur l'entrepôt de données Fast Track. Un
groupe de fichiers sera dédié aux partitions volatiles et l'autre aux partitions statiques.
Une partition volatile est une partition dans laquelle plus de 10 % des lignes vont être
modifiées dans le temps. Une partition statique est une partition qui n'est pas volatile.
Créez une table primaire partitionnée d'index cluster dans le groupe de fichiers statique.
Créez une table compatible avec l'une des deux approches générales suivantes :
o Une seule table de segments de mémoire avec une contrainte qui reflète le
schéma de partition de la table primaire. Cette contrainte doit représenter la
plage volatile du jeu de données primaire et peut couvrir une ou plusieurs plages
de partition du schéma de table primaire. Cela est particulièrement utile si les
performances de chargement initiales constituent le principal critère de décision,
car les chargements dans un segment de mémoire sont généralement plus
efficaces que les chargements dans un index cluster.
o Une seule table d'index cluster avec un schéma de partition qui est compatible
avec la partition de table primaire. Cela permet des insertions directes avec un
faible degré de parallélisme (DOP) dans la table primaire à mesure que les
partitions volatiles deviennent obsolètes. Une fois qu'elles sont devenues obsolètes
via insertion dans la table primaire, les partitions sont supprimées et de nouvelles
plages sont ajoutées.
Créez une vue qui unit les deux tables. Elle représente la combinaison des deux tables
comme un objet unique du point de vue de l'utilisateur.
Une fois que les plages de données volatiles sont statiques du point de vue des
données modifiées, utilisez un processus de vieillissement approprié, tel que le
basculement de partition :
o Si une table de segments de mémoire avec contrainte est utilisée, déplacez les
données par plage de partition dans le groupe de fichiers statique via insertion
dans la table intermédiaire. Utilisez CREATE INDEX et le basculement de
partition pour déplacer les données dans la table primaire. Pour plus d'informations
sur ce type d'opération des configurations d'entrepôt de données Fast Track,
consultez la section Migration des données de ce document.
o Si un index cluster partitionné est utilisé, utilisez un degré de parallélisme
inférieur ou égal à 8. Ensuite, utilisez l'instruction INSERT limitée par plage de
partition directement dans la table primaire. Vous devrez peut-être définir le
degré de parallélisme à 1 pour éviter la fragmentation selon la concurrence
globale du système.
Migration des données
Cela englobe les scénarios à grande échelle de chargement unique ou peu fréquent dans un
environnement d'entrepôt de données. Ces situations peuvent se produire lors de la migration
de la plateforme ou du chargement des données de test pour l'évaluation du système. Cette
rubrique présente les scénarios de chargement d'un ou plusieurs des attributs suivants :
Opérations de chargement qui dépassent la mémoire système disponible
Opérations de chargement de grande taille à forte concurrence, qui sollicitent la
mémoire disponible
Processus de chargement des tables de segments de mémoire
Suivez les instructions fournies plus haut pour le traitement de la charge incrémentielle.
Processus de chargement d'index cluster
Il existe plusieurs approches générales pour charger des tables d'index cluster avec une
fragmentation minimale des tables.
Option 1
Utilisez BULK INSERT pour charger des données directement dans une table cible d'index
cluster. Les opérations de tri et la taille totale de validation doivent tenir en mémoire pour
obtenir de meilleures performances. Veillez à ce que les lots distincts de données chargés
n'aient pas de plages de clés d'index qui se chevauchent.
Option 2
Exécutez une insertion en bloc multithread ou en série dans une table vide intermédiaire d'index
cluster de structure identique. Utilisez une taille de lot modérée et différente de zéro pour
42
conserver les tris en mémoire. Ensuite, insérez des données dans une table d'index cluster vide
à l'aide d'une seule instruction SELECT INSERT… avec la valeur 1 affectée à MAXDOP.
Option 3
Utilisez des insertions en bloc multithread dans une table intermédiaire de segments de partition
conforme, avec des valeurs de taille de lot modérées et différentes de zéro pour conserver les
tris en mémoire. Ensuite, utilisez des instructions SELECT INSERT en série ou parallèles
couvrant chaque plage de partition pour insérer des données dans la table d'index cluster.
Option 4
Utilisez les opérations de basculement de partition dans un processus en plusieurs étapes qui
fournit généralement des résultats optimaux pour les opérations de chargement. Cette méthode
rend plus complexe le processus global et est conçue pour illustrer une approche optimale pour
les performances de chargement brutes. L'objectif principal de cette approche consiste à activer
l'activité d'écriture parallèle dans toutes les phases de l'opération d'insertion dans l'index cluster
sans introduire de fragmentation logique. Cela est possible en créant la table intermédiaire dans
plusieurs groupes de fichiers avant d'insérer des données dans la table de destination finale.
1. Identifiez le schéma de partition de la table d'index cluster de destination finale.
2. Créez un groupe de fichiers d'étape.
3. Créez une table intermédiaire « de base » de segments de mémoire non compressée et
non partitionnée dans le groupe de fichiers d'étape.
4. Insérez en bloc des données en utilisant WITH TABLOCK dans la table intermédiaire de
base. Plusieurs opérations de copie en bloc parallèle constituent la méthode la plus
efficace si plusieurs fichiers sources sont une option. Le nombre d'opérations de
chargement parallèle pour atteindre le débit maximal dépend des ressources du serveur
(UC et mémoire) et des données chargées.
5. Identifiez le nombre de groupes de fichiers primaires à prendre en charge. Ce nombre
doit être un multiple du nombre total de partitions dans la table de destination. Il représente
également le nombre d'opérations INSERT et CREATE INDEX à exécuter simultanément
dans les étapes ultérieures. Par exemple, pour une table avec 24 partitions et un serveur
avec huit cœurs, une base de données avec huit groupes de fichiers primaires est
indiquée. Cette configuration permet l'exécution de huit insertions parallèles aux étapes
suivantes, une pour chacun des huit groupes de fichiers primaires et cœur d'UC.
Chaque groupe de fichiers, dans ce cas, contient trois plages de partition de données.
6. Créez le nombre de groupes de fichiers primaires comme déterminé précédemment.
7. Créez une table temporaire de segments de mémoire dans chaque groupe de fichiers
primaire pour chaque plage de partition, sans compression. Créez une contrainte sur la
table intermédiaire correspondant à la plage de partition correspondante de la table de
destination. Dans l'exemple donné précédemment, il y aura trois tables intermédiaires
par groupe de fichiers primaire créé au cours de cette étape.
8. Créez la table d'index cluster partitionnée de destination avec compression de page. Cette
table doit être partitionnée dans tous les groupes de fichiers primaires. Les partitions doivent
s'aligner avec les plages de contrainte de table intermédiaire de segments de mémoire.
43
44
9. Exécutez une instruction INSERT ou SELECT de la table intermédiaire de base dans les
tables de groupes de fichiers intermédiaires pour chaque groupe de fichiers primaire. Cette
opération doit être effectuée en parallèle. Assurez-vous que le prédicat pour l'instruction
INSERT ou SELECT correspond aux plages de partition correspondantes. N'exécutez
jamais simultanément plusieurs instructions INSERT ou SELECT par groupe de fichiers.
10. Exécutez une commande CREATE CLUSTERED INDEX avec compression de page par
groupe de fichiers pour les tables intermédiaires nouvellement remplies. Cela peut être
accompli en parallèle mais jamais avec un degré de parallélisme supérieur à 8. N'exécutez
jamais simultanément plusieurs opérations CREATE INDEX par groupe de fichiers.
Veillez à utiliser l'option SORT_IN_TEMPDB lorsque vous effectuez une opération
CREATE INDEX de façon à éviter de réduire les groupes de fichiers primaires en
fragments. Le nombre optimal d'opérations CREATE INDEX dépend de la taille du
serveur, de la mémoire et des données elles-mêmes. Essayez d'obtenir une utilisation
élevée du processeur pour l'ensemble des cœurs sans surutilisation (85 à 90 %
d'utilisation totale).
11. Exécutez des opérations de basculement de partition en série des tables intermédiaires
dans la table de destination. Cela est également possible à la fin de chaque opération
temporaire CREATE INDEX.
Tests d'évaluation et validation
Cette section fournit une description de base des processus utilisés pour créer et qualifier les
architectures de référence d'entrepôt de données Fast Track SQL Server. L'objectif de la mise
à disposition de ces informations consiste à prendre en charge les architectures de référence
définies par l'utilisateur ou personnalisées basées sur la méthodologie d'entrepôt de données
Fast Track. Pour les tests d'évaluation, le dépannage ou la vérification des architectures de
référence des partenaires publiées et prévalidées, contactez le partenaire de publication
(HP, Dell, EMC, IBM, Cisco, etc.).
Le processus de validation d'entrepôt de données Fast Track peut être divisé en deux
catégories décrites ici.
Validation du matériel de base
L'objectif de la validation du matériel consiste à établir des mesures de performances réelles
plutôt que des évaluations pour les principaux composants matériels de l'architecture de
référence Fast Track. Ce processus détermine les caractéristiques de base des performances
réelles des principaux composants matériels de la pile de base de données.
45
Validation de base de données Fast Track
L'établissement des caractéristiques de performances SQL Server, selon une charge de travail
d'entrepôt de données, permet de comparer les hypothèses de performances fournies par le
processus d'évaluation du matériel de référence. En général, les mesures de débit de charge
de travail de base de données doivent refléter au moins 80 % des taux de base pour les
architectures de référence Fast Track validées. Les mesures de performances calculées dans
ce processus servent de base pour les valeurs de performances d'entrepôt de données Fast
Track publiées et reposent sur des charges de travail simultanées de requête SQL exécutées
au moyen de l'outil d'évaluation Reference Point Fast Track.
Reference Point est un outil logiciel Microsoft distribué aux partenaires fournisseurs de matériel
Fast Track et constitue l'unique infrastructure dans laquelle une architecture de référence Fast
Track officielle peut être validée et approuvée par Microsoft. L'outil instancie un schéma de base
de données de référence et plusieurs charges de travail simultanées de requête conçues pour
identifier les goulots d'étranglement et établir les mesures de performances clés du système.
Validation Fast Track avec des index columnstore optimisés en mémoire xVelocity
SQL Server 2012 implémente la technologie relative aux index columnstore en tant qu'option
d'indexation non cluster des tables préexistantes. Les requêtes individuelles peuvent ou non
utiliser des plans d'optimisation d'index columnstore selon la structure des requêtes. Cela signifie
que la combinaison de plans de requête de ligne traditionnels et de nouveaux plans de requête
de colonne pour un environnement d'entrepôt de données Fast Track à un moment donné n'est
pas prédictible.
C'est pourquoi, l'Entrepôt de données Fast Track pour la conception et la validation du système
SQL Server 2012 repose sur des tests d'évaluation d'index non columnstore. Les systèmes
d'entrepôt de données Fast Track sont conçus pour fonctionner efficacement dans le cas où
aucune optimisation de colonnes n'est effectuée pour une période donnée. Des gains de
performances significatifs sont souvent réalisés lorsque les plans de requête d'index columnstore
sont actifs et ces performances peuvent être considérées comme incrémentielles à la création
du système de base.
Les architectures de référence validées par les partenaires Fast-Track pour SQL Server 2012
publient une évaluation du débit logique supplémentaire pour les tests d'évaluation d'index
columnstore avancés. Ces chiffres peuvent être utilisés pour évaluer l'impact positif sur les
performances des requêtes auquel les clients peuvent s'attendre dans le contexte de charges
de travail de requêtes simultanées. Ces chiffres sont basés sur les mêmes tests d'évaluation et
schéma d'entrepôt de données Fast Track que ceux utilisés pour toutes les validations du système.
Effectuer la validation d'entrepôt de données Fast Track de référence
La validation de référence est effectuée au niveau du système d'exploitation à l'aide d'un outil
tel que SQLIO. Le test des applications SQL Server n'est pas effectué au cours de cette phase,
et tous les tests reposent sur les scénarios synthétiques les plus optimistes. L'objectif est de
s'assurer que la configuration du matériel et du système d'exploitation est appropriée et fournit
les résultats attendus conformément aux tests d'évaluation de la conception et du développement.
46
L'Analyseur de performances et fiabilité Windows Server (également appelé perfmon) peut être
utilisé pour le suivi, la journalisation et la création de rapports sur les performances d'E/S. Un
outil tel que SQLIO peut être utilisé pour tester la bande passante d'E/S. Pour plus d'informations
sur SQLIO, notamment les instructions et les sites de téléchargement, consultez le livre blanc
SQLCAT Predeployment I/O Best Practices (http://sqlcat.com/sqlcat/b/whitepapers/archive/
2007/11/21/predeployment-i-o-best-practices.aspx).
Les composants et les processus de validation suivants sont utilisés pour générer des tests
d'évaluation du matériel de référence.
Tests d'évaluation de référence avec SQLIO
L'utilisation de SQLIO est décrite plus en détail dans l'article sur les meilleures pratiques.
Les tests de lecture sont généralement au format suivant :
sqlio –kR –fSequential -s30 -o120 -b512 d:\iobw.tst –t1
Dans ce cas, R indique un test de lecture, 30 est la durée du test en secondes, 120 est le
nombre de demandes en attente effectuées, 512 est la taille des blocs en kilo-octets de
demandes effectuées, d:\iobw.tst est l'emplacement du fichier de test, 1 est le nombre de threads.
Pour tester des scénarios d'agrégation de la bande passante, plusieurs tests SQLIO doivent
être effectués en parallèle. Une seule instance de SQLIO doit être utilisée pour chaque point
de montage primaire de données (volume disque). La parallélisation des instances SQLIO
peut être réalisée à l'aide de Windows PowerShell ou d'autres méthodes de script. Pour les
architectures de référence validées par le partenaire d'entrepôt de données Fast Track,
des scripts de validation d'E/S de référence peuvent être disponibles auprès du partenaire.
L'article consacré aux meilleures pratiques de prédéploiement traite également du suivi des
tests à l'aide de l'Analyseur de performances et fiabilité Windows Server. L'enregistrement
et le stockage des résultats de ces tests vous donnera une ligne de base pour une future
analyse des performances et la résolution des problèmes.
Étape 1 - Valider la bande passante d'E/S
La première étape de validation d'une configuration d'entrepôt de données Fast Track consiste
à déterminer le débit d'agrégation maximal qui peut être obtenu entre le réseau d'E/S de
stockage et le serveur. Cela implique de supprimer le disque comme goulet d'étranglement et
cibler les composants autres que ceux du disque (autrement dit, les adaptateurs HBA, l'infrastructure
de basculement et les contrôleurs de groupe). Pour effectuer cette tâche avec SQLIO,
procédez comme suit :
1. Générez un petit fichier de données sur chaque numéro d'unité logique à utiliser pour
les fichiers de base de données. Ces fichiers doivent être d'une taille permettant à tous
les fichiers de données de tenir dans le cache de lecture sur les contrôleurs de groupe
(par exemple, 50 Mo par fichier).
47
2. Utilisez SQLIO pour effectuer simultanément des lectures séquentielles sur le fichier
à l'aide de tailles d'E/S de grand bloc (512 Ko) et au moins deux threads de lecture par
fichier. Veillez à calculer les lectures en attente globales. Par exemple, 2 threads de
lecture avec 50 requêtes en attente comptent pour 100 requêtes en attente au total dans
le numéro d'unité logique cible.
3. Démarrez avec une valeur relativement basse d'E/S en attente (-o) et répétez les tests
en augmentant cette valeur jusqu'à ce qu'il n'y ait plus de gain de débit d'agrégation.
L'objectif de ce test est d'optimiser le débit global raisonnable par rapport aux limites théoriques
des composants dans le chemin d'accès entre le serveur et le stockage. Ce test valide la bande
passante entre le serveur et les processeurs de stockage SAN ; c'est-à-dire les chemins
d'accès Multi-Path Fibre Channel.
Étape 2 - Valider la bande passante LUN/volume
Ce test est semblable au test précédent. Toutefois, un fichier de plus grande taille est utilisé
pour supprimer les avantages potentiels du cache des baies du cache du contrôleur. Ces
fichiers de test doivent être suffisamment volumineux pour simuler la taille du fichier de base de
données cible par volume, par exemple, 25 Go par volume. Des paramètres similaires doivent
être utilisés pour SQLIO, tel que le décrit l'étape 1.
Des lectures séquentielles de grand bloc (512 Ko) doivent être effectuées dans les fichiers de
test sur chaque volume. Nous vous recommandons d'utiliser un seul thread par fichier avec une
profondeur de demande en attente comprise entre 4 et 16 (commencez petit et augmentez
jusqu'à ce que le débit maximal soit atteint). Dans un premier temps, testez individuellement
chaque volume, puis testez les deux simultanément. Le débit du groupe de disques varie selon
le fournisseur et la configuration de stockage. Cependant, la comparaison peut toujours être
réalisée avec les taux de lecture d'un seul HDD. Un groupe de 4 disques RAID1+0, par
exemple, peut atteindre un taux de lecture de pointe près de quatre fois supérieur au taux de
lecture d'un seul HDD pour ce type de modèle de lecture de base. Les performances de niveau
RAID 1 ou 1+0 varient selon le produit de stockage, car certaines technologies des fournisseurs
permettent la « lecture en miroir », ce qui permet aux E/S d'être traitées des deux côtés de la
paire en miroir lors de la réception de requêtes contiguës.
Étape 3 - Valider la bande passante globale
Dans ce test, les lectures séquentielles doivent être effectuées sur tous les volumes de
données disponibles simultanément dans les mêmes fichiers que ceux utilisés à l'étape 2.
SQLIO doit être exécuté avec deux threads par fichier de test, avec une taille d'E/S de 512 Ko
et un nombre optimal d'E/S en attente comme déterminé par le test précédent.
Les résultats de ce test illustrent le débit d'agrégation maximal pouvant être obtenu lors de la
lecture des données à partir de disques physiques.
Les données sont lues dans le fichier de données volumineux, comme dans le test précédent,
sur chaque volume simultanément.
Les performances globales du disque doivent se situer entre 80 et 90 % de la bande passante
globale d'E/S de stockage, pour les systèmes d'entrepôt de données Fast Track équilibrés.
48
Évaluation des composants
Le diagramme suivant illustre les résultats des tests d'évaluation synthétiques cohérents avec
les valeurs constatées dans les architectures de référence Fast Track similaires.
Illustration 4 : Exemple de test d'évaluation synthétique de bande passante pour un serveur
à 2 sockets, 12 cœurs et 3 adaptateurs HBA double port de 8 Gbit/s, et 4 disques RAID1+0 de
12 volumes de données primaires
Résumé
Les tests de matériel de référence valident la bande passante réelle des principaux composants
matériels de la pile de base de données. Cette opération s'effectue avec une série de tests
synthétiques les plus optimistes réalisés à l'aide d'un outil comme SQLIO.
49
Effectuer un test d'évaluation de base de données Fast Track
Cette phase d'évaluation de l'architecture de référence Fast Track mesure les performances
SQL Server pour la charge de travail d'entrepôt de données Fast Track selon deux mesures
principales. La première, le taux de consommation maximal d'UC (MCR), mesure le débit de
pointe du traitement des E/S. La deuxième, le taux de consommation d'UC du test d'évaluation
(BCR), mesure le débit réel du traitement des E/S pour une requête ou une charge de travail
basée sur la requête.
Qu'est-ce que la mesure MCR ?
Le calcul MCR fournit une valeur de débit d'E/S par cœur en Mo ou en Go par seconde. Cette
valeur est mesurée en exécutant une requête non optimisée, en lecture seule et prédéfinie dans
le cache des tampons et en mesurant le temps nécessaire pour l'exécution par rapport à la
quantité de données en Mo ou en Go. Étant donné que MCR est exécuté à partir du cache,
il représente la fréquence d'analyse non optimisée de pointe pouvant être obtenue avec SQL
Server pour le système évalué. Pour cette raison, MCR fournit un taux de pointe de référence
pour la création initiale. Il n'est pas conçu pour afficher les résultats moyens ou attendus d'une
charge de travail réelle. Les architectures d'entrepôt de données validées obtiendront des résultats
d'agrégation de débit d'E/S de référence au moins équivalents à 100 % du MCR évalué pour le
serveur. Une autre façon permettant d'expliquer cela est que le MCR représente le taux de
traitement SQL Server le meilleur possible pour une charge de travail pessimiste raisonnable.
Le MCR peut également être utilisé comme cadre de référence lors de la comparaison d'autres
architectures de référence d'entrepôt de données Fast Track publiées et validées pour
SQL Server 2012.
En bref :
50
Le MCR ne correspond pas à un résultat réel définitif pour une charge de travail d'un
client.
Le MCR fournit une référence de taux de traitement des données maximal pour
SQL Server et une seule requête associée à la charge de travail Fast Track.
Le MCR est spécifique à une UC et à un serveur. En général, les taux pour une UC
donnée ne varient pas beaucoup par architecture de serveur et de carte mère, mais le
MCR final doit être déterminé par le test réel.
L'évaluation du débit MCR peut être utilisée comme valeur comparative par rapport aux
architectures de référence d'entrepôt de données Fast Track publiées existantes. Cela
peut aider à sélectionner le matériel avant le test des composants et des applications.
Calcul du MCR
Un taux de consommation d'UC de référence pour l'application SQL Server est établi en
exécutant une requête SQL standard définie pour le programme Entrepôt de données Fast Track.
Cette requête est conçue pour être une représentation relativement simple d'une requête
standard pour le type de charge de travail (dans ce cas, un entrepôt de données) et est exécutée
dans le cache de tampons. La valeur obtenue est spécifique à l'UC et au serveur par rapport
auxquels la requête est exécutée. Utilisez la méthode suivante pour calculer le MCR :
1. Créez un dataset de référence basé sur la table lineitem TPC-H ou sur un jeu de
données similaire. La table doit être d'une taille pouvant être entièrement mise en cache
dans le pool de mémoires tampons SQL Server, mais conservez toujours une durée
d'exécution minimale d'une seconde pour la requête spécifiée ici.
2. Pour l'entrepôt de données Fast Track, la requête suivante est utilisée : SELECT
sum([champ de nombres entiers]) FROM [table] WHERE [limiter au volume de données
approprié] GROUP BY [col].
3. L'environnement doit :
o Garantir que les paramètres du Gouverneur de ressources sont les valeurs
par défaut.
o Garantir que la requête s'exécute à partir du cache de tampons. Exécuter la
requête une fois doit mettre les pages en mémoire tampon, et les exécutions
suivantes doivent lire intégralement la mémoire tampon. Vérifiez qu'il n'y a
aucune lectures physiques dans la sortie de statistiques sur les requêtes.
o Affecter la valeur ON à STATISTICS IO et STATISTICS TIME pour générer
le résultat.
4. Exécuter la requête plusieurs fois, avec MAXDOP = 4.
5. Enregistrer le nombre de lectures logiques et le temps processeur de la sortie de
statistiques à chaque exécution de la requête.
6. Calculer le MCR en Mo/s à l'aide de la formule suivante :
([Lectures logiques] / [temps processeur en secondes)] * 8 Ko / 1024
7. Une plage de valeurs cohérentes (+/- 5 %) doit apparaître sur un minimum de cinq
exécutions de la requête. Des valeurs hors norme importantes (+/- 20 % ou plus)
peuvent indiquer des problèmes de configuration. La moyenne d'au moins 5 résultats
calculés correspond au MCR d'entrepôt de données Fast Track.
51
Selon le calcul MCR, un schéma de débit de l'architecture des composants peut être construit.
Pour les besoins d'évaluation du MCR système, le débit des composants repose sur la bande
passante évaluée par le fournisseur. Ce schéma peut être utile pour la création, la sélection du
système et l'analyse des goulets d'étranglement. L'illustration 5 constitue un exemple :
Illustration 5 : Exemple de taux de consommation processeur maximal (MCR) et de bande
passante composant évaluée pour un serveur à 2 sockets, 12 cœurs sur UC Intel Westmere
Pour plus d'informations sur la mesure du MCR, consultez Test de charge de travail dans l'annexe.
Calcul du BCR
Un taux de consommation d'UC de test pour l'application SQL Server est établi en exécutant un
ensemble de base de requêtes SQL, sous un niveau approprié de concurrence, qui sont spécifiques
à votre charge de travail d'entrepôt de données. Le nombre de requêtes et le niveau de concurrence
utilisés dépendent entièrement du cas d'usage prévu. La charge de travail de requête doit être
traitée à partir du disque, et non pas à partir du pool de mémoires tampons SQL Server comme
avec MCR. La valeur obtenue est spécifique à l'UC, au serveur et à la charge de travail par
rapport auxquels elle est exécutée. L'entrée d'annexe Test de charge de travail fournit un
exemple plus détaillé de création d'un test d'évaluation de charge de travail BCR.
52
Utilisez la méthode suivante pour calculer le BCR :
1. Créez un dataset de référence qui contient au moins une table. La table doit être de
taille suffisamment importante de façon à ce qu'elle ne soit pas entièrement mise en
cache dans le cache de pool de mémoires tampons SQL Server ou dans le cache des
baies SAN. En l'absence de données client, un dataset synthétique peut être utilisé.
Il est important de tenter de se rapprocher des caractéristiques attendues des données
pour le cas d'usage ciblé.
2. Le format d'une requête de base pour un entrepôt de données Fast Track est le suivant :
SELECT sum([champ de nombres entiers]) FROM [table] WHERE [limiter au volume de
données approprié] GROUP BY [col]. Cette requête peut être utilisée comme point de
départ pour la conception de charge de travail de requête si les requêtes des clients ne
sont pas aisément disponibles. TPC-H est un autre test d'évaluation de requête utilisé
communément qui peut servir d'ensemble de requêtes de référence.
3. Pour effectuer un test d'évaluation de client d'entrepôt de données Fast Track, il est
préférable de choisir des requêtes représentatives de la charge de travail cible. Les
requêtes doivent être planifiées dans plusieurs sessions simultanées représentatives
de l'activité historique maximale ou prévue pour l'environnement du client. Les critères
suivants peuvent être pris en compte dans la sélection des requêtes :
Représente les spécifications de charge de travail cible moyenne. Cela peut
impliquer l'augmentation ou la diminution de la complexité du formulaire de
requête de base, l'ajout de jointures et/ou l'abandon de plus ou moins de
données via projection et restriction.
La requête ne doit pas provoquer des écritures de données dans tempdb,
sauf si cette caractéristique est une partie critique de la charge de travail cible.
La requête doit retourner un nombre minimal de lignes. L'option SET ROWCOUNT
peut être utilisée pour gérer cela. Une valeur ROWCOUNT supérieure à 100 doit
être utilisée (105 est une valeur standard pour les tests Fast Track). Utilisez
également l'agrégation pour réduire les enregistrements retournés dans les
analyses volumineuses illimitées.
4. L'environnement doit :
Garantir que les paramètres du Gouverneur de ressources sont les valeurs par défaut.
Garantir que les caches sont vidés avant d'exécuter la requête, en utilisant DBBC
dropcleanbuffers.
Affecter la valeur ON à STATISTICS IO et STATISTICS TIME pour générer le résultat.
5. Exécuter la requête ou la charge de travail plusieurs fois, en commençant avec
MAXDOP 8. Chaque fois que vous exécutez la requête, augmentez la valeur du
paramètre MAXDOP pour la requête, en vidant les caches avant chaque exécution.
Enregistrer le nombre de lectures logiques et le temps processeur de la sortie
de statistiques.
Calculer le BCR en Mo/s à l'aide de la formule suivante :
([Lectures logiques] / [temps processeur en secondes)] * 8 Ko / 1024
Cela vous donne une plage pour le BCR. Si plusieurs requêtes sont utilisées,
utilisez une moyenne mobile pondérée pour déterminer le BCR.
53
Résultats du BCR
La figure 6 indique les résultats des tests d'évaluation basés sur des charges de travail
SQL Server qui sont cohérents avec les valeurs constatées dans les architectures de référence
d'entrepôt de données Fast Track similaires.
Illustration 6 : Exemple de test d'évaluation synthétique de bande passante pour un serveur
à 2 sockets, 12 cœurs et 3 adaptateurs HBA double port de 8 Gbit/s, et 4 disques RAID1+0 de
12 LUN de données primaires
54
Interprétation du BCR
Si votre BCR de requête moyenne est bien inférieur au MCR évalué pour l'architecture de
référence Fast Track, vous êtes susceptible d'être lié à l'UC. En réponse, pensez à réduire
le volume de stockage, par exemple en réduisant le nombre de baies, à introduire d'autres
disques par baie, ou à augmenter la taille des disques. Ces étapes peuvent aider à réduire le
coût de l'infrastructure de stockage à un niveau d'état d'équilibre. Pensez également à un plus
grand nombre de sockets sur le serveur ou à des UC à performances de niveau supérieur qui
peuvent tirer parti de l'excédent du débit d'E/S de stockage. Dans les deux cas, l'objectif est
d'équilibrer les fonctions de traitement de la base de données avec le débit d'E/S de stockage.
En outre, si votre BCR est supérieur au MCR, vous pouvez avoir besoin de plus de débit d'E/S
pour traiter une charge de travail de requête de manière équilibrée.
Architectures de référence d'entrepôt de données Fast Track publiées
Les spécifications détaillées d'architecture de référence du matériel sont disponibles auprès
de chaque partenaire Entrepôt de données Fast Track participant. Pour plus d'informations,
y compris des liens vers chaque partenaire, consultez Entreposage de données Fast Track
(http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/fasttrack.aspx).
Capacité d'entrepôt de données Fast Track évaluée par quantité estimée de fichiers de données
utilisateur non compressés qui peuvent être chargés dans la base de données. On parle de
capacité pour les données utilisateur (UDC). Ce calcul suppose que la compression de page est
activée pour toutes les tables et les volumes de données sont mis en miroir. Un taux moyen de
compression de 3.5:1 est utilisé. En outre, au plus 30 % de la capacité non compressée est
allouée à tempdb avant de calculer l'UDC. Notez que pour de plus grandes configurations avec
une capacité totale supérieure, ce taux est réduit à 20 %.
Pour plus d'informations sur la taille de tempdb, consultez Planification des capacités pour
tempdb (http://msdn.microsoft.com/fr-fr/library/ms345368.aspx).
Conclusion
L'entrepôt de données Fast Track SQL Server offre un modèle et des outils pour faire passer
un entrepôt de données de la conception au déploiement. Ce document décrit la méthodologie,
les options de configuration, les meilleures pratiques, les configurations de référence et les
techniques d'évaluation et de validation pour un entrepôt de données Fast Track.
Pour plus d'informations :
Site Web SQL Server
Site Web SQL Server Fast Track
SQL Server TechCenter
55
Ressources en ligne SQL Server
10 meilleures pratiques pour générer des entrepôts de données relationnelles à grande échelle
(équipe SQLCAT)
Procédure : activer l'option Verrouiller les pages en mémoire (Windows)
Options de paramétrage pour SQL Server 2005 et SQL Server 2008 pour des charges de travail
haute performance
Procédure : configurer SQL Server pour utiliser soft-NUMA
Initialisation des fichiers de base de données
Procédure : afficher ou modifier le mode de récupération d'une base de données (SQL Server
Management Studio)
Surveillance de l'utilisation de la mémoire
Dépannage des problèmes de réseau SAN
Installation et configuration de MPIO
Livre blanc SQL Server 2000 I/O Basics (en anglais)
Compression de données : Stratégie, planification des capacités et meilleures pratiques
Avez-vous trouvé ce document utile ? Nous apprécions vos commentaires. Sur une échelle
de 1 (faible) à 5 (excellent), quelle note donneriez-vous à ce document ? Expliquez pourquoi.
Par exemple :
Avez-vous attribué une bonne note car le document fournit de bons exemples, contient
des captures d'écran très utiles, est clairement rédigé, ou pour d'autres raisons ?
Avez-vous attribué une mauvaise note car le document fournit de mauvais exemples,
contient des captures d'écran pas claires ou est mal rédigé ?
Vos commentaires nous aident à améliorer la qualité des livres blancs que nous publions.
Envoyez vos commentaires.
56
Annexe
FTDW System Sizing Tool
L'outil de dimensionnement d'entrepôt de données Fast Track (System Sizing Tool) est un
tableur Excel qui vous aide lors du calcul de spécifications de charge de travail du client en
termes de débit du système d'entrepôt de données Fast Track. Utilisez-le en l'absence de
plateforme de test ou comme point de départ lors de l'évaluation des exigences du client.
Cet outil est disponible sur le site Entreposage de données Fast Track
(http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/fasttrack.aspx). De plus, certains fournisseurs partenaires ont créé leurs propres outils de
dimensionnement Fast Track conformes. Ceux-ci se trouvent sur les sites Web des partenaires.
Validation d'une architecture de référence Fast Track définie par l'utilisateur
Test d'E/S synthétiques
SQLIO est un outil fourni par Microsoft disponible au téléchargement qui permet de tester le
sous-système d'E/S indépendamment de SQL Server.
Génération des fichiers de test avec SQLIO
Lorsque vous exécutez SQLIO, il crée un fichier approprié de test, si ce fichier n'existe pas.
Pour générer un fichier de taille spécifique, utilisez le paramètre –F. Par exemple, utilisez un
fichier de paramètres (param.txt) qui contient les éléments suivants :
C:\stor\pri\1\iobw.tst 1 0x0 50
L'exécution de SQLIO avec le paramètre –F génère un fichier de 50 Mo à la première exécution :
Eq sqlio -kW -s60 -fsequential -o1 -b64 -LS -Fparam.txt
Ce processus peut prendre du temps pour les fichiers volumineux. Créez un fichier sur chaque
disque de données sur lequel vous allez héberger des fichiers de données SQL Server et tempdb.
Pour y parvenir, ajoutez plusieurs lignes dans le fichier de paramètres, ce qui va créer les
fichiers requis un par un. Pour créer des fichiers en parallèle, créez plusieurs fichiers de
paramètres et exécutez plusieurs sessions SQLIO simultanément.
Valider la bande passante de stockage (du cache)
L'utilisation d'un petit fichier de test avec une durée de lecture de plusieurs minutes garantit que
le fichier réside entièrement dans le cache des baies de stockage. L'illustration 7 montre le
compteur Disque logique > Octets lus/s sur les disques dans un exemple de système Fast
Track avec des nombres de requêtes en attente et une taille de bloc différents. Les tests
doivent s'exécuter pendant au moins quelques minutes pour garantir des performances
cohérentes. L'illustration montre que des performances optimales exigent une file d'attente
de demandes en attente composée d'au moins quatre demandes par fichier. Chaque disque
individuel doit contribuer à la bande passante totale.
57
Illustration 7 : compteur Disque logique > Octets lus/s
Valider la bande passante LUN/Volume (sur disque)
Ces tests garantissent que tous les modules de disques présentés à Windows par les sections
de disque sont en mesure de contribuer à la bande passante d'agrégation globale, en lisant
chaque volume, un par un. Vous constaterez peut-être que certains numéros d'unité logique
semblent être légèrement plus rapides que d'autres. Cela n'est pas inhabituel, mais des
différences supérieures à 15 % doivent être analysées.
Illustration 8 : Validation de la bande passante de la paire LUN/volume et RAID
58
Exécutez des tests simultanés sur un ou plusieurs volumes qui partagent le même groupe de
disques. L'illustration suivante montre la sortie des tests sur 8 groupes de disques.
Illustration 9 : Test des LUN qui partagent des groupes de disques
Valider la bande passante d'agrégation (sur disque)
Le test suivant montre l'impact de l'augmentation pas à pas du débit d'E/S, en ajoutant un volume
dans le test à intervalles réguliers. État donné que chaque test s'exécute pour un intervalle défini,
vous voyez une étape vers le bas. Vous devez observer un modèle semblable. La bande passante
globale maximale du disque doit avoisiner 80 à 90 % de la bande passante illustrée à partir du
cache au cours de la première étape. Le graphique affiche le test à plusieurs tailles de bloc de
512 Ko et 64 Ko.
Illustration 10 : Bande passante globale à plusieurs tailles de bloc
59
Test de la charge de travail
Mesure du MCR pour votre serveur (facultatif)
L'objectif du MCR est d'évaluer le débit maximal d'un seul cœur d'UC, qui exécute SQL Server,
en l'absence de problèmes de goulet d'étranglement d'E/S. Le MCR est évalué par cœur.
Si vous choisissez de calculer cette valeur pour votre propre serveur, d'autres informations
décrivant la méthodologie de calcul du MCR sont fournies ci-après :
1. Créez un dataset de référence basé sur la table lineitem TPC-H ou sur un jeu de
données similaire. La table doit être d'une taille pouvant être entièrement mise en cache
dans le pool de mémoires tampons SQL Server, mais conservez toujours une durée
d'exécution minimale de deux secondes pour la requête spécifiée ici.
2. Pour l'entrepôt de données Fast Track, la requête suivante est utilisée : SELECT
sum([champ de nombres entiers]) FROM [table] WHERE [limiter au volume de données
approprié] GROUP BY [col].
3. L'environnement doit :
Garantir que les paramètres du Gouverneur de ressources sont les valeurs par défaut.
Garantir que la requête s'exécute à partir du cache de tampons. Exécuter la
requête une fois doit mettre les pages en mémoire tampon, et les exécutions
suivantes doivent lire intégralement la mémoire tampon. Vérifiez qu'il n'y
a aucune lectures physiques dans la sortie de statistiques sur les requêtes.
Affecter la valeur ON à STATISTICS IO et STATISTICS TIME pour générer
le résultat.
4. Exécuter la requête plusieurs fois, avec MAXDOP = 4.
Enregistrer le nombre de lectures logiques et le temps processeur de la sortie de
statistiques à chaque exécution de la requête.
Calculer le MCR en Mo/s à l'aide de la formule suivante :
([Lectures logiques] / [temps processeur en secondes)] * 8 Ko / 1024
Une plage de valeurs cohérentes (+/- 50 %) doit apparaître sur un minimum de
cinq exécutions de la requête. Des valeurs hors norme importantes (+/- 20 % ou
plus) peuvent indiquer des problèmes de configuration. La moyenne d'au moins
5 résultats calculés correspond au MCR d'entrepôt de données Fast Track.
Mesure du BCR pour votre charge de travail
La mesure du BCR est similaire à la mesure du MCR, mais les données sont traitées à partir du
disque, et non pas à partir du cache. La requête et le dataset pour le BCR sont représentatifs
de votre charge de travail cible de stockage des données.
Une méthode pour BCR est d'effectuer une requête simple, une requête moyenne et une
requête complexe de la charge de travail. Les requêtes complexes doivent être celles qui
sollicitent le plus l'unité centrale. La requête simple doit être analogue au MCR et doit avoir une
valeur similaire de travail, afin qu'elle soit comparable au MCR.
60
Création de la base de données
Voici un exemple d'une instruction CREATE DATABASE pour un système d'entrepôt de données
Fast Track à 8 cœurs et 16 LUN de données.
CREATE DATABASE FT_Demo ON
( NAME = N 'FT_Demo_.mdf' ,
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v1.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v2.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v3.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v4.ndf'
FILEGROWTH = 0 ),
PRIMARY Filegroup FT_Demo
FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 100MB ,
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
, FILENAME = N'C:\FT\PRI\SE2-SP1-DG6-v6' , SIZE = 417GB ,
(
,
(
,
(
,
(
,
, FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP1-DG2-v2' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP2-DG3-v3' , SIZE = 417GB ,
'FT_Demo_v6.ndf'
= 0 ),
'FT_Demo_v7.ndf'
= 0 ),
'FT_Demo_v8.ndf'
= 0 ),
'FT_Demo_v9.ndf'
= 0 ),
, FILENAME = N'C:\FT\PRI\SE1-SP2-DG4-v4' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE2-SP1-DG7-v7' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE2-SP2-DG8-v8' , SIZE = 417GB ,
NAME = N 'FT_Demo_v11.ndf'
FILEGROWTH = 0 ),
NAME = N 'FT_Demo_v12.ndf'
FILEGROWTH = 0 ),
NAME = N 'FT_Demo_v13.ndf'
FILEGROWTH = 0 ),
NAME = N 'FT_Demo_v14.ndf'
FILEGROWTH = 0 ),
, FILENAME = N'C:\FT\PRI\SE2-SP2-DG9-v9' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE3-SP1-DG11-v11' , SIZE = 417GB
, FILENAME = N'C:\FT\PRI\SE3-SP1-DG12-v12' , SIZE = 417GB
, FILENAME = N'C:\FT\PRI\SE3-SP2-DG13-v13' , SIZE = 417GB
, FILENAME = N'C:\FT\PRI\SE3-SP2-DG14-v14' , SIZE = 417GB
LOG ON
( NAME = N 'FT_LOG_v5.ldf' , FILENAME = N 'C:\FT\LOG\SE1-SP2-DG5-v5' , SIZE = 100GB ,
MAXSIZE = 500GB , FILEGROWTH = 50 )
GO
/*********************** Configurer les paramètres recommandés *****************/
ALTER DATABASE FT_Demo SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE
GO
FT_Demo
SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE
GO
FT_Demo
SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE
GO
FT_Demo
SET RECOVERY SIMPLE
sp_configure 'show advanced options', 1
go
reconfigure with override
go
/******** Veillez à ce que toutes les tables soient dans notre groupe de fichiers et
non pas dans le groupe de fichiers primaire****/
61
ALTER DATABASE FT_Demo
MODIFY FILEGROUP FT_Demo
DEFAULT
GO
Création des tables de test
Voici un exemple d'instruction CREATE TABLE.
CREATE TABLE lineitem
( l_orderkey
bigint not null,
l_partkey
integer not null,
l_suppkey
integer not null,
l_linenumber
integer not null,
l_quantity
float not null,
l_extendedprice float not null,
l_discount
float not null,
l_tax
float not null,
l_returnflag
char(1) not null,
l_linestatus
char(1) not null,
l_shipdate
datetime not null,
l_commitdate
datetime not null,
l_receiptdate
datetime not null,
l_shipinstruct char(25) not null,
l_shipmode
char(10) not null,
l_comment
varchar(132) not null
)
ON FT_Demo
GO
CREATE CLUSTERED INDEX cidx_lineitem
ON lineitem(l_shipdate ASC)
WITH( SORT_IN_TEMPDB = ON
, DATA_COMPRESSION = PAGE
)
ON FT_Demo
GO
Chargement des données pour la mesure du BCR
Comme décrit précédemment dans ce document, les systèmes d'entrepôt de données Fast Track
sont sensibles à la fragmentation des fichiers de base de données. Utilisez l'une des techniques
décrites dans ce document pour charger des données. Au cours du test d'entrepôt de données
Fast Track, la méthode de chargement d'index cluster décrite à l'option 2 a été utilisée. À l'aide
de l'outil TPC-H datagen, des données de la table lineitem ont été générées d'une taille de
70 Go, à l'aide des options -s100, générant le fichier en 8 parties et les options – S et –C.
L'indicateur de trace 610 a été défini au cours des opérations de chargement de façon à utiliser
la journalisation minimale lorsque cela est possible.
62
À l'aide de BULK INSERT, ces données ont été insérées en parallèle dans une table intermédiaire
d'index cluster, avec journalisation minimale ; nous avons choisi une taille de bloc qui n'accable
pas la mémoire disponible et qui limite de débordement sur le disque. La désactivation des
verrous et l'escalade des verrous de page dans la table intermédiaire ont amélioré les
performances au cours de cette phase.
Une insertion finale a été effectuée dans une table cible identique, avec la valeur 1 affectée
à MAXDOP (avec l'indicateur TABLOCK) et en évitant un tri.
Exécution de requêtes pour la mesure de BCR
Utilisez l'outil Générateur de profils SQL Server afin de stocker les informations pertinentes pour
les tests d'évaluation des requêtes. Le Générateur de profils SQL Server doit être installé pour
stocker les lectures logiques, l'UC, la durée, le nom de la base de données, le nom de schéma,
l'instruction SQL et les plans de requête réels. Utilisez également les paramètres de session de
statistiques set statistics io on et set statistics time on.
Voici quelques exemples de requêtes (selon les requêtes du test d'évaluation TPC-H) et BCR
obtenus sur des systèmes de référence. Notez que cet exemple n'indique pas les performances
qui seront atteintes sur un système donné. Les chiffre du BCR sont uniques au système, à la
taille de schéma, aux types de données, à la structure de requête et aux statistiques pour citer
quelques-unes des variables.
Complexité de la requête
Simple
Moyenne
Complexe
Tableau 7 : Exemples de tests d'évaluation
BCR par cœur
(Page compressée) avec la valeur
4 affectée à MAXDOP
201 Mo/s
83 Mo/s
56 Mo/s
Simple
SELECT
sum(l_extendedprice * l_discount) as revenue
FROM
lineitem
WHERE
l_discount between 0.04 - 0.01 and 0.04 + 0.01 and
l_quantity < 25
OPTION (maxdop 4)
63
Moyenne
SELECT
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count_big(*) as count_order
FROM
lineitem
WHERE
l_shipdate <= dateadd(dd, -90, '1998-12-01')
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus
OPTION (maxdop 4)
Complexe
SELECT
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice*(1-l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as
promo_revenue
FROM
lineitem,
part
WHERE
l_partkey = p_partkey
and l_shipdate >= '1995-09-01'
and l_shipdate < dateadd(mm, 1, '1995-09-01')
OPTION (maxdop 4)
64
Facteurs affectant le taux de consommation de requête
Toutes les requêtes n'atteindront pas le taux de consommation processeur maximal (MCR) ou
le taux de consommation de test d'évaluation (BCR). De nombreux facteurs affectent le taux de
consommation d'une requête. Les requêtes plus simples que la charge de travail utilisée pour
générer le taux de consommation auront des taux de consommation supérieurs, et les charges
de travail plus complexes auront des taux de consommation inférieurs. De nombreux facteurs
affectent cette complexité et le taux de consommation, notamment :
65
Complexité de la requête : Plus la requête sollicite l'UC, par exemple en termes de
calculs et nombre d'agrégations, plus le taux de consommation est faible.
Complexité du tri : Les tris dans un ordre explicite par opérations ou le regroupement
par opérations génèrent davantage de charge de travail sur l'UC et diminuent le taux de
consommation. Les écritures supplémentaires dans tempdb provoqué par le débordement
de ces requêtes sur le disque ont un impact négatif sur le taux de consommation.
Complexité du plan de requête : Plus le plan de requête est complexe, plus il
y a d'étapes et d'opérateurs, plus le taux de consommation sera bas, car chaque unité
de données est traitée via un pipeline plus long d'opérations.
Compression : La compression diminue le taux de consommation des données en
valeur réelle, car le taux de consommation par définition est mesuré pour les requêtes
qui sont liées à l'UC, et la décompression consomme des cycles d'UC. Toutefois,
les avantages liés au débit accru l'emportent généralement sur le traitement d'UC
supplémentaire impliqué dans la compression, sauf si la charge de travail sollicite
beaucoup l'UC. Lorsque vous comparez les taux de consommation des données
compressées et non compressées, tenez compte du taux de compression. Une autre
méthode pour observer cela consiste à considérer le taux de consommation en termes
de lignes par seconde.
Utilisation des données : Ignorer les données pendant des analyses (par exemple,
via la projection de requête et la sélection) est un processus assez efficace. Les requêtes
qui utilisent toutes les données d'une table ont des taux de consommation inférieurs,
car davantage de données sont traitées par débit d'unité de données.
