1 / 16

Optimisation SQL Server

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

nau
Download Presentation

Optimisation SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Optimisation SQL Server Alexis Comte

  2. Plan • Organisation physique • Query optimizer • Statistiques • Procédures stoquées • Indexed Views • Covering index • Index Intersection

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

  4. Schéma d’une page

  5. 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 )

  6. Clustered index

  7. Non clustered index

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

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

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

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

  12. 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)

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

  14. select shipname from orders where shipcountry = 'France‘ create index covering on orders (shipcountry, shipname)

  15. Index intersection • Le query optimiser peut utiliser plusieurs indexs d’une table.

More Related