Developpez.com
Accueil
Je voudrais revenir sur un article de Rudi éclairant pour moi :
http://rudi.developpez.com/sqlserver/tutoriel/optimisation/
Dans cet article, il écrit « Une erreur commune, qui peut affecter fortement les performances, est d’écrire du code SQL avec la même approche intellectuelle que pour l’écriture de code procédural. »
Lire la suite
Apprenti DBA, je viens d’apprendre pourquoi les DBA Production utilisent les compteurs « logical reads,physical reads… » plutôt que la durée de la requête. Ecoutez donc…
Lire la suite
Si vous possèdez Power AMC, cet article ne s’adresse pas à vous car Power AMC indexe toutes les clefs étrangères sur simple demande, par contre, si vous utilisez Toad Data Modeler 4.1, vous devez préciser pour chaque entite, la création de l’index de clef etrangère.
Sachez que vous pouvez gagner un temps précieux dans le temps d’execution de vos requêtes en indexant les clés étrangères de vos tables. c’est un travail facile, les clés étrangères sont identifiés, il suffit de créer un index sur la clé manuellement ou à l’aide d’un outil.Toutes les jointures s’en trouveront améliorées. Avant de vous cassez la tête en index recouvrant et autre analyses complexes… Pensez y! Ca a le mérite d’être simple et efficace.
Vous pouvez utiliser aussi le tuning advisor de 2005 ou l’index tuning wizard de 2000.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx
Vous devez néanmoins mesurer à postériori, le gain de la pause de votre index, sur les différentes requêtes… En effet,Selon Yves Drothier de JDN :
« Par défaut, les clés primaires des tables disposent d’un index et il est pertinent de le conserver mais les clés secondaires posent un problème plus complexe. Parfois, selon le volume, la sollicitation de la base ou l’évolution des applications métiers, les index des clés secondaires perdent de leur attrait. Il est donc préférable de poser ses index en mesurant régulièrement l’efficacité de ses requêtes. »
Pour indexer une base de données, utiliser l’assistant parametrage du moteur de bases de données sql serveur 2005.
Dans un premier temps, il est nécessaire de capturer une trace à l’aide du query analyser. On stocke l’information dans un fichier ou une table de bases de données.
exemple :
USE AdventureWorks
SELECT *
FROM Production.Product
ORDER BY Name ASC ;
Dans un second temps, on lance l’assistant parametrage du moteur de bases de données, on indique sur quel trace on va travailler, sur quel base et quel table portera l’analyse. On lance le conseiller et il nous fournit des recommandations.
Dans le cas présent, un index portant sur name.
Attention! sur des petites requetes ( 20 lignes ), le temps d’execution est trop court pour pouvoir mettre en place une optimisation. Meme si l’analyse de la requete permet d’imaginer la mise en place d’un index.
exemple :
USE Test
SELECT item,color,sum(quantity)FROM inventory2 GROUP BY item,color,quantity order by quantity
un webcast pour comprendre sur le site technet :
un didactiel microsoft :
http://technet.microsoft.com/fr-fr/library/ms166575.aspx
Pour information, il existe aussi un outil de paramétrage des index sous sql serveur 2000.
L’ importance de la normalisation de la base est essentiel. Le modèle relationnelle a fait passer le gros du travail de la partie développement à la partie conception.
L’ Architecte de la base, premier intervenant du projet, qui utilise POWER AMC ou Toad Data Modeler doit respecter absolument la 3 ème forme normale dans la définition de son modèle. On ne peut pas faire l’ économie d’un modéle relationnel dans la conception d’ une application.
Le deuxième pôle de l’ optimisation est le soin apporté à l’ écriture du SQL. Privilégié le plus possible l’ écriture de SQL à l’ écriture de Transact SQL. L’ utilisation de SQL permet l’ indexation et des performances supérieurs. Par exemple, la semaine dernière, mon patron me signale une application dont les temps de réponses dépassés 30 secondes. Fier de mes connaissances, je propose d’ optimiser la requête par une indexation adéquate. Je passe 3H00 sur la requête pour finalement découvrir que les 30 secondes sont perdus par l’ utilisation en cascade de fonctions. Évidemment, en tant que développeur, on doit répondre à des besoins fonctionnelles et l’encapsulation en fonction répond bien à nos besoins mais en production, avec 100000 lignes dans une table, on perd un temps précieux à l’ exécution.
Le troisième pôle de l’ optimisation est l’ indexation. Sur des tables de plus de 1000 lignes, il devient important d’ indexer. Un produit comme POWER AMC vous facilite le travail puisqu’ il génére la majorité des index à la conception pour des tables bien normalisé.
On découvre donc bien que l’ essentiel de l’ optimisation d’ une base de données ne se passe pas donc pas en production, par le DBA, mais lors de la fabrication, par le développeur et l’architecte de la base.
Ne simplifions pas trop, les développeurs règlent généralement très bien les problèmes apparents et les problèmes de performances apparaissent souvent très tard dans le cycle de vie et entraîne des interventions des DBA. Néanmoins, la montée en charge future d’ une application peut être prévu très tôt par le respect de normes.
Indexer…
les tables qui ont de nombreuses lignes ( au moins 100 000 ).
les colonnes souvent utilisées dans les requetes.
les colonnes utilisées dans les fonctions d’agregation
les colonnes utilisées dans les requetes group by
les colonnes utilisées dans les requetes order by
les colonnes utilisées dans les jointures, nottament les clef étrangères!
Ne pas indexer…
Les tables qui ont peu de lignes ( moins de 10 000 )
Les colonnes utilisées rarement dans les requêtes
Les colonnes de taille importante
Les colonnes souvent modifiées mais peu interrogées.
Pour optimiser une requete, la premiere des choses à faire, si la ou les tables comptent plusieurs miliers d’enregistrement avec des valeurs trés différentes, est de placer des index, de faire tourner et de supprimer les index non utilisés. Si vous avez exploré toute les possibilités des index, et que votre requete est toujours trop lente, la solution est la denormalisation. La dénormalisation doit être utilisée sans complexe dans les bases en lecture seule, sans mise à jour. Dans les bases transactionnelles, il est nécessaire de mettre en place des triggers pour assurer la cohérence de la base.
Imaginez deux tables en 3 eme forme normale.
element(idelement,nomelement)
structure(id, nom, idelement )
la dénormalisation consiste à garder element à l’identique et modifier structure de la façon suivante (2 eme forme normale):
structure(id,nom,idelement,nomelement)
ainsi la requete associant element et structure est instantannée…
SELECT id,nom,nomelement from structure.
bon developpement
pour aller plus loin, un debat : http://www.developpez.net/forums/showthread.php?t=6231
« Le remplissage de l’index par defaut determine la quantité d’espace que SQL Serveur doit réserver lorsqu’il crée un nouvel index avec les données existantes. La définitition du facteur de remplissage suppose un compromis ; si vous définissez un facteur trop élevé, SQL Serveur ralentit lorsque vous ajoutez des données à une table. Toutefois, un facteur de remplissage fixé trop bas risque d’affecter les performances en lecture de façon inversement proportionnelle au facteur de remplissage. Par exemple, un taux de remplissage de 25% peut diviser les performances en lecture par 4, mais il permet d’accomplir plus rapidement des mises à jour importantes qu’à l’origine. »
Par défaut, le remplissage de l’index est établi à 0. Mais la plage admise s’etend de 0 à 100.
Pour définir un facteur de remplissage, une valeur faible laisse plus de place pour les insertions sans necessiter de fractionnements de pages, mais l’index est plus encombrant.
Une valeur forte laisse moins de place aux insertions mais l’index prend moins de place.
exemple: sp_configure « fill factor (%) »,90
cet exemple configure la valeur par defaut du serveur
La syntaxe d’utilisation de CREATE INDEX est la suivante :
CREATE [UNIQUE] [CLUSTEREDNONCLUSTERED] INDEX nom_index ON nom_table
( nom_colonne, nom_colonne… ) WITH options ON nom_groupe_de_fichier.
Un index CLUSTERED contient les données de la table. Par conséquent, il ne peut y avoir qu’un seul index Clustered par table. En contrepartie, Microsoft met en garde contre l’utilisation de cette structure quand les enregistrements sont de taille importante car on ne peut alors mettre que peu d’enregistrements dans un noeud de l’arbre, ce qui risque de faire perdre à l’index une partie de son efficacité.
Un index est performant pour la recherche sur
- une clef : SELECT * FROM Film WHERE Titre=’Gladiator’
- un intervalle : SELECT * FROM Film WHERE Annee BETWEEN 1999 AND 2001
- ou une partie de la clé : SELECT * FROM Film WHERE Titre LIKE ‘G%’
Les options :
PAD_INDEX : utilisé avec le paramètre FILL_FACTOR; indique qu’il faut laisser de l’espace dans les noeuds branches et pas seulement dans les noeuds feuilles.
FILL_FACTOR : spécifie le degre de remplissage de chaque noeud feuille; pourcentage compris entre 0 et 100. 0 est une valeur particulière, la gestion est laissée à sql server.
DROP_EXISTING : indique que si il existe deja un index portant ce nom, cet index sera supprimé puis recree avec la nouvelle définition.
ONLINE : commande de sql server 2005 enterprise , les tables sont accessibles en requete et modification durant les opérations d’indexation.
SORT_IN_TEMPDB : par default, OFF, les résultats des tris sont calculés dans la base courante. Sur ON, les résultats des tris sont stockées dans TEMPDB.
exemple :
CREATE CLUSTERED INDEX item_index ON INVENTORY(item) WITH ( PAD_INDEX=ON,FILLFACTOR=50 )
© 2000-2020 - www.developpez.com
Partenaire : Hébergement Web
