Optimisation SQL Server by xxb14148

VIEWS: 45 PAGES: 16

									Optimisation SQL Server

       Alexis Comte
                    Plan
•   Organisation physique
•   Query optimizer
•   Statistiques
•   Procédures stoquées
•   Indexed Views
•   Covering index
•   Index Intersection
           Organisation physique
• Objets organisés en pages de 8k.
   – Header de 96 octets contenant
       •   le type de page (donnée, index, log, )
       •   taille libre dans la page
       •   id de l’objet propriétaire de la page
       •   pointers vers la page précédente et la page suivante de l’objet
   – La taille d’une ligne ne peut donc pas dépasser 8k
   – Une page ne contient que des données d’un objet

• Un extend est composé de 8 pages.
   – Extend mixte contient des pages de plusieurs objets
   – Extend uniforme : seulement un objet
       • tous les objets dont la taille dépasse 8 pages sont placés dans des
         extends uniformes
Schéma d’une page
                Query optimiser
• Détermination du meilleur plan d’exécution
  – Type d’index :
     • Clustered (Index non dense, arbre B)
        – Clés primaires
        – Colonnes n’ayant pas beaucoup de mises à jour
        – Requêtes retournant une large plage de valeurs ( > , <,
          between)
        – Colonnes utilisés dans des opérations « Order by » ou « Group
          by »
     • Non Clustered (Index dense, arbre B)
        – Colonnes ayant un grande sélectivité.
        – Requêtes ramenant un très faible nombre de données.


  – Algorithme de jointures
     • Nested loop join (boucles imbriquées)
     • Merge join (tri fusion)
     • Hash join (Hachage )
Clustered index
Non clustered index
                          STATISTIQUES
•   Calcule le degré de Sélectivité d’une colonne.

•   Création
     – Automatiquement lors de la création d’un index ou pour toute colonne dans une
       clause where (AUTO_CREATE_STATISTICS)
     – Manuellement.

•   Mises à jour
     – Automatiquement en fonction du volume de données mise à jour
       (AUTO_UPDATE_STATISTICS)
     – Manuellement.

•   Méthode de calcul
     – Echantillonnage sur des pages (de la table ou de l’index) prises au hasard
     – FULLSCAN pour les tables dont la taille < 8 MB
     – Choix manuel

•   La création manuelle de statistiques sur plusieurs colonnes peut aider le
    Query Analyser à choisir le meilleur plan.
     – Exemple : WHERE a = 7 and b = 9
          Une stat sur (a,b) permet de connaître le nombre de couples (a,b) distincts
            Procédures stockées
• Réduction du traffic réseau
   – Permet de placer plusieurs requêtes dans une même procédure

• Meilleures performances
   – Sauvegarde compilée : plus besoin de recalculer le plan d’exécution
   – Mise en mémoire après la première exécution.

• Exemple
   – Création
       Create procedure GetTop5 as
       select top 5 productid, sum(unitprice*quantity*discount) as rebate
       from order_details
       group by productid
       order by rebate desc

   – Exécution
       GetTop5
                      Indexed views
• Vues pouvant utiliser des index (clustered ou secondaires).

• Stockage physique des résultats de la requête

• Mises à jour dès que les données sous jacentes sont mise à jour.

• Particulièrement intéressantes sur
    – les opérations d’agrégation (sum, count,avg,…)
    – Résultats de jointures sur de larges tables.
    – Créer des vues sur des tables ordonnées différemment (différentes clé
      d’index cluster)

• A ne pas utiliser sur
    – Tables dont les données sont fréquemment mises à jour

• Peuvent permettre de très forts gains de performances
               Indexed views
• Utilisation uniquement sur des requêtes ayant
  un fort coût estimé.

• Peut être choisie par l’analyseur de requête
  même si elle n’y est pas référencé.

• NOEXPAND : Force l’utilisation
• EXPAND VIEWS : Exclue l’utilisation

• Un grand nombre de vues indexées peut ralentir
  le choix du meilleur plan d’exécution.
  select top 5 productid, sum(unitprice*quantity*discount) as rebate from order_details
  group by productid order by rebate desc




create view vdiscount2 with schemabinding as
select sum(unitprice*quantity*discount) as sumdiscountprice2, productid
from dbo.order_details group by productid

create unique clustered index cdiscountint on vdiscount2(productid)
              Covering Index
• Cas spécial de Non clustered index

• Index contenant toutes les colonnes utilisées
  dans une requête

• Gain de performances car
  – Inutile d’accéder aux pages de données de la table
    car tout est contenu dans les pages de l’index.
  – Les pages de l’index sont plus compactes que les
    pages de la table.
  – Les pages de l’index sont triées
select shipname from orders where shipcountry = 'France‘




 create index covering on orders (shipcountry, shipname)
          Index intersection
• Le query optimiser peut utiliser plusieurs
  indexs d’une table.

								
To top