fast track data warehouse reference guide for sql...

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

SommaireHistorique 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..................................................................................................................31

Indexation..........................................................................................................................................32

Index columnstore optimisés en mémoire xVelocity.........................................................................32

Statistiques de base de données.......................................................................................................34

Compression......................................................................................................................................35

Gestion de la fragmentation des données.............................................................................................36

Fragmentation du système de fichiers...............................................................................................36

Plusieurs groupes de fichiers.............................................................................................................38

Chargement des données......................................................................................................................38

Chargements incrémentiels...............................................................................................................39

Migration des données......................................................................................................................41

Tests d'évaluation et validation.................................................................................................................43

Effectuer la validation d'entrepôt de données Fast Track de référence................................................44

Tests d'évaluation de référence avec SQLIO......................................................................................45

Effectuer un test d'évaluation de base de données Fast Track..............................................................48

Calcul du MCR....................................................................................................................................49

Calcul du BCR.....................................................................................................................................50

Architectures de référence d'entrepôt de données Fast Track publiées...................................................53

Conclusion.................................................................................................................................................53

4

Annexe......................................................................................................................................................55

FTDW System Sizing Tool.......................................................................................................................55

Validation d'une architecture de référence Fast Track définie par l'utilisateur.....................................55

Test d'E/S synthétiques.....................................................................................................................55

Génération des fichiers de test avec SQLIO.......................................................................................55

Test de la charge de travail....................................................................................................................58

Mesure du MCR pour votre serveur (facultatif).................................................................................58

Mesure du BCR pour votre charge de travail.....................................................................................58

Facteurs affectant le taux de consommation de requête..................................................................63

5

Historique des modifications du Guide de référence pour entrepôt de données Fast TrackLe 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 EmplacementNouveautés de SQL Server 2012 4.0 Liens vers d'autres documents de

meilleures pratiques SQL Server Important

Nouveautés de SQL Server 2012 4.0 Tests d'évaluation et validation Attention

Nouveautés de SQL Server 2012 4.0 Mémoire requise RAM

Nouveautés de SQL Server 2012 4.0 Index columnstore optimisés en

mémoire xVelocity Index columnstore

Nouveautés de SQL Server 2012 4.0 Stockage à l'état solide État solide

Nouveautés de SQL Server 2012 4.0 Validation et index columnstore Validation

Nouveautés de SQL Server 2012 4.0 Validation d'E/S de base SQLIO

Tableau 1 : Historique des modifications

IntroductionCe 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-TrackLa 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 valeurLes 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 composantsLes 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 travailLes 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éesToutes 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éesEn 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 travailLors 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 :

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.

11

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éesDé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 Affinité de charge de travail :Entrepôt de données OLTP/ODS

Description de cas d'utilisation

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)

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)

Modèle de données

Modèle d'entrepôt de données centralisé très normalisé

Dénormalisation prenant en charge les besoins en rapports

Modèle de données opérationnelles très normalisé

Dénormalisation fréquente pour l'aide à la décision ; accès

12

Attribut Affinité de charge de travail :Entrepôt de données OLTP/ODS

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

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

Architecture des données

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)

Optimisation de la base de données

Utilisation minimale des index secondaires (décrite précédemment comme allégée en index)

Le partitionnement est courant

Utilisation importante de l'optimisation des index secondaires

Tableau 2 : Attributs de charge de travail d'entrepôt de données

Choix d'une configuration de référence d'entrepôt de données Fast TrackIl 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.

Option 1 : évaluation de baseDans 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

13

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 clientLes 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 travailLa 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écisionL'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.

Charge de travailLes attributs suivants de charge de travail sont prioritaires :

14

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éesCela 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éeUn 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èteLes 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 processusLe 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 travailCe 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 TrackAvant 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 :

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-988F-22C3F94B08E0/Fast%20Track%20Core%20Calculator%20v1.2.xlsx.

16

http://download.microsoft.com/download/D/F/A/DFAAD98F-0F1B-4F8B-988F-22C3F94B08E0/Fast%20Track%20Core%20Calculator%20v1.2.xlsx

