Réglage et optimisation des performances de Microsoft SQL Server pour développeurs

Sur cette page

Réglage et optimisation des performances de Microsoft SQL Server pour développeurs - Partie 1 : Présentation des problèmes de performances
Objectifs de performances
Temps de réponse et débit
Mode de mesure des performances
Facteurs ayant une incidence sur les performances
Ressources système
Mémoire
Système d'exploitation Windows NT
Threads
SQL Server
Configuration
Application de base de données
Conception logique et physique
Application client
Besoins utilisateur
Solutions proposées
Ajout de matériel
Avantages de Windows NT
Architecture évolutive
Méthodologie de réglage des performances
Méthodologie de réglage des performances (suite)
Approches du réglage des performances
Présentation – Stratégies d'indexation
Objectifs
DSS et OLTP
Systèmes d'analyse décisionnelle
Indexation pour récupération
Création d'index utiles
Sélectivité
Sélectivité : Exemple
Densité de jointures
Création du type approprié d'index
Candidats adaptés aux index en clusters
Candidats adaptés aux index non ordonnés en clusters
Candidats adaptés à des index composites
Index composites et index multiples à une colonne
Index globaux
Index en clusters et index non ordonnés en clusters
Correspondance entre l'index et la requête
Indexation d'une plage de données : Exemple
Pas d'index sur une table
Index en clusters sur la colonne price
Index non ordonné en clusters sur la colonne price
Index global sur les colonnes price, title
Indexation pour opérations logiques AND  : Exemple
Indexation pour SELECT * : Exemple
Indexation pour requêtes multiples
Choix 1
Choix 2
Choix 3
Choix 4
Aspects liés à la mise à jour
Directives d'indexation
Maintenance des index
Directives de création d'index
Quand faut-il éviter les index ?
Équilibrage de DSS avec OLTP
Présentation de l'optimiseur de requêtes

Réglage et optimisation des performances de Microsoft SQL Server pour développeurs - Partie 1 : Présentation des problèmes de performances

DAT 410
Présenté à Tech-Ed 97

Adam Shapiro
Directeur de programme
Microsoft Corporation

Objectifs de performances

L'objectif du réglage des performances est d'obtenir des temps de réponse acceptables par requête en minimisant le trafic réseau, en réduisant les E/S disque, et en minimisant le temps UC pour offrir un débit de traitement maximal pour tous les utilisateurs. Cet objectif est atteint par une analyse approfondie des besoins de l'application, une compréhension de la structure logique et physique des données, et la faculté d'évaluer et de négocier des compromis entre utilisations conflictuelles de la base de données, par exemple entre le traitement transactionnel et l'analyse décisionnelle.

Temps de réponse et débit

Le temps de réponse est une mesure du temps requis pour le renvoi de la première ligne du jeu de résultats. Le temps de réponse correspond généralement à la durée de l'attente par l'utilisateur de la réception d'une confirmation visuelle du traitement de la requête.

Le débit mesure le nombre total de requêtes pouvant être traitées par le serveur pendant une période donnée.

La multiplication des utilisateurs favorise l'augmentation des conflits, et peut entraîner un allongement du temps de réponse et une diminution globale du débit.

Mode de mesure des performances

Trois facteurs permettent de mesurer les performances : la quantité d'E/S requise pour traiter une transaction, le temps UC et le temps de réponse. Les performances varient en fonction de chaque environnement spécifique et dépendent de l'application, de l'architecture et des ressources, du serveur et des activités concomitantes.

Facteurs ayant une incidence sur les performances

Ressources système

Mémoire

Une capacité RAM suffisante est cruciale pour garantir les performances de SQL Server.

Processeur

Le nombre de processeurs, ainsi que leur vitesse, a une incidence directe sur les performances globales.

Disque

Le nombre, la vitesse et le type des disques durs, ainsi que les types de contrôleurs employés, influent sur les performances.

Réseau

L'activité réseau concomitante peut avoir un impact sur les performances de SQL Server. La bande passante du réseau et le débit de transfert des données sont également importants.

Système d'exploitation Windows NT

Threads

L'ajustement des priorités des threads permet à SQL Server d'équilibrer ses besoins avec ceux d'autres services et du système d'exploitation Microsoft Windows NT® proprement dit. Le nombre de threads alloué par SQL Server peut avoir un impact sur les performances.

Fichier d'échange

La taille, le nombre et l'emplacement des fichiers d'échange peuvent avoir un impact significatif sur les performances du système.

Services

D'autres services sous Windows NT seront en compétition pour les ressources requises par SQL Server. L'arrêt des services inutiles peut avoir un impact positif.

