Internet Data Center : Conception de bases de données SQL Server

Sur cette page

Microsoft Internet Data Center : Conception de bases de données SQL Server
Résumé
Introduction
À qui s'adresse ce chapitre ?
Questions de conception
Ressources nécessaires
Ressources matérielles nécessaires
Ressources logicielles nécessaires
Configuration requise
Sécurisation de SQL Server
Mode d'authentification
Comptes d'administration système
Considérations relatives au compte de service
Système de fichiers
Considérations relatives au Registre
Considérations relatives à l'audit
Considérations relatives à la sauvegarde et à la restauration
Compte Invité Windows
Considérations relatives à l'accès physique
Alias de compte de base de données
Délégation et Kerberos
Cryptage du trafic réseau
Système de fichiers de cryptage
Optimisation des performances de SQL Server
Configuration de SQL Server 2000
Systèmes de disque
Mémoire
Activités d'E/S
Optimisation SMP
Modèle de données physique
Concept de modèle de données physique
Placement et indexation des données
Optimisation de l'espace libre des pages et du fractionnement des pages
Modèle de données logique
Partitionnement
Vues indexées
Instructions SQL
Comparaison des instructions SQL orientées enregistrement et des instructions SQL orientées jeu
Différences entre les jeux
Tri des données
Identification des goulets d'étranglement
Questions associées
Configuration de l'environnement de contrôle
Analyseur de performances SQL
Profileur SQL
Analyseur de requêtes SQL
Extension avec des fédérations SQL Server
Problèmes d'évolutivité des bases de données
Questions relatives à la conception de fédérations
Partitionnement des données
Partitions symétriques
Partitions asymétriques
Comparaison des méthodes disponibles
Implémentation de vues partitionnées distribuées
Installation du serveur
Ajout de définitions de serveurs liés
Activation de la validation différée de schéma
Objets base de données
Création de tables membres de partitions
Création de la DPV sur chaque serveur fédéré
Interrogation de la DPV
Mise à jour d'une DPV
Implémentation du routage dépendant des données (DDR, Data Dependent Routing)
Mise en cache des informations de routage dans une application ASP
Mise en cache des informations de routage dans une application COM+
Implémentation du partitionnement de hachage
Un système auto-ajustable
Mise à jour de données dupliquées
Environnements de développement et de test
Maintenance des partitions
Récupération après incident et partitionnement
Sauvegarde et restauration de bases de données partitionnées
Mise en cluster de SQL Server pour une disponibilité élevée
Organigramme du processus de mise en cluster
Architecture de clusters
Configuration de basculement à une seule instance
Configuration de basculement à plusieurs instances
Disponibilité et serveurs fédérés
Résumé

Microsoft Internet Data Center : Conception de bases de données SQL Server

Cet article est tiré du Guide de référence Internet Data Center

Résumé

Ce chapitre décrit les problèmes et défis auxquels un développeur se trouve confronté lors de la conception d'un système de base de données basé sur Microsoft® SQL Server™ 2000. Ces défis consistent notamment à s'assurer que le stockage et l'accès à vos données sont sécurisés, que les performances d'accès aux données satisfont aux besoins de votre application, que votre solution est suffisamment évolutive pour prendre en charge le nombre d'utilisateurs prévu, et que la base de données satisfait aux critères de disponibilité de l'application.

Introduction

Dans la mesure où la plupart, sinon toutes, les applications doivent stocker, extraire et manipuler des données, il en résulte qu'un des facteurs essentiels dans la conception de l'infrastructure d'une application est la façon dont les données sont gérées. Microsoft® SQL Server™ 2000 offre des services sécurisés et évolutifs de stockage de données pour les données relationnelles et inclut des fonctionnalités étendues de gestion ainsi que la prise en charge d'une disponibilité élevée. Cependant, bien que SQL Server soit conçu pour permettre autant que possible sa propre optimisation, il est nécessaire de tenir compte d'un certain nombre de facteurs pour qu'une application réponde aux critères de sécurité, de performances, d'évolutivité et de disponibilité de la solution Microsoft Internet Data Center. Ce chapitre est destiné à identifier ces facteurs et à étudier les stratégies permettant de concevoir une solution SQL Server pour l'architecture Internet Data Center.

À qui s'adresse ce chapitre ?

Ce chapitre est destiné à tous ceux qui sont impliqués dans la planification ou l'implémentation d'une solution SQL Server sécurisée et aux performances élevées. En particulier, les professionnels des bases de données qui ont les fonctions suivantes trouveront ce chapitre utile :

• administrateur de base de données ;

• développeur d'applications de base de données.

Questions de conception

Les stratégies étudiées dans ce chapitre ne concernent aucune application en particulier. Elles proposent des suggestions pour les domaines dans lesquels vous devez optimiser les performances et l'évolutivité d'une application et doivent être considérées comme des recommandations d'ordre général. Dans la mesure où chaque application particulière est différente sur de nombreux aspects, il n'est pas possible de proposer des configurations particulières d'optimisation des performances. En revanche, vous devez adapter les conseils proposés à votre cas et vos besoins particuliers.

Ressources nécessaires

Pour concevoir une base de données sécurisée et dotée d'une disponibilité et de performances élevées, vous devez disposer de personnel pour les fonctions suivantes :

• ingénieur système de base de données (administrateur de base de données et développeur de base de données) ;

• administrateur de stockage disque/réseau partagé ;

• administrateur réseau ;

• architecte d'application.

Pour construire la base de données, vous avez besoin des fonctions suivantes :

• des ingénieurs système de base de données pour construire les serveurs, les objets et le code de la base de données ;

• un architecte d'application pour concevoir l'application distribuée ;

• des développeurs pour écrire le code de routage dépendant des données et d'accès aux données ;

• un technicien matériel pour assembler le matériel.

Ressources matérielles nécessaires

Les règles suivantes s'appliquent dans le choix du matériel pour les bases de données à volume élevé :

• un minimum de 8 processeurs par serveur est recommandé ;

• des réseaux système (SAN, System Area Network), bien que pas indispensables, sont vivement recommandés entre les serveurs.

Ressources logicielles nécessaires

L'évolutivité de l'architecture Internet Data Center nécessite les logiciels suivants :

• Microsoft Windows® 2000 Advanced Server ou Windows 2000 Datacenter Server (préférable).

• SQL Server 2000 Édition Entreprise.

Configuration requise

Le serveur et les composants doivent être certifiés pour une utilisation avec le système d'exploitation Windows 2000. Dès lors que le fabricant a certifié un serveur, ce dernier est ajouté à la liste de compatibilité matérielle (HCL, Hardware Compatibility List ) de Windows 2000.

Avant d'installer la base de données, vous devez vérifier que la connexion est opérationnelle entre les serveurs de bases de données et les réseaux VLAN.

Sécurisation de SQL Server

Dans une architecture Internet Data Center, la sécurisation des données d'un serveur de base de données est essentielle, car la base de données stocke des données sensibles, telles que les numéros de carte bancaire ou les mots de passe des utilisateurs. Cette section décrit comment sécuriser une base de données SQL Server dans un environnement Internet Data Center.

Mode d'authentification

SQL Server 2000 prend en charge deux formes d'authentification utilisateur, à savoir l'authentification Windows intégrée et les connexions SQL Server. Par défaut, SQL Server 2000 est configuré pour prendre en charge uniquement l'authentification Windows, bien que vous puissiez configurer le serveur pour prendre en charge à la fois les connexions Windows et SQL Server en configurant le mode d'authentification en mode mixte.

Le mode d'authentification Windows est généralement plus sûr que le mode d'authentification mixte, car il permet de tirer parti de la prise en charge par Windows 2000 du cryptage des mots de passe, de l'audit et des stratégies de sécurité, comme par exemple la longueur minimale ou l'expiration des mots de passe. Le mode mixte n'est pris en charge que pour assurer la compatibilité descendante, ou lorsque SQL Server 2000 est installé sur les systèmes d'exploitation Windows 98 ou Windows Millennium Edition. Pour la plupart des applications, Microsoft recommande la configuration de SQL Server 2000 avec le mode d'authentification Windows afin d'offrir le niveau de sécurité le plus élevé. Cependant, certaines applications telles que Microsoft BizTalk™ Server 2000, nécessitent des connexions SQL Server. Si vous prévoyez d'utiliser ces applications, vous devez configurer SQL Server pour prendre en charge l'authentification en mode mixte.

Comptes d'administration système

L'architecture Internet Data Center recommande que tous les administrateurs de SQL Server aient accès à SQL Server grâce à leur appartenance à un groupe Windows, et que ce même groupe soit membre du rôle de serveur sysadmin. Cette approche peut toutefois soulever un problème : les administrateurs Windows peuvent accorder à n'importe qui des autorisations sysadmin sur SQL Server 2000, car ils peuvent ajouter n'importe quel utilisateur au groupe Windows approprié. Si un site Web n'est pas conçu pour permettre aux administrateurs Windows d'attribuer un accès sysadmin à SQL Server, il faut que seuls les comptes Windows individuels puissent se voir attribuer le rôle sysadmin.

Dans les deux cas, l'architecture Internet Data Center recommande que la connexion SQL Server Administrateur système ne soit pas utilisée pour l'administration quotidienne. En revanche, les rôles des fonctions administratives spécifiques, tels que la sauvegarde de bases de données ou la gestion des connexions, doivent être utilisés. Vous devez attribuer au compte Administrateur système un mot de passe difficile à découvrir et verrouillé en lieu sûr avec accès d'urgence uniquement.

Dans l'architecture Internet Data Center, avec SQL Server en mode d'authentification Windows, vous ne pouvez pas ouvrir une session à l'aide du compte Administrateur système, car seules les connexions approuvées sont autorisées. Cependant, comme nous l'avons précisé, certaines applications telles que BizTalk Server nécessitent la prise en charge des ouvertures de session SQL Server standard, et l'authentification mixte doit donc être utilisée lorsque ces applications sont intégrées à l'environnement Internet Data Center.

Remarque : Bien que le compte sysadmin ne puisse pas être utilisé pour ouvrir une session sur SQL Server 2000 en mode d'authentification Windows, l'architecture Internet Data Center nécessite néanmoins de spécifier un mot de passe pour ce compte. En effet, une simple modification du Registre peut faire passer le mode de sécurité de l'authentification Windows au mode mixte. Si le mot de passe sysadmin est vide (ce qui est le cas d'une installation par défaut), un intrus (ou un administrateur Windows) peut accéder au serveur. Pour en savoir plus sur la méthode utilisée pour réduire les chances de succès d'une telle attaque, consultez la section "Considérations relatives au Registre", plus loin dans ce chapitre.

Considérations relatives au compte de service

SQL Server 2000 s'exécute comme trois services Windows : MSSQLServer,SQLServerAgent et Microsoft Search.

• Les services SQL Server (également appelé MSSQLServer) et SQL Server Agent peuvent être configurés pour utiliser le compte système local, un compte utilisateur local ou un compte utilisateur de domaine ; le choix dépend de la fonction requise pour SQL Server 2000. Les deux services peuvent être configurés pour utiliser le même compte utilisateur Windows. Le compte système local peut être utilisé si l'ordinateur qui exécute SQL Server n'est pas configuré pour la réplication et ne nécessite pas d'accès aux ressources du réseau.

• Le service Microsoft Search permet d'effectuer des recherches en texte clair. Dans l'architecture Internet Data Center, il est toujours configuré pour utiliser le compte système local.

Pour que SQL Server 2000 exécute ses tâches correctement, les autorisations suivantes doivent être accordées au compte système local (elles sont attribuées automatiquement par le programme d'installation) :

• Contrôle total sur le répertoire SQL Server (par défaut, \Program Files\Microsoft SQL Server\MSSQL)

• Contrôle total sur tous les fichiers de base de données all .mdf, .ndf et .ldf

• Contrôle total sur les clés (et sous-clés) de Registre suivantes :

  HKEY_LOCAL_MACHINE\Software\
  Microsoft\MSSQLServer
  HKEY_LOCAL_MACHINE\System\
  CurrentControlset\Services\MSSQLServer

  Ou, pour une instance nommée :

  HKEY_LOCAL_MACHINE\Software\
  Microsoft\Microsoft SQL Server\InstanceName
  HKEY_LOCAL_MACHINE\System\
  CurrentControlset\Services\MSSQL$InstanceName

Un compte utilisateur local présente les mêmes exigences que le compte système local, avec en plus l'autorisation suivante (accordée par défaut par le programme d'installation) :

• Le compte utilisateur doit disposer de l'autorisation Se connecter en tant que service.

L'option Compte utilisateur de domaine offre le niveau de flexibilité le plus élevé et réduit la surcharge administrative dans un environnement multiserveur. Voici quelques exemples des fonctionnalités disponibles lorsque seul un compte utilisateur de domaine est utilisé :

• Réplication.

• Sauvegarde et restauration au moyen des unités du réseau.

• Jointures hétérogènes impliquant des sources de données distantes.

• Fonctionnalités de messagerie de SQL Server Agent et SQLMail.

Système de fichiers

L'architecture Internet Data Center recommande l'application d'autorisations de fichiers NTFS aux données et aux fichiers journaux de toutes les bases de données. Le compte utilisateur que SQL Server 2000 doit utiliser doit disposer d'autorisations Contrôle total sur les fichiers de bases de données.

Tous les fichiers SQL Server 2000, y compris les fichiers exécutables et les bibliothèques de liaison dynamique (DDL, Dynamic-Link Libraries) doivent être configurés de sorte que les utilisateurs ne puissent pas les manipuler. Les autorisations sur ces fichiers doivent être configurées de façon à accorder au compte utilisateur utilisé par SQL Server, au groupe Administrateurs et aux comptes système locaux des autorisations Contrôle total. Aucune autre autorisation ne doit être accordée.

Considérations relatives au Registre

Pour protéger l'installation SQL Server 2000 contre les attaques d'utilisateurs disposant de droits d'accès sur le serveur physique, il est conseillé de définir les autorisations Windows ci-après dans les clés de Registre qui sont utilisées pour configurer SQL Server 2000. Toutes ces clés se trouvent sous :

• HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER (pour une instance par défaut)

• HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCENAME (pour une instance nommée)

Les autorisations du groupe Tout le monde sur cette clé doivent être supprimées et des autorisations Contrôle total doivent être ajoutées au groupe Administrateurs, au compte système local et au compte de service SQL Server. Le programme d'installation fait cela automatiquement pour les comptes de service sélectionnés au cours du processus d'installation.

La configuration d'autorisations sur les clés de Registre est particulièrement importante si les administrateurs SQL Server souhaitent empêcher les administrateurs Windows d'accéder à l'ordinateur qui exécute SQL Server. Dans ce cas, les administrateurs SQL Server doivent également prendre possession de la clé de Registre et supprimer les autorisations accordées au groupe Administrateurs. Il est alors impératif que le compte de service SQL Server dispose des autorisations Contrôle total. Bien que cela n'empêche pas les administrateurs d'accéder au système, cela permet aux administrateurs SQL Server de savoir quand les administrateurs Windows ont pris des risques avec la sécurité. Les administrateurs peuvent toujours prendre possession, mais ils ne peuvent pas l'attribuer. Pour en savoir plus sur les administrateurs Windows qui accèdent à SQL Server, consultez la section "Comptes administrateur système" plus haut dans ce chapitre.

Considérations relatives à l'audit

SQL Server 2000 permet d'auditer les ouvertures de session sur le serveur dans le journal des événements de Windows. Le niveau d'audit peut être configuré à l'aide de SQL Enterprise Manager ou de la procédure stockée étendue xp_loginconfig.

Les paramètres d'audit possibles sont les suivants :

• Aucun. Aucune information d'audit n'est enregistrée.

• Réussite. N'enregistre que les ouvertures de session ayant abouti.

• Échec. N'enregistre que les ouvertures de session ayant échoué.

• Tout. Enregistre les ouvertures de session ayant réussi et échoué.

Le paramètre d'audit Échec est le minimum recommandé. Si les exigences en termes de sécurité sont très élevées, toutes les ouvertures de session doivent être auditées, à la fois les réussites et les échecs. Les informations d'audit sont enregistrées dans le journal des erreurs de SQL Server 2000.

Considérations relatives à la sauvegarde et à la restauration

L'une des méthodes de sauvegarde des données SQL Server consiste à utiliser SQL Server 2000 pour sauvegarder les fichiers de données, puis à utiliser le programme de sauvegarde de Windows 2000 pour sauvegarder ces fichiers sur le support approprié. Il est important de comprendre que les données elles-mêmes ne sont pas cryptées. Ainsi, le support de sauvegarde doit être physiquement sécurisé et stocké hors site. Les fichiers de données sauvegardés doivent se trouver dans une partition NTFS dotée d'autorisations d'accès aux répertoires suffisantes pour que l'utilisateur normal n'ait pas accès à ces fichiers.

Compte Invité Windows

Lors de l'exécution de SQL Server 2000 en mode d'authentification Windows, le serveur s'appuie sur Windows pour procéder à toute authentification des clients. Le compte Invité de Windows constitue une menace pour la sécurité, il doit donc être désactivé.

Considérations relatives à l'accès physique

L'accès physique doit être limité dans la mesure du possible. L'un des risques de l'accès physique non autorisé est la possibilité pour un intrus de démarrer le serveur au moyen d'une disquette et d'accéder au système de fichiers Windows 2000. Les serveurs de base de données de production essentiels pour l'activité de l'entreprise doivent être sécurisés physiquement.

Alias de compte de base de données

SQL Server 2000 prend en charge l'utilisation d'alias pour des comptes utilisateur dans une base de données, à des fins de compatibilité descendante. Cela permet à plusieurs ouvertures de session de partager le même compte utilisateur dans la base de données, ce qui simplifie la gestion des autorisations d'accès aux objets de base de données dans les scénarios impliquant un grand nombre d'utilisateurs. Dans une solution de base de données SQL Server 2000, l'utilisation de rôles est préférable et les alias ne sont pas nécessaires. Les rôles sont plus puissants et apportent des fonctionnalités identiques à celles des alias.

Délégation et Kerberos

Kerberos est le principal mécanisme d'authentification des réseaux Windows 2000. La délégation fait référence à la possibilité de transférer des informations d'identification de sécurité sur plusieurs ordinateurs et applications. Lors de chaque saut entre ordinateurs, les informations d'identification de l'utilisateur sont conservées. SQL Server 2000 prend totalement en charge le protocole Kerberos, y compris la possibilité d'accepter des tickets Kerberos délégués et de déléguer ensuite ces tickets (sous le système d'exploitation Microsoft Windows 2000) à des contrôleurs de domaine Windows 2000 et au service Windows 2000
Active Directory™.

Cryptage du trafic réseau

SQL Server 2000 prend en charge le cryptage des données et tout autre trafic réseau acheminé entre les systèmes client et serveur d'un réseau. La puissance de cryptage dépend des possibilités de cryptage autorisées par le certificat installé pour SQL Server ainsi que par les fonctionnalités de cryptage du client et
du serveur.

Le certificat sélectionné pour SQL Server doit se voir attribuer le nom du serveur sous la forme du nom DNS pleinement qualifié du serveur ; par exemple, SQLServer1.Redmond.Microsoft.com. Le certificat doit être valide pour l'authentification par le serveur. Pour installer un certificat, vous devez ouvrir une session sur SQL Server sous le compte de service SQL Server, obtenir le certificat (d'une autorité de certification interne ou d'un fournisseur tiers approuvé), puis l'installer sur le serveur dans l'emplacement suggéré par défaut lors de l'importation du certificat.

Système de fichiers de cryptage

Les fichiers de données SQL Server 2000 peuvent être protégés par le système de fichiers de cryptage (EFS, Encrypting File System). Les fichiers de données doivent être cryptés à l'aide du compte de service de SQL Server.

Si ce compte de service doit être changé, les fichiers doivent d'abord être décryptés, le compte de service des services SQL Server doit être changé, puis les fichiers doivent être recryptés avec le nouveau compte de service. Si toutes ces opérations ne sont pas effectuées, SQL Server risque de ne pas pouvoir démarrer, car il ne pourra pas décrypter les fichiers qui ont été cryptés avec les informations d'identification du précédent compte de service.

Optimisation des performances de SQL Server

Quatre facteurs principaux affectent les performances de SQL Server 2000 :

• la configuration de SQL Server 2000 ;

• le modèle de données physique ;

• le modèle de données logique ;

• les instructions SQL.

Dans cette section, nous allons étudier chacun de ces facteurs et identifier les méthodes recommandées qui permettent d'optimiser les performances de votre application SQL Server.

Configuration de SQL Server 2000

L'un des moyens les plus évidents d'améliorer les performances d'une application SQL Server consiste à optimiser la configuration du serveur lui-même. Sous de nombreux aspects, SQL Server 2000 s'optimise en quelque sorte lui-même, en ce sens qu'il ajuste de façon dynamique ses paramètres de configuration en fonction de l'activité et de l'utilisation des ressources. Il existe cependant un certain nombre de décisions d'installation et de configuration qui peuvent grandement améliorer les performances de vos applications.

Systèmes de disque

Les bases de données SQL Server 2000 de production ne doivent généralement pas être installées sur des disques internes. En installant chaque base de données sur son propre sous-système de disque externe, les performances des opérations de lecture/écriture de données peuvent être améliorées. À mesure que des disques sont ajoutés, le risque de défaillance augmente. Ainsi, afin d'éviter les pertes de données dues à des défaillances matérielles, vous devez exécuter tous les environnements sur des systèmes RAID (Redundant Array of Inexpensive Disks). Vous offrez ainsi une tolérance de pannes et réduisez le risque de perte de données suite à une défaillance matérielle.

Les trois niveaux RAID pris en charge par Windows 2000 sont les suivants :

• Raid 0. Cette approche répartit simplement les données de façon égale sur les disques physiques. Chaque volume distribué est appelé agrégat par bandes ou stockage. RAID 0 ne protège pas contre les défaillances matérielles, mais il peut améliorer grandement les performances en lecture et en écriture.

• RAID 1. Ce mode, couramment appelé "mise en miroir de disques" (ou duplexage de disques lorsque chaque disque dispose de son propre contrôleur), écrit les données sur deux disques. Il protège ainsi contre les défaillances matérielles, car il crée deux copies conformes des données, une sur chaque partition du jeu de miroir. Si votre installation peut choisir à partir de quel miroir elle lit les données, vous pouvez optimiser le canal E/S afin d'atteindre de meilleures performances. Cela ne constitue cependant pas une approche courante.

• RAID 5. RAID 5, souvent appelé "agrégat par bandes avec parité", répartit les données de façon égale sur trois ou plus de disques, de la même façon que RAID 0. Cependant, la tolérance de pannes est offerte grâce au calcul des informations de récupération sur les données écrites sur les disques. Ces informations de récupération (parité) sont réparties de façon égale sur tous les disques, de sorte que si un disque connaît une défaillance, ses données peuvent être reconstruites à partir des informations de parité des autres membres du jeu de disques. RAID 5 protège contre la défaillance d'un seul disque, mais les performances en écriture s'en trouvent réduites.

Le niveau RAID le plus courant dans les environnements de base de données est une combinaison des niveaux RAID 0 et 1. Cette combinaison est également appelée RAID 10 ou RAID 0/1.

Pour des raisons de performances, les fichiers SQL Server suivants doivent être répartis sur plusieurs jeux de stockage différents :

• fichiers journaux des transactions ;

• fichiers Tempdb ;

• fichiers de données ;

• fichiers d'index.

Il est vivement recommandé que les fichiers journaux de transactions et les fichiers tempdb soit placés sur des disques RAID 0/1 et non RAID 5, en raison de l'affectation des performances de ce dernier mode, due à des taux d'écriture E/S élevés. De la même façon, les fichiers de données et les fichiers d'index doivent également être stockés sur des jeux de stockage RAID 0/1, mais dans le cas d'une application ne nécessitant pas trop d'opérations d'écriture, ils peuvent être placés sur des jeux RAID 5.

