> Data > Améliorez les performances base de données avec l’assistant d’optimisation des index

Améliorez les performances base de données avec l’assistant d’optimisation des index

Data - Par iTPro.fr - Publié le 24 juin 2010

Windows

par Itzik Ben-Gan
En utilisant des index appropriés, les requêtes deviennent plus performantes qu'elles ne l'ont jamais été Avez-vous créé des index optimisés pour votre base de données ? Avez-vous pensé à utiliser les meilleurs index de tables clusterisés ? Avez-vous déterminé quels index peuvent améliorer les performances des requêtes ? Le choix du meilleur index clusterisé pour une table base de données principale constitue l'un des problèmes les plus ardus que rencontrent les administrateurs de bases de données.
Toutefois, le choix des meilleurs index non clusterisés n'est pas non plus très aisé. En effet, cela impose de prendre en considération la distribution statistique des données, les différentes techniques utilisées par l'optimiseur de requêtes pour concevoir un plan d'exécution efficace, ainsi que le nombre de sélections et de modifications effectuées par les utilisateurs sur la base de données pour ne pas créer d'index superflus.

Les développeurs SQL Server 7.0 disposent déjà de SQL Profiler, un outil inestimable permettant de suivre les requêtes

Ne serait-il pas idéal de disposer d'un outil capable d'analyser les requêtes effectuées sur sa base de données et de recommander les index à créer ? Et bien coup de chance. Les développeurs SQL Server 7.0 disposent déjà de SQL Profiler, un outil inestimable permettant de suivre les requêtes effectuées sur une base de données. On peut enregistrer le résultat de Profiler dans un fichier, une table ou un script SQL. On peut ensuite analyser ce résultat à l'aide d'un autre outil de SQL Server 7.0, l'assistant d'optimisation d'index ou Index Tuning Wizard (ITW), lequel recommande les index à concevoir.

Pour s'assurer que l'ITW donne des indications efficaces, il faut assurer un suivi des requêtes pendant une période d'activité type sur le système (et non une période d'activité exceptionnellement intense ou faible ni une période où se produisent des activités exceptionnelles). Il faut également décider pendant combien de temps Profiler doit suivre les requêtes. On peut par exemple avoir une représentation caractéristique de l'activité du système, en ne faisant une trace avec Profiler que durant quelques heures. Ou alors, il faudra effectuer un suivi sur quelques jours ou plus pour capturer les variations d'activité en cours de journée ou sur plusieurs jours.

Pour comprendre le fonctionnement de l’ITW, analysons l’exemple d’une table des
employés dans la base de données HR, tel qu’indiquée dans le listing 1. La table
des employés se compose de 1.000 enregistrements occupant chacun une page entière.
Aussi, la table occupe-t-elle 1.000 pages de données.

Analysons à présent les performances de quelques requêtes effectuées sur la base
de données HR. Pour commencer, comme le montre le listing 2, mettons STATISTICS
IO à « On ». Cela permet de compter les lectures logiques. Vous noterez que chaque
requête utilise 1.000 lectures logiques car, pour l’instant, le seul moyen d’exécuter
une requête est de passer en revue toute la table.

On peut ensuite générer une trace dans SQL Profiler, qui sera analysée à l’aide
de l’ITW. Les propriétés de suivi par défaut de Profiler, qui inclut les événements
SQL:BatchCompleted et RPC:Completed, dans la catégorie TSQL, et les colonnes de
données EventClass et Text suffisent à l’ITW. Après avoir nommé le suivi, on demande
au Profiler d’envoyer le résultat vers un fichier.

Il faut également s’assurer que le suivi ne génère pas de sortie superflue en
définissant un filtre qui permette de ne capturer que les requêtes effectuées
dans la base de données HR. Pour définir ce filtre, on exécute l’instruction SELECTDB_
ID(‘HR’) pour récupérer l’ID de la base de données et on utilise cette valeur
comme ID filtre de la base de données. Pour démarrer le suivi, cliquez sur OK
dans la boîte de dialogue Propriétés du suivi (Trace Properties), exécutez à nouveau
toutes les requêtes précédentes, et arrêtez le suivi. Maintenant que l’on a le
résultat du suivi de Profiler, on peut lancer l’ITW. (Notez que l’ITW peut également
utiliser un script SQL, semblable au script du listing 2, pour effectuer l’analyse.)

On peut démarrer l’ITW depuis le Profiler à partir du menu Tools (Outils) ou depuis
SQL Enterprise Manager (choisissez Wizards (Assistants) dans le menu Tools (Outils),
ouvrez Management (Gestion) et sélectionnez l’Index Tuning Wizard (Index Tuning
Wizard)). Après l’écran de bienvenue, un écran vous demande un nom de serveur
et de base de données comme cela est visible sur l’écran 1. Cet écran demande
également si on souhaite conserver tous les index existants. En annulant cette
option, l’ITW est susceptible de recommander de laisser tomber ou de remplacer
les index existants. En sélectionnant Perform thorough analysis sur cet écran,
on indique à l’ITW qu’il doit effectuer une analyse exhaustive des combinaisons
de colonnes et d’index, ce qui peut avoir pour résultat une recommandation d’index
plus optimale, mais peut durer longtemps et augmenter la charge sur le serveur.