Gestion des disques

Windows NT comporte un certain nombre de fonctionnalités de gestion de disques telles que les agrégats par bande et la mise en miroir pouvant avoir une incidence sur les performances. Cet impact n'est pas toujours positif.

Activités concurrentes

D'autres activités, telles que des programmes client et des compilateurs, peuvent être en compétition avec SQL Server pour les cycles UC, l'accès au disque et la bande passante du réseau.

SQL Server

Configuration

De nombreuses variables de configuration de SQL Server peuvent avoir un impact direct sur les performances du serveur.

Verrouillage

La contention des ressources de base de données (tables et pages individuelles) peut entraîner le blocage de processus et avoir une incidence majeure sur les performances globales d'un système multi-utilisateur.

Enregistrement dans le fichier journal

À l'exception d'opérations spécifiques non enregistrées dans le fichier journal, chaque modification apportée dans une base de données doit être journalisée. L'écriture du journal peut elle-même avoir un impact sur les performances, et le journal des transactions (syslogs) peut constituer une source de contention.

Activités concurrentes

Les activités de maintenance, telles que la sauvegarde et la restauration, DBCC et la construction d'index, peuvent interférer sur les activités de production.

Application de base de données

Conception logique et physique

Le niveau de normalisation et/ou de dénormalisation peut affecter les performances des requêtes. La conception physique inclut le choix d'index et sera traitée de façon détaillée.

Éviter le blocage

Les blocages répétés peuvent ralentir une application. Certaines techniques de programmation permettent de réduire la probabilité de blocages.

Contrôle des transactions

Les transactions, le verrouillage et les blocages sont étroitement associés. Le niveau de contrôle des transactions d'une application peut avoir un impact majeur sur la longueur des verrouillages et le débit global d'une application.

Requêtes

Le mode d'écriture des requêtes individuelles, notamment le fait qu'elles soient ou non encapsulées dans des procédures stockées, peut déterminer si un plan optimal est employé pour assurer des performances maximales.

Application client

Besoins utilisateur

Les besoins des utilisateurs en termes de modifications à apporter et de requêtes à exécuter peuvent avoir une influence significative sur les performances de l'application.

Traitement des blocages

Les programmes client peuvent réagir à des blocages SQL de diverses manières. Les réponses les plus efficaces peuvent considérablement améliorer les performances du système client.

Contrôle des transactions

Les transactions peuvent également être contrôlées à partir de l'application client. En outre, certaines applications client peuvent émettre des instructions de contrôle des transactions sans que le programmeur ou l'utilisateur en soit conscient.

Curseurs

Il existe de nombreuses manières de définir et de manipuler les curseurs, chacune ayant un impact différent sur les performances.

Solutions proposées

Le réglage des performances s'apparente plus à un art qu'à une science exacte. L'objectif du réglage consiste à améliorer les performances en éliminant les goulets d'étranglement, qu'ils se rapportent aux E/S, à l'UC ou au réseau. Cet objectif peut être atteint en réduisant le temps de traitement système par un réglage du serveur, un ajustement de la base de données, une amélioration des processus et une réduction de la contention de données.

Ajout de matériel

• Des solutions utiles et peu coûteuses peuvent parfois être trouvées.

Réglage de SQL Server

• Ajuster des valeurs d'option de configuration.

Réglage de la base de données

• Améliorer la conception logique et physique.

• Écrire de meilleures requêtes.

• Créer des index utiles.

Résolution des problèmes de contention et de concurrence

• Coordonner les activités de lecture et d'écriture.

• Minimiser la contention de verrouillage.

• Éviter les blocages.

Réglage de l'application client

• Utiliser des procédures stockées plutôt que des requêtes.

• Analyser et établir des priorités entre les transactions.

• Répartir les données et le traitement du serveur lorsque cela est approprié.

Avantages de Windows NT

SQL Server tire parti des fonctionnalités améliorées du système d'exploitation Windows NT.

Architecture évolutive

SQL Server peut tirer parti de l'évolutivité de Windows NT, des ordinateurs portables aux super-serveurs multi-processeurs symétriques avec la prise en charge des processeurs Intel® et RISC (reduced instruction set computing).

Capacité élevée

SQL Server peut adresser jusqu'à 2 gigaoctets (Go) de mémoire, capacité qu'autorise Windows NT pour les processus utilisateur. La taille de la partition de disque dur peut être d'environ 17 milliards de Go (en utilisant NTFS).

Multitraitement symétrique (SMP)