Pour calculer le nombre de fichiers de bases de données et de jeux de stockage, vous devez tenir compte des caractéristiques de stockage et d'accès des applications.

Remarque : Le stockage est configuré de différentes façons pour différentes solutions de stockage. Pour en savoir plus, reportez-vous à la solution de stockage de votre infrastructure.

Vous trouverez des informations détaillées sur les niveaux RAID et SQL Server dans l'article Guide d'optimisation des performances de Microsoft SQL Server 7.0.

Mémoire

Par défaut, SQL Server 2000 alloue le maximum de mémoire physique disponible sur le serveur, tout en laissant au système d'exploitation des ressources suffisantes pour éviter toute pagination excessive. Outre le code exécutable, la mémoire allouée est utilisée pour le pool de mémoire SQL Server. Le pool de mémoire est constitué des zones suivantes :

• structures de données de niveau système ;

• cache de journal ;

• cache de procédure ;

• contexte de connexion ;

• cache tampon (également appelé cache de données).

La configuration par défaut de la mémoire détermine automatiquement la taille requise pour chacune de ces zones. SQL Server est configuré pour allouer et désallouer automatiquement et dynamiquement la mémoire à chacune de ces zones, en fonction des besoins pour optimiser les performances.

Pour configurer manuellement la mémoire allouée, vous pouvez utiliser les paramètres suivants :

Paramètre

Fonction

max server memory (mémoire max. du serveur (Mo))

Mémoire maximale allouée par SQL

min server memory (mémoire min. du serveur (Mo))         

Mémoire minimale allouée par SQL

min memory per query (mémoire min. par requête (ko))

Quantité minimale de mémoire allouée par chaque requête

