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.
Améliorez les performances base de données avec l’assistant d’optimisation des index
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
Les étapes pour sécuriser votre système d’impression
Longtemps sous-estimée, la sécurisation d’un système d’impression d’entreprise doit être pleinement prise en compte afin de limiter le risque de fuite d’informations sensibles. Voici les 3 principales précautions à prendre.
Les articles les plus consultés
- Dark Web : où sont vos données dérobées ?
- 10 grandes tendances Business Intelligence
- Stockage autonome, Evolutivité & Gestion intelligente, Pure Storage offre de nouvelles perspectives aux entreprises
- Databricks lève 1 milliard de dollars !
- Intelligence Artificielle : DeepKube sécurise en profondeur les données des entreprises
Les plus consultés sur iTPro.fr
- Les cybercriminels veulent transformer les blockchains en hébergeurs de contenus malveillants
- À l’ère du numérique, la sécurité est la clé d’un avenir financier innovant
- NIS2 et SecNumCloud : renforcer la cybersécurité européenne à l’ère du cloud
- Nouvelle vague d’attaques sur les sites de réservation
- En quête d’un juste équilibre entre sécurité et expérience utilisateur