Windows NT est un système d'exploitation compatible SMP. Il peut exécuter le code du système d'exploitation et le code utilisateur sur n'importe quel processeur disponible. En présence d'un plus grand nombre de threads à exécuter que de processeurs disponibles, le système d'exploitation SMP assure également une gestion multitâche, divisant le temps de chaque processeur entre toutes les threads en instance.

SQL Server tire parti des possibilités multithread de Windows NT. Plutôt que de mettre en œuvre son moteur de thread, SQL Server emploie des threads Windows NT pour servir chaque client. Windows NT assure automatiquement un équilibrage de charge et une planification des threads entre processeurs.

Sur des ordinateurs SMP, vous pouvez utiliser SQL Server pour dédier toutes les ressources UC à SQL Server.

Processus unique, multithread

SQL Server prend en charge SMP au niveau des threads et tire parti de la gestion des threads Windows NT de la manière suivante :

• L'architecture multithread à processus unique réduit l'intendance du système et les besoins de mémoire.

• Les applications multithread utilisent un espace d'adressage unique. Puisque toutes les threads appartiennent au même processus, il n'est plus nécessaire de coordonner des processus à mémoire partagée.

E/S asynchrones

Windows NT utilise des fonctions d'E/S asynchrones dans lesquelles une application émet une requête d'E/S puis continue à s'exécuter pendant que le périphérique transfère les données. Ce système se démarque d'un système d'E/S synchrone, qui ne redonne la main à l'application qu'à la fin de la requête d'E/S. SQL Server tire parti de l'E/S asynchrone de Windows NT et assure un débit plus élevé.

Utilisation des services Windows NT

SQL Server utilise des services Windows NT pour la gestion des threads, la planification, la notification d'événements, la synchronisation des processus, les E/S asynchrones, le traitement des exceptions et la sécurité intégrée.

SQL Server :

• utilise l'enregistrement des événements dans le journal de Windows NT en complément du journal d'erreurs de SQL Server ;

• tire parti de l'équilibrage de charge automatique de Windows NT ;

• est entièrement intégré avec l'Analyseur de performances de Windows NT ;

• peut exploiter les fonctionnalités de sécurité de Windows NT pour mettre en œuvre des ouvertures de session et des mots de passe intégrés.

Méthodologie de réglage des performances

Cette méthodologie de réglage des performances offre un point de départ pour régler une base de données et obtenir des performances optimales. Elle sert également de cadre pour les sujets inclus dans ce cours.

Méthodologie de réglage des performances (suite)

Les étapes de cette méthodologie peuvent être effectuées dans une séquence différente de celle présentée ici, ou certaines étapes peuvent être omises, en fonction du stage de production de l'environnement de base de données.

Approches du réglage des performances

Le réglage peut s'effectuer de deux manières différentes.

Dans ce cours, vous apprendrez comment SQL Server accède aux données, contrôle les activités concurrentes de plusieurs utilisateurs et entre en interaction avec le système d'exploitation. Vous pouvez utiliser ces connaissances pour planifier votre conception logique et physique, configurer SQL Server, planifier vos transactions et écrire vos requêtes pour obtenir des performances optimales.

Vous pouvez également traiter le réglage comme un problème spécifique. Une requête peut être exécutée lentement ou le débit peut être inférieur au débit nécessaire. Vous pouvez recueillir des informations sur le comportement de SQL Server et apporter les ajustements nécessaires à votre requête et à la configuration du système afin d'obtenir des performances optimales.

Les deux approches sont nécessaires. Si vous avez une connaissance approfondie du serveur, des utilisateurs, des données et des processus, mais aucune information sur les performances, vous ne pourrez pas déceler que votre application théoriquement bien conçue ne fonctionne pas aussi bien qu'elle le devrait. Inversement, si vous possédez toutes les données de mesure de performances disponibles, mais ne disposez d'aucune information sur l'application ou le serveur, vous identifierez des problèmes au niveau des performances, mais ne serez pas en mesure de les résoudre.

Présentation – Stratégies d'indexation

Objectifs

• sélectionner des index utiles pour les différents types de requêtes,

• comparer et opposer les stratégies d'indexation pour les systèmes d'analyse décisionnelle (DSS) et le traitement transactionnel en ligne (OLTP),

• créer des index efficaces,

• établir une différence entre la sélectivité et la densité des jointures,

• identifier les index inutiles,

• tester l'utilité des index.

DSS et OLTP

La plupart des requêtes correspondent à l'une de ces deux catégories. Comme les deux catégories peuvent poser des problèmes d'indexation très différents, elles seront décrites séparément.

Systèmes d'analyse décisionnelle