index create memory (mémoire de création d'index (ko))

Quantité de mémoire utilisée par les opérations de tri au cours de la création d'index

Il n'est généralement pas recommandé de changer les options de mémoire par défaut. Cependant, si d'autres applications s'exécutent sur le même ordinateur (par exemple, BizTalk Server), vous pouvez empêcher SQL Server d'allouer trop de mémoire en configurant le paramètre max server memory. À l'inverse, vous pouvez empêcher les autres applications d'utiliser (toute) la mémoire requise par SQL (par exemple, le cache de données) en configurant le paramètre min server memory à une valeur autre que 0.

Pour chaque requête, SQL Server 2000 alloue la quantité de mémoire appropriée. En particulier, les requêtes qui nécessitent des opérations intensives de hachage ou de tri nécessitent de grandes quantités de mémoire. Il n'est pas recommandé d'effectuer des opérations de hachage ou de tri sur disque.

Pour détecter les opérations de tri effectuées sur disque au cours d'une requête, exécutez la requête exclusivement sur votre ordinateur et recherchez les activités d'E/S sur le disque ou la batterie RAID où vous avez placé le(s) fichier(s) tempdb. Pour rechercher ces activités, utilisez l'Analyseur de performances de Windows 2000, lequel utilise un compteur prédéfini pour les activités d'E/S. Si votre requête provoque des opérations de tri sur disque, il est possible d'allouer davantage de mémoire à l'aide du paramètre min memory per query. Pour calculer le chiffre pour le paramètre min memory per query, vous pouvez procéder de la façon suivante :

1. Notez la quantité totale de mémoire de l'ordinateur.

2. Retirez la mémoire nécessaire pour Microsoft Windows NT® et les autres applications qui s'exécutent sur cet ordinateur.

3. Retirez la mémoire que vous souhaitez affecter au pool de mémoire.

4. Divisez le reste par le nombre de requêtes dont vous prévoyez l'exécution simultanée.

Remarque : En général, il n'est pas recommandé de modifier cette option, car SQL Server 2000 alloue la mémoire par requête de façon dynamique, en tenant compte de tous les autres composants de la mémoire SQL Server.

Comme tous les autres paramètres, index create memory est configuré automatiquement. Si vous prévoyez de rencontrer des difficultés avec une opération de création d'index en raison d'activités de tri nécessaires, augmentez cette valeur.

Pour configurer les paramètres mentionnés ci-dessus, vous devez activer show advanced options à l'aide de la procédure stockée sp_configure.

sp_configure "show advanced options",  1

go

Configurez ensuite les valeurs souhaitées pour les paramètres. Pour que les nouvelles valeurs prennent effet, vous devez exécuter RECONFIGURE.

Vous pouvez à présent définir les options de configuration à l'aide de la procédure stockée sp_configure-system, comme illustré ci-après :

sp_configure "min server memory", 32

go

Vous trouverez davantage d'informations sur la configuration de la mémoire dans les ressources suivantes :

• "Configuration Option Specifications" (Spécifications des options de configuration) dans la documentation "SQL Server Books Online"

• Microsoft SQL Server 2000 Performance Tuning Technical Reference (Référence technique sur l'optimisation des performances de Microsoft SQL Server 2000)

Activités d'E/S

Vous devez en permanence avoir pour objectif la réduction des activités d'E/S. SQL Server 2000 génère des activités d'E/S dans les trois situations suivantes :

• écriture dans les journaux de transactions en raison de la journalisation ;

• lecture ou écriture de données ou de fichiers d'index suite à des requêtes qui ne peuvent pas être satisfaites par le cache de données ;

• diverses activités de disque dues au comportement du système SQL Server, comme par exemple le tri des données sur le disque ou l'exécution périodique de points de contrôle.

Les deux paramètres suivants permettent d'influencer les performances d'E/S :

Paramètre

Fonction

recovery interval (intervalle de récupération (min))

Nombre minimum de minutes requises par SQL Server pour récupérer les bases de données.

max async io (max. E/S asynchrones)

Nombre maximum de requêtes d'E/S en attente
par fichier.

Avec le paramètre recovery interval, vous pouvez influencer l'intervalle des points de contrôle. Après l'installation de SQL Server 2000, le paramètre recovery interval est configuré sur environ une minute. Autrement dit, SQL Server calcule le nombre de points de contrôle de sorte que la durée de récupération reste d'environ une minute par défaut. Par conséquent, si vous souhaitez réduire les activités d'E/S suite au vidage du cache, vous devez augmenter le paramètre recovery interval. L'inconvénient de cette méthode est l'augmentation du temps de récupération.

Outre l'augmentation de l'intervalle de récupération, vous pouvez améliorer les performances d'E/S en tirant parti des requêtes E/S asynchrones qui sont envoyées par SQL Server à Windows 2000 et au contrôleur de disque. Par défaut, SQL Server envoie un maximum de 32 requêtes d'E/S en attente vers un fichier, ce qui est acceptable pour les contrôleurs de disque classiques. Afin de réduire les mouvements des têtes de lecture des disques, les contrôleurs intelligents peuvent traiter plus de 32 requêtes par fichier. Cependant, lorsque vous augmentez max async io, il existe une limite au-delà de laquelle les performances diminuent.

Il n'est généralement pas recommandé de modifier le paramètre recovery interval, car cela augmente le temps global de récupération. Cependant, si le contrôle du système révèle une grande quantité d'activités d'E/S suite aux points de contrôle (par exemple dans une application composée de nombreuses petites transactions), l'augmentation du paramètre recovery interval permet un impact positif sur les performances.

Vous pouvez configurer les paramètres étudiés ci-dessus à l'aide de la procédure stockée sp_configure-system, comme illustré dans les exemples suivants :

Pour manipuler les E/S asynchrones :

sp_configure "max async IO", <valeur>

Go

Reconfigure

Go

Pour manipuler la fréquence du point de contrôle :

sp_configure "recovery interval", <valeur>

Go

Reconfigure

go

Vous trouverez davantage d'informations sur les activités d'E/S dans les ressources suivantes :

• "Recovery Performance" (Performances de récupération) dans la documentation "SQL Server Books Online"

• Déploiement de Microsoft SQL Server 7.0 : "Chapitre 5, Sélections et configurations matérielles"

Optimisation SMP

Un système doté de plusieurs processeurs, dans lequel tous les processeurs ont accès à une large zone mémoire (RAM), est appelé un système multiprocesseur symétrique (SMP, Symmetric Multiprocessor). SMP est la technologie actuelle la plus évolutive. Avec un système SMP, SQL Server 2000 utilise plusieurs threads pour répartir les tâches entre différents processeurs. Par exemple, l'option de requête parallèle permet à SQL Server 2000 d'utiliser plusieurs threads pour répartir certaines requêtes afin d'analyser des tables. Les threads sont traitées en parallèle en dédiant chaque thread à un processeur différent. Les instances de base de données correctement optimisées présentent généralement une fréquence de correspondance du cache de 98 % ou plus. Si ce chiffre n'est pas atteint, le goulet d'étranglement peut être dû à un manque de puissance des processeurs. En outre, la fréquence d'horloge d'un processeur ou le nombre de processeurs peut avoir un impact majeur sur les performances globales.

Les paramètres de configuration suivants peuvent être utilisés pour contrôler le comportement SMP.

Paramètre

Fonction

affinity mask (masque d'affinité)

Association entre en processeur et une thread

max worker threads (nombre max. de threads actives)

Nombre de threads actives disponibles pour SQL Server

max degree of parallelism (degré max. de parallélisme)

Nombre de processeurs à utiliser lors de l'exécution d'un plan parallèle

cost of threshold for parallelism (coût du seuil pour le parallélisme)

Le seuil pour la création et l'exécution de plans parallèles

Avec le paramètre affinity mask, il est possible d'exclure certains processeurs de l'utilisation par les threads SQL Server 2000. Par conséquent, un autre effet est la réduction, voire même la suppression, des sauts de threads sur différents processeurs, et donc l'augmentation de l'effet du cache processeur. La valeur 0 par défaut permet à SQL Server 2000 d'utiliser tous les processeurs disponibles. Par exemple, si une autre application s'exécute sur le même ordinateur, il est possible de dédier des threads SQL Server 2000 à certains processeurs.

La valeur maximale du paramètre max worker threads est de 255, valeur acceptable pour la plupart des applications. SQL Server 2000 utilise des pools de threads s'il y a plus de 255 connexions simultanées. Cela signifie que la prochaine thread active disponible peut traiter la requête sans augmenter le paramètre max worker threads.

Le paramètre max degree of parallelism détermine le nombre de threads affectées à une même requête. Il est souvent appelé le degré de parallélisme d'une requête. Le paramètre max degree of parallelism tient compte du nombre de processeurs disponibles et est donc limité par l'utilisation du paramètre affinity mask.

Lors de l'utilisation d'une requête parallèle, SQL Server 2000 doit étudier le coût associé au fractionnement de cette requête et au regroupement des résultats intermédiaires. SQL Server 2000 compare les coûts estimés de l'exécution non parallèle d'une requête avec la valeur spécifiée dans le paramètre cost of threshold for parallelism. Si le coût estimé est élevé, SQL Server 2000 parallélise la requête sur les systèmes SMP. Autrement dit, le paramètre cost of threshold for parallelism empêche SQL Server 2000 d'exécuter des requêtes simples en parallèle, lorsque le gain en termes de performances n'est pas significatif.

Il n'est pas recommandé de modifier les paramètres mentionnés ci-dessus, à l'exception du paramètre affinity mask. Ce paramètre ne doit être modifié que si une autre application ou une charge de travail significative s'exécute sur le même ordinateur. En outre, dans ce cas, vous devez contrôler l'utilisation des processeurs par les différents processus afin de dédier les threads SQL Server 2000 aux processeurs les moins utilisés, voire inactifs. Pour contrôler l'utilisation des processeurs, utilisez l'Analyseur de performances Windows 2000. Sélectionnez le compteur % Temps Utilisateur de l'objet processeur et sélectionnez tous les processeurs présents.

Pour configurer les paramètres mentionnés ci-dessus, utilisez la procédure stockée sp_configure pour définir Show Advanced Options sur 1.

Les paramètres affinity mask, max degree of parallelism et cost threshold for parallelism peuvent également être configurés via Enterprise Manager.

Vous trouverez davantage d'informations sur le traitement des requêtes dans un serveur SMP dans les ressources suivantes :

• MS SQL Server 7.0 Query Processor (Processeur de requêtes Microsoft SQL Server 7.0) par Goetz Graefe, Jim Ewel et Cesar Galindo-Legaria

• Requêtes parallèles : "Option Max Degree of Parallelism" et "Option Cost Threshold for Parallelism" dans la documentation "SQL Server Books Online"

• Attribution de threads à des processeurs : "Option Affinity Mask" dans la documentation "SQL Server Books Online"

• Nombre de threads actives : "Option Max Worker Threads" dans la documentation "SQL Server Books Online"

Modèle de données physique

Un autre facteur pouvant affecter les performances est le modèle de données physique de la base de données. Les choix que vous faites lors de la conception du stockage physique de vos objets de base de données peuvent avoir un impact significatif sur les performances des bases de données.

Concept de modèle de données physique

Le modèle de données physique inclut tous les aspects de conception d'un modèle de base de données qui peuvent être modifiés sans changer les composants de l'application. Par exemple, il n'est pas nécessaire de modifier l'instruction SQL après avoir créé un nouvel index sur une colonne donnée.

SQL Server 2000 utilise des groupes de fichiers pour répartir sur plusieurs disques les données des tables ou des index. Les objets de base de données, tels que les tables ou les index, appartiennent à un même groupe de fichiers. Un groupe de fichiers comporte au moins un fichier physique. Lorsqu'un groupe de fichiers comportant plus d'un fichier est créé, SQL Server 2000 utilise une stratégie de remplissage proportionnel, en allouant de l'espace en fonction de la taille de chaque fichier. Par exemple, si fichier1 comporte 600 Mo d'espace libre et que fichier2 comporte 100 Mo d'espace libre, SQL Server 2000 alloue six extensions de pages dans fichier1 et une extension de pages dans fichier2.

Le modèle de données d'une application est généralement stocké dans une seule et même base de données. La règle consiste donc à placer les données à l'aide de fichiers et de groupes de fichiers qui appartiennent à la base de données. Des bases de données distinctes, ou même des instances séparées, ne sont normalement utilisées pour une application unique que dans les situations suivantes :

• Les tables présentent des modèles d'accès totalement différents. Par exemple, certaines tables appartiennent à la portion OLTP de l'application, tandis que d'autres sont utilisées pour le signalement de problèmes.

• L'évolutivité est améliorée par l'équilibrage de charge des transactions sur les différentes instances de serveur. Cette solution peut être utilisée lorsque le placement des données à l'aide de fichiers et de groupes de fichiers n'est pas suffisant. Cette approche utilise souvent des vues partitionnées distribuées afin d'offrir une vue cohérente des données réparties sur plusieurs serveurs.

• Une solution à disponibilité élevée est nécessaire, via une solution de cluster SQL Server 2000.

Lors de l'utilisation de fichiers et de groupes de fichiers pour répartir des données dans une base de données unique, vous devez tenir compte des facteurs suivants :

• le nombre de groupes de fichiers à créer ;

• le nombre de fichiers de données à créer ;

• le nombre de fichiers de données par groupe de fichiers ;

• quel fichier de données doit être placé sur quel jeu de stockage (ou disque) ;

• quel objet de base de données (tel que table ou index) doit être placé sur quel groupe de fichiers.

La configuration optimale dépend des caractéristiques d'accès de l'application. Pour analyser les modèles d'accès, isolez les cas d'utilisation qui représentent des comportements utilisateur classiques ou critiques. Avec ces informations, vous devez pouvoir décider du nombre de groupes de fichiers et de fichiers de données et d'index qui doivent être créés. Déterminez ensuite quels objets de base de données (par exemple, tables, index, procédures et fonctions) doivent être placés sur quel groupe de fichier.

Pour exploiter les options de parallélisme de SQL Server 2000, telles que les analyses parallèles de tables, les analyses parallèles d'index ou les opérations JOIN parallèles, il est vivement recommandé de créer des fichiers et des groupes de fichiers supplémentaires. SQL Server 2000 utilise des lettres d'unité pour sélectionner les requêtes E/S qui peuvent être exécutées en parallèle. Par défaut, les bases de données d'applications SQL Server 2000 ne comportent qu'un seul groupe de fichiers, appelé le groupe de fichiers principal. Il s'agit de l'emplacement où sont stockées les tables système. Chaque groupe de fichiers comporte au moins un fichier. Chaque fichier de données ou d'index supplémentaire doit être dédié à une unité différente (donc à une lettre d'unité différente) ou à des batteries RAID différentes. Par conséquent, une requête parallèle est prise en charge de façon optimale par le matériel de stockage si chaque requête est satisfaite par différents disques ou agrégats.

Les règles pour effectuer cette tâche sont les suivantes :

• Utilisez des groupes de fichiers pour placer les objets sur des disques physiques spécifiques.

• Créez un ou plusieurs groupes de fichiers secondaires pour les fichiers supplémentaires et faites de ce nouveau groupe de fichiers le groupe par défaut. Utilisez le groupe de fichiers principal uniquement pour les tables système.

• Il est également souhaitable que chaque disque ne comporte qu'un seul fichier de données.

• Utilisez des groupes de fichiers pour répartir vos données sur autant de disques que possible.

• Si l'application présente un point d'accès fréquent, envisagez de le placer sur son propre disque.

• Répartissez dans différents fichiers les données qui seront utilisées ensemble dans des jointures.

• Séparez les analyses séquentielles de celles à accès direct (à l'aide d'un index).

• Séparez les index non mis en cluster des données des tables.

• Si vous utilisez le partitionnement horizontal, placez vos partitions sur différents jeux de stockage ou disques.

• Envisagez l'utilisation d'une base de données distincte lorsque des tables d'une base de données présentent des modèles d'accès totalement différents (lecture fréquente/écriture fréquente).

Pour ajouter un groupe de fichiers et un fichier complémentaire à une base de données d'application existante, utilisez la commande ALTER DATABASE.

création d'un deuxième groupe de fichiers pour la base de données Northwind

alter database Northwind add file group secondary

ajout d'un fichier de données au nouveau groupe de fichiers

alter database Northwind add file

(      name = 'logical_filename',

filename = 'c:\Program Files\Microsoft SQL Server\MSSQL\ DATA\physical_filename.NDF',

SIZE= required_size

)

to file group secondary

Placez ensuite les nouveaux objets de base de données dans le nouveau groupe de fichiers en spécifiant le nom du groupe de fichiers :

create table table_name(…) on secondary

La même tâche peut être accomplie par l'intermédiaire de SQL Server 2000 Enterprise Manager. Pour ajouter des fichiers et des groupes de fichiers, utilisez SQL Server Enterprise Manager, dans les propriétés de la base de données.

Vous trouverez davantage d'informations sur l'utilisation des fichiers et des groupes de fichiers dans les ressources suivantes :

• "Deploying Microsoft SQL Server 7.0, Notes from the field" : page 212ff.

• "Using Files and File Groups" (Utilisation des fichiers et des groupes de fichiers) dans la documentation "SQL Server Books Online"

• "Querying Distributed Partitioned Views" (Requêtes sur des vues partitionnées distribuées) par Kalen Delaney et Itzik Ben-Gan. SQL Server Magazine  , septembre 2000.

Placement et indexation des données

SQL Server 2000 dispose de deux méthodes pour accéder aux données, soit en analysant toute la table de façon séquentielle, soit en utilisant des index pour localiser directement les pages qui contiennent les données requises. Dans le contexte des performances, il est utile de définir un index comme une structure de données redondante et secondaire contenant les colonnes indexées organisées dans une structure B-Tree. Grâce à la méthode d'analyse séquentielle, toutes les lignes d'une table sont lues dans l'ordre de leur stockage physique. De la même façon, il est possible d'utiliser un index pour accéder aux seules lignes qui satisfont aux critères de sélection. SQL Server 2000 utilise deux stratégies d'index différentes :

• index non en cluster ;

• index en cluster.

La principale différence entre ces deux stratégies est qu'un index en cluster trie les données des tables et les stocke dans l'ordre de l'index en cluster. Les index en cluster améliorent les performances en stockant les données des tables avec les données d'index de niveau feuille. Cela signifie que chaque fois que SQL Server 2000 lit l'entrée d'un nœud d'index en cluster de niveau feuille, il lit simultanément la ligne de données de la table. Il ne peut y avoir qu'un seul index en cluster à la fois par table et l'index en cluster ne peut pas être séparé dans des groupes de fichiers différents des données de la table.

Un index non en cluster, à l'inverse, stocke seulement un identificateur unique (rowid) au niveau feuille. Par conséquent, avec les index non en cluster, des E/S complémentaires sont nécessaires pour extraire les données de la table.

L'inconvénient d'un index en cluster apparaît lorsqu'un index en cluster et un index non en cluster cohabitent sur la même table. L'index non en cluster ne pointe pas directement sur les lignes de la table, mais il utilise les valeurs des index en cluster comme pointeurs vers les lignes de la table. Par conséquent, toute opération de lecture qui passe par un index non en cluster doit d'abord descendre l'arborescence B-Tree de l'index non en cluster. Après avoir trouvé la valeur au niveau feuille, il doit descendre l'arborescence B-Tree de l'index en cluster. En outre, une opération UPDATE sur des colonnes d'index en cluster provoque des mouvements de lignes, car les lignes mises à jour doivent être placées en fonction de l'ordre de tri de l'index en cluster.

Planifiez les index avec soin, en essayant de limiter le nombre d'index utiles pour les opérations de lecture/écriture spécifiques de votre application. S'il y a trop d'index, l'optimiseur passe trop de temps à créer et à évaluer les plans d'exécution potentiels. En outre, un nombre trop important d'index réduit les performances d'écriture. Vous devez créer des index non en cluster sur les colonnes pour les requêtes qui sont limitées dans le prédicat WHERE et qui présentent un niveau élevé de sélectivité.

Les index en cluster accélèrent les opérations dans les situations suivantes :

• Les requêtes qui doivent présenter des résultats triés ou des résultats intermédiaires triés, comme par exemple les opérations JOIN.

• Les requêtes qui extraient un ou plusieurs intervalles réguliers. Ces requêtes contiennent généralement le prédicat BETWEEN dans la clause WHERE. Les lignes de tables triées par index en cluster sont placées en fonction de l'ordre de tri de l'index en cluster. Par conséquent, les requêtes exécutent les commandes SELECT sur des plages de valeurs nécessitant moins de lectures d'E/S.

Les index en cluster ralentissent les opérations sur tous les autres index. Vous devez être prudent lors de l'utilisation conjointe d'index en cluster et d'index non en cluster sur une même table :

• Si un index non en cluster connaît des accès et parcours fréquents, l'index un cluster sera également parcouru chaque fois afin d'extraire la ligne réelle.

• La création d'un index en cluster sur des colonnes plus importantes ou sur des index comportant plusieurs colonnes provoque un accroissement de la structure d'index B-Tree. Pour un accès plus rapide aux données, vous devez essayer de maintenir un index en cluster le plus petit possible.

• L'utilisation d'index en cluster sur les colonnes connaissant une mise à jour fréquente peut provoquer de nombreux débordements de pages et de nombreuses réindexations.

Envisagez d'abord l'utilisation d'index non en cluster chaque fois que des index sont nécessaires. Examinez les colonnes du prédicat WHERE et de la liste SELECT. Une opération de recouvrement d'index est possible lorsque l'index inclut toutes les colonnes du prédicat WHERE ainsi que les colonnes de la liste SELECT. La condition préalable est que la valeur de la colonne d'index la plus à gauche doit être donnée dans le prédicat WHERE dans lequel les index composites sont utilisés ; dans le cas contraire, SQL Server 2000 ne tient pas compte de cet index.

Utilisez l'analyseur de requêtes pour déterminer si une requête donnée utilise les index que vous avez définis. Copiez la requête souhaitée pour évaluer la fenêtre de saisie des requêtes et cliquez sur le raccourci "Afficher le plan d'exécution estimé" ou appuyez sur CTRL-L. La fenêtre qui apparaît affiche sous forme graphique le plan d'exécution de la requête. La requête n'est pas réellement exécutée, mais les estimations des ressources nécessaires s'affichent.

Par exemple, supposez qu'une application stocke les commandes des clients dans une relation maître-détail entre le client et les tables Orders (Commandes). L'exemple suivant d'instruction SQL sélectionne les commandes du client avec l'ID 2020 :

select ct.cust_name, od.ord_item_id, od.ord_it_name

from customer ct, orders od

where ct.cust_id = 2020 and

ct.cust_id = od.cust_id

Vous pouvez utiliser la fenêtre Requête de l'analyseur de requêtes, comme le montre la Figure 1, pour déterminer si les index sont utilisés correctement :

Figure 1. Plan d'exécution de la requête sans index

Dans la Figure 1, la sortie graphique illustre deux analyses de tables suivies d'une opération JOIN avec boucle imbriquée. Pour améliorer les performances de cette requête, vous pouvez créer deux index qui prennent en charge la restriction dans le prédicat WHERE, comme le montre la Figure 2 :

create unique clustered index idx_cust_pk on customer(cust_id)

create unique clustered index idx_ord_pk on orders(cust_id, ord_item_id)

Pour vérifier que les nouveaux index sont utilisés dans la requête, vous pouvez une fois de plus utiliser l'analyseur de requêtes, comme le montre la Figure 2.

Figure 2. Plan d'exécution de la requête avec index

Les deux index sont désormais utilisés pour prendre en charge la requête.

Vous trouverez davantage d'informations sur les index et les plans d'exécution des requêtes dans les ressources suivantes :

• Deploying Microsoft SQL Server 7.0, "Planning Indexes" (Déploiement de Microsoft SQL Server 7.0, Planification d'index), page 234ff

• "Graphically Displaying the Execution Plan Using SQL Query Window" (Affichage graphique du plan d'exécution à l'aide de la fenêtre Requête SQL) dans la documentation "SQL Server Books Online"

Optimisation de l'espace libre des pages et du fractionnement des pages

Les données sont stockées dans deux objets de base de données différents : les tables et les index. Les tables SQL Server 2000 stockent les données dans des structures de mémoire séquentielles triées ou non triées. La structure de mémoire séquentielle non triée est appelée table de segment et la structure de mémoire triée est appelée table en cluster. Les principales différences entre les deux structures de mémoire sont que les lignes des tables de segment sont stockées dans un ordre aléatoire et que les pages ne sont liées que de façon séquentielle, tandis que les lignes des tables en cluster sont triées en fonction de la ou des colonnes de l'index en cluster. Par conséquent, le placement d'une nouvelle ligne est calculé au cours de l'opération INSERT à l'aide de la valeur d'index et la ligne est stockée dans la page appropriée. Par défaut, chaque page de données et chaque page d'index de niveau feuille est remplie à 100 %. Si la table et ses index s'accroissent par la suite, vous risquez le fractionnement des pages et le rééquilibrage de l'arborescence B-Tree. Il en va de même si la table est mise à jour avec des lignes qui sont plus longues que les valeurs d'origine. Le fractionnement des pages peut avoir un impact négatif sur les performances d'E/S. Pour éviter le fractionnement des pages, utilisez les paramètres suivants :

Paramètre

Fonction

Fillfactor                 

Espace vide à conserver sur chaque page

Pad_index

Spécifie l'espace à laisser libre sur chaque page (nœud) dans les niveaux intermédiaires de l'index

Le paramètre fillfactor affecte les pages de données de la table ainsi que les pages de niveau feuille de l'index, tandis que le paramètre pad_index affecte le niveau non-feuille (nœuds) d'un index B-Tree. Vous devez toujours utiliser le paramètre fillfactor avec le paramètre pad_index, car le paramètre pad_index utilise la valeur fillfactor.

Pour optimiser les performances E/S, vous devez parvenir à un équilibre entre maintenir la taille globale de la base de données à un niveau peu élevé en réduisant l'espace libre sur les pages d'une part, et éviter le fractionnement des pages en appliquant un facteur de remplissage d'autre part.

Il n'est pas recommandé de changer la valeur par défaut dans les applications en lecture seule, telles que les applications de data warehouse. Les applications de data warehouse sont chargées de façon incrémentale, ce qui signifie que les valeurs de clé primaire sont insérées par ordre croissant, ce qui évite les problèmes de fractionnement de pages.

Il est recommandé de réduire fillfactor et pad_index à une valeur inférieure à 100 % lorsqu'une opération INSERT est prévue dans des structures de mémoire triée, comme par exemple un index ou une table en cluster, ou lors d'une opération UPDATE avec des lignes qui risquent d'être plus longues que les lignes d'origine.

L'option fillfactor est utilisée dans l'instruction CREATE TABLE ou CREATE INDEX et l'option pad_index est utilisée dans l'instruction CREATE INDEX.

Les tables sont créées avec l'instruction T-SQL suivante :

CREATE TABLE(….)

[WITH FILLFACTOR = valeur]

[ON file_group]

Les index non en cluster sont créés avec l'instruction T-SQL suivante :

CREATE [UNIQUE] INDEX index_name

ON table_name (column_name,..)

[PAD_INDEX]

[WITH FILLFACTOR = valeur]

[ON file_group]

Les index en cluster sont créés avec l'instruction T-SQL suivante :

CREATE [UNIQUE] CLUSTERED INDEX index_name

ON table_name (column_name,..)

[PAD_INDEX]

[WITH FILLFACTOR = valeur]

[ON file_group]

Soyez conscient du fait que chaque contrainte PRIMARY KEY crée un index en cluster unique avec des valeurs par défaut. Les valeurs autres que les valeurs par défaut, par exemple le paramètre fillfactor, doivent être spécifiées lors de la création de la contrainte PRIMARY KEY.

Pour contrôler le fractionnement de pages, utilisez l'Analyseur de performances de Windows 2000 et choisissez le compteur Fractionnement de page dans l'objet Méthode d'accès.

Utilisez l'instruction ci-après dans la fenêtre de requête pour afficher en détail la fragmentation des tables et des index. L'indicateur clé est la densité d'analyse. Une valeur inférieure à 100 indique un certain degré de fragmentation.

DBCC SHOWCONTIG (table_name)

[WITH  ALL_INDEXES]

La fragmentation au niveau feuille peut être résolue en ligne à l'aide de l'instruction DBCC INDEXDEFRAG. L'inconvénient est que ce type de réorganisation n'est pas aussi efficace que les méthodes hors connexion ci-après.

Pour résoudre tout problème de fractionnement de page, reconstruisez les index à l'aide de la clause DROP_EXISTING :

CREATE [UNIQUE] [CLUSTERED] INDEX index_name

ON table_name (column_name,..)

[DROP_EXISTING]

[PAD_INDEX]

[WITH FILLFACTOR = valeur]

[ON file_group]

Par exemple, supposez qu'une application recueille les commandes des clients. Les commandes sont insérées dans la table ci-dessous.

CREATE TABLE orders (

customer_id int not null,

item_id       int not null,

order_date datetime not null,

amount int,

….)

Supposez maintenant qu'une analyse des requêtes exécutée sur cette table montre qu'un index en cluster non unique doit être défini sur les colonnes (customer_id, item_id) afin d'améliorer les performances en lecture. Dans ce scénario, il existe un danger potentiel de fractionnement de pages, car les colonnes customer_id et item_id ne croissent pas de façon séquentielle avec chaque nouvelle opération INSERT. Par conséquent, SQL Server 2000 doit exécuter une opération INSERT sur les nouvelles lignes en fonction de l'ordre de tri défini dans l'index en cluster. Pour éviter le fractionnement de pages, vous pouvez décider d'utiliser une valeur inférieure à 100 pour les paramètres fillfactor et pad_index lors de la création de l'index en cluster et de la table. La première hypothèse pour le paramètre fillfactor est le pourcentage de clients et articles actifs de cette application qui effectuent un certain nombre de commandes au cours d'un intervalle donné. Plus ce pourcentage est faible, plus le paramètre fillfactor est faible.

L'instruction CREATE INDEX suivante peut être utilisée pour créer un index avec un paramètre fillfactor de 75 % :

CREATE CLUSTERED INDEX idx_Orders

ON orders (customer_id, item_id)

DROP_EXISTING

PAD_INDEX

WITH FILLFACTOR = 75

Remarque : Si vous contrôlez le fractionnement des pages de la table, vous devez exporter les données de la table, supprimer la table, créer une nouvelle table, puis importer de nouveau les données dans la table. Une fois les données importées dans la table, créez les index sur la nouvelle table.

Vous trouverez davantage d'informations sur la gestion des index et le fractionnement de pages dans les ressources suivantes :

• "Heap Tables" (Tables de segment) et "Clustered Tables" (Tables en cluster) dans la documentation "SQL Server Books Online"

• "CREATE INDEX and Create Table" dans la documentation "SQL Server Books Online"

• "DBCC SHOWCONTIG" dans la documentation "SQL Server Books Online"

• Deploying Microsoft SQL Server 7.0, "Planning Indexes" (Déploiement de Microsoft SQL Server 7.0, Planification d'index), page 234ff

Modèle de données logique

Le modèle de données logique de la base de données détermine la façon dont vous structurez vos données de façon logique, à l'aide de tables et de relations. Cette structure peut également avoir un impact important sur les performances de votre application.

Partitionnement

Le partitionnement est un moyen de fractionner les données d'une table ou d'un index. Les lignes de données sont placées dans des tables et index physiques différents. Les données d'une base de données peuvent être partitionnées de deux façons : verticalement ou horizontalement. Dans cette section, nous allons examiner les deux approches, à l'aide de l'échantillon de données client/commande suivant :

ord_id

ord_item_id

ord_it_name

amount

unit_price

cust_id

cust_name

cust_first

country

1

5567

Cement

10,9

120,0 $

A1230

Miller

Justus

Germany

2

9876

Concrete

4,5

60,50 $

B2345

Schulz

Bob

USA

3

3654

Blocks

7

12,60 $

C5679

Vogt

Martin

USA

4

1211

Pipes

3

10,0 $

A1230

Miller

Justus

Germany

5

5567

Cement

12,5

60,50 $

C5679

Vogt

Martin

USA

6

4655

Slump

9

5,55 $

A1230

Miller

Justus

Germany

7

1211

Pipes

133

10,0 $

A1230

Miller

Justus

Germany

Partitionnement vertical

Le type de partitionnement le plus courant dans une base de données est le partitionnement vertical, généralement le résultat de la normalisation de la base de données. La normalisation est le processus qui consiste à séparer des objets qui sont différents d'un point de vue sémantique. Il en résulte un stockage plus compact des données, car les groupes de répétition ou les valeurs NULL sont éliminées. En normalisant une base de données, il est possible de réduire la charge d'E/S sur les analyses de tables complètes et d'augmenter les fréquences de correspondance dans le cache de la base de données. Le seul inconvénient de la normalisation est que des jointures supplémentaires doivent être effectuées, ce qui entraîne une utilisation plus importante du processeur (et éventuellement davantage d'E/S de disque), si les colonnes des deux partitions sont requises. Dans ce cas, vous devez contrôler le temps écoulé pendant les instructions SQL qui extraient des instances de tables faisant partie de la jointure, afin de déterminer quel modèle de données est le plus rapide.

Vous pouvez normaliser les données client/commande illustrées ci-dessus en fractionnant les données dans deux tables, une pour les commandes et l'autre pour les clients. Pour joindre les deux tables, vous devez ajouter la colonne de clé étrangère cust_id à la table orders.

Orders (Commandes)

ord_id

ord_item_id

ord_it_name

amount

unit_price

cust_id

1

5 567

Cement

10,9

120,0 $

A1230

2

9 876

Concrete

4,5

60,50 $

B2345

3

3 654

Blocks

7

12,60 $

C5679

4

1 211

Pipes

3

10,0 $

A1230

5

5 567

Cement

12,.5

60,50 $

C5679

6

4 655

Aggregate

9

5,55 $

A1230

7

1 211

Pipes

133

10,0 $

A1230

Customers (Clients)

cust_id

cust_name

cust_first

country

A1230

Miller

Justus

Germany

B2345

Schulz

Bob

USA

C5679

Vogt

Martin

USA

Le partitionnement vertical affecte la conception de l'application, car il augmente le nombre de tables logiques. Jusqu'à un certain point, les changements apportés au modèle logique peuvent être déduits des applications clients à l'aide de vues. Cependant, toute modification de données effectuée à l'aide de la vue peut nécessiter du code supplémentaire (tel qu'un déclencheur INSTEAD-OF) pour affecter plusieurs tables sous-jacentes.

La normalisation présente plusieurs avantages en termes de performances. Dans une base de données entièrement normalisée, les données n'existent qu'en un seul exemplaire. Ainsi, la quantité de données des tables de la base de données est inférieure et les opérations d'E/S qui impliquent les données d'une seule table sont généralement plus rapides. Un autre avantage de la suppression de la redondance de données dans la base de données est que toutes les opérations de modification (INSERT, UPDATE, DELETE) ne sont exécutées que dans un seul emplacement. Par exemple, si vous stockez le nom d'un client dans une table qui contient des commandes, et que ce client a effectué plusieurs commandes, chaque opération UPDATE sur la colonne de nom du client entraîne plusieurs opérations au lieu d'une seule qui serait requise dans un schéma de base de données entièrement normalisé.

En revanche, la normalisation présente quelques inconvénients en termes de performances, principalement en ce qui concerne la jointure de tables. Pour cette raison, un schéma de base de données entièrement normalisé n'est souvent pas en mesure d'offrir des performances optimales, en particulier pour les requêtes qui joignent plusieurs tables ou requêtes et qui calculent de grandes quantités de données. Dans ces cas, vous pouvez choisir de dénormaliser la base de données afin d'améliorer les performances.

Le processus de dénormalisation peut être réalisé à l'aide des techniques suivantes :

• colonnes dupliquées ;

• valeurs calculées.

Colonnes dupliquées

La jointure de tables peut avoir un impact négatif sur les performances du système, dans la mesure où l'opération JOIN est une des opérations de base de données qui prend le plus de temps. Lorsqu'un goulet d'étranglement est provoqué par une opération JOIN, vous devez envisager la suppression de clés étrangères en dupliquant des colonnes dans les tables référencées. Par exemple, l'instruction
T-SQL suivante illustre une relation maître/détail typique avec clients et commandes :

create table customer(

cust_id              int    NOT NULL,

cust_name     varchar(20),

cust_first    varchar(20),

country              varchar(20))

create table orders(

ord_id        int    NOT NULL,

ord_item_id   int,

ord_it_name   varchar(20),

amountint    NOT NULL,

unit_price           money,

cust_id              int)

Les contraintes suivantes sont appliquées :

alter table customer add primary key (cust_id)

alter table orders add primary key (ord_id)

alter table orders add foreign key (cust_id) references customer(cust_id)

La requête suivante extrait le montant total des commandes par client et par article :

select ct.cust_name, ct.cust_first, ct.country, od.ord_it_name, sum(od.amount * od.unit_price)

from customer ct, orders od

where ct.cust_id = od.cust_id

group by ct.cust_name, ct.cust_first, ct.country, od.ord_it_name

Cela entraîne une opération JOIN.

La duplication des colonnes de la liste SELECT et de la clé primaire de la table customer dans la table orders contribue à éviter l'opération JOIN.

create table orders_customers(

ord_id        int    NOT NULL,

ord_item_id   int,

ord_it_name   varchar(20),

amountint    NOT NULL,

unit_price           money,

cust_id              int,

cust_id              int    NOT NULL,

cust_name     varchar(20),

cust_first    varchar(20),

country              varchar(20));

La requête équivalente pour la nouvelle table est désormais la suivante :

select oc.cust_name, oc.cust_first,oc.country,oc.ord_it_name, sum(oc.amount * oc.unit_price)

from orders_customers oc

group by oc.cust_name, oc.cust_first, oc.country, oc.ord_it_name

L'inconvénient de la dénormalisation est que chaque fois qu'une commande est générée et insérée, vous devez également insérer les données client correspondantes. En outre, lorsque des données client doivent être mises à jour suite à un changement de nom du client, toutes les lignes client correspondantes doivent être mises à jour ; si tel n'est pas le cas, la base de données est incohérente. Enfin, un client qui a été supprimé doit être supprimé dans la table des commandes, sinon l'entrée correspondante doit être définie sur NULL.

Valeurs calculées

Un schéma de base de données entièrement normalisé ne stocke aucune donnée calculée, comme par exemple les sommes ou les moyennes. Ces valeurs sont en revanche calculées lors de l'exécution, à l'aide de toutes les données détaillées. En général, les requêtes ont intérêt à conserver les valeurs calculées dans les tables plutôt que de les générer lors de l'exécution, comme le montre l'exemple ci-dessus. L'inconvénient est que vous devez implémenter des fonctions supplémentaires pour contrôler la redondance des données.

Par exemple, si vous souhaitez extraire les informations relatives aux clients qui ont passé des commandes pour plus de 10 000 $ chacun, vous devez étendre la requête de la façon suivante :

select ct.cust_id, od.ord_it_name, sum(od.sale)

from customer ct, orders od

where ct.cust_id = od.cust_id and(od. amount * od.unit_price) > 10.000

group by ct.cust_id, od.ord_it_name;

Dans ce cas, SQL Server 2000 ne peut pas tirer parti d'index prenant en charge la nouvelle restriction, car celle-ci est calculée comme une expression (fonction) de deux colonnes. Pour prendre en charge ce type de restriction, ajoutez à la table orders d'origine une colonne calculée qui sera nommée sale (vente). La colonne sale est calculée de la façon suivante : unit_price * amount (prix unitaire * quantité).

Alter table orders add sale AS amount * unit_price

Bien que cette instruction définisse le calcul dans le cadre de la table, l'expression est toujours résolue lors de l'exécution, comme le montre le plan d'exécution de la requête, Figure 3.

Figure 3. Colonne calculée résolue lors de l'exécution

Le nœud scalaire calculé indique que la colonne sale n'est pas stockée de façon permanente dans la base de données, mais qu'elle est calculée lors de l'exécution. Les colonnes calculées peuvent être utilisées dans les listes SELECT, les clauses WHERE et les clauses ORDER BY.

Pour améliorer encore davantage les performances de l'instruction SELECT, créez un index sur la colonne calculée. Cet index (basé sur une fonction) stocke le résultat de la fonction (sale = amount * unit_price) dans la base de données. Par conséquent, le moteur de requête peut tenir compte de ce chemin d'accès. La condition préalable à l'indexation de colonnes calculées est que la colonne doit être déterministe ; pour plus de détails, consultez la documentation "SQL Server Books Online" : "Deterministic and Nondeterministic Functions" (Fonctions déterministes et non déterministes). L'instruction permettant de créer l'index requis est illustrée ci-dessous :

create index idx_sale on orders(sale)

Le plan d'exécution de la Figure 4 illustre la façon dont l'index est utilisé lors de l'extraction des données de vente.

Figure 4. Colonne calculée indexée

Ce plan de requête présente le même nombre de nœuds que le plan précédent. Néanmoins, il améliore le temps de réponse, car le moteur de requête peut tirer parti de la restriction du prédicat WHERE, qui spécifie les ventes supérieures à 10 000 $. SQL Server n'a pas besoin d'analyser toutes les lignes de la table de commandes et de supprimer toutes celles qui ne satisfont pas à ce critère, comme dans le plan d'exécution précédent : elle les extrait directement à l'aide du nouvel index idx_sale.

En général, la dénormalisation du schéma de base de données n'est pas recommandée, car il existe des effets négatifs :

• La dénormalisation modifie de façon significative la structure de votre base de données.

• Vous devez implémenter une stratégie afin d'éviter les anomalies de mise à jour.

L'application de la dénormalisation à votre schéma de base de données n'est recommandée que dans le cas où les performances ne peuvent pas être atteintes en appliquant les recommandations des chapitres précédents.

Les colonnes non volatiles, c'est-à-dire les colonnes dont les valeurs sont rarement modifiées, constituent de bons candidats pour la duplication. Si vous devez dupliquer des colonnes volatiles, il est vivement recommandé de créer un déclencheur correspondant afin de garantir l'intégrité des données.

Les valeurs calculées ne sont pas stockées dans la base de données, mais sont calculées chaque fois que vous les sélectionnez (SELECT). Vous n'avez pas besoin de les mettre à jour (UPDATE) lorsqu'une opération INSERT ou UPDATE est exécutée sur les colonnes sous-jacentes.

Le processus de dénormalisation, lorsqu'il est nécessaire, doit être effectué étape par étape et doit être soigneusement planifié et exécuté. Recherchez les parties de la base de données qui peuvent être normalisées pour atteindre de meilleures performances uniquement si les objectifs de performances attendus ne sont pas atteints.

Vous trouverez davantage d'informations sur la normalisation et la dénormalisation dans les ressources suivantes :

• Conceptual Database Design—An Entity-Relationship Approach (Conception d'une base de données – approche par relation d'entité) par Carlo Batini, Stefano Ceri et Shamkant Navathe

• Database Modeling and Design (Modélisation et conception de bases de données) par Toby J. Teorey

• A Practical Guide to Logical Data Modeling (Guide pratique de la modélisation logique de données) par George Tillmann

• "Creating Indexes on Computed Columns" (Création d'index sur des colonnes calculées) dans la documentation "SQL Server Books Online"

• "Deterministic and Nondeterministic Functions" (Fonctions déterministes et non déterministes) dans la documentation "SQL Server Books Online"

Partitionnement horizontal

Le partitionnement horizontal est un moyen de fractionner de grandes quantités de lignes d'une table en plusieurs tables appelées partitions. Les tables présentent toutes des structures équivalentes, c'est-à-dire les mêmes noms de colonnes et types de données. Le partitionnement horizontal implique des critères de partitionnement (par exemple, la colonne d'heure) qui associent les lignes de la table aux différentes partitions. Chaque partition contient une certaine plage de valeurs. À titre d'exemple, chaque mois de l'année est fractionné sur sa propre partition. Il existe deux raisons principales, liées aux performances, pour le partitionnement horizontal d'une table :

• répartir les données sur différents groupes physiques de fichiers dans une même base de données, afin de réduire les conflits de ressources ;

• répartir les données sur plusieurs serveurs, étendant ainsi la base de données afin d'améliorer la disponibilité.

Pour partitionner horizontalement les données des commandes client, nous pouvons fractionner la table orders_customers en quatre partitions avec la colonne ord_id comme critère de partitionnement.

Partition Orders_customers_part1

ord_id

ord_item_id

ord_it_name

amount

Unit_price

cust_id

cust_name

cust_first

country

1

5 567

Cement

10,9

120,0 $

A1230

Miller

Justus

Germany

2

9 876

Concrete

4,5

60,50 $

B2345

Schulz

Bob

USA

Partition Orders_customers_part2

ord_id

ord_item_id

ord_it_name

amount

Unit_price

cust_id

cust_name

cust_first

country

3

3 654

Blocks

7

12,60 $

C5679

Vogt

Martin

USA

4

1 211

Pipes

3

10,0 $

A1230

Miller

Justus

Germany

Partition Orders_customers_part3

ord_id

ord_item_id

ord_it_name

amount

 Unit_price

cust_id

cust_name

cust_first

country

5

5 567

Cement

12,5

60,50 $

C5679

Vogt

Martin

USA

6

4 655

Slump

9

5,55 $

A1230

Miller

Justus

Germany

Partition Orders_customers_part4

ord_id

ord_item_id

ord_it_name

amount

Unit_price

cust_id

cust_name

cust_first

country

7

1 211

Pipes

133

10,0 $

A1230

Miller

Justus

Germany

Pour appliquer les critères de partitionnement, une contrainte CHECK est utilisée. Par exemple, les instructions CREATE TABLE pour les tables illustrées ci-dessus seraient les suivantes :

CREATE TABLE Partition_Orders_customers_Part1

(ord_id integer primary key

  CONSTRAINT ckPartition1 CHECK(order_id > 1 AND ord_id < 3),

ord_item_id integer,

ord_it_name varchar(20),

amount double,

Unit_price money,

Cust_id char(5),

Cust_name varchar(20),

Cust_first varchar(20),

Country varchar(20))

CREATE TABLE Partition_Orders_customers_Part2

(ord_id integer primary key

  CONSTRAINT ckPartition2 CHECK(order_id > 2 AND ord_id < 5),

ord_item_id integer,

ord_it_name varchar(20),

amount double,

Unit_price money,

Cust_id char(5),

Cust_name varchar(20),

Cust_first varchar(20),

Country varchar(20))

CREATE TABLE Partition_Orders_customers_Part3

(ord_id integer primary key

  CONSTRAINT ckPartition3 CHECK(order_id > 4 AND ord_id < 7),

ord_item_id integer,

ord_it_name varchar(20),

amount double,

Unit_price money,

Cust_id char(5),

Cust_name varchar(20),

Cust_first varchar(20),

Country varchar(20))

CREATE TABLE Partition_Orders_customers_Part4

(ord_id integer primary key

  CONSTRAINT ckPartition4 CHECK(order_id > 6),

ord_item_id integer,

ord_it_name varchar(20),

amount double,

Unit_price money,

Cust_id char(5),

Cust_name varchar(20),

Cust_first varchar(20),

Country varchar(20))

Pour extraire des données des tables non partitionnées, utilisez une vue qui combine toutes les tables avec l'opérateur UNION. SQL Server 2000 prend en charge les vues partitionnées, maintenant ainsi la structure de l'application transparente, ce qui signifie que la syntaxe des instructions SQL n'est pas affectée. Du point de vue des performances, chaque fois que vous devez changer les critères de partitionnement (par exemple lorsque vous appliquez le partitionnement à l'aide d'une colonne différente ou que vous ajoutez une nouvelle partition à un jeu de partitions existant en raison d'une augmentation du volume de données), la syntaxe de l'instruction SQL reste la même. L'administrateur de base de données doit simplement modifier la vue partitionnée.

Il existe deux types de vues partitionnées :

• **Vues partitionnées locales ** : toutes les partitions se trouvent sur une même instance SQL Server 2000.

• **Vues partitionnées distribuées ** : les partitions sont réparties sur plusieurs instances SQL Server 2000 (processus appelé fédération de serveur). Autrement dit, chaque table membre (une partition) de la vue partitionnée peut être stockée sur un serveur membre distinct.

Pour créer une vue locale partitionnée :

1. Générez autant de tables partitionnées que nécessaire à l'aide de l'instruction CREATE TABLE.

2. Appliquez une contrainte CHECK sur la colonne contenant les critères de partitionnement.

3. Créez une vue à l'aide de l'opérateur UNION ALL sur toutes les partitions, comme illustré ci-dessous.

CREATE VIEW customer_orders

AS

SELECT * FROM Partition_orders_customer_part1

UNION ALL

SELECT * FROM Partition_orders_customer_part2

UNION ALL

SELECT * FROM Partition_orders_customer_part3

UNION ALL

SELECT * FROM Partition_orders_customer_part4

Remarque : La conception d'une solution de serveur fédéré à l'aide de vues partitionnées distribuées est étudiée dans la section suivante de ce chapitre (Extension avec des serveurs SQL fédérés).

Il est conseillé d'utiliser le partitionnement horizontal dans les situations suivantes :

• Lorsqu'une requête ne peut pas être prise en charge par un index. Par exemple, lorsque les plages faisant l'objet de la requête sont trop importantes, l'analyse d'une ou de quelques partitions implique beaucoup moins d'E/S que l'analyse de toute la table. Cela est vrai si les requêtes peuvent être satisfaites par seulement une ou quelques partitions. La raison en est que le moteur de requête de SQL Server 2000 ne tient compte que des partitions qui satisfont les requêtes (sous réserve que la requête contienne la colonne de la contrainte de partitionnement CHECK dans le prédicat WHERE).

• Pour satisfaire aux requêtes SQL Server parallèle. L'exécution par SQL Server 2000 de requêtes en parallèle, comme par exemple les analyses de requêtes parallèles, peut être prise en charge par le matériel. Les partitions peuvent être placées sur des disques différents ou même dans des instances SQL Server 2000 différentes.

• Les opérations nécessitant de nombreuses écritures sur des tables indexées.

• Les modèles d'accès direct ou les requêtes SQL qui utilisent des index. Ceux-ci peuvent tirer parti d'arborescences B-Tree comportant moins de niveaux. En comparaison avec une table indexée complète, les partitions indexées produisent des arborescences B-Tree peu profondes. Les arborescences B-Tree moins profondes sont rééquilibrées beaucoup plus rapidement si le fractionnement a lieu suite à des opérations d'écriture sur l'index et elles sont en outre parcourues en moins de temps par les requêtes SELECT.

En outre, les partitions bien conçues aident les administrateurs de base de données à gérer leurs bases de données. Au niveau d'une partition, les administrateurs de base de données peuvent effectuer toutes les opérations qu'ils effectueraient en temps normal sur une table. Par exemple, les partitions créées sur des critères d'heure prennent en charge l'exportation ou l'importation de données historiques. L'espace pour les nouvelles données peut être alloué en ajoutant une nouvelle partition et les anciennes données peuvent être supprimées par l'élimination d'une partition existante.

Le partitionnement horizontal n'est recommandé que lorsque le moteur de requêtes limite l'accès aux partitions dont on sait qu'elles contiennent les données souhaitées. Si vous utilisez des vues partitionnées locales ou distribuées dans des requêtes plus complexes, comme par exemple des jointures avec les autres tables, le moteur de requête peut utiliser un plan différent contenant toutes les partitions. Pour éviter cela, vous devez concevoir avec soin le schéma logique de votre base de données, de sorte que les données liées soient partitionnées horizontalement afin de réduire les jointures entre partitions.

Pour plus d'informations sur le partitionnement horizontal, consultez les ressources suivantes :

• "Using Portioned Views" (Utilisation de vues partitionnées) dans la documentation "SQL Server Books Online"

• "Creating a Partitioned View" (Création d'une vue partitionnée) dans la documentation "SQL Server Books Online"

• "Designing Partitions" (Conception de partitions) dans la documentation "SQL Server Books Online"

Vues indexées

La fonctionnalité de vue indexée de SQL Server 2000 vous permet de stocker les jeux de données spécifiés dans l'instruction SELECT de la vue qui se trouve de façon permanente dans la base de données. Cette opération est également appelée "matérialisation de la vue". Ce stockage redondant du contenu de la vue présente l'avantage d'accélérer certaines requêtes, au prix d'une utilisation plus importante du disque. Les instructions SQL qui font référence à la vue ou aux tables de base peuvent extraire les données du jeu de données pré-regroupé, constitué des données stockées sous forme de vue indexée. Autrement dit, le moteur de requête tient compte de la vue indexée, même si la requête n'utilise que les tables sous-jacentes. Les insertions et mises à jour des tables de base sont automatiquement appliquées au jeu de stockage de la vue indexée.

Une vue indexée est créée à partir d'une vue classique en appliquant à cette vue un index en cluster unique. Le jeu de données spécifié est ensuite stocké sous forme de cluster ou de structure de mémoire triée. Une fois l'index en cluster créé, tout autre index non en cluster peut être appliqué au jeu de stockage.

Il est recommandé d'utiliser des vues indexées dans les situations ci-après, en raison des temps de réponse nettement meilleurs qui en résultent.

• Pour les applications nécessitant de nombreuses lectures. Les vues indexées sont moins recommandées dans les environnements impliquant de nombreuses écritures, car les insertions ou les mises à jour sur les tables sous-jacentes signifient que SQL Server 2000 doit maintenir des mises à jour complémentaires pour les vues indexées.

• Dans les situations où des instructions SELECT extraient un jeu de résultat qui est un regroupement, notamment les sommes ou autres calculs sur une ou plusieurs tables. Dans ce cas, un jeu de résultat pré-regroupé, stocké sous forme de vue indexée, accélère considérablement les performances en lecture.

Pour créer une vue indexée, procédez de la façon suivante :

1. Créez une vue avec l'option SCHEMABINDING. Cette option n'autorise aucun changement du schéma d'un quelconque des objets sous-jacents participant à la vue matérialisée. Vous ne pouvez pas créer un index sur une vue sans utiliser cette option.

2. Créez un index en cluster unique sur la vue. La vue est matérialisée à ce stade.

3. Créez tout autre index non en cluster sur la vue.

Vous trouverez davantage d'informations sur les vues indexées dans l'article suivant : Les vues indexées de SQL Server 2000 pour une amélioration des performances.

Instructions SQL

Cette section contient des règles sur la façon d'écrire des instructions T-SQL afin d'optimiser les performances.

Comparaison des instructions SQL orientées enregistrement et des instructions SQL orientées jeu

L'accès orienté enregistrement à une base de données signifie qu'une application envoie des requêtes pour chaque ligne au serveur de base de données. Dans le cas le plus défavorable, une requête est équivalente à une seule instruction SQL qui extrait la ligne demandée. La base de données est utilisée comme un opérateur d'accès "factice", qui fournit des lignes pour l'application. La logique d'interprétation des lignes est implémentée dans la couche client.

L'accès orienté jeu à une base de données signifie que la requête d'une application renvoie simultanément un certain nombre de lignes de la base de données. Avec cette méthode d'accès, vous impliquez la logique de la base de données dans le traitement des enregistrements. Dans les situations les plus extrêmes, toute la requête est traitée par la base de données, sans intervention à aucun autre niveau.

L'avantage d'une approche orientée jeu par rapport à une approche orientée enregistrement est la réduction des échanges entre le client et la base de données. Les enregistrements n'ont pas besoin d'être transférés au client, ils peuvent être traités sur le serveur. En outre, vous tirez parti de toutes les fonctionnalités d'un serveur de base de données qui permettent d'améliorer les performances, comme par exemple le mécanisme de mise en cache ou les stratégies d'indexation. L'inconvénient de l'utilisation de requêtes orientées jeu est que l'évolutivité peut s'en trouver réduite, dans la mesure où la base de données est une ressource partagée et qu'il est important de réduire au minimum le traitement propre à l'utilisateur.

Il est recommandé d'écrire les instructions T-SQL pour qu'elles soient le plus possible orientées jeu. En d'autre termes, vous devez essayer de remplacer les instructions de contrôle IF……ELSE par la fonction SQL Server 2000 CASE chaque fois que cela est possible. L'ensemble de l'opération est alors traitée dans le contexte du serveur et le gain en termes de performances est considérable, dans la mesure où l'intelligence du serveur de base de données est mise à contribution.

Par exemple, les clients peuvent être classés dans une application en fonction de leurs revenus. Les revenus réels du client peuvent être stockés dans la table revenue (revenus) suivante :

Create table revenue(

Cust_id              int    NOT NULL PRIMARY KEY,

Tot_sale             money,

Cust_type            varchar(2))

Les commandes des clients sont placées dans la table order (commande) suivante :

create table orders(

ord_id        int    NOT NULL PRIMARY KEY,

ord_item_id   int,

ord_it_name   varchar(20),

amountint    NOT NULL,

unit_price           money,

sale AS (amount * unit_price),

cust_id              int)

Les revenus de chaque client sont identifiés et classés selon la méthodologie de l'analyse ABC. Les clients "A" ont un revenu supérieur à 10 000 $, les clients "B" ont un revenu compris entre 1 000 $ et 10 000 $, et les clients "C" ont un revenu inférieur à 1 000 $. Insérez (INSERT) ces informations dans la table revenue.

Pour la première solution, supposez que vous avez 100 clients, avec cust_id présentant des valeurs de 1 à 100. Dans ce cas, le code suivant, qui utilise une seule instruction SELECT, fonctionnera :

CREATE PROCEDURE classify_cust

AS

DECLARE @h\_ci int, @h\_tot\_sale money, @h\_cust\_type varchar(2)

SET @h\_ci =0

while @h\_ci <= 100

SET @h\_ci = @h\_ci + 1

BEGIN

Select sum(sale) AS sales  INTO   h_tot_sale

From orders

Where cust_id = @h\_ci

IF @h\_tot\_sale <= 1000

SET @h\_cust\_type = 'C'

IF @h\_tot\_sale > 1000 and @h\_tot\_sale < 10000

SET @h\_cust\_type = 'B'

ELSE

SET @h\_cust\_type = 'A'

insert into revenue(cust_id, tot_sale, cust_type) values (@h_ci, @h\_tot\_sale, @h\_cust\_type)

END

Dans cette procédure, chaque enregistrement de la table orders est traité de façon individuelle. Utilisez l'instruction SELECT unique SELECT … INTO … Cette instruction SELECT est traitée pour chaque enregistrement. La base de données est utilisée comme un opérateur d'accès factice, qui fournit des lignes pour l'application. Toute l'intelligence liée à l'interprétation des lignes se trouve au niveau de l'hôte (client).

Il serait préférable d'utiliser un curseur et d'extraire les données du curseur. L'avantage de la solution précédente est que l'instruction SELECT n'est traitée qu'une seule fois. Le jeu de résultat est conservé dans le curseur. Chaque ligne est ensuite extraite du curseur en vue de son traitement ultérieur dans le contexte des variables de l'hôte.

CREATE PROCEDURE classify_cust

AS

DECLARE @h\_ci int, @h\_tot\_sale money, @h\_cust\_type varchar(2)

DECLARE cust_sale CURSOR  FOR

Select cust_id, sum(sale) AS sales

From orders

group by cust_id

OPEN cust_sale

FETCH NEXT FROM cust_sale INTO @h\_ci, @h\_tot\_sale

WHILE @@FETCH_STATUS = 0

BEGIN

IF @h\_tot\_sale <= 1000

SET @h\_cust\_type = 'C'

IF @h\_tot\_sale > 1000 and @h\_tot\_sale < 10000

SET @h\_cust\_type = 'B'

ELSE

SET @h\_cust\_type = 'A'

insert into revenue(cust_id, tot_sale, cust_type) values (@h_ci, @h\_tot\_sale, @h\_cust\_type)

FETCH NEXT FROM cust_sale INTO @h\_ci, @h\_tot\_sale

END

CLOSE cust_sale

DEALLOCATE cust_sale

Dans cette solution, vous continuez de traiter chaque enregistrement individuel en l'extrayant dans les variables (h_ci, h_tot_sale, h_cust_type) de l'hôte (client) afin de classer les clients à l'aide de la syntaxe IF … ELSE suivie d'une instruction INSERT dans la table revenue.

SQL Server 2000 peut effectuer entièrement cette itération sans traiter chaque enregistrement unique au niveau de l'hôte. Pour cela, utilisez l'opérateur de jeu CASE, qui peut être utilisé dans la liste SELECT d'une instruction T-SQL.

insert into revenue(cust_id, tot_sale, cust_type)

Select cust_id, sum(sale) AS tot_sale, cust_type = CASE

                                         WHEN sum(sale)       <= 1000 THEN 'C'

                                          WHEN sum(sale) > 1000 and sum(sale) < 10000 THEN 'B'

                                         WHEN sum(sale) >= 10000 THEN 'A'

                                         END

From orders

group by cust_id

L'ensemble de la procédure est désormais écrit en une seule instruction T-SQL. L'ensemble du traitement est effectué dans le serveur de base de données. Ainsi, le nombre de communications entre le client et le serveur de base de données est réduit à un minimum. Le serveur prend en charge le traitement de type quantité, contrairement au traitement de type ligne illustré dans les deux premiers exemples.

L'échantillon de base de données illustre ces temps de réponse pour les différents modèles d'accès :

 

Temps écoulé

Temps processeur en ms

E/S

Solution 1

14 200

4 317

53 103

Solution 2

12 916

4 126

57 602

Solution 3

1 170

100

4 314

Vous trouverez davantage d'informations sur Transact-SQL dans les ressources suivantes :

• "CASE" dans la documentation "SQL Server Books Online"

• The Guru's Guide to Transact-SQL (Le guide du gourou de Transact-SQL) par Ken Henderson

Différences entre les jeux

SQL est un langage non orthogonal, ce qui signifie qu'il est possible d'utiliser différentes instructions SQL pour parvenir au même jeu de résultat. En général, chaque variante présente des inconvénients différents en termes de performances, en raison de la façon dont le processeur de requêtes analyse les instructions (par exemple, la quantité de données à extraire, le nombre de lignes des tables, le modèle de données physique). Il n'est pas possible de faire une recommandation générale sur la formule préférée, mais vous devez être conscient du fait qu'il existe différents moyens d'extraire les mêmes jeux de résultats.

Cette section présente trois solutions pour extraire le même jeu de données. Cet exemple développe celui de la section précédente, "Comparaison des instructions SQL orientées enregistrement et des instructions SQL orientées jeu". Dans ce scénario, vous souhaitez voir les commandes qui ne comportent aucune entrée dans la table revenue (revenus).

Les revenus réels du client sont stockés dans la table suivante :

Create table revenue(

Cust_id              int    NOT NULL PRIMARY KEY,

Tot_sale             money,

Cust_type            varchar(2))

Les commandes des clients sont placées dans la table order (commande) suivante :

create table orders(

ord_id        int    NOT NULL PRIMARY KEY,

ord_item_id   int,

ord_it_name   varchar(20),

amountint    NOT NULL,

unit_price           money,

sale AS (amount * unit_price),

cust_id              int)

Il existe trois solutions possibles pour l'extraction des jeux répondant à la requête de l'utilisateur. Il s'agit des suivantes :

• sous-requête non corrélée ;

• sous-requête corrélée ;

• jointure externe.

Sous-requête non corrélée

Cette sous-requête est non corrélée, car il n'existe aucun point de référence entre les instructions SELECT interne et externe :

select od.ord_id, od.ord_it_name, od.amount

from orders od

where cust_id NOT IN (select cust_id from revenue)

Ainsi, l'instruction SELECT interne est toujours évaluée dès le départ, puis son résultat est transmis à l'instruction SELECT externe, comme le montre la Figure 5.

Figure 5. Plan d'exécution d'une requête pour une sous-requête non corrélée

Pour obtenir la différence entre la table orders et la table revenue, le moteur de requête insère les données de la colonne cust_id de la table revenue dans une table temporaire. Ces données sont ensuite analysées pour chaque entrée de la clé primaire de la table orders, en raison de la jointure de boucle imbriquée qui effectue la jointure (externe).

Sous-requête corrélée

Cette sous-requête contient une ligne itérative de référence ou de données avec l'instruction SELECT externe :

select od.ord_id, od.ord_it_name, od.amount

from orders od

where NOT EXISTS (select rv.cust_id from orders od, revenue rv where od.cust_id = rv.cust_id)

Ainsi, les sous-requêtes corrélées doivent être fournies et traitées avec la valeur de la colonne par ligne de l'instruction SELECT externe, comme le montre la Figure 6.

Figure 6. Plan d'exécution d'une requête pour une sous-requête corrélée

Dans ce cas, le moteur de requête effectue d'abord une jointure (interne) sur la table revenue et la table orders de la sous-requête. Les données cust_id résultantes sont insérées dans la table temporaire. Ensuite, la jointure de boucle imbriquée effectue l'opération JOIN externe nécessaire pour déterminer la différence entre les jeux.

Jointure externe

De la même façon, vous pouvez utiliser une requête SELECT pour extraire le même jeu de résultat à l'aide d'une condition JOIN.

SELECT od.ord_id, od.ord_it_name, od.amount

FROM orders od LEFT OUTER JOIN revenue rv ON od.cust_id = rv.cust_id

WHERE rv.cust_id is NULL

Cette requête est illustrée Figure 7 :

Figure 7. Plan d'exécution d'une requête pour une jointure externe

Ce plan d'exécution est semblable à celui de la sous-requête non corrélée, excepté concernant le nœud de filtrage. Le nœud de filtrage est nécessaire pour déterminer quelles lignes sont spécifiées dans le prédicat WHERE.

Utilisation d'un index pour éviter une table temporaire

Nous avons vu trois instructions SQL dont la syntaxe est différente mais qui sont identiques d'un point de vue sémantique, puisqu'elles extraient toutes le même jeu de lignes. Chacune d'elle, à savoir la sous-requête non corrélée, la sous-requête corrélée et la solution par jointure externe, présente un plan d'exécution différent. La solution la plus rapide dépend de la situation spécifique (volume réel de données, charge actuelle, etc.).

Les trois plans d'exécution ont un point en commun : le moteur de requête doit insérer des lignes de données dans une table temporaire (qui est stockée dans la base de données tempdb et qui n'existe que pendant la durée de vie de la requête). En fonction du volume de données, cela peut entraîner une requête relativement longue. Afin d'éviter cela, voici une stratégie d'indexation qui donne lieu à un plan d'exécution sans table temporaire :

1. Supprimez la clé primaire sur la table orders.

2. Créez une nouvelle clé primaire avec l'option suivante :

alter table orders add CONSTRAINT pk_nc primary key NONCLUSTERED (ord_id);

3. Triez les données de la table en fonction de la clé étrangère :

create clustered index idx_orders on orders(cust_id)

La Figure 8 illustre ce plan d'exécution :

Figure 8. Plan d'exécution de la requête avec index en cluster

Le nouveau plan d'exécution n'utilise pas de table temporaire. L'algorithme JOIN de boucle imbriquée est remplacé par une opération JOIN de fusion, car le moteur de requête tient compte du fait que les données des deux tables sont désormais stockées dans l'ordre de tri des critères de jointure. SQL Server 2000 doit simplement extraire les lignes de façon séquentielle et les fusionner afin d'effectuer l'opération JOIN externe.

Comme nous l'avons vu dans cet exemple, il existe généralement de nombreuses façons d'écrire des instructions SQL. Il est recommandé d'évaluer chaque instruction SQL, puis d'implémenter la plus adaptée à vos besoins en termes de performances. Dans ce cas, la table temporaire peut conduire à un goulet d'étranglement de performances. Le seul moyen pour éviter cela est de créer un index en cluster sur la clé utilisée dans la jointure.

Vous trouverez davantage d'informations sur l'utilisation de jointures et de sous-requêtes dans les ressources suivantes :

• Hash Joins and Hash Teams in Microsoft SQL Server (Jointures de hachage et équipes de hachage dans Microsoft SQL Server) par Graefe, Bunker et Cooper

• Understanding the New SQL: A Complete Guide (Guide complet du nouveau langage SQL) par Simon Melton

• "Creating Outer Joins" (Création de jointures externes) dans la documentation "SQL Server Books Online"

Tri des données

Cette section vous aide à atteindre vos objectifs en termes de performances lorsqu'il est nécessaire de trier les jeux de résultat. Les lignes triées sont traitées de façon stricte à l'aide de la clause ORDER BY. L'utilisation des opérateurs DISTINCT ou UNION nécessite de trier pour supprimer les valeurs dupliquées.

Lorsque aucun ordre de tri n'est spécifié, les lignes sont généralement sélectionnées en fonction de leur emplacement physique sur le disque et en fonction de la stratégie d'accès choisie par le moteur de requête.

À la fois les opérations JOIN et les opérations de tri des données peuvent prendre beaucoup de temps. Ainsi, l'un de vos objectifs en termes d'optimisation sera d'éviter le tri dans le plan d'accès. Il n'est pas recommandé de réécrire les requêtes qui nécessitent un tri, mais il est conseillé de mettre en place une structure de stockage appropriée pour vos requêtes qui nécessitent un tri, de sorte que le tri devienne inutile.

C'est par exemple le cas lorsque vous souhaitez analyser les commandes d'un client, triées en fonction de leur montant. Par défaut, les lignes sont sélectionnées par ordre croissant.

select ord_id, cust_id, ord_it_name, amount

from orders

where cust_id = ‘value'

order by amount

Indexation avec colonne de tri

Les commandes des clients sont placées dans la table order (commande) suivante :

create table orders(

ord_id        int    NOT NULL,

ord_item_id   int,

ord_it_name   varchar(20),

amountint    NOT NULL,

unit_price           money,

sale AS (amount * unit_price),

cust_id              int)

alter table orders add primary key NONCLUSTERED (ord_id)

create index idx_ord1 on orders(cust_id)

SQL Server 2000 utilise le plan d'exécution illustré Figure 9 pour déterminer le jeu de résultat :

Figure 9. Plan d'exécution pour trier les données

Le plan d'accès montre trois nœuds typiques : une analyse d'index pour déterminer la valeur spécifiée dans le prédicat WHERE, une recherche de signet pour extraire les données spécifiées dans la liste SELECT, ainsi qu'un nœud de tri pour trier les résultats en fonction de la clause ORDER BY.

L'optimiseur décide de la façon dont le tri doit être effectué, en fonction des options d'accès physique disponibles et d'autres critères. Vous pouvez à présent tenter d'examiner les effets de l'utilisation d'un accès physique trié. La stratégie d'optimisation présentée ici est destinée à réduire l'utilisation des ressources pour le traitement d'une instruction SQL. Le plan d'exécution illustre trois étapes qu'il est nécessaire d'accomplir pour traiter l'instruction SQL. Des améliorations des performances peuvent ensuite être réalisées par la réduction des trois étapes.

Create index idx_ord2 on orders (cust_id, amount)

Dans ce cas, la valeur donnée de cust_id (cust_id = ‘value') est triée selon la colonne amount (montant). Avec cette information, l'optimiseur doit pouvoir établir le taux de correspondance à l'aide de l'index, comme le montre la Figure 10.

Figure 10. Plan d'exécution sans tri

Comme le montre ce plan d'exécution, l'optimiseur établit le taux de correspondance sans tri complémentaire. Cela signifie que l'optimiseur reconnaît l'ordre physique dans l'index idx_ord2 sans aucune autre intervention manuelle. Le plan d'exécution contient également les deux nœuds suivants :

• L'index non en cluster idx_ord2 détermine le taux de correspondance, satisfaisant ainsi la condition de la requête dans le prédicat WHERE.

• Les autres colonnes de la liste SELECT sont extraites via l'accès à la table de base, ce qui est indiqué par la recherche de signet.

Accès par index seul

Une autre méthode d'optimisation peut être implémentée pour la requête illustrée dans l'exemple précédent. L'expression contient uniquement les colonnes ord_id, cust_id, ord_it_name et amount. Ces colonnes sont stockées de façon redondante dans l'index suivant :

Create index idx_ord3 on orders(cust_id, amount, ord_id, ord_it_name);

Figure 11. Plan d'exécution avec de recouvrement

Remarquez que, dans ce plan d'exécution, le jeu de résultat est désormais entièrement extrait de l'index idx_ord3. Le tri et l'accès à la table de base sont supprimés. Cette méthode est connue sous le nom d'opération d'index de recouvrement, ou d'accès par index seul.

Accès par index en cluster

Une alternative à l'opération d'index de recouvrement consiste à utiliser une table en cluster qui recouvre les lignes de données en fonction de l'ordre de tri requis.

Create unique clustered index idx_ord4 on orders(cust_id, amount, ord_id);

Il n'est pas nécessaire d'impliquer toutes les colonnes dans le nouvel index, dans la mesure où SQL Server 2000 extrait toute la ligne au niveau feuille de l'arborescence d'index en cluster, comme le montre la Figure 12.

Figure 12. Plan d'exécution avec index en cluster

Dans ce plan d'exécution, la requête est de nouveau satisfaite uniquement par l'accès à l'index en cluster et non par le tri des lignes renvoyées par les pages de données réelles. Vous obtenez ainsi un plan d'exécution à "ligne unique", semblable à celui obtenu via l'opération d'index de recouvrement. Mais ce concept va au-delà et présente deux avantages :

• La structure de stockage sélectionnée ici permet la lecture et la sélection simultanées des entrées de l'index idx_ord4 ainsi que des lignes correspondantes de la table de base orders.

• Il n'est pas nécessaire de stocker toutes les colonnes de l'expression de façon redondante dans un index.

Vous trouverez davantage d'informations sur le tri de données dans les ressources suivantes :

"New Features for Query Processing" (Nouvelles fonctionnalités de traitement des requêtes), par Dusan Petkovic et Christian Unterreitmeier, SQL Server Magazine  , juillet 1999.

Identification des goulets d'étranglement

La première étape dans la recherche des goulets d'étranglement des performances consiste à identifier les fonctions ou instructions importantes qui sont responsables de la charge la plus élevée. Définissez les valeurs caractéristiques des opérations au cours d'un intervalle donné en fonction de la fréquence, de la priorité, du temps de réponse demandé et de la répartition de la charge. Vous ne pouvez atteindre les objectifs de performances de l'application que si la conception de ces fonctions est optimale.

Questions associées

Recueillez les scénarios d'utilisation pour les mesures de performances. Les questions suivantes doivent vous aider, en plus de toute question liée aux autres facteurs qui peuvent s'avérer importants dans des cas particuliers :

• Quelles sont les tâches les plus fréquentes (appels des utilisateurs finaux, appels d'interface, appels système, etc.) ?

• Quelles sont les fonctions les plus critiques en termes de durée (par exemple, la sélection de données dans des tables différentes pour remplir une seule boîte de dialogue pour l'utilisateur final) ?

• Quelles sont les fonctions les plus importantes (tâche principale de votre application) ?

• Quelles fonctions consomment le plus de ressources (fonctions très complexes eu égard à l'algorithme implémenté) ?

Collectez chaque fonction ou instruction dans un script, de sorte que vous puissiez l'exécuter sur votre base de données. Par exemple, la fonction peut être une instruction (ou un groupe d'instructions) SQL, des procédures T-SQL, ou encore faire partie du code qui envoie des instructions T-SQL à la base de données afin d'extraire des données. Pour tester et mesurer vos interventions relatives à l'optimisation, vous devez exécuter plusieurs fois les scripts collectés afin de vous assurer de leurs performances au cours des différentes étapes de votre processus de déploiement.

Configuration de l'environnement de contrôle

Nous avons présenté de nombreux aspects de SQL Server 2000 qui peuvent être optimisés, comme par exemple la configuration, les modèles de données physiques et logiques, ou encore les instructions SQL. Cette section indique comment mesurer les performances à l'aide des outils fournis avec SQL Server 2000.

Les performances globales d'un système peuvent généralement être définies par la durée d'un certain scénario d'utilisation ou de test. La durée est le temps nécessaire pour exécuter l'ensemble du scénario d'utilisation, donc également le temps pendant lequel l'utilisateur doit attendre. En termes de performances, la durée d'un scénario de test est définie de la façon suivante :

Durée = temps processeur + E/S mesurées

Bien qu'il ne s'agisse pas d'un calcul mathématique exact de la durée totale, cette formule contribue à déterminer si les performances du scénario de test dépendent du processeur ou des E/S, et elle offre également des pistes pour de futures interventions d'optimisation.

Pour contrôler et effectuer le suivi de l'application SQL Server 2000, utilisez les trois outils suivants :

• Analyseur de performances SQL

• Profileur SQL

• Analyseur de requêtes SQL

Analyseur de performances SQL

Utilisez l'Analyseur de performances SQL pour mesurer l'activité du processeur, les activités d'E/S et l'utilisation de la mémoire.

Pour mesurer l'utilisation du processeur, exécutez votre scénario de test afin de vous assurer que le travail est bien effectué. Pour contrôler l'utilisation du processeur, utilisez le compteur prédéfini suivant :

• le compteur % Temps Utilisateur de l'objet Processeur

Vous devez sélectionner tous les processeurs en spécifiant le nombre qui apparaît dans la fenêtre. Le compteur indique l'utilisation du processeur par seconde.

Il est important de noter que si l'utilisation du processeur reste en permanence à 80 % ou plus, cela indique un goulet d'étranglement au niveau du processeur. Vous devez ensuite commencer à analyser votre scénario de test en détail. Voici quelques raisons qui peuvent provoquer ce goulet d'étranglement :

• le nombre et type d'instructions SQL ;

• le nombre et type d'opérations JOIN ;

• le nombre d'opérations de tri.

Utilisez le Profileur SQL et/ou l'Analyseur de requêtes SQL pour obtenir une analyse plus détaillée de la requête identifiée.

Le compteur prédéfini suivant est utile pour contrôler les activités de disque :

• le compteur Transferts disque/s de l'objet Disque physique.

Voici quelques-unes des causes possibles d'un goulet d'étranglement d'E/S suite à d'importantes activités sur le disque :

• la structure du modèle de données physique, par exemple le nombre de fichiers de données et d'index ;

• la configuration RAID, par exemple le nombre de jeux de stockage ;

• le modèle de données logique, par exemple le partitionnement ;

• les instructions SQL, par exemple la structure des transactions.

Le cache de données est l'aspect le plus important de SQL Server 2000. Le compteur prédéfini suivant est réservé à la mesure du pourcentage de pages de données qui sont trouvées dans le cache de données plutôt que lues sur le disque :

• le compteur Taux d'obtention de résultat des accès cache de l'objet Gestionnaire de tampon.

Il est recommandé d'avoir un taux de correspondance supérieur à 95 %. Si la valeur observée est nettement inférieure, procédez de la façon suivante :

• Vérifiez la configuration du paramètre max-server-memory dans SQL Server 2000.

• Ajoutez davantage de mémoire.

Profileur SQL

Utilisez le Profileur SQL pour rechercher les instructions SQL critiques et pour mesurer les effets des interventions d'optimisation. Avec le Profileur SQL, vous pouvez facilement contrôler le temps requis par l'exécution d'une instruction SQL unique. Concentrez votre attention sur les colonnes durée, lectures, écritures et processeur pour trouver les instructions SQL critiques.

Analyseur de requêtes SQL

Utilisez l'Analyseur de requêtes SQL pour voir le plan d'exécution des requêtes et observer les interventions. Pour voir des exemples spécifiques, consultez les sections "Différences entre les jeux" et "Tri des données".

Extension avec des fédérations SQL Server

SQL Server 2000 offre des performances impressionnantes lorsque l'application est optimisée et que le matériel adéquat est utilisé. Cependant, lorsqu'une base de données et une application entièrement optimisées font face à une charge supérieure aux prévisions raisonnables sur un serveur unique, l'extension peut s'avérer intéressante. L'extension offre l'évolutivité à un système pour lequel le débit est plus important que la rapidité de chaque transaction individuelle. L'extension à l'aide de SQL Server 2000 permet la prise en charge des énormes charges de traitement et exigences de croissance des plus gros systèmes Web ou d'entreprise.

Problèmes d'évolutivité des bases de données

Pour tirer un meilleur parti de votre application, vous envisagerez sans aucun doute un moyen de faire en sorte qu'elle accepte une charge de travail supérieure et qu'elle soit plus rapide. Cependant, il est important de se rendre compte que l'évolutivité n'est qu'un des facteurs qui constituent les performances globales d'une application. Vous devrez affecter des priorités à vos exigences et les équilibrer d'une façon qui tienne compte des exigences du système ainsi que du personnel qui assure la gestion du système. Lors de l'étude des performances d'une base de données, il est courant que les professionnels des bases de données mesurent la réussite en termes de débit, de temps de réponse et de disponibilité.

Le débit est une mesure du nombre de transactions que la base de données peut traiter simultanément et avec succès. Cela se mesure généralement en transactions par seconde, mais une mesure plus précise peut être obtenue via le compteur \SQLServer:SQL Statistics\Batch Requests/sec par rapport au compteur \Processor(_Total)\% Temps processeur. Ces deux compteurs doivent évoluer en parallèle. Le temps de réponse réel peut être mesuré sur le serveur via la durée de chaque requête.

Le temps de réponse obtenu implique toutes les parties du système et peut être mesuré comme étant l'intervalle entre le moment où un utilisateur envoie une requête et le moment où il reçoit les résultats. En général, la plupart des approches visant à obtenir une évolutivité élevée cherchent à optimiser le débit, mais vous devez toujours étudier les conséquences de la structure sur le temps de réponse. Pour être considérée comme évolutive, une application de base de données doit prendre en charge le débit nécessaire pour anticiper le nombre d'utilisateurs simultanés prévus, tout en offrant des temps de réponse acceptables pour toutes les requêtes utilisateur.

Avant de décider d'une extension, vous devez étudier soigneusement la structure de votre application. L'extension va généralement de pair avec l'augmentation de la complexité de la structure et de la gestion du système. L'extension doit donc être l'étape finale de l'amélioration de l'évolutivité d'une base de données déjà hautement évolutive et performante. Dans la plupart des cas, vous devez procéder à une évolution verticale plutôt qu'à une extension, sauf si vous travaillez sur un système distribué.

La liste ci-après pose des questions élémentaires de conception, qui peuvent également être utilisées pour quantifier les avantages de l'extension pour votre système, qu'il s'agisse d'un système existant, d'un nouveau système ou d'un système migré à partir d'une autre plate-forme de base de données.

• Essayez-vous de prendre en charge une quantité élevée de transactions simultanées ? La décision de procéder à une extension est le plus souvent dictée par la nécessité de prendre en charge un volume supérieur à la capacité d'un gros serveur multiprocesseur. Le volume des transactions n'est pas le vrai problème ; il s'agit plutôt de la charge globale sur le système de gestion de base de données (SGBD).

  Les problèmes de simultanéité sont souvent dus à des problèmes de conception des applications, mais peuvent également être dus à des restrictions d'E/S sur les disques. Ce problème peut être résolu en ajoutant des disques physiques, en ajoutant des canaux de contrôleur de batterie supplémentaires ou plus performants, en utilisant des groupes de fichiers sur plusieurs jeux importants de disques, ou en augmentant de façon significative la quantité de mémoire afin de réduire les E/S physiques à l'aide d'un cache plus grand. Dans la mesure où l'application de solutions standard permet d'accroître la simultanéité, ce seul problème ne justifie pas de passer à un scénario de fédération.

  Les exigences en termes de capacité de traitement constituent en revanche le facteur essentiel. La raison principale de l'extension est que vos exigences dépassent la capacité des processeurs du matériel SMP (Symmetric MultiProcessing) le plus performant dont vous disposez.

• Le débit des transactions est-il plus important que la vitesse de chaque requête individuelle ? Cette question devient importante dès lors que votre application nécessite régulièrement des données provenant d'une partition sur un serveur distant. Selon quelle fréquence devez-vous recueillir des données de plusieurs serveurs pour créer un jeu de résultat ? Plus le nombre d'appels à destination de serveurs distants pour recueillir les données permettant de satisfaire la requête sera faible, meilleures seront les performances.

  Plus vous recueillez fréquemment des données distantes pour le jeu de résultat total, plus le problème des performances s'accentue. Cependant, le problème est moindre lorsqu'une requête est utilisée uniquement de façon occasionnelle ou lorsque la requête peut être dirigée vers le serveur contenant la majeure partie des données associées à ce jeu de résultat. Par exemple, si un jeu d'informations est fréquemment demandé, vous devez organiser les données et les requêtes de sorte que les requêtes s'exécutent sur le même nœud que les données, et de sorte qu'un maximum d'informations associées soient disponibles localement sur ce nœud.

• Pouvez-vous stocker des données partitionnées associées sur un même serveur de la fédération (on parle de cohabitation des données), puis diriger la requête entrante vers le serveur qui possède ces données ? Un facteur essentiel à prendre en considération pour l'extension est la façon dont les données seront organisées, ainsi que le mode d'accès aux données, en fonction des tranches de la base de données.

  Le caractère local (la "localité") des données est un facteur essentiel pour les performances ; il s'agit de mesurer quelle proportion d'une requête peut être effectuée avec des données locales plutôt qu'avec des données distantes. Si le degré de localité des données est élevé, les performances seront meilleures.

• Quelle quantité de données doit se déplacer entre les serveurs ? Vous pouvez déterminer ce chiffre en multipliant le nombre de lignes par le nombre d'octets par ligne. Si vous transférez fréquemment une grande quantité de données, avec des milliers de kilo-octets par ensemble de lignes, les conséquences sur les performances doivent influencer votre conception. Dans ce cas, recherchez un moyen plus optimal de partitionner vos données, ou une meilleure façon de traiter l'accès aux données.

  Comme pour tous les aspects d'une base de données, la bonne approche concernant le partitionnement des données dépend de l'utilisation. Pour les requêtes qui sont utilisées fréquemment, examinez les données impliquées et déterminez selon quelle fréquence elles sont exécutées, par rapport à la quantité de données demandées. Si vous ne pouvez pas faire cohabiter les données associées, calculez la quantité de données qui devrait être demandée par les serveurs distants. Si les ensembles de lignes impliqués sont volumineux (des milliers de kilo-octets) et que la fréquence des requêtes est élevée, envisagez une organisation différente des données. Si les ensembles de lignes sont volumineux, mais que la requête ne sera exécutée que de façon occasionnelle, les performances globales pour le système peuvent s'avérer acceptables.

• Quelle est la taille des données impliquées ? Il est facile de supposer que vous devez simplement partitionner vos tables les plus volumineuses. Les entreprises qui envisagent ce scénario disposent souvent de tables volumineuses, par exemple plus d'un milliard de lignes réduites ou plus de 20 millions de lignes étendues (plus de 5 ko). Cependant, si vous utilisez le partitionnement dans le cadre d'une solution d'extension, vous devez partitionner en fonction des modèles d'accès aux données et pas uniquement choisir les tables les plus volumineuses (même si elles présentent une taille de l'ordre du téraoctet). Certaines tables partitionnables peuvent croître de façon importante, mais la taille n'est pas le seul facteur décisif.

  La taille des données est également un facteur important concernant la quantité de données échangées entre les serveurs. Bien que les volumes élevés puissent poser problème dans certains cas extrêmes, la connexion serveur peut facilement prendre en charge des échanges de centaines ou de milliers de lignes par transaction, en fonction du nombre d'octets des lignes en question. L'échange de volumes élevés de données ne devient un problème que si le volume absolu de trafic distant dégrade le temps de réponse jusqu'à un niveau inacceptable.

Questions relatives à la conception de fédérations

Les fédérations de serveurs sont des groupes d'ordinateurs qui exécutent SQL Server et sur lesquels sont réparties les données pour une application particulière. Par de nombreux aspects, une fédération de serveur est semblable à un groupe de serveurs Web en cluster, en ce sens qu'elle inclut plusieurs serveurs qui répartissent entre eux la charge de traitement. Cependant, il existe une différence fondamentale entre une fédération SQL Server et un cluster à équilibrage de charge. Dans un cluster à équilibrage de charge, les serveurs sont des réplicas exacts l'un de l'autre, ce qui permet à toutes les requêtes utilisateur d'être traitées par n'importe quel membre du cluster. Dans une fédération SQL Server, chaque serveur contient un sous-ensemble des données, et les requêtes sur une donnée spécifique ne peuvent être satisfaites que par le serveur sur lequel se trouve cette donnée.

Remarque : Etant donné que le mot "cluster" est souvent utilisé pour faire référence à un groupe de serveurs, prenez soin de bien faire la différence entre une "fédération" et un cluster de basculement. Bien que les deux configurations puissent être utilisées ensemble, il s'agit de technologies totalement différentes.

La conception d'une solution d'extension, quel qu'en soit le type, nécessite une analyse ainsi qu'une soigneuse planification. Plusieurs techniques peuvent être appliquées pour cela :

• le routage dépendant des données ;

• les vues partitionnées distribuées ;

• les méthodes de partitionnement définies par l'utilisateur (comme par exemple le hachage) ;

• la réplication ;

• la mise en file d'attente des messages (MSMQ, Message Queuing) ;

• une combinaison de ces techniques.

Bien que ces techniques puissent être combinées de nombreuses façons différentes, en fonction de vos besoins, le scénario classique de fédération associe l'utilisation des vues partitionnées distribuées au routage dépendant des données, généralement avec une certaine forme de réplication. Il ne s'agit cependant pas d'une structure imposée, et vous pouvez décider d'utiliser simplement une de ces stratégies, en fonction de vos besoins.

La première étape dans la conception d'une fédération consiste à déterminer comment partitionner les données. Pour qu'elle fonctionne correctement, la partition doit être créée sur une valeur de clé qui peut être utilisée exclusivement pour orienter les requêtes vers la partition de données appropriée. Par exemple, dans une solution de commerce électronique, vous pouvez partitionner sur plusieurs serveurs les données des profils utilisateur (tels que les noms, les adresses, les adresses de messagerie, les numéros de téléphone, etc.), comme le montre la Figure 13.

Figure 13. Une fédération SQL Server

Dans cet exemple, SQLServer1 contient tous les enregistrements des profils client pour les clients 1000 à 1999, SQLServer2 contient les clients 2000 à 2999, et SQLServer3 contient les clients 3000 à 3999. Vous pouvez utiliser n'importe quelle clé pour partitionner les données, mais vous devez essayer d'utiliser une clé qui donne lieu à une répartition plus ou moins égale en fonction de l'utilisation sur les différents serveurs. Il est essentiel de comprendre que les données doivent être réparties en fonction de leur utilisation, et non en fonction du nombre de lignes. Si la structure de votre application ne vous permet pas de répartir facilement les données en fonction de leur utilisation, vous pouvez envisager le partitionnement par hachage, une technique qui permet une répartition plus aléatoire. Les partitions de hachage sont étudiées plus loin dans ce document.

Bien entendu, certaines applications peuvent nécessiter l'extraction de données à partir de plusieurs partitions. Pour permettre ce type d'accès, une abstraction des tables sous-jacentes doit être créée.

L'un des moyens de créer une abstraction des partitions à partir de l'application client consiste à créer une vue partitionnée distribuée (DPV, Distributed Partitioned View) sur chaque serveur de la fédération. Cette vue réunit les données distribuées en associant les données du serveur local et des serveurs distants, afin de produire une vue consolidée unique de toutes les données de la table partitionnée. Lorsque la vue de chaque serveur de la fédération est dupliquée, toutes les données de la table partitionnée sont rendues disponibles, indépendamment du serveur auquel l'application client se connecte. La Figure 14 illustre une vue partitionnée distribuée.

Figure 14. Une vue partitionnée distribuée

Dans cet exemple, une vue partitionnée distribuée nommée pv_CustomerProfiles est définie sur tous les serveurs de la fédération. Cette vue peut être utilisée pour extraire des données d'une quelconque des tables sous-jacentes, afin d'offrir une vue consolidée unique des données client. SQL Server 2000 permet la prise en charge des vues partitionnées distribuées pouvant être mises à jour, ce qui permet la modification des données de plusieurs serveurs par l'intermédiaire d'une vue, si les exigences de conception sont satisfaites (pour plus de détails, consultez SQL Server 2000 Books Online). En outre, le moteur de requête SQL Server 2000 peut évaluer de façon intelligente les requêtes sur les vues partitionnées distribuées, afin d'éviter les requêtes inutiles sur le serveur distant.

Cependant, l'objectif principal de la vue partitionnée distribuée est de créer une couche d'abstraction qui facilite la compréhension du concept de la fédération. Bien qu'elle soit efficace, elle présente un inconvénient : si un serveur de la fédération devient indisponible, la vue renvoie un message d'erreur après la recompilation du plan de requête (par exemple, si vous interrogez la vue peu fréquemment, de sorte qu'elle n'est pas stockée dans le cache), ou bien les données de la partition indisponible sont demandées.

En raison de cet inconvénient, vous pouvez décider de réserver les DPV aux situations qui nécessitent une simplicité administrative, et les ignorer dans la conception de votre application. Vous pouvez également décider d'utiliser le routage dépendant des données ou le partitionnement défini par l'utilisateur (décrit plus loin dans ce document), chaque fois que cela est possible.

Les partitions de données (également appelées membres de partitions) sont semblables à n'importe quelle table, à ceci près qu'elles représentent une sous-section d'un jeu de données plus important, et qu'elles sont toujours indexées et interrogées en fonction de la clé de partitionnement. Si la table partitionnée participe à une DPV, la clé doit satisfaire les exigences particulières du partitionnement et fait l'objet d'une contrainte de vérification. Consultez la documentation "SQL Server Books Online" pour en savoir plus.

En clair, vous devez être capable de gérer le système que vous concevez. La gestion de serveurs fédérés est complexe en soi ; il ne serait donc pas judicieux d'augmenter cette complexité en ajoutant des difficultés d'administration inutiles. Le coût de la complexité administrative peut être mesuré en termes de coût du support, pas uniquement en ce qui concerne le personnel supplémentaire requis, mais également concernant les compétences nécessaires pour accomplir le travail. En outre, lorsque la complexité d'un problème dépasse les compétences ou le temps disponible, cela entraîne des périodes d'interruption du système. Votre groupe doit pouvoir évaluer le coût des périodes d'interruption par unité de temps, en termes de perte de chiffre d'affaires, de perte de temps, de perte de clients et d'autres coûts moins tangibles, tels que les conséquences sur la réputation professionnelle de l'entreprise si l'application est présentée aux clients. Pour cela, avant d'entreprendre la conception d'une fédération, vous devez être résolu à créer une bonne structure, un solide plan de test et un niveau élevé de support.

En gardant ces problèmes à l'esprit, planifiez soigneusement la façon dont vous allez distribuer et redistribuer les données sur les différentes partitions. Vous pouvez choisir de partitionner plusieurs tables pour faire cohabiter les données qui feront l'objet d'un accès fréquent. Si vous n'utilisez pas de marques de journaux de transactions pour vos sauvegardes et qu'il est indispensable de disposer de données cohérentes dans le cadre des transactions, vous pouvez également envisager de faire cohabiter les données qui doivent être cohérentes pour les transactions. De cette façon, chaque section partitionnée est une unité complète, qu'il s'agisse d'une base de données ou simplement d'un groupe de fichiers (comme cela peut être le cas si vous avez hébergé plusieurs partitions sur le même serveur).

Dans certaines méthodes de partitionnement, il se peut que vous ayez plusieurs partitions sur un même serveur, voire même dans la même base de données. Dans ce cas, il est plus simple de placer les partitions dans des groupes de fichiers distincts. Remarquez toutefois que cela n'est pas essentiel. Même si vous restaurez un groupe de fichiers, la base de données entière reste cohérente dans le cadre des transactions. Il peut donc sembler que la simplicité du concept soit le seul avantage lié à la séparation, en groupes de fichiers, de partitions qui se trouvent dans la même base de données.

Cependant, l'avantage le plus important associé à l'utilisation de groupes de fichiers est la rapidité de la restauration. Si vous avez besoin de restaurer une seule section de votre base de données, plutôt que toute la base de données, le temps d'interruption sera nettement plus court. Dans la mesure où la base de données devra rétablir la cohérence entre le groupe de fichiers restauré et le reste de la base de données, le processus de récupération devrait être encore plus rapide si toutes les données se trouvent dans le même groupe de fichiers. En outre, une telle séparation nécessite nettement moins d'analyse si vous décidez ultérieurement de déplacer les données, car il s'agit déjà d'une unité cohérente. Enfin, les E/S de chaque groupe de fichiers peuvent être contrôlées séparément via fn_virtualfilestats.

Partitionnement des données

Vous devez déterminer soigneusement les tables de votre application de base de données qui bénéficieront du partitionnement et quelles sont les clés les mieux adaptées pour le partitionnement. En général, vous devez partitionner les tables qui font l'objet de l'accès le plus fréquent (pas nécessairement les tables contenant le plus de données). Lors du choix d'une clé de partitionnement, vous devez garder à l'esprit le fait que le partitionnement sur une clé numérique donne de meilleures performances qu'avec une clé basée sur des caractères. Si aucune clé convenable ne peut être trouvée, vous devez soit créer une nouvelle colonne de clé de partitionnement, soit utiliser un algorithme de hachage pour partitionner les données en fonction des champs existants.

Lors de la conception, vous devez vous rappeler que vous partitionnez les données pour que tous les serveurs se partagent équitablement le travail. Pour cela, vous ne pouvez pas simplement partitionner les données pour les répartir en fonction de leur valeur. Vous devez les répartir en fonction de leur utilisation. Cela signifie que vous devez vous attendre à ce que vos partitions ne soient pas de taille égale ; dans certains cas, le nombre de lignes par partition pourra même s'avérer très différent.

Pour comparer, observons un exemple qui ne fonctionnerait pas. Supposons que vous ayez partitionné la table Orders en fonction de la colonne OrderID (identité), de sorte que la première partition contienne les commandes 1 à 999, la deuxième les commandes 1000 à 1999 et la troisième les commandes 2000 à 2999. Bien qu'il s'agisse d'une "répartition équitable", cela donne lieu à deux problèmes immédiats. Tout d'abord, étant donné que les valeurs OrderID sont ajoutées de façon séquentielle, votre troisième partition serait la seule qui continuerait de croître, et vous devriez rapidement repartitionner les données afin d'élargir la plage des valeurs de clés de chaque partition. Ensuite, les commandes les plus récentes feraient l'objet de requêtes plus fréquentes que les autres, ce qui fait que la majeure partie de l'activité concernerait la troisième partition.

Avant de concevoir les partitions, vous devez donc examiner la façon dont les données sont demandées et comprendre comment le système évoluera. Cela peut conduire à une répartition inégale des données en fonction du nombre de lignes, car la quantité de données de chaque partition n'a de sens que si l'on considère la fréquence de l'accès à ces données.

La configuration matérielle des serveurs de votre fédération ne doit pas nécessairement être physiquement identique. Le matériel doit cependant être équipé du même processeur et de la même quantité de mémoire, car vous utilisez (ou devriez utiliser) les partitions de façon égale, mais si vous déterminez qu'une partition comporte beaucoup plus de données, vous pouvez compenser la différence en ajoutant davantage de disques à ce serveur, plutôt que de créer une autre partition sur un nouveau serveur. Si l'utilisation d'une partition est supérieure, de sorte que ses données font l'objet d'un accès plus fréquent et que vous êtes confronté à des goulets d'étranglement au niveau du débit, vous devez soit redistribuer les données afin que les serveurs soient utilisés de façon équitable, soit ajouter un nouveau serveur à la fédération. Votre structure doit être suffisamment souple pour faire face à un changement dans l'emplacement des données.

De nombreuses opérations de base de données impliquent l'accès aux données associées par l'intermédiaire de jointures, de contraintes de clés ou de déclencheurs, et les avantages du partitionnement distribué risquent d'être perdus si des appels de serveurs distants sont nécessaires. Ainsi, une fois que vous avez identifié les données qui font l'objet d'un accès fréquent, vous devez également décider quelles données associées doivent, d'un point de vue stratégique, être placées sur le même serveur. Il existe un certain nombre d'approches concernant le traitement des données associées lors du partitionnement sur plusieurs serveurs :

Partitions symétriques

Cohabitation. La méthode standard consiste à partitionner les tables à clé étrangère avec la table partitionnée d'origine. Bien entendu, cette approche ne fonctionne que si les tables à clé étrangère peuvent être partitionnées avec succès sur la même base que la table d'origine. Par exemple, supposez que notre site de commerce électronique permette aux clients d'ajouter des commentaires pour chaque produit proposé sur le site. Les commentaires peuvent être stockés dans une table nommée ProductReviews (commentaires produit) et associée aux tables membres de partitions de profil client sur le champ CustomerID. Dans la mesure où chaque commentaire ne concerne qu'un seul client, la table ProductReviews peut être partitionnée avec succès de sorte que le commentaire pour un produit particulier se trouve sur le même serveur que l'enregistrement du client qui l'a créé.

Partitions asymétriques

Dénormalisation. Certaines données associées ne pourront pas être si facilement partitionnées avec la table partitionnée d'origine. Par exemple, notre base de données de commerce électronique peut inclure une table nommée DeliveryMethods (méthodes de livraison), qui répertorie les méthodes de livraison (standard, express, etc.) parmi lesquelles les clients peuvent choisir. Les clients peuvent indiquer une préférence pour une méthode de livraison par défaut dans leur profil, si un champ PreferredDeliveryMethod (méthode de livraison préférée) est ajouté dans la table CustomerProfiles (profils client), en tant que clé étrangère de la table DeliveryMethods.

Dans la mesure où il est probable que plusieurs clients sélectionnent la même méthode préférée, il peut être prudent de dénormaliser la base de données en dupliquant certaines des données fournisseur sous forme de colonnes supplémentaires dans les tables CustomerProfiles. Cela permet l'extraction des données DeliveryMethod à accès fréquent (comme par exemple le nom et le coût de la méthode de livraison) en même temps que chaque client, ce qui supprime la nécessité d'accéder à la table DeliveryMethods.

Il est également nécessaire d'étudier la fréquence à laquelle les données sont ajoutées à la table DeliveryMethods, ainsi que la fréquence à laquelle les colonnes dupliquées de la table DeliveryMethods sont mises à jour. Si votre structure pour le maintien de l'intégrité des données dupliquées implique la mise à jour de toutes les données associées à l'intérieur d'une transaction, la table est verrouillée jusqu'à ce que cette opération soit terminée. Il existe des moyens de procéder par la programmation, mais il est important de faire les bons compromis lorsque vous essayez d'accroître l'évolutivité. Bien que vous deviez vous attendre à une légère dégradation des temps de réponse par transaction en échange de la possibilité de prendre en charge un nombre quasiment illimité de transactions, vous ne souhaiterez pas accroître de façon importante ces temps de réponse suite à des erreurs de conception.

Réplication. La dénormalisation peut réduire les appels des serveurs distants ; cependant, elle peut également compliquer le maintien de l'intégrité des données. Une des approches permettant de réduire ce problème consiste à utiliser la réplication SQL Server pour répartir les mêmes données sur les serveurs de la fédération. Par exemple, pour empêcher les problèmes d'intégrité associés à la dénormalisation des données relatives aux méthodes de livraison, vous pouvez répliquer la table DeliveryMethods sur les trois serveurs, ce qui permet d'extraire de n'importe quel serveur de la fédération la liste complète de toutes les méthodes de livraison. L'intégrité des données est préservée, car toute mise à jour des méthodes de livraison est répliquée.

Cependant, la réplication vous oblige à sacrifier une certaine quantité de ressources processeur et de mémoire. Dans un système très actif, le prix à payer pour cela peut s'avérer supérieur aux gains réalisés. Si tel est le cas dans votre structure, vous devez tester un scénario dans lequel vous partitionnez les tables partitionnables de la fédération, mais où vous stockez sur un même serveur toutes les autres tables qui devraient sinon être répliquées, comme le montre la Figure 15.

Figure 15. Stockage des données courantes sur un même serveur

Cette conception permet l'extraction des données partitionnées par l'intermédiaire de la vue partitionnée distribuée, mais elle offre un emplacement commun et unique pour toutes les autres données. Remarquez que cet emplacement peut être un des membres de la fédération ou un serveur supplémentaire distinct.

Alternative de réplication. La réplication utilise les ressources du processeur, de la mémoire et des disques du serveur. Si vous avez planifié votre serveur pour qu'il accepte cette charge supplémentaire et que votre administrateur de base de données est expérimenté en matière de réplication, vous ne devez pas vous préoccuper de ce problème. Il existe cependant une alternative. Vous pouvez utiliser des déclencheurs INSTEAD-OF pour mettre à jour toutes les données répliquées. Par exemple, si vous possédez une table de recherche qui doit se trouver sur chaque serveur de la fédération, mais que vous ne la mettez pas à jour de façon régulière, le déclencheur constitue une solution plus simple et plus efficace. Le déclencheur INSTEAD-OF, créé sur chaque copie de la table de recherche, effectue le changement de données sur chaque copie de la table (y compris la copie qui comporte le déclencheur).

Comparaison des méthodes disponibles

Il est utile de comprendre le rôle de chaque partie individuelle de la configuration fédérée, de sorte que vous puissiez mieux choisir comment les implémenter dans un groupe.

Le routage dépendant des données (DDR, Data Dependent Routing) fait référence à la technique qui consiste à orienter les requêtes vers la partition appropriée, à l'aide de code et soit d'une table de recherche, soit d'une clé de hachage.

Les vues partitionnées distribuées (DPV, Distributed Partitioned Views) offrent un moyen simple d'accéder aux partitions à l'aide d'une vue, plutôt qu'en faisant référence à chaque table individuelle.

Une partition de hachage ressemble plus au DDR, en ce sens que les requêtes sont orientées vers le serveur contenant les données.

La bande passante réseau et les requêtes entre serveurs motiveront votre décision. Il est plus facile d'expliquer cela à l'aide d'une analogie. Supposez que la connexion entre les serveurs soit un trottoir. Si vous utilisez un réseau à haut débit entre les serveurs, imaginez un trottoir très large ; si vous disposez d'une connexion réseau standard, imaginez un trottoir plus étroit. Chaque personne représente 100 ko de données. Ainsi, les requêtes présentant des résultats plus petits (moins de personnes) pourront facilement être acheminées, jusqu'à ce que le volume des requêtes devienne trop important. Si vous émettez des requêtes qui impliquent des jeux de résultat plus conséquents, cela peut être représenté par un groupe de personnes marchant ensemble sur le trottoir. Il est aisé de comprendre que la vitesse à laquelle une personne arrive à destination dépend du nombre de personnes qui utilisent le trottoir en même temps. Il s'agit de la seule raison pour laquelle un réseau haut débit est requis entre des serveurs fédérés.

Si vous prévoyez d'avoir régulièrement des requêtes qui nécessitent des données provenant de plusieurs serveurs, il est plus simple d'utiliser une combinaison de DDR ou de partitionnement par hachage et de DPV. Il est également possible de contrôler par la programmation les résultats de différents serveurs, mais cela peut rapidement devenir très complexe, en fonction du type des requêtes qui sont émises. Remarquez que les requêtes sur plusieurs partitions d'un même serveur n'entraînent aucune dégradation notable des performances.

Implémentation de vues partitionnées distribuées

Pour créer une solution fédérée, vous devez procéder à quelques opérations élémentaires de configuration sur chaque serveur membre. Ces opérations sont décrites ci-dessous :

Installation du serveur

Un certain nombre de tâches doivent être effectuées pour configurer une fédération de serveurs. Ces tâches sont décrites ci-après.

Ajout de définitions de serveurs liés

Chaque serveur fédéré doit posséder une définition de serveur lié pour chacun des autres serveurs. Vous pouvez ajouter une définition de serveur lié à l'aide de la procédure système stockée sp_addlinkedserver, ou à l'aide de SQL Server Enterprise Manager.

Remarque : A ce stade, vous devez déterminer de quel type de sécurité vous aurez besoin entre les serveurs. Déterminez également comment les nouveaux utilisateurs seront ajoutés au système, ainsi que les autres stratégies de sécurité qui peuvent être en place. Il n'est pas judicieux de simplement mapper tous les utilisateurs sur le compte sysadmin du serveur distant, et ce pour des raisons évidentes.

Par exemple, pour ajouter SQLServer2 et SQLServer3 en tant que serveurs liés sur SQLServer1, l'instruction Transact-SQL suivante peut être exécutée :

EXEC sp_addlinkedserver    @server = 'SQLServer2',

                           @provider = 'SQLOLEDB'

                           @datasrc = 'SQLServer2'

EXEC sp_addlinkedserver    @server = 'SQLServer3',

                           @provider = 'SQLOLEDB'

                           @datasrc = 'SQLServer3'

Le paramètre @server est utilisé pour spécifier un nom interne pour le serveur distant. Ce nom peut être différent du nom NetBIOS réel de l'ordinateur, mais lors de la construction d'une fédération de serveurs, il est recommandé d'utiliser le nom réel de l'ordinateur. De cette façon, les références aux tables dans la vue partitionnée sont cohérentes entre tous les serveurs membres (dans la mesure où la table locale peut être référencée à l'aide du nom NetBIOS du serveur local). Le paramètre @provider spécifie le fournisseur d'accès aux données utilisé pour la connexion au serveur distant, et le paramètre @datasrc spécifie le nom NetBIOS du serveur distant. La procédure stockée sp_addlinkedserver prend en charge d'autres paramètres qui permettent la liaison de bases de données non SQL Server. Ces paramètres ne sont pas requis lors de la liaison de sources de données SQL Server.

Des instructions similaires devraient être exécutées sur SQLServer2 (pour lier SQLServer1 et SQLServer3) et sur SQLServer3 (pour lier SQLServer1 et SQLServer2).

Activation de la validation différée de schéma

Cette option serveur, configurée via sp_serveroption, garantit que le processeur de requêtes ne demande pas les métadonnées pour une quelconque des tables liées tant que les données de la table de la partition membre distante ne sont pas réellement nécessaires. Cette option doit être configurée sur chacun des serveurs fédérés.

Remarque : Vous ne devez pas définir cette option si vos partitions se trouvent toutes dans la même base de données sur le serveur local. Cette situation se produit si vous concevez des partitions à des fins d'évolutivité, mais que vous n'implémentez pas encore de fédération.

Pour configurer l'option de validation différée de schéma, exécutez l'instruction suivante sur chaque serveur fédéré :

EXEC sp_serveroption @@servername, ‘Lazy Schema Validation', true

Objets base de données

Une fois que les serveurs ont été configurés, vous devez créer les objets base de données nécessaires pour une base de données partitionnée.

Création de tables membres de partitions

Les tables membres qui contiendront les données partitionnées doivent être créées sur les serveurs de la fédération. Les schémas utilisés pour créer ces tables doivent être identiques (à l'exception de la contrainte utilisée pour partitionner les données) sur chacun des serveurs fédérés. Une contrainte doit être définie sur la colonne de la clé de partitionnement pour garantir que chaque table peut contenir uniquement les données appropriées pour le serveur sur lequel elle se trouve. Cette contrainte est utilisée par le moteur de requêtes lors de la conception du plan d'exécution d'une requête sur une vue partitionnée distribuée.

Par exemple, l'instruction CREATE TABLE suivante peut être exécutée sur SQLServer1 pour créer une table pour les clients 1000 à 1999 :

CREATE TABLE CustomerProfiles

(

CustomerID INTEGER

       CONSTRAINT ck_Profiles

CHECK(CustomerID BETWEEN 1000 AND 1999),

FirstName Varchar(20),

EMail Varchar(70)

CONSTRAINT PK_CProfiles PRIMARY KEY(CustomerID)

)

Sur SQLServer2, l'instruction CREATE TABLE serait semblable, comme indiqué ci-dessous :

CREATE TABLE CustomerProfiles

(

CustomerID INTEGER

CONSTRAINT ck_Profiles

CHECK(CustomerID BETWEEN 2000 AND 2999),

FirstName Varchar(20),

EMail Varchar(70)

CONSTRAINT PK_CProfiles PRIMARY KEY(CustomerID)

)

Enfin, pour compléter le jeu, l'instruction sur SQLServer3 serait la suivante :

CREATE TABLE CustomerProfiles

(

CustomerID INTEGER

CONSTRAINT ck_Profiles

CHECK(CustomerID BETWEEN 3000 AND 3999),

FirstName Varchar(20),

EMail Varchar(70)

CONSTRAINT PK_CProfiles PRIMARY KEY(CustomerID)

)

Important : Toutes les options SET doivent être identiques sur chaque serveur lors de la création de la table. Dans le cas contraire, la DPV ne pourra pas être mise à jour.

Remarquez que le nom de chaque table est le même dans l'exemple. Si vous nommez chaque table membre avec le même nom, l'instruction utilisée pour accéder à la table membre sera cohérente sur tous les serveurs membres.

Cependant, si vous concevez une application partitionnable dans une base de données unique avec l'intention un jour de séparer les partitions sur des serveurs distincts, vous devez nommer les tables membres de la partition avec des noms uniques. Dans ce cas, choisissez des noms qui n'ont rien à voir avec la plage de clés. Par exemple, si vous avez nommé votre troisième table CustomerProfiles_3000_3999, lorsque vous repartitionnez la table ultérieurement pour répartir de façon équitable l'utilisation entre les serveurs, le nom ne sera plus approprié.

Création de la DPV sur chaque serveur fédéré

Enfin, la vue partitionnée doit être créée sur chaque serveur membre. Il est important de noter que cette étape ne peut être effectuée qu'une fois que les tables membres et les définitions de serveurs liés ont été créées sur tous les serveurs membres, ce qui rend l'écriture de scripts pour la création de la fédération de serveurs plus complexe qu'il n'y paraît. Pour ce faire, vous devez exécuter un script pour créer les tables membres, définir les serveurs liés et configurer l'option de validation différée de schéma sur chaque serveur, puis exécuter un script distinct pour créer la vue partitionnée sur chaque serveur.

La vue partitionnée est constituée d'une instruction SELECT pour chacune des tables membres consolidées à l'aide de l'opérateur UNION (l'option ALL empêche SQL Server de supprimer les lignes dupliquées dans le jeu de résultat). Par exemple, la vue CustomerProfiles peut être définie sur SQLServer1 à l'aide de l'instruction suivante :

CREATE VIEW pv_Customers

AS

SELECT CustomerID, FirstName, EMail

FROM SQLServer1.FederatedRetail.dbo.CustomerProfiles

UNION ALL

SELECT CustomerID, FirstName, EMail

FROM SQLServer2.FederatedRetail.dbo.CustomerProfiles

UNION ALL

SELECT CustomerID, FirstName, EMail

FROM SQLServer3.FederatedRetail.dbo.CustomerProfiles

Des instructions similaires sont exécutées sur SQLServer2 et SQLServer3, de sorte que chaque serveur de la fédération contienne une vue nommée pv_CustomerProfiles incluant tous les enregistrements de profils client.

Interrogation de la DPV

Étant donné que la vue existe sur chaque serveur, elle peut être interrogée comme s'il s'agissait d'une table sur un serveur quelconque. Ainsi, si vous utilisez des procédures stockées pour l'accès, vous pouvez créer la procédure stockée suivante pour extraire un enregistrement de profil client en fonction de la valeur CustomerID :

CREATE PROC getCustomerProfile (@CustomerID Int)

AS

SELECT CustomerID, FirstName, EMail

FROM pv_Customers

WHERE CustomerID = @CustomerID

Il est important de noter que, dans la mesure où les données sont partitionnées en fonction de CustomerID, vous devez inclure cette colonne chaque fois que vous faites une recherche dans la table, afin d'éviter d'avoir à rechercher dans chaque partition. Par exemple, si vous aviez créé la procédure suivante, elle serait moins efficace, car elle nécessiterait la recherche dans chaque partition :

CREATE PROC getCustomerByEMail (@Email Varchar (70) )

AS

SELECT CustomerID, FirstName, EMail

FROM pv_Customers

WHERE EMail = @EMail

En incluant CustomerID, vous pouvez rendre la requête beaucoup plus efficace.

En fonction de la requête et des contraintes, les partitions redondantes sont écartées, lors de la compilation si possible ou lors de l'exécution, via un mécanisme de filtres de démarrage ou de filtres dynamiques. Un filtre de démarrage est une condition sur les paramètres de la requête, qui indique si la partition doit faire l'objet d'un accès. Le coût de l'évaluation locale d'un filtre de démarrage est nettement inférieur à celui de l'accès à un serveur distant, de l'exécution d'une requête, puis de la découverte qu'aucune ligne ne correspond.

Pour voir l'exécution de votre requête partitionnée, examinez une sortie de type "showplan" des plans de filtre de démarrage. Vous verrez que toutes les partitions sont représentées dans le plan, mais que le filtre de démarrage inscrit la plage des valeurs disponibles pour chaque partition. Vous pouvez utiliser les options "set statistics i/o on" et "set statistics showplan on" pour obtenir des informations plus complètes.

Lorsque vous interrogez la table, vous obtenez la sortie showplan ci-après. Lorsque la requête s'exécute, les expressions de démarrage écartent de façon dynamique les partitions redondantes. Lorsque les valeurs de clés ne sont pas connues au moment de la création du plan, SQL Server construit un plan d'exécution avec une logique de condition afin de contrôler quelles tables membres font l'objet d'un accès. Ce plan d'exécution présente des filtres de démarrage, c'est-à-dire une logique de condition qui détermine quelle table membre fait l'objet d'un accès, en fonction de la valeur du paramètre en entrée. Vous verrez dans la vue graphique de l'analyseur de requêtes que toutes les partitions distantes sont représentées dans l'affichage. Les partitions inutiles sont écartées du plan lors de l'exécution, en fonction du paramètre fourni, et vous verrez dans le profil des statistiques qu'une seule partition est effectivement interrogée.

SET STATISTICS IO ON

SET STATISTICS PROFILE ON

EXEC getCustomerProfile 1020

Les statistiques des E/S montrent que la table CustomerProfiles a été balayée.

Résultats des statistiques d'E/S

Table 'CustomerProfiles'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0

Le profil des statistiques montre également que la table CustomerProfiles de la première partition a fait l'objet d'une exécution et que l'index en cluster de la clé primaire (nommée PK_CProfiles dans cet exemple) a été utilisé.

Résultats des statistiques de profil

Executes

StmtText

1

SELECT * FROM pv_Customers

WHERE CustomerID = @CustomerID

1

Concatenation

1

Filter(WHERE:(STARTUP EXPR([@CustomerID]<=1999 AND [@CustomerID]>=1000)))

1

Clustered Index Seek(OBJECT:([FederatedRetail].[dbo].[CustomerProfiles].[PK__CProfiles), SEEK:([CustomerProfiles].[CustomerID]=[@CustomerID]) ORDERED FORWARD)

1

Filter(WHERE:(STARTUP EXPR([@CustomerID]<=2999 AND [@CustomerID]>=2000)))

0

Remote Query(SOURCE:(Server2), QUERY:(SELECT Col1015,Col1014,Col1013 FROM (SELECT Tbl1003."CustomerID" Col1013,Tbl1003."FirstName" Col1014,Tbl1003."EMail" Col1015 FROM "FederatedRetail"."dbo"."CustomerProfiles" Tbl1003) Qry1016 WHERE Col1013

1

Filter(WHERE:(STARTUP EXPR([@CustomerID]<=3999 AND [@CustomerID]>=3000)))

0

Remote Query(SOURCE:(Server3), QUERY:(SELECT Col1020,Col1019,Col1018 FROM (SELECT Tbl1008."CustomerID" Col1018,Tbl1008."FirstName" Col1019,Tbl1008."EMail" Col1020 FROM "FederatedRetail"."dbo"."CustomerProfiles" Tbl1008) Qry1021 WHERE Col1018

L'analyse des requêtes et des plans d'exécution est une étape importante de la préparation du partitionnement des données. Lors du partitionnement d'une base de données existante, il est judicieux d'ajouter un code d'audit à vos requêtes ou d'exécuter le profileur SQL pour établir des informations de base sur les requêtes spécifiques utilisées, les données auxquelles elles accèdent, ainsi que la fréquence de cet accès. Ces informations fournissent de précieux renseignements sur les meilleures options de distribution des données.

Mise à jour d'une DPV

Pour qu'une vue partitionnée puisse être mise à jour, elle doit satisfaire aux exigences décrites dans SQL Server 2000 Books Online. Lorsque vous souhaitez mettre à jour une partition par l'intermédiaire d'une DPV, vous devez utiliser la commande "set XACT_ABORT on", comme dans l'exemple suivant :

CREATE PROC ChangeEmail (@CustomerID Int,

     @NewEMail varchar(50))

AS

SET XACT_ABORT ON

BEGIN TRAN

UPDATE pv_Customers

SET Email = @NewEMail

WHERE CustomerID = @CustomerID

IF (@@error <> 0)

  GOTO ErHand

COMMIT TRAN

RETURN 0

ErHand:

If @@trancount > 0

BEGIN

ROLLBACK TRAN

RETURN -1

END

Si vous procédez à la mise à jour de la partition sans utiliser de vue partitionnée distribuée, vous n'avez pas besoin du paramètre XACT_ABORT, car vous pouvez interagir directement avec la table membre de la partition.

Si vos tables ne satisfont pas aux exigences d'une vue partitionnée pouvant être mise à jour, vous pouvez également envisager leur mise à jour à l'aide d'un déclencheur INSTEAD-OF. Les déclencheurs peuvent être appelés à la place de n'importe quelle instruction INSERT, UPDATE ou DELETE exécutée sur la vue, et ils contiennent du code qui examine les données, qui détermine la partition correcte et qui effectue l'action requise sur la table membre de cette partition. Cependant, gardez à l'esprit le fait que l'optimiseur de requêtes ne crée généralement pas de plans d'exécution pour une vue qui utilise un déclencheur INSTEAD-OF et qui soient aussi efficaces que les plans d'une vue partitionnée réelle.

Implémentation du routage dépendant des données (DDR, Data Dependent Routing)

Comme avec tout autre type de système, la conception des applications a un impact important sur les performances. En utilisant le routage dépendant des données, et donc en utilisant des informations de routage pour accéder directement au serveur concerné, vous pouvez réduire le trafic entre les serveurs qui exécutent SQL Server. Cette méthode utilise du code pour déterminer où se trouvent les données cible, puis achemine les connexions vers le serveur approprié. Les données sont disposées de la même façon que dans le cas d'une vue partitionnée distribuée. La différence avec le routage dépendant des données est que les informations sur la façon d'accéder aux données sont mises à la disposition de l'application. Le routage dépendant des données n'utilise pas les connexions de serveurs liés ; il envoie les requêtes de données directement au serveur approprié. Il est souhaitable que les bases de données contenant la table partitionnée présentent des noms et des structures identiques. Les différences peuvent être prises en considération dans la table de routage, mais cela ajoute une certaine complexité à la structure.

La Figure 16 illustre une solution de serveur fédéré dans laquelle les informations de routage dépendant des données sont utilisées dans le niveau intermédiaire.

Figure 16. La logique du routage dépendant des données

La structure standard de serveurs fédérés utilise à la fois les vues partitionnées distribuées et le routage dépendant des données pour tirer pleinement parti de toutes les opportunités possibles en termes de performances. Un moyen simple de faire cela consiste à créer une table de routage contenant des informations indiquant quel serveur possède les données que l'application demande. La décision relative à l'emplacement de la table de routage est importante. Pour faciliter l'administration, vous pouvez placer la table dans SQL Server, où elle peut être mise à jour facilement et aussi souvent que nécessaire. Vous devez toutefois concevoir votre application de telle sorte que la table de routage n'ait pas à être extraite de SQL Server chaque fois que vous souhaitez extraire des données de la table partitionnée. Lors de la première requête de chaque serveur d'application, les informations de routage peuvent être extraites de la base de données SQL Server et mises en cache (par exemple, en tant que jeu d'enregistrements de niveau application dans IIS, ou en tant que tableau dans le Gestionnaire des propriétés partagées COM+). Toutes les futures requêtes utilisent ensuite les données du cache pour déterminer le serveur le plus approprié auquel se connecter pour extraire des enregistrements spécifiques.

Si vous ne souhaitez pas utiliser de table de routage, vous pouvez envoyer les données vers la partition ou le serveur approprié(e) à l'aide d'un algorithme de hachage personnalisé, basé sur la colonne de partitionnement. Cela facilite le repartitionnement, car vous pouvez simplement modifier l'algorithme afin de changer le nombre de partitions.

Il est essentiel de bien déterminer la méthode qui vous permettra de gérer le plus facilement possible les partitions de données en déplaçant les données afin d'équilibrer l'utilisation. Il serait désastreux de ne pas le faire.

Si l'on suppose qu'une table de routage est utilisée, celle-ci doit contenir au moins suffisamment d'informations pour déterminer le serveur le plus approprié sur lequel exécuter une requête pour des données spécifiques. Vous pouvez inclure des colonnes complémentaires qui autorisent l'application de règles de routage différentes en fonction du moment (par exemple, en fonction d'un calendrier de maintenance), ou encore une logique de routage plus complexe. Par exemple, la logique nécessaire pour acheminer correctement les requêtes dans notre exemple de solution de commerce électronique pourrait ressembler à cela :

Plage inférieure

Plage supérieure

Nom de la colonne

Nom de la table

Instance serveur

Date de début

Date de fin

Indicateur actif

0

10

Category

Products_1

SQLServer1

01 jan 2001

 

O

11

20

Category

Products_2

SQLServer2

01 jan 2001

 

O

21

30

Category

Products_3

SQLServer3

01 jan 2001

 

O

Cette table n'est qu'un exemple ; vous pouvez la concevoir avec les informations dont vous avez besoin et stocker ces informations dans un format adapté à votre application. Les colonnes Date de début et Date de fin offrent une certaine souplesse concernant les interruptions de planning et les basculements, au cas où vous devriez transférer le contrôle à un autre serveur, ou si vous procédez à un repartitionnement sans fenêtre de maintenance. Il est judicieux d'ajouter du code de traitement des erreurs qui détectera et signalera toute interruption du serveur. Ajoutez également des colonnes d'audit à (au moins) toutes les tables associées aux données partitionnées.

Bien que votre logique de routage puisse être aussi complexe que l'imposent vos besoins, vous devez faire votre possible pour la maintenir relativement simple, afin d'éviter tout impact négatif sur les performances. Cela peut sembler évident, mais vous devez tester soigneusement les performances de votre logique de routage afin de vous assurer que la surcharge qu'elle introduit est inférieure à la surcharge occasionnée par les requêtes distantes qu'elle permet d'éviter !

Remarque : Bien que nous traitions ici exclusivement des serveurs fédérés, vous pouvez également utiliser le DDR dans n'importe quelle application, comme un moyen de connexion par la programmation à une autre source de données, au cas où la source principale deviendrait indisponible.

Mise en cache des informations de routage dans une application ASP

Pour mettre en cache les informations de routage dans une application ASP, une variable de niveau application peut être initialisée dans le script global.asa avec la chaîne de connexion au serveur contenant la table de routage, comme le montre l'exemple ci-dessous :

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart

  Set Application("rsRouting") = Nothing

  Application("CONN_STRING") = _

    "PROVIDER = SQLOLEDB;DATA SOURCE=(local);” & _

    “INITIAL CATALOG=FederatedRetail;” & _

  “USER ID=retailsite;PASSWORD=password;"

End Sub

</SCRIPT>

La variable de niveau application est vérifiée sur le script ASP, lequel nécessite une requête des données de profil d'un utilisateur spécifique. Si elle ne contient pas d'informations de routage, un jeu d'enregistrements contenant la table de routage est extrait et déconnecté. Ce jeu d'enregistrements peut ensuite être utilisé par n'importe quelle future requête de données. Le code ASP utilisé pour extraire les données sur le produit peut ressembler à l'exemple suivant, lequel suppose que la valeur CustomerID d'un utilisateur authentifié est passée au serveur sous forme de cookie :

<%

Dim Lgn

Dim rs

Dim strConn

Dim strInstance

Lgn = Request.Cookies("LoginID")

'Ajout d'informations de routage au cache si elles ne s'y trouvent pas déjà

If Application("rsRouting") Is Nothing Then

  Set rs = server.CreateObject("ADODB.Recordset")

  rs.CursorLocation = 3

  rs.Open "SELECT * FROM routing",

    Application("CONN_STRING"), 3, 4

  Set rs.ActiveConnection = Nothing

  Set Application("rsRouting") = rs

End If

‘Construction d'une chaîne de connexion correcte à partir de la table de routage dans le cache

Dim conFound

Application("rsRouting").MoveFirst

conFound=False

Do Until Application("rsRouting").EOF OR ConFound

  If Application("rsRouting").fields("LowerRange").Value <= Cint(Lgn) And Application("rsRouting").fields("UpperRange").Value >= Cint(Lgn) Then

    conFound = True

    strInstance = Application("rsRouting").fields("ServerInstance").value

    strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" & strInstance & _

    ";INITIAL CATALOG=FederatedRetail;” & _

    “USER ID=retailsite;PASSWORD=password;"

    'Remarquez que la sécurité standard est utilisée car l'utilisateur IUsr_X

    'est un compte local (c'est-à-dire pas membre d'un domaine)

  End If

  Application("rsRouting").MoveNext

Loop

'Get Profile Data

Dim rsCustProfile

Dim cmd

Set cmd = Server.CreateObject("ADODB.Command")

With cmd

    .ActiveConnection = strConn

    .CommandText = "GetCustomerProfile"

    .CommandType = 4

    .Parameters.Append(cmd.CreateParameter("LoginID", 3, 1, 4, Lgn))

  End With

Set rsCustProfile = cmd.Execute

Response.Write "Hello " & rsCustProfile.fields("FirstName").value Response.Write “<BR>Your email address is " &  _

  rsCustProfile.Fields("EMail").Value

%>

Mise en cache des informations de routage dans une application COM+

Dans de nombreuses applications de commerce électronique, l'accès réel aux données s'effectue via le code d'un composant, plutôt que directement dans les pages ASP. Dans ce scénario, vous devez mettre en cache les informations de routage, de sorte qu'elles puissent être réutilisées par les composants de l'application. Le Gestionnaire des propriétés partagées (SPM, Shared Property Manager) COM+ peut être utilisé pour cela. Le SPM est un objet utilisé dans les applications COM+ pour stocker les valeurs des variables. Dans la mesure où le SPM ne peut pas être utilisé pour stocker des variables d'objets (comme par exemple les jeux d'enregistrements), vous pouvez être amené à convertir la table de routage en tableau. Cela peut être effectué à l'aide de la méthode GetRows d'un objet jeu d'enregistrements, comme le montre l'exemple de code suivant :

Public Function getCustomer(ByVal intLoginID As Integer)

As ADODB.Recordset

Dim SPM As COMSVCSLib.SharedPropertyGroupManager

Dim SPG As COMSVCSLib.SharedPropertyGroup

Dim SP As COMSVCSLib.SharedProperty

Dim bExists As Boolean

Dim strConn

Dim strInstance

Set SPM = New COMSVCSLib.SharedPropertyGroupManager

Set SPG = SPM.CreatePropertyGroup("RoutingProps", LockSetGet,

  Process, bExists)

Set SP = SPG.CreateProperty("routingtable", bExists)

'Ajout de la table de routage au cache si elle ne s'y trouve pas déjà

If Not bExists Then

  Dim rs As ADODB.Recordset

  Set rs = New ADODB.Recordset

  rs.CursorLocation = adUseClient

  rs.Open "SELECT * FROM routing", CONN_STRING,

adOpenStatic, adLockBatchOptimistic

  Set rs.ActiveConnection = Nothing

  SP.Value = rs.GetRows

End If

'Obtention de la chaîne de connexion correcte à partir de la table de routage dans le cache

Dim aRouting

aRouting = SP.Value

Dim i As Integer

For i = 0 To UBound(aRouting)

    If aRouting(0, i) <= intLoginID And aRouting(1, i) >= intLoginID Then

      strInstance = aRouting(4, i)

      strConn = "PROVIDER = SQLOLEDB;DATA SOURCE=" & _

   strInstance &  _

          ";INITIAL CATALOG=FederatedRetail;” & _

   “INTEGRATED SECURITY=sspi;"

      'Remarquez que la sécurité intégrée peut être utilisée, car

      'un compte de domaine peut être affecté à l'application COM+

      Exit For

    End If

Next i

'Obtention des données de profil

Dim cmd As ADODB.Command

Dim rsCustomer As ADODB.Recordset

  Set cmd = New ADODB.Command

  With cmd

    .ActiveConnection = strConn

    .CommandText = "GetCustomerProfile"

    .CommandType = adCmdStoredProc

    .Parameters.Append cmd.CreateParameter("LoginID", adInteger,

         adParamInput, 4, intLoginID)

  End With

Set rsCustomer = cmd.Execute

Set getCustomer = rsCustomer

End Function

Implémentation du partitionnement de hachage

Le partitionnement sur un hachage est semblable au routage dépendant des données. La différence est que la clé utilisée pour sélectionner les données est basée sur un algorithme de hachage dans lequel un calcul sur la clé donne un résultat qui détermine le serveur sur lequel l'enregistrement est stocké. Pour l'exemple suivant, les clients doivent pouvoir ouvrir une session sur le site Web afin d'activer leur profil utilisateur. Le champ E-mail constitue le meilleur choix pour une clé de partitionnement ; cependant, la longueur de ce champ de caractères est variable, et dans la plupart des cas, il est préférable d'utiliser une valeur numérique pour le partitionnement. Vous devez également concevoir les partitions de sorte qu'elles soient basées sur une valeur dynamique, afin de faciliter la maintenance.

Vous pouvez facilement combiner un hachage et une table de routage, éventuellement pour compenser l'absence de clé entière ou l'utilisation de plusieurs clés de colonnes, mais l'exemple ci-après a été choisi dans ce chapitre, car il montre un moyen d'utiliser un hachage sans table de routage.

Il existe plusieurs moyens d'utiliser un hachage pour le partitionnement, celle-ci n'est qu'une méthode parmi d'autres. Ici, un coefficient de valeur égale au nombre de serveurs de la fédération est utilisé pour identifier les serveurs et pour effectuer le mappage sur les serveurs, ce qui supprime la nécessité d'une table de routage pour la fédération (bien que vous puissiez tout de même inclure une table de routage pour la simplicité de gestion). Pour voir comment l'utilisation d'un coefficient de trois fonctionne pour trois emplacements de données, exécutez l'extrait de code ci-après : vous remarquez que le code crée 100 lignes dans la variable table et que le hachage affecte à chaque ligne un nombre entre 1 et 3. Dans cet exemple, il y a trois serveurs, de sorte que le coefficient est défini sur 3. Remarquez que dans cet exemple, le résultat du hachage est augmenté de 1 afin que le résultat soit plus parlant : les serveurs et les partitions sont ainsi numérotés de 1 à 3, et non de 0 à 2. 

set nocount on

declare @Locations smallint,

       @counter tinyint,

       @current tinyint

select@current = 1, @counter = 100, @Locations = 3

declare @tab table (modulos int)

while @counter >= @current

begin

       insert @tab SELECT (@current % @Locations) + 1

       select @current = @current + 1

end

select [Server assigned followed by total locations] =

       modulos from @tab group by modulos compute count (modulos)

Ce principe est appliqué à la fonction ci-après, qui affecte une valeur de hachage basée sur les valeurs ASCII des première et dernière lettres de l'alias d'e-mail (la partie située avant le signe @). En voici un exemple :

create function dbo.udf_hashmail2

(@EMail varchar(100), @GroupServerCount int)

returns table

as

return (select 

[Server or Group ID] =

(ascii(left(@EMail, 1))

% @GroupServerCount) + 1 ,

[Partition ID] =

(ascii(  substring(  @EMail,

charindex('@', @EMail) - 1,

                                  1))

% @GroupServerCount) + 1

       )

GO

-- Cette instruction illustre la façon dont cette fonction peut être exécutée :

declare @email varchar(100),

@gl\_servercount smallint

select @gl\_servercount = 3, 

@email = 'plato@msn.com'

select[Server or Group ID],

[Partition ID]

from   dbo.udf_hashmail2 (@email, @gl\_servercount)

Ainsi, lorsqu'un nouveau profil client est créé, les clés de hachage déterminent où les données seront insérées. En outre, lorsque les clients se connectent au système, ils indiquent leur adresse e-mail et la clé de hachage est déterminée afin de localiser leur enregistrement.

Les noms des serveurs et des partitions constituent la dernière étape pour que cela fonctionne. Vous pouvez combiner cette méthode avec une table de recherche, comme dans le DDR. Vous pouvez également nommer les serveurs avec le même nom suivi du numéro d'identification. De la même façon, chaque partition présente le même nom avec comme suffixe des numéros différents (par exemple MonServeur001, MonServeur002, etc.). Une stratégie de dénomination similaire peut être utilisée pour les partitions à l'intérieur du serveur. Lorsque l'application est chargée, elle génère les emplacements des partitions (serveurs et tables, dans cet exemple), et met en cache ces informations. L'exemple de code suivant illustre la façon dont ce nom peut être généré en T-SQL :

create function dbo.udf_get_locname

(@hashkey smallint,

@locname varchar(30)  )

returns table

as

return(select [Location of Data] =

@locname+

substring(    '00'+ convert(varchar(3), @hashkey),

len(@hashkey),

3 )

)

GO

-- Cette instruction illustre la façon dont cette fonction peut être exécutée :

declare @hashkey smallint,

@locname varchar(30)

select @hashkey = 3 

,@locname = 'MyServer'

select [Location of Data] from dbo.udf_get_locname (@hashkey, @locname)

En général, vous ne souhaiterez pas avoir à vous connecter au serveur pour obtenir ces informations ; cependant, la version T-SQL sera utile chaque fois que vous devrez obtenir ces informations auprès du serveur alors que vous y êtes déjà connecté. En particulier, pour permettre à l'administrateur de base de données d'extraire les données pour un système de génération de rapports, ou pour localiser les enregistrements.

Le code suivant illustre la façon dont un composant de niveau intermédiaire, permettant de déterminer le serveur et la partition corrects associés à une adresse e-mail particulière, peuvent être écrits en Microsoft Visual Basic® :

Public Function HashPartition(ByVal strEMail As String) As String

intNumServers = 3

strBaseServerName = "Server00"

strBaseTableName = "Table00"

'D'abord, affectation d'une valeur de hachage à l'aide de l'adresse e-mail

Dim intServerID As Integer

Dim intPartitionID As Integer

intServerID = (Asc(strEMail) Mod intNumServers) + 1

intPartitionID = (Asc(Mid$(strEMail, InStr(strEMail, "@") - 1)) _ Mod intNumServers) + 1

'Ensuite, affectation des noms des serveurs et des tables

Dim strServerName As String

Dim strTableName As String

strServerName = strBaseServerName & CStr(intServerID)

strTableName = strBaseTableName & CStr(intPartitionID)

'Renvoi des valeurs de hachage au format XML

Dim xmlHashData As String

xmlHashData = "<hashdata>"

xmlHashData = xmlHashData & "<servername>" & strServerName & _ "</servername>"

xmlHashData = xmlHashData & "<tablename>" & strTableName & _ "</tablename>"

xmlHashData = xmlHashData & "</hashdata>"

HashPartition = xmlHashData

End Function

Dans ce cas, les serveurs et les partitions sont nommés à l'aide d'un modèle prévisible et les données de hachage sont renvoyées sous forme de chaîne XML au format suivant :

<hashdata>

<servername>Server002</servername>

<tablename>Table001</tablename>

</hashdata>

Bien que la logique indiquée ici puisse être traitée efficacement soit dans le niveau intermédiaire, soit dans le niveau de données, le choix de l'emplacement de stockage de la logique doit être basé sur vos besoins en termes de simplicité de gestion et de flexibilité, ainsi que sur ce que votre personnel peut prendre en charge tout en respectant vos normes d'exploitation. La logique de traitement est généralement stockée dans les deux emplacements ; le coefficient, les noms des serveurs et les noms des partitions sont stockés dans la base de données, mais ils sont mis en cache dans la couche d'application.

Le partitionnement de hachage est le plus efficace lorsqu'il est très simple de modifier le coefficient quand vous ajoutez des serveurs ou des partitions. Vous pouvez ajouter une autre variable au code indiqué ici, de sorte qu'il peut y avoir un nombre différent de serveurs et de partitions. Cependant, si le nombre de partitions par serveur varie, vous avez besoin d'une table semblable à la table de routage DDR pour déterminer l'emplacement des données.

Vous devez stocker les informations sur le coefficient et sur le nom des serveurs et des partitions dans des variables globales ; évitez de coder des noms qui sont susceptibles de devoir être modifiés ultérieurement. Si vous utilisez le DDR avec le partitionnement de hachage, vous pouvez ensuite stocker les noms des serveurs et des partitions ainsi que le coefficient actuel et le nouveau coefficient dans la table de routage. Cela n'est nécessaire que si les noms des serveurs et des partitions ne suivent pas un modèle prévisible.

Un système auto-ajustable

En utilisant des partitions de hachage ou des DPV, une application peut rester abstraite par rapport à la fédération. Cependant, il existe un avantage important à concevoir le système comme étant partitionné dans toute l'application ; les fonctions peuvent être placées soit dans le niveau intermédiaire, soit dans le niveau de données, afin de réagir aux changements dans la distribution des données et d'ajuster leurs propres processus.

Par exemple, vous devez envisager l'implémentation de procédures et de code qui permettraient aux données de se déplacer entre partitions sans interruption du service. Un moyen de faire cela consiste à ajouter quelques colonnes aux tables membres des partitions : status (avec les valeurs current, relocate, delete), NewGroupServerID et NewPartitionID. Chaque fois qu'un profil est extrait, l'application détermine l'état de l'enregistrement afin de voir s'il a été marqué pour être déplacé vers une autre partition. Si tel est le cas, l'application doit contenir une logique permettant de revérifier l'emplacement de cet enregistrement s'il est de nouveau extrait au cours de la même session.

En outre, l'administrateur de base de données doit envisager l'ajout d'une fonction de contrôle au serveur de base de données afin de détecter les niveaux supérieurs d'activité sur les partitions. Vous devez toutefois créer une alerte que l'administrateur de base de données pourra utiliser pour informer l'administrateur système qu'une utilisation supérieure a été détectée sur une des partitions, plutôt que d'automatiser un processus qui engage un repartitionnement. Cela permet à l'administrateur de base de données de prendre une décision réfléchie sur la façon de repartitionner les données et d'engager ce processus au moment opportun.

Mise à jour de données dupliquées

Votre fédération peut comporter des tables qui doivent être dupliquées sur chaque serveur de la fédération. Si vous n'utilisez pas la réplication pour cela, vous devrez mettre à jour les modifications de ces tables sur chaque serveur. Un moyen simple de faire cela consiste à utiliser des déclencheurs INSTEAD-OF. Voici un exemple permettant de faire cela (remarquez que dans un but de clarté, le traitement des erreurs a été omis) :

-- table devant être copiée sur chaque serveur

CREATE TABLE ProfileOptions

(      [ProfileOptionID] int not null,

       [SettingGroupID] int not null,

       [Description] char(50) not null,

       [ActiveFlag] char(1) not null default('Y') )

GO

CREATE TRIGGER IO_Trig_I_ProfileOptionRepl on ProfileOptions

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON

IF (NOT EXISTS

   (SELECT p.[ProfileOptionID]

   FROM       [Server001].[DB1].[dbo].[ProfileOptions] p

   JOIN       [inserted] i

   ON         p.[SettingGroupID] = i.[SettingGroupID]

   AND        p.[Description] = i.[Description])

   )

       INSERT INTO   [Server001].[DB1].[dbo].[ProfileOptions]

       SELECT[ProfileOptionID],

              [SettingGroupID],

              [Description],

              case   when [ActiveFlag] not in ('Y', 'N')

then 'Y' else [ActiveFlag]

end

       FROM   [inserted]

IF (NOT EXISTS

   (SELECT p.[ProfileOptionID]

   FROM       [Server002].[DB2].[dbo].[ProfileOptions] p

   JOIN       [inserted] i

   ON         p.[SettingGroupID] = i.[SettingGroupID]

   AND        p.[Description] = i.[Description])

   )

       INSERT INTO   [Server002].[DB2].[dbo].[ProfileOptions]

       SELECT[ProfileOptionID],

              [SettingGroupID],

              [Description],

              case   when [ActiveFlag] not in ('Y', 'N')

then 'Y' else [ActiveFlag]

end

       FROM   [inserted]

IF (NOT EXISTS

   (SELECT p.[ProfileOptionID]

   FROM       [Server003].[DB3].[dbo].[ProfileOptions] p

   JOIN       [inserted] i

   ON         p.[SettingGroupID] = i.[SettingGroupID]

   AND        p.[Description] = i.[Description])

   )

       INSERT INTO   [Server003].[DB3].[dbo].[ProfileOptions]

       SELECT[ProfileOptionID],

              [SettingGroupID],

              [Description],

              case   when [ActiveFlag] not in ('Y', 'N')

then 'Y' else [ActiveFlag]

end

       FROM   [inserted]

END

GO

CREATE TRIGGER IO_Trig_U_ProfileOptionRepl on ProfileOptions

INSTEAD OF UPDATE

AS

BEGIN

SET NOCOUNT ON

   UPDATE     p

   SET        p.[Description] = isnull(  i.[Description],

                                         p.[Description]) ,

              p.[ActiveFlag] =

case when i.[ActiveFlag] not in ('Y','N')

then 'Y' else i.[ActiveFlag]

end

   FROM       [Server001].[DB1].[dbo].[ProfileOptions] p

   INNER JOIN[inserted] i

   ON         p.[ProfileOptionID] = i.[ProfileOptionID]

   AND        p.[SettingGroupID] = i.[SettingGroupID]

   UPDATE     p

   SET        p.[Description] = isnull(  i.[Description],

                                         p.[Description]) ,

              p.[ActiveFlag] =

case when i.[ActiveFlag] not in ('Y','N')

then 'Y' else i.[ActiveFlag]

end

   FROM       [Server002].[DB2].[dbo].[ProfileOptions] p

   INNER JOIN[inserted] i

   ON         p.[ProfileOptionID] = i.[ProfileOptionID]

   AND        p.[SettingGroupID] = i.[SettingGroupID]

   UPDATE     p

   SET        p.[Description] = isnull(  i.[Description],

                                         p.[Description]) ,

              p.[ActiveFlag] =

case when i.[ActiveFlag] not in ('Y','N')

then 'Y' else i.[ActiveFlag]

end

   FROM       [Server003].[DB3].[dbo].[ProfileOptions] p

   INNER JOIN[inserted] i

   ON         p.[ProfileOptionID] = i.[ProfileOptionID]

   AND        p.[SettingGroupID] = i.[SettingGroupID]

END

GO

CREATE  TRIGGER IO_Trig_D_ProfileOptionRepl on ProfileOptions

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

   IF EXISTS (select [ProfileOptionID] from [deleted])

       UPDATEp

       SET    [ActiveFlag] = 'N'

       FROM   [Server001].[DB1].[dbo].[ProfileOptions] p

       JOIN   [deleted] d

       ON     p.ProfileOptionID = d.ProfileOptionID

       AND    p.SettingGroupID = d.SettingGroupID

   IF EXISTS (select [ProfileOptionID] from [deleted])

       UPDATEp

       SET    [ActiveFlag] = 'N'

       FROM   [Server002].[DB2].[dbo].[ProfileOptions] p

       JOIN   [deleted] d

       ON     p.ProfileOptionID = d.ProfileOptionID

       AND    p.SettingGroupID = d.SettingGroupID

   IF EXISTS (select [ProfileOptionID] from [deleted])

       UPDATEp

       SET    [ActiveFlag] = 'N'

       FROM   [Server003].[DB3].[dbo].[ProfileOptions] p

       JOIN   [deleted] d

       ON     p.ProfileOptionID = d.ProfileOptionID

       AND    p.SettingGroupID = d.SettingGroupID

END

GO

Environnements de développement et de test

Pour configurer un environnement de développement, vous avez deux possibilités.

• Vous pouvez combiner toutes les partitions dans une même base de données, sous réserve que les noms des tables membres des partitions soient uniques. Vous pouvez également les placer dans des bases de données distinctes sur la même instance. Il s'agit de la méthode la plus simple, qui fonctionne plutôt bien si vous concevez un système partitionnable parce que vous souhaitez qu'il soit évolutif au-delà de sa structure d'origine, afin d'être préparé à une éventuelle croissance importante de l'utilisation.

• Vous pouvez également créer une instance de SQL Server sur un ordinateur unique pour chaque partition. Il s'agit d'une simulation presque exacte de l'environnement de production, les serveurs liés étant la seule différence. Dans la mesure où il existe une petite différence dans la façon dont l'authentification a lieu lorsqu'elle n'a pas à se déplacer au-delà du serveur physique, vous pouvez rencontrer des problèmes de sécurité avec les liaisons lorsque vous placez les bases de données partitionnées sur des serveurs distincts.

Remarque : Il est possible d'exécuter un système de production constitué de partitions sur plusieurs instances qui s'exécutent sur le même serveur physique, mais cela n'est pas recommandé, car il n'y a aucun avantage à faire cela. Pour isoler les ressources entre les serveurs, il est préférable de laisser SQL Server équilibrer la charge sur les différentes ressources, car ces partitions font toutes partie du même système. Cependant, si vous devez effectuer un partitionnement dans un serveur, faites-le sur une instance, puis répartissez vos partitions dans différents groupes de fichiers, comme cela a été étudié plus haut, dans la section "Questions relatives à la conception de fédérations" de ce chapitre.

Si vous concevez un système partitionnable, que vous prévoyiez ou non de l'implémenter immédiatement en tant que fédération, vous devez le tester de manière approfondie à l'aide d'un script de test réaliste sur des serveurs distincts d'une fédération (plutôt que sur des instances distinctes du même serveur). Cela vous permettra de détecter les éventuels problèmes restant et de les supprimer avant la mise en production du système.

L'apprentissage de la maintenance du système est un aspect essentiel du processus de test. S'il s'agit d'un nouveau système, vous devez créer suffisamment de données afin de pouvoir procéder à des tests longs, simuler différents types de défaillances et travailler à la récupération suite à ces défaillances. Cela vous aidera à créer une documentation pratique pour vous guider dans l'environnement de production.

Vous devez effectuer les tâches suivantes pendant l'exécution de vos scripts de test et documenter vos découvertes (y compris la durée de chaque action) :

• Recueillez les données des compteurs de performances et du profileur afin d'observer des valeurs de base (et conservez-les afin de comparer les performances après avoir fait des modifications).

• Contrôlez l'activité par partition.

• Sauvegardez et restaurez une partition unique et plusieurs partitions.

• Restaurez les données qui ont été désynchronisées entre les serveurs (si cette possibilité s'applique à votre structure).

• Déterminez de quelle façon vos opérations standard de maintenance affectent le serveur au cours des tests.

• Implémentez des changements sur les objets partitionnés sans provoquer d'interruptions majeures.

• Repartitionnez les données avec peu (ou pas) d'interruption.

• Mettez à jour la table de routage afin d'apporter une modification (par exemple, changez ou ajoutez une partition, supprimez une partition, redirigez une partition vers un nouveau serveur).

Il est également souhaitable de tester des plans de récupération d'urgence, de sorte que vous n'ayez pas à tester votre plan en production si un problème réel survient. Les tests suivants constituent une bonne préparation :

• Supprimez une partition pendant que l'application s'exécute, puis restaurez-la, et déterminez comment l'application a réagi.

• Invalidez la table de routage avec des données erronées (s'il s'agit d'un objet distinct) et voyez ce qui se passe.

• Si vous utilisez des clusters, basculez-les et voyez ce qui se passe. Essayez d'abord avec un, puis basculez-les tous afin de tester une situation extrême. Une fois qu'ils fonctionnent, revenez aux serveurs d'origine. Prenez des notes sur le processus et sur ce que vous apprenez.

• Si vous avez un site de serveur distant, vous devez basculer progressivement vers le site distant.

Si vous effectuez ces différents types de tests et que vous documentez correctement les leçons apprises, vous serez très bien préparé pour assurer la maintenance du système en production.

Maintenance des partitions

La réussite du partitionnement d'un système existant de grande taille nécessite généralement certaines améliorations. Les tables choisies pour le partitionnement à chaque étape sont généralement celles qui donneront le meilleur gain en termes de performances à cet instant précis. Cependant, l'utilisation des données change au cours du temps ; ainsi, pour que les partitions restent efficaces, elles devront parfois être ajustées.

L'équilibrage de charge entre les tables, ou le repartitionnement, est un véritable défi. Il n'existe actuellement aucune méthode automatisée pour cela. Si vous avez fait des erreurs de calcul dans la planification de vos partitions, vous pouvez être amené à repartitionner toute une table. Par exemple, si vous partitionnez votre table de commandes en fonction d'un numéro de commande séquentiel, vous découvrirez rapidement que la dernière partition est non seulement celle qui croît le plus, mais également celle qui fait l'objet des requêtes les plus fréquentes. Si vous partitionnez plutôt en fonction de la zone géographique, il se peut également que l'activité d'une zone augmente plus rapidement ou que cette zone fasse l'objet de requêtes beaucoup plus fréquentes. Dans les deux cas, vous devrez rééquilibrer la charge en déplaçant certaines de ces données. La perturbation qui en résulte sera moins importante si vous le faites plus souvent, en déplaçant de plus petites quantités de données.

Le moyen le plus direct de faire cela consiste à désactiver provisoirement la contrainte concernée, pendant que vous déplacez les données entre les partitions, puis à recréer la contrainte une fois que vous avez terminé. Si votre application peut utiliser exclusivement le routage dépendant des données, au moins pour la durée de l'ajustement de la partition, vous n'avez pas à vous préoccuper du temps d'interruption. Vous devez d'abord déplacer les données, mettre à jour votre table de routage, mettre à jour les objets COM ou associés au routage dépendant des données qui contiennent des informations propres à la partition, actualiser l'éventuelle mise en cache des informations de routage dépendant des données, et enfin supprimer les lignes qui ne doivent plus se trouver dans une partition particulière parce qu'elles ont été déplacées vers un emplacement plus approprié.

Si vous ajoutez une nouvelle partition, vous devrez en grande partie procéder de la même façon, en plus de la modification des éventuelles vues partitionnées distribuées ou autres procédures SQL, fonctions ou tâches (en particulier les tâches de sauvegarde) qui doivent prendre connaissance de la nouvelle partition.

Il est bon de se rappeler que les tables partitionnées sont comme n'importe quelle autre table, excepté la maintenance nécessaire des partitions. Chaque serveur et chaque base de données d'une fédération doivent être gérés séparément. La maintenance des index est toujours nécessaire et ceux-ci devront toujours être sauvegardés. Vous pouvez à l'occasion exécuter des vérifications DBCC afin d'effectuer une maintenance la plus complète possible. L'un des avantages de la maintenance dans une fédération est que vous pouvez exécuter la maintenance sur toutes les partitions en même temps, dans la mesure où chaque partition est une base de données indépendante et où la maintenance est exécutée de façon distincte sur chacune d'elles. Chaque partition tire également parti du traitement parallèle au cours des vérifications DBCC et des constructions d'index, une fonctionnalité de l'Édition Entreprise.

Récupération après incident et partitionnement

Les sections suivantes proposent des conseils sur la création d'une stratégie optimale de sauvegarde et de restauration pour les bases de données partitionnées.

Sauvegarde et restauration de bases de données partitionnées

SQL Server 2000 ne nécessite pas que vous coordonniez des sauvegardes entre les serveurs membres. Si vous n'avez pas besoin de la cohérence transactionnelle entre les serveurs membres, des sauvegardes indépendantes peuvent être effectuées sur chaque base de données, quel que soit l'état des autres bases de données membres. Cette méthode présente une surcharge de synchronisation minimale, de sorte qu'elle a un impact minimal sur le traitement des transactions. Une autre option consiste à tenter de sauvegarder simultanément toutes les bases de données. Cette option n'est toutefois pas évolutive. Elle est extrêmement difficile à gérer et ne permet pas le partage des périphériques de sauvegarde.

Si la cohérence transactionnelle (en s'assurant que toutes les partitions peuvent être sauvegardées jusqu'au même point) est une nécessité, vous pouvez y parvenir dans SQL Server 2000 en utilisant des marques nommées dans le journal des transactions. Cette marque spéciale permet la restauration d'une base de données jusqu'à un point nommé avec des vues partitionnées. Les marques nommées permettent la synchronisation jusqu'au même point de toutes les bases de données avec des tables membres. Cela nécessite que le mode de récupération soit configuré sur complet pour chaque base de données faisant partie de la partition.

Au cours des opérations de sauvegarde et de restauration, les vues partitionnées distribuées et les bases de données partitionnées via le routage dépendant des données doivent rester synchronisées. Dans la mesure où le routage dépendant des données est essentiellement un partitionnement contrôlé par le code, vous pouvez utiliser des méthodes telles que les sauvegardes marquées. Pour rester à jour concernant les transactions et éviter de perdre des transactions, vous pouvez utiliser le codage ou la technologie de niveau intermédiaire.

Le routage dépendant des données peut également tirer parti de marques de journaux. Contrairement aux vues partitionnées, les données peuvent toutefois être désynchronisées, car chaque serveur est totalement indépendant des autres, du point de vue du partitionnement. Cela signifie que chaque serveur n'est en théorie lié que dans le code de l'application, même si les serveurs sont liés pour placer la marque dans le journal des transactions.

Pour restaurer une base de données partitionnée qui utilise le routage dépendant des données, rétablissez chaque base de données jusqu'à la marque indiquant la dernière bonne configuration connue du serveur endommagé, afin de garantir la synchronisation de tous les serveurs. Cette opération peut entraîner des pertes de transactions. Ce problème peut être traité à l'aide d'une autre technologie que SQL Server, comme par exemple Message Queuing ou COM+, ou à l'aide de code qui utilise le langage XML pour stocker l'état des transactions. Si vous choisissez une de ces technologies, testez-la soigneusement avant de l'utiliser pour la récupération des données perdues.

Pour plus de détails sur le marquage des transactions, consultez "Backup and Recovery of Related Databases" (Sauvegarde et restauration de bases de données liées) et "Recovering to a Named Transaction" (Récupération jusqu'à une transaction nommée) dans la documentation "SQL Server Books Online".

Mise en cluster de SQL Server pour une disponibilité élevée

Pour améliorer la disponibilité de SQL Server, vous pouvez utiliser la mise en cluster Windows et les clusters de basculement SQL Server. SQL Server 2000 facilite l'installation, la configuration et la gestion des clusters SQL Server par rapport aux versions précédentes. Il offre également la prise en charge des environnements à quatre nœuds ainsi qu'une meilleure prise en charge des environnements de basculement à plusieurs instances.

Organigramme du processus de mise en cluster

L'organigramme suivant illustre le processus de conception lors du choix d'une implémentation efficace des clusters SQL Server.

Figure 17. Processus de conception de l'implémentation de clusters SQL Server

Architecture de clusters

Windows 2000 et SQL Server 2000 prennent en charge le modèle de cluster sans partage, ce qui signifie que chaque nœud du cluster est un ordinateur indépendant possédant ses propres ressources et son propre système d'exploitation. Chaque nœud gère ses propres ressources et offre des services de données sans partage. En cas de défaillance d'un nœud, les disques et les services qui s'exécutent sur ce nœud peuvent basculer (ou redémarrer) sur un nœud non défaillant, mais un seul nœud gère un jeu de disques et de services particulier à un instant donné.

Figure 18. Connexion réseau en cluster

Vous pouvez configurer la mise en cluster de basculement de SQL Server 2000 de deux façons différentes : une configuration (active/passive) de basculement à une seule instance, ou une configuration (active/active) de basculement à plusieurs instances.

Configuration de basculement à une seule instance

Dans la configuration de basculement à une seule instance, le cluster exécute une instance unique de SQL Server. En cas d'échec du serveur principal, l'autre serveur du cluster peut exécuter la même instance. Dans cette configuration, les deux serveurs partagent une base de données maître et le jeu de bases de données utilisateur.

Configuration de basculement à plusieurs instances

Dans la configuration de basculement à plusieurs instances, qui n'est actuellement pas utilisée dans l'architecture Internet Data Center, chacun des deux nœuds actifs possède sa propre instance de SQL Server. Chaque instance de SQL Server est une installation différente du service complet et peut être gérée, mise à niveau et arrêtée de façon indépendante.

Pour implémenter une configuration de basculement à plusieurs instances, vous devez procéder de la façon suivante :

• Installez au moins deux instances de SQL Server sur le cluster.

• Configurez chaque instance pour qu'elle s'exécute sur un certain nœud en tant que serveur principal.

Figure 19. Une configuration de basculement à plusieurs instances.

Les bases de données qui font référence les unes aux autres doivent être placées sur la même instance SQL Server. Voici quelques exemples de bases de données qui peuvent parfaitement être placées dans une instance distincte :

• Microsoft Commerce Server 2000 pour les bases de données de catalogue et les bases de données d'application ;

• Microsoft BizTalk™ Server pour les bases de données XLANG et de planification.

Avant d'implémenter une configuration de basculement à plusieurs instances, vous devez évaluer la charge prévue sur chacune des applications de base de données et déterminer si un nœud pourra ou non traiter la charge combinée en cas de basculement. Si tel n'est pas le cas, vous devez envisager l'utilisation de deux clusters en mode de basculement à une seule instance.

Disponibilité et serveurs fédérés

Les serveurs fédérés n'offrent aucune fonctionnalité de basculement. Chaque serveur fédéré reste un serveur SQL Server indépendant et doit être traité comme tel pour toute technologie implémentée avec la fédération, comme par exemple la mise en cluster de basculement ou la fourniture de journaux.

Vous pouvez par exemple créer chaque partition d'un cluster de basculement en tant que serveur virtuel distinct sur un cluster de basculement SQL Server dédié. De cette façon, si vous disposez de trois partitions de base de données, vous avez besoin de six serveurs pour créer trois clusters de basculement indépendants, ce qui offre la possibilité de basculement à votre fédération. Cela nécessite des tâches supplémentaires dans l'application, car outre le traitement du DDR, vous devez également inclure du code prenant en charge les clusters. Cette approche est illustrée Figure 20.

Figure 20. Mise en cluster de chaque partition

Si votre base de données est partitionnée sur trois serveurs virtuels, la meilleure solution consiste à utiliser un minimum de quatre serveurs pour créer le scénario N+1 à l'aide de Windows 2000 Datacenter, comme le montre la Figure 21.

Figure 21. Un cluster à quatre nœuds

Avec ou à la place de la mise en cluster, vous pouvez utiliser la fourniture de journaux pour créer un serveur de secours prêt à fonctionner. Ce serveur peut être un seul gros serveur permettant d'héberger toutes les bases de données partitionnées (sauf si vos bases de données ne présentent pas de noms uniques), ou des serveurs partitionnés et des serveurs de fourniture de journaux dans un rapport de 1 pour 1.

Résumé

Dans ce chapitre, nous avons vu comment SQL Server peut être configuré pour offrir un magasin de données sécurisé, évolutif et aux performances et à la disponibilité élevées.

Lors de la conception d'une solution de base de données, vous devez tenir compte des besoins spécifiques de votre application. Ces besoins ont un impact sur les paramètres de sécurité et sur les options de configuration du serveur que vous choisissez. Dans un environnement de production, votre serveur SQL Server utilise généralement l'authentification Windows, accompagnée de mesures réfléchies concernant la sécurité physique ainsi que celle du réseau, du système de fichiers et du Registre.

La structure de votre application doit inclure des index appropriés, et la structure logique et physique doit garantir les meilleures performances en fonction de vos besoins spécifiques en termes d'accès aux données.

Avant d'étendre une solution SQL Server, vous devez étudier les implications d'une base de données distribuée sur la structure et la simplicité de gestion de l'application. Cela implique notamment l'identification de la solution la plus appropriée de routage des données, comme par exemple les tables de routage dépendant des données ou le hachage.

La mise en cluster Windows constitue l'approche préférée afin d'offrir une disponibilité élevée. Vous devez évaluer avec soin le nombre de bases de données que vous devez prendre en charge ainsi que la charge prévue sur chacune d'elles, puis choisir entre une configuration de basculement à une seule instance ou une configuration de basculement à plusieurs instances, selon le cas.

<< 1 2 3 4 5 6 7 8 9 >>

Dernière mise à jour le vendredi 15 mars 2002

Pour en savoir plus

• Lire la version US de cet article

• Plus d'infos techniques sur SQL Server