http://download.microsoft.com/download/D/F/A/DFAAD98F-0F1B-4F8B-988F-22C3F94B08E0/Fast%20Track%20Core%20Calculator%20v1.2.xlsx

Étape 2 : établir les mesures d'évaluationLes 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.

MCRCette 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.

BCRBCR 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 utilisateurIl 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

17

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.

Étape 3 : choisir une architecture de référence d'entrepôt de données Fast TrackUne 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/fast-track.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'utilisateurCette 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 travailLa 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 composantsL'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.

18

http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/fast-track.aspx

6. Créez la configuration définitive du serveur et du stockage.

Étape 3 : Validation du systèmeL'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 TrackLe tableau suivant récapitule les trois options de sélection d'architecture de référence Fast Track.

Option Avantages InconvénientsÉvaluation de base 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

Possibilité de stockage surdimensionné ou d'UC sous-dimensionnée

Évaluation complète 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

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

Architecture de référence définie par l'utilisateur

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

Le processus prend plusieurs mois

Requiert des compétences significatives en infrastructure

Requiert des compétences significatives en SQL Server

Tableau 3 : Comparaison des différentes options d'évaluation

19

Configuration standard d'entrepôt de données Fast Track

Architecture des composants matérielsLes 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 serveurRAM 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 maximale1 socket 64 Go 128 Go2 sockets 128 Go 256 Go4 sockets 256 Go 512 Go8 sockets 512 Go 768 GoTableau 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 ChannelADAPTATEUR 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.

StockageDisque 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 R2Sauf 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émarrageVous 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 SQLPour 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 ressourcesLes 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 stockageLa 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 stockageL'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 :

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

25

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 stockagePour 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)

Allocation minimale recommandée des journauxEspace libre en miroir (Go)

<= 96 <=10 300 Go x 1 volume

<= 128 >10 <=40

300 Go x 2 volumeou600 Go x 1 volume

Tableau 5 : Recommandations d'allocation du journal

28

Consultez les meilleures pratiques existantes pour l'allocation et la gestion du journal des transactions SQL Server.

Stockage à l'état solideLes 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 solideLes 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.

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.

29

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 TrackLes 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 tableLe 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émoireLes 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

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 clusterDans 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 tableLe 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).

IndexationPour 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 xVelocitySQL 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éesLes 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

http://msdn.microsoft.com/fr-fr/library/dd578580(v=SQL.100).aspx

http://msdn.microsoft.com/fr-fr/library/dd578580(v=SQL.100).aspx

Création d'index columnstore pour les modèles de données normalisésLes 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 dimensionnelsSuivez 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 columnstoreLes 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 xVelocityL'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éesVotre 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éesNous 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.

CompressionLes 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éesLa 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'étendueDans 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 Statut Action

>400 Idéal 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

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'indexUn 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 fichiersDes 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éesL'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/fr-fr/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émentielsCette 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émoireLes 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 clusterIl existe deux approches générales pour charger des tables d'index cluster avec une fragmentation minimale des tables.

Option 1Utilisez 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 2Cré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 3Cette 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 :

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

41

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éesCela 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émoireSuivez les instructions fournies plus haut pour le traitement de la charge incrémentielle.

Processus de chargement d'index clusterIl existe plusieurs approches générales pour charger des tables d'index cluster avec une fragmentation minimale des tables.

Option 1Utilisez 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 2Exé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 3Utilisez 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 4Utilisez 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

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 validationCette 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 baseL'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.

44

Validation de base de données Fast TrackL'é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 xVelocitySQL 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érenceLa 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.

45

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 SQLIOL'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/SLa 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).

46

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/volumeCe 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 globaleDans 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.

47

Évaluation des composantsLe 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.

48

Effectuer un test d'évaluation de base de données Fast TrackCette 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 :

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.

49

Calcul du MCRUn 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 / 10247. 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.

50

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 BCRUn 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.

51

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

52

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.

Résultats du BCRLa 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

53