Un système d'analyse décisionnelle implique généralement plusieurs arguments de recherche et plusieurs tables. Les requêtes peuvent être relativement complexes, utiliser des agrégats, un groupement et des opérations CUBE et ROLLUP. Ce type d'opération est parfois qualifié de traitement analytique en ligne (OLAP).

Les requêtes peuvent être arbitraires et imprévisibles et emploient pratiquement n'importe quelle colonne pour spécifier les lignes souhaitées.

La vitesse de récupération et le renvoi de résultats constituent les aspects les plus critiques de ces types de requêtes.

Traitement transactionnel en ligne (OLTP)

OLTP implique fréquemment une seule table et généralement un tout petit nombre de lignes sont affectées. Pour INSERT, les transactions d'application OLTP peuvent simplement insérer une seule ligne.

Avec OLTP, les requêtes sont souvent plus prévisibles que dans l'analyse décisionnelle.

La vitesse de modification des données constitue l'aspect le plus critique des requêtes OLTP.

Indexation pour récupération

Création d'index utiles

La création d'index utiles est probablement l'intervention la plus importante pouvant être effectuée pour améliorer les performances. Le type et le nombre d'index, ainsi que les colonnes indexées doivent être sélectionnés avec précautions en se basant sur une compréhension approfondie des besoins des utilisateurs et des données proprement dites. Les index sont utiles, qu'il s'agisse d'une simple interrogation de la table ou d'une modification des données. Dans chaque cas, les index peuvent fournir un accès plus rapide aux données à des fins de lecture ou d'écriture.

Analyse utilisateur

Il est essentiel de comprendre les demandes des utilisateurs en matière de données, ainsi que les types de requêtes exécutées et leur fréquence. Une bonne compréhension des besoins des utilisateurs favorise l'identification des compromis qui s'imposeront. En équilibrant les performances des requêtes les plus cruciales, vous devrez consentir des sacrifices en termes de vitesse sur une requête pour obtenir de meilleures performances sur une autre.

Analyse des données

Comprenez les données et leur organisation dans la conception logique et physique.

Compréhension du fonctionnement de SQL Server

Une connaissance approfondie de SQL Server vous permettra de mieux concevoir le système et de prendre des décisions intelligentes. Vous devrez notamment comprendre comment SQL Server enregistre et récupère les données et comment l'optimiseur des requêtes sélectionne le plan d'exécution le plus efficace.

Aspects généraux

• L'optimiseur de requêtes emploie généralement un index par table, par requête.

• Pour accélérer une requête sur une plus grande table, il convient de disposer d'un index sur les colonnes dans la clause WHERE.

• Pour identifier un nombre raisonnable d'index, vous devez prendre en compte la fréquence des mises à jour et celle des récupérations.

• Les index doivent être choisis en fonction des types de clauses WHERE ou des jointures que vous effectuerez.

• L'important lors de la sélection d'index, est de choisir judicieusement le type et le nombre d'index. Identifiez le nombre minimal à créer de façon à éviter que la maintenance entraîne une dégradation des performances. Identifiez les index les plus utiles.

• Ne créez pas un index s'il risque de ne jamais être employé.

• C'est l'optimiseur de requêtes qui, en dernier recours, détermine s'il convient d'employer ou non un index.

Sélectivité

L'évaluation du jeu de résultats est utile pour sélectionner les types d'index à créer sur une table pour un ensemble donné de transactions.

La sélectivité d'une requête correspond au pourcentage de lignes dans une table auquel une instruction SELECT, UPDATE ou DELETE donne accès. Une sélectivité élevée peut renvoyer une ligne qui répond aux critères de recherche. Une faible sélectivité n'est pas discriminante et peut renvoyer la majorité des lignes de la table.

La densité constitue un concept associé, correspondant au pourcentage moyen de lignes en double dans un index. Un index comportant un grand nombre de doublons présente une densité élevée. Un index unique a une faible densité.

Analyse de la table

L'analyse de la table est avantageuse pour les requêtes dans lesquelles le jeu de résultats inclut un pourcentage élevé d'une table (faible sélectivité).

Distribution des données

La distribution des données indique la plage de valeurs dans une table de données et combien de lignes se situent dans cette plage. Dans de nombreux cas, il est possible d'évaluer le pourcentage de données à renvoyer dans un jeu de résultats. Par exemple, si le critère est mâle/femelle, le jeu de résultats pour femelles peut être évalué à 50 %.

Vous pouvez déterminer la distribution d'une colonne avec une requête de ce type :

SELECT column, count(*)
FROM table
GROUP BY column