Si on souhaite utiliser l’ITW pour analyser un serveur très chargé ou faire une
analyse approfondie des options de colonne et d’index, essayez d’exécuter l’ITW
sur un serveur test et non votre serveur de production.

Comme le montre l’écran 2, la boîte de dialogue suivante demande de déterminer
la charge de travail que l’on souhaite faire analyser. On sélectionne la première
option (I have a saved workload file – Je dispose d’un fichier de charge de travail
enregistré), qui permet de passer à l’écran suivant et de fournir plus de détails.

Mais si on sélectionne une autre option (I will create a workload file on my own
– Je créerai moi-même un fichier de charge de travail) et que l’on clique sur
Next, l’ITW ne charge pas Profiler. En revanche, l’ITW, qui nécessite une entrée
pour effectuer son analyse, suppose qu’il faille créer un fichier de charge de
travail, et donc il s’arrête tout simplement. L’écran 3 illustre la boîte de dialogue
qui apparaît lorsqu’on sélectionne I have a saved workload file. On peut sélectionner
l’entrée d’un fichier script.sql, un fichier de suivi Profiler ou une table de
suivi Profiler. En cliquant sur le bouton Advanced Options (options avancées),
on arrive à la boîte de dialogue illustrée par l’écran 4 et qui permet de choisir
entre :

Maximum queries to tune (nombre maximum de requêtes à optimiser)
: le nombre spécifié ici indique à l’ITW d’ignorer toutes les requêtes mais
pas les événements au-delà de ce nombre.
Maximum space for the recommended indexes (MB) (Espace maximum pour
les index recommandés (Mo)) : Cette valeur représente l’espace maximum,
en Mo, occupé par tous les index. Notez que cela inclut les index existants
si on a sélectionné Keep all existing indexes (conserver tous les index existants)
dans la première boîte de dialogue.
Maximum columns per index (nombre maximum de colonnes par index)
: cette option permet de restreindre la largeur maximum d’un index composé;
la largeur par défaut est de 16, qui est également le nombre maximum d’index
autorisés par table.

Listing 1 Création de la table Employees dans la base de données HR

-- Create the HR database CREATE DATABASE HR GO

USE HR GO -- Create the Employees table IF object_id('dbo.employees', 'U') IS NOT NULL DROP TABLE dbo.employees CREATE TABLE employees( emp_id int NOT NULL IDENTITY(1,1), emp_fname varchar(25) NOT NULL, emp_lname varchar(25) NOT NULL, department_id int NOT NULL, salary money NOT NULL, remarks CHAR(5000) CONSTRAINT df_remarks DEFAULT 'No remarks') GO -- The column remarks is used here as char to make sure -- the table will consume a lot of space. -- Load the Employees table with sample data SET NOCOUNT ON DECLARE @counter int SET @counter = 1 WHILE @counter <= 1000 BEGIN INSERT INTO employees(emp_fname, emp_lname, department_id, salary) VALUES('fname ' + cast(@counter as varchar), 'lname ' + cast(@counter as varchar), (@counter - 1) % 70 + 1, -- 70 departments with even distribution ((@counter -1) % 30 + 1) * $10000.00) -- 30 different salaries SET @counter = @counter + 1 END -- Look at the data in the table SELECT * FROM employees emp_id emp_fname emp_lname department_id salary remarks ------ --------------- ---------------- ------------------- ------------ -------------- 1 fname 1 lname 1 1 10000.0000 No remarks 2 fname 2 lname 2 2 20000.0000 No remarks . . . 1000 fname 1000 lname 1000 20 100000.0000 No remarks

(1000 row(s) affected)

Listing 2 Exécution de requêtes sur la base de donnees HR avec STATISTICS
IO actif

SET STATISTICS IO ON

SELECT * FROM employees WHERE emp_id = 13 SELECT * FROM employees WHERE emp_id = 701 SELECT * FROM employees WHERE emp_id = 312 SELECT * FROM employees WHERE department_id = 5 SELECT * FROM employees WHERE department_id = 15 SELECT * FROM employees WHERE department_id in (1,2,3) SELECT department_id, avg(salary) AS avg_salary FROM employees GROUP BY department_id ORDER BY department_id SELECT * FROM employees ORDER BY department_id SELECT emp_fname, emp_lname FROM employees WHERE emp_id = 109 SELECT * FROM employees WHERE emp_fname = 'fname 109' AND emp_lname = 'lname 109' SELECT * FROM employees WHERE emp_fname = 'fname 5' AND emp_lname = 'lname 5' SELECT * FROM employees WHERE emp_fname = 'fname 909' AND emp_lname = 'lname 909' SELECT * FROM employees WHERE emp_fname = 'fname 900' AND emp_lname = 'lname 900' SELECT emp_id, emp_lname, emp_fname FROM employees WHERE salary BETWEEN $20000.00 AND $50000.00

SET STATISTICS IO OFF

Téléchargez cette ressource

Travail à distance – Guide complet pour les Directions IT et Métiers

Le travail à distance met à l'épreuve la maturité numérique des entreprises en termes de Cybersécurité, d'espace de travail, de bien-être des collaborateurs, de communication et gestion de projet à distance. Découvrez, dans ce nouveau Guide Kyocera, quels leviers activer prioritairement pour mettre en place des solutions de travail à domicile efficaces, pérennes et sécurisées.

Data - Par iTPro.fr - Publié le 24 juin 2010