Interprétation du BCRSi 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éesLes 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/fast-track.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).

ConclusionL'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

54



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.

55

http://technet.microsoft.com/fr-fr/library/ee619752(v=WS.10).aspx

Annexe

FTDW System Sizing ToolL'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/fast-track.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étiquesSQLIO 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 SQLIOLorsque 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.

56


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

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.

57

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

58

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 travailLa 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.

59

Création de la base de donnéesVoici 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 PRIMARY Filegroup FT_Demo( NAME = N 'FT_Demo_.mdf' , FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 100MB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v1.ndf' , FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v2.ndf' , FILENAME = N'C:\FT\PRI\SE1-SP1-DG2-v2' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v3.ndf' , FILENAME = N'C:\FT\PRI\SE1-SP2-DG3-v3' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v4.ndf' , FILENAME = N'C:\FT\PRI\SE1-SP2-DG4-v4' , SIZE = 417GB , FILEGROWTH = 0 ), ( NAME = N 'FT_Demo_v6.ndf' , FILENAME = N'C:\FT\PRI\SE2-SP1-DG6-v6' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v7.ndf' , FILENAME = N'C:\FT\PRI\SE2-SP1-DG7-v7' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v8.ndf' , FILENAME = N'C:\FT\PRI\SE2-SP2-DG8-v8' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v9.ndf' , FILENAME = N'C:\FT\PRI\SE2-SP2-DG9-v9' , SIZE = 417GB , FILEGROWTH = 0 ), ( NAME = N 'FT_Demo_v11.ndf' , FILENAME = N'C:\FT\PRI\SE3-SP1-DG11-v11' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v12.ndf' , FILENAME = N'C:\FT\PRI\SE3-SP1-DG12-v12' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v13.ndf' , FILENAME = N'C:\FT\PRI\SE3-SP2-DG13-v13' , SIZE = 417GB , FILEGROWTH = 0 ),( NAME = N 'FT_Demo_v14.ndf' , FILENAME = N'C:\FT\PRI\SE3-SP2-DG14-v14' , SIZE = 417GB , FILEGROWTH = 0 ),

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 ONGO

ALTER DATABASE FT_Demo SET AUTO_UPDATE_STATISTICS ONGO

ALTER DATABASE FT_Demo SET AUTO_UPDATE_STATISTICS_ASYNC ON GO

ALTER DATABASE FT_Demo SET RECOVERY SIMPLE GO

sp_configure 'show advanced options', 1go

reconfigure with overridego

60

/******** Veillez à ce que toutes les tables soient dans notre groupe de fichiers et non pas dans le groupe de fichiers primaire****/ALTER DATABASE FT_DemoMODIFY FILEGROUP FT_DemoDEFAULTGO

Création des tables de testVoici 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_DemoGO

CREATE CLUSTERED INDEX cidx_lineitem ON lineitem(l_shipdate ASC)WITH( SORT_IN_TEMPDB = ON , DATA_COMPRESSION = PAGE )ON FT_DemoGO

Chargement des données pour la mesure du BCRComme 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.

61

À 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 BCRUtilisez 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 BCR par cœur(Page compressée) avec la valeur 4 affectée à MAXDOP

Simple 201 Mo/sMoyenne 83 Mo/sComplexe 56 Mo/sTableau 7 : Exemples de tests d'évaluation

Simple

SELECT sum(l_extendedprice * l_discount) as revenueFROM lineitemWHERE l_discount between 0.04 - 0.01 and 0.04 + 0.01 and l_quantity < 25OPTION (maxdop 4)

62

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_orderFROM lineitemWHERE l_shipdate <= dateadd(dd, -90, '1998-12-01')GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatusOPTION (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_revenueFROM lineitem, partWHERE l_partkey = p_partkey and l_shipdate >= '1995-09-01' and l_shipdate < dateadd(mm, 1, '1995-09-01')OPTION (maxdop 4)

63

Facteurs affectant le taux de consommation de requêteToutes 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 :

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.

64

fast track data warehouse reference guide for sql...

Documents