Sélectivité : Exemple

Dans l'exemple ci-dessus, les deux jeux ont le même nombre de X, bien que le pourcentage de X (sélectivité) soit différent.

Évaluez la sélectivité de ces requêtes (supposez qu'il y a 10 000 lignes dans la table membre et les numéros de membre se situent dans la plage 1 – 10000, correspondant tous à des valeurs uniques) :

Densité de jointures

La densité de jointures est le nombre moyen de lignes dans la table interne qui correspondront à une ligne dans la table externe. La densité de jointures peut également être assimilée au nombre moyen de doublons.

Une colonne avec un index unique présente une faible densité et une sélectivité de jointures élevée. Si la colonne comporte un grand nombre de doublons, elle a une densité élevée et n'est pas très sélective pour les jointures.

Les index sur la colonne de jointures ne seront utiles que sur la table interne d'une jointure. Si la densité d'une jointure est faible, les deux types d'index seraient utiles. Si la densité de jointures est élevée, seul un index en clusters serait utile.

Création du type approprié d'index

Candidats adaptés aux index en clusters

Aspects à prendre en compte

La colonne utilisée pour l'index en clusters détermine l'ordre physique des données. Placez un index en clusters sur les colonnes de données qui sont le plus souvent requises par ordre physique.

Les index doivent être choisis en fonction des types d'instructions SELECT employés.

Les index en clusters sont recommandés pour les clés étrangères, puisque ces clés sont généralement non uniques.

Il ne peut y avoir qu'un seul index en clusters par table.

Remarque Le placement d'un index en clusters sur la clé primaire (surtout si les données sont monotoniques) constitue rarement le choix idéal. La clé primaire doit avoir un index unique. Un index unique non ordonné en clusters peut être presque aussi efficace qu'un index unique en clusters dans de nombreux cas.

Candidats adaptés aux index non ordonnés en clusters

Ajoutez des index non ordonnés en clusters uniquement lorsqu'ils sont réellement utiles, puisqu'une intendance significative est requise pour maintenir ces index pendant la modification des données.

Ne créez pas un index s'il risque de ne pas être employé.

Aspects à prendre en compte

• Besoins d'espace de stockage.

• Impact de la modification des données.

• Volatilité de la colonne candidate.

• Sélectivité des requêtes. Une analyse de table est plus adaptée à des requêtes à faible sélectivité.

• Unicité des colonnes candidates. Un niveau élevé de doublons diminue l'efficacité de l'index.

• Les index non ordonnés en clusters ne sont pas utiles pour les requêtes sur une plage, sauf si la plage représente un petit pourcentage de la table.

Candidats adaptés à des index composites

Un index composite peut être un index comportant une clé de tri multi-colonne. Un index en clusters ou un index non ordonné en clusters peut avoir une clé composite.

Aspects à prendre en compte

• Un index composite est utile si la première colonne de la clé est spécifiée dans la clause WHERE.

• Les candidats peu adaptés sont les index qui ont tendance à devenir trop larges et ceux dans lesquels seule la deuxième ou la troisième colonne est employée dans la clause WHERE.

Par exemple, un index sur (lastname, firstname) ** est adapté à la sélection de lastname et lastname, firstname, mais pas pour la sélection de firstname.

Index composites et index multiples à une colonne

• L'utilisation d'index multiples peut avoir une incidence sur les performances des instructions de modification des données.

• Généralement, un seul index est employé par table dans une requête, et les index multiples ne sont donc pas aussi utiles que des index composites.

• Un index composite peut constituer un meilleur choix pour une requête accédant à plusieurs colonnes dans une table unique.

Remarque L'ordre de spécification des colonnes dans la clause WHERE n'a pas d'incidence sur le mode d'utilisation des index composites. Il importe uniquement que la colonne la plus à gauche dans l'index composite soit contenue dans la clause WHERE.

Un index composite peut être utilisé même si la colonne de poids fort (celle la plus à gauche) dans la clé de tri d'index n'est pas dans la clause WHERE. Cette situation impose que toutes les colonnes référencées dans la liste SELECT et la clause WHERE soient contenues dans la clé de tri d'index. Voir les index globaux ci-dessous pour plus de détails.

Index globaux

Aspects à prendre en compte

• Ajoutez des colonnes à certains index pour couvrir la plupart des requêtes.

• Ne définissez pas une clé d'index trop large. Cela augmente la taille de l'index et compromet l'amélioration des performances. Si les lignes sont trop larges, le nombre de niveaux augmente, ainsi que le nombre de pages. Si le nombre de pages augmente, le temps requis pour analyser un index s'allonge lui aussi.

• L'ajout de plusieurs index non ordonnés en clusters a une incidence négative sur les performances de mise à jour.

• Il peut convenir dans certains cas de combiner deux index en un index global composite.

• Les index non organisés en clusters qui couvrent une requête à faible sélectivité sont très rapides, puisque les pages de données ne sont jamais utilisées ; une analyse de table est ainsi évitée.

• Un index global peut être très utile, même si la colonne de poids fort dans la clé de tri d'index n'est pas dans la clause WHERE.

Index en clusters et index non ordonnés en clusters

Correspondance entre l'index et la requête

Indexation d'une plage de données : Exemple

Pas d'index sur une table

• Une analyse de table (53 000 E/S) est plus efficace qu'un index non ordonné en clusters.

Index en clusters sur la colonne price

• Recherche dans un index en clusters une valeur minimale, dans ce cas $20,00.

• Lit les lignes en commençant à $20,00 et arrête les recherches à $30,00.

• Puisque la colonne price est ordonnée en clusters, l'ordre physique des données est arrangé en fonction du prix. Toutes les données se situant dans cette plage sont en ordre séquentiel sur les pages suivantes, ce qui simplifie l'extraction des données.

• Cette recherche impose la lecture de 10 000 pages (190 000/19 lignes par page).

Index non ordonné en clusters sur la colonne price

• Recherche dans un index non ordonné en clusters ligne par ligne pour déterminer si chaque ligne répond aux critères de recherche.

• Pour toutes les lignes admissibles, des données doivent être récupérées de chaque page stockant une ligne.

• Cette recherche constitue le pire des cas, puisque 190 000 pages de données (une pour chaque occurrence), plus le niveau feuille de l'index, doivent être lues. Chaque page de données est lue plusieurs fois dans la mémoire cache.

Index global sur les colonnes price, title

• Puisque les colonnes price et title sont dans l'index, aucune recherche ne doit être effectuée dans les pages de données, ce qui économise les ressources d'E/S.

• Dans l'index (price, title), il y a en moyenne 38 lignes d'index par page de feuille. Cette recherche impose la lecture de 5 000 pages (190 000/38 lignes par page).

Indexation pour opérations logiques AND  : Exemple

Examinez l'exemple sur la diapositive. Utilisez les choix indiqués dans les notes fournies pour évaluer le meilleur type d'index à créer pour cette instruction. Le numéro 4 constitue le meilleur choix, puisque toutes les lignes admissibles seraient regroupées. Le choix 6 couvrirait la requête. Cependant, l'index serait trop grand, en raison de la clé large. Le choix 7 est identique à un index en clusters sur dept, sauf que l'index est beaucoup plus grand.

On pense souvent à tort que l'ordre dans lequel les colonnes sont répertoriées dans la clause WHERE affecte le mode d'utilisation des index composites. C'est faux. Il importe uniquement que la colonne la plus à gauche dans l'index composite se trouve dans la clause WHERE.

Vous pouvez également noter que nous supposons au départ une répartition des données relativement normale. Si tout le monde ou personne gagnait > 50 000, ou si l'ensemble de la société était inclus dans la recherche, notre stratégie d'indexation aurait pu être différente.

Si les deux conditions sont satisfaites, la ligne répond aux critères de recherche.

Choix à évaluer pour l'exemple ci-dessus

1. Index en clusters ou non ordonné en clusters sur dept

2. Index en clusters ou non organisé en clusters sur salary

3. Un sur dept, un sur salary

4. Index composite en clusters sur (dept, salary)

5. Index composite en clusters sur (salary, dept)

6. Index non organisé en clusters sur (dept, salary, name, address) (index global)

7. Index en clusters sur (dept, salary, name, address)

Indexation pour opérations logiques OR

Les opérations logiques OR constituent une situation très différente de celles observées pour les AND.

Plusieurs conditions associées aux opérations AND assurent une admissibilité progressivement plus stricte des données souhaitées. Étant donné l'ensemble de toutes les lignes satisfaisant à l'une des conditions AND, les lignes de résultats finales seraient contenues dans ce jeu.

Avec des conditions OR, cela n'est pas vrai. Étant donné l'ensemble des lignes qui satisfont à l'une des conditions OR, certaines lignes pourraient satisfaire à l'une des autres conditions OR non incluses dans ce jeu de résultats.

Les opérations logiques OR seront traitées de façon beaucoup plus détaillée ultérieurement dans ce cours.

Indexation pour SELECT * : Exemple

SELECT * renvoie des informations de toutes les colonnes.

Le choix d'un index n'a pas d'incidence sur le contenu sélectionné. Sa signification se limite aux éléments admissibles. L'index simplifie la localisation des lignes. Il n'a aucune incidence sur les informations sélectionnées à partir d'une ligne. SELECT * ne peut pas être couvert par un index non ordonné en clusters sauf si toutes les colonnes sont dans la clé de tri.

SELECT * n'est pas le meilleur type de requête à utiliser pour obtenir des performances élevées s'il implique que vous récupériez plus de données que la quantité réellement requise.

Choix à évaluer pour l'exemple ci-dessus

• Index en clusters sur la colonne au_id

• La création d'un index en clusters sur la colonne au_id enregistre les lignes dans cet ordre.

• Index non ordonné en clusters sur la colonne au_id

• Cela imposerait l'accès à la page de données. Cela créerait potentiellement une E/S supplémentaire.

• Index non ordonné en clusters sur (au_id, au_lname)

Indexation pour requêtes multiples

Dans les exemples précédents, le meilleur type d'index a été sélectionné en fonction d'une requête individuelle. L'indexation pour les requêtes multiples est plus complexe, puisque l'index optimal pour une requête n'est pas nécessairement l'index optimal pour une autre. L'objectif est d'atteindre des performances acceptables pour toutes les requêtes à priorité élevée.

Choix à évaluer pour l'exemple ci-dessus

Hypothèses : La requête 1 représente 15 % de la table. La requête 2 est très sélective ; accès à une ligne.

Choix 1

• Index en clusters sur (price)

• Index non ordonné en clusters sur (title)

La requête 1 est très rapide. La requête 2 est rapide, mais nécessite une E/S de plus que si un index en clusters était placé sur la colonne title.

Choix 2

• Index non ordonné en clusters sur (price)

• Index en clusters sur (title)

La requête 1 est plus lente que dans le choix 1. La requête 2 est très rapide.

Choix 3

• Index en clusters sur (price)

• Index non ordonné en clusters sur (title, price)

La requête 1 est rapide, la requête 2 très rapide.

Choix 4

• Index non ordonné en clusters sur (price, title)

• Index en clusters sur (title)

C'est la meilleure option. Les requêtes 1 et 2 sont toutes deux très rapides.

Aspects liés à la mise à jour

Les requêtes de traitement transactionnel en ligne (OLTP) incluent certains aspects de récupération de données imposant une recherche préalable des lignes avant leur modification. Cependant, si vos requêtes les plus importantes sont des requêtes OLTP, d'autres aspects doivent être pris en compte :

• Une maintenance doit être effectuée sur les annexes. Chaque modification à une table indexable impose la mise à jour d'au moins un index, voire même plusieurs. Plus un index comporte de colonnes, plus le travail de maintenance sera important.

• Outre l'enregistrement de chaque ligne de données modifiée, chaque ligne d'index modifiée devra également être enregistrée.

Directives

• Pour une application principalement OLTP, conservez le nombre d'index à un niveau minimal.

• La ou les colonnes d'un index en clusters doivent être non volatiles.

Directives d'indexation

Maintenance des index

Index en clusters

En présence d'un index en clusters sur une table, les lignes doivent être insérées dans l'ordre de la clé d'index en clusters. S'il n'y a pas de place sur une page, celle-ci devra éventuellement être divisée, ce qui génère une gestion supplémentaire.

Index non ordonné en clusters

Un index non ordonné en clusters comporte un pointeur vers chaque ligne de données. À chaque insertion ou suppression de ligne, chaque index non ordonné en clusters doit être ajusté.

Si une instruction UPDATE correspond à une DELETE/INSERT complète ou à une UPDATE différée, chaque index non ordonné en clusters doit être ajustée à la fois pour les lignes supprimées et insérées. Même si une UPDATE est en place sur la même page, tout index sur une colonne modifiée devra être ajusté. Si vous utilisez de larges index composites, cet aspect pourrait générer une gestion considérable.

Directives de création d'index

Identification des priorités de toutes les requêtes

• Veillez à acquérir une compréhension globale des données et de leur mode d'utilisation.

• Identifiez les transactions prioritaires pour la base de données.

Identification de la sélectivité de chaque requête

• Identifiez la sélectivité de chaque partie de la clause WHERE.

Transposition sur un graphique de l'activité de chaque table

• Analysez l'activité effectuée sur chaque colonne de la table.

Identification des colonnes à indexer

• La colonne est-elle employée dans la clause WHERE ?

  Si une colonne n'est jamais référencée dans la clause WHERE d'une requête ou d'une instruction de modification de données, la création d'un index sur cette colonne n'est pas justifiée.

• La colonne est-elle employée comme clé de jointure ?

  La création d'un index sur une colonne utilisée comme clé de jointure améliore les performances de la jointure, parce qu'elle fournit à l'optimiseur de requêtes la possibilité d'utiliser un index plutôt que d'effectuer une analyse de table.

• La colonne fait-elle l'objet de fréquentes recherches ?

Choix de la colonne la mieux adaptée à un index en clusters

• Y a-t-il accès à une plage de données ? Une correspondance LIKE est-elle incluse dans l'instruction transactionnelle ?

• Un index en clusters est mieux adapté aux plages.

• Les données sont-elles toujours triées ?

  Si les données sont fréquemment triées sur une colonne spécifique, le placement d'un index en clusters sur cette colonne réduit l'intendance de tri.

• Si la colonne contient des valeurs uniques, un index unique offre-t-il des avantages ?

• Créez l'index en clusters avant de créer les index non ordonnés en clusters.

• Le placement d'un index en clusters sur la clé primaire (surtout si les données sont monotoniques) n'est pas nécessairement le choix idéal.

• Un index en clusters n'est pas nécessaire pour les colonnes de clé de jointure.

Identification des autres index nécessaires

• Identifiez le nombre minimal d'index pouvant être créés pour chaque table.

• Équilibrez les gains de performances obtenus par l'index ou une maintenance de mise à jour.

• Vérifiez que les colonnes référencées dans les clauses WHERE des requêtes à priorité la plus élevée sont indexées.

• Si une requête est exécutée peu fréquemment, il convient éventuellement d'envisager la création d'un index pour la durée d'une activité spécifique et sa suppression à la fin de cette activité. Par exemple, si tous les rapports ou analyses récapitulatives s'effectuent en fin de mois ou en fin d'année, des index peuvent être créés pour la durée de cette activité, puis supprimés.

Identification des types d'index non ordonnés en clusters à créer

• Un index composite offrira-t-il plus d'avantages qu'un index à une seule colonne ?

• La requête peut-elle être couverte par l'index ?

• La sélectivité de la requête donne-t-elle une correspondance exacte ?

  Les index non ordonnés en clusters sont utiles pour les correspondances exactes (renvoi d'une ligne), pour les jointures, ou pour les colonnes de clé primaire uniques.

• Si la colonne contient des valeurs uniques, un index unique offre-t-il des avantages ?

Test des performances des requêtes

• Une fois les index créés, testez les performances des requêtes présentant la plus haute priorité.

• Effectuez les choix suivants : SET SHOWPLAN ON, SET STATISTICS IO ON, SET STATISTICS TIME ON. Exécutez ensuite chaque requête.

Quand faut-il éviter les index ?

Dans certaines situations, il est préférable de ne pas utiliser d'index, notamment :

• Si l'index n'est jamais utilisé par l'optimiseur.

• Si plus de 10 à 20 % des lignes doivent être renvoyées.

• Si la colonne contient une, deux ou trois valeurs uniques (faible sélectivité).

• Si la colonne à indexer est longue (> 20 octets).

• Si l'intendance de maintenance de l'index est supérieure aux avantages obtenus.

• Si la table est très petite.

Équilibrage de DSS avec OLTP

Les besoins d'indexation étant très différents pour les environnements DSS et OLTP, les stratégies d'indexation peuvent être très difficiles à identifier si les deux environnements sont nécessaires.

Des copies séparées des données peuvent être conservées afin que la récupération et la modification ne soient pas effectuées exactement sur les mêmes données. Dans ce cas, une stratégie de réconciliation des données sera nécessaire. Les coûts et les avantages offerts par l'indexation optimale des deux environnements devront être comparés aux coûts de maintenance et de réconciliation de deux jeux de données.

Présentation de l'optimiseur de requêtes

L'optimiseur de requêtes de SQL Server détermine si un index est véritablement un bon index, et pour une requête particulière, quel index il convient d'employer. L'optimiseur choisit également le mode de traitement des jointures de tables multiples, en sélectionnant un ordre de tables et une méthode. Il détermine également le mode d'exécution idéal des opérations de mise à jour.

Le module suivant expliquera de façon détaillée comment l'optimiseur de SQL Server récupère les informations disponibles et les utilise pour déterminer un plan d'exécution optimal.

<< 1 2 3 >>

Dernière mise à jour le mercredi 20 septembre 2000

Pour en savoir plus

• Toute l'informations technique sur Microsoft SQL Server

• Le Site Microsoft SQL Server