SQL Server vu par Christian Robert

Optimiser : Utilisation des index - Améliorer des tris

Lors de mon dernier billet sur la performance dans SQL Server, nous avons vu l'amélioration d'une simple recherche de données via des index essentiellement de type non clustered : http://blogs.codes-sources.com/christian/archive/2008/01/15/optimiser-utilisation-des-index-am-liorer-une-simple-recherche.aspx

Cette fois ci nous voir comment améliorer des tris.

Je vais créer une table sur le même modèle que la dernière fois, mais avec 2 champs supplémentaires, eux aussi remplis de manière aléatoire :

IF OBJECT_ID('dbo.MaTable') IS NOT NULL
DROP TABLE dbo.Matable

CREATE TABLE dbo.MaTable
(
Id int identity(1,1),
Nom varchar(100), 
Nom2 varchar(100), 
Nom3 varchar(100)
)
GO

DECLARE @var int
SET @var = 0

INSERT INTO dbo.MaTable(Nom)
VALUES('0000000000')

WHILE @var < 18
BEGIN

INSERT INTO dbo.MaTable(Nom, Nom2, Nom3)
SELECT CAST(@var as varchar(10)), CAST(NEWID() as varchar(50)), CAST(NEWID() as varchar(50)) FROM dbo.MaTable

SET @var = @var + 1

END
GO

Indépendamment de la technique que j'utilise, mes données ne sont pas triées dans ma table… Ici il se trouve que ma méthode de remplissage me donne un résultat à peu près trié par rapport au champ Id, mais dès le premier mouvement de données je suis susceptible de me retrouver avec tout autre chose.

      -- Résultat ici totalement aléatoire 
      select * 
      from dbo.MaTable

La seule garantie de retrouver les données triées est d'ajouter a votre requête la clause ORDER BY et c'est impératif, faute de quoi les erreurs qui serait engendrées pourraient se révéler catastrophiques.

select *
from dbo.MaTable
order by Id

Nous donnera un plan très simple

La différence entre la requête réalisant le tri et l'autre est uniquement de l'autre du temps processeur… Le tri pour cette requête nous consomme environ 200ms de processeur. C'est tout à fait normal, la majeure partie du temps les données à trier peuvent l'être en mémoire, et le tri c'est de l'algorithme qui consomme uniquement du processeur.

On voit nettement apparaître que dans un plan à base d'ORDER BY la tâche la plus consommatrice de ressource est le tri. Sur une machine a base de cœurs multiple SQL Server décide même de réaliser le tri en parallèle sur les 2 unités de calcul, ce qui provoque une réduction de la durée d'exécution de la requête, mais pas de la consommation de CPU (au contraire).

Ajoutons un index sur le champ Id, pour voir si nous obtenons un gain :

create nonclustered index ix_test on dbo.MaTable(Id)

Comme la dernière fois, pas de surprise, pas d'utilisation de l'index le plan reste le même… Mais est ce normal ?

En fait oui, un index réalise un tri lors de sa création, mais uniquement du champ sur lequel il est défini… Ici nous avons donc l'ensemble du champ Id qui est trié et stocké dans la base de donnée dans l'index ix_test, mais la moteur ne peut l'utiliser, car il n'a pas besoin que du champ Id, mais aussi de tous les autres de la table. Cela reviendrait si je forçais l'utilisation de cet index à :

select *
from dbo.MaTable with(index = ix_test)
order by Id

Le fait de forcer l'index ici me donne le plan suivant :

Ce qui saute aux yeux c'est la taille des flèches qui est gigantesque… Et le résultat c'est que mon moteur pour exécuter une telle requête doit traiter 262635 alors que la table n'en comporte que 3085. Le fait de forcer l'utilisation de l'index a supprimé la tâche de tri en contrepartie de quoi, le moteur a du utiliser l'index pour tous les enregistrements présent dans la table, soit 85 fois plus de données que nécessaire.

Clairement ici l'utilisation de ce type d'index n'est pas l'utilisation et le moteur s'en est rendu compte bien avant nous ! Par contre une variante de la requête va s'avérer intéressante :

select Id
from dbo.MaTable
order by Id

select *
from dbo.MaTable

Cela nous donne le même plan, mais avec une variante dans l' « Index Scan »

Quand on regarde de plus près de qu'il y a dans les propriétés de l'Index Scan, on constate que la ligne « Ordered » a changé entre les 2 requêtes !

En passant le nombre de lecture est lui tombé de manière vertigineuse à 491 pages, et le temps CPU à environ 50 ms. Le gain est très net ici par rapport à notre requête sans index.

Ce qui s'est passé est tout simple, notre requête n'a besoin que d'un seul champ : Id, il se trouve que ce champ est déjà indexé et donc que le champ Id y est trié… Donc le moteur n'a rien d'autre à faire qu'à lire l'index et renvoyer le résultat.

Mais, il y a un mais, il n'y a aucune garantie que malgré le tri de l'index (tri qui est réalisé de manière logique, chaque page pointe sur la page suivante et la précédente pour s'assurer de l'ordre des données présentes dans l'index) les données soient triées correctement (physiquement) sur le disque. Or çà n'a pas de sens pour mon moteur de réaliser des lectures page par pages en fonction de leur ordre directement depuis le disque, c'est beaucoup trop couteux. On réalise alors une lecture séquentielle (indépendante de l'ordre des données) pour charger toutes les pages en mémoire.

C'est alors qu'intervient le « Ordered » à True ou False… Si l'on spécifie ORDER BY Id, il est à True, ce qui force le moteur à remettre les données dans l'ordre, mais sans réaliser une couteuse tâche de tri, juste en suivant l'ordre des pages en mémoire (par les liens internes des pages d'index), cette tâche est extrêmement dépendante de la fragmentation des données… Par contre dans le cas où nous ne souhaitons pas trier nos données, le « Ordered » est laissé à False, pas d'ordre à rétablir, les données sont renvoyées telles quelles.

Comment réaliser le même type de chose, avec l'ensemble des données de la table et donc supporter le SELECT * que j'ai spécifié au début.

En utilisant un Index dit « Clustered » :

create clustered index ix_test2 on dbo.MaTable(Nom)

Ici je le crée sur le champ Nom pour bien vous montrer son comportement en dehors du cas classique où il est utilisé. La requête associée :

select *
from dbo.MaTable
order by Nom

Donne le plan :

L'index est bien utilisé, le « Ordered » est bien à True, et la tâche de trie est absente du plan. On a donc bien un gain à l'utilisation de cet index. Regardons le nombre de pages lues, et là par contre c'est un peu la déception car nous lisons 3290 pages, un peu plus que lorsque nous lisions la table seule au début.

En fait l'index clustered présente une caractéristique particulière. Là où les autres index ne stockent que les données de la clef ou les clefs sur lesquelles ils sont définis (ici le champ nom), le clustered contient tous les champs de la table. Cela explique l'index clustered soit un peu plus volumineux que les données même de la table.

Il nous reste toujours un problème, si je veux faire un tri sur l'un des autres champs de ma table :

select *
from dbo.MaTable
order by Nom2

Le plan :

Dès qu'un index clustered est crée sur la table je suis obliger de passer par lui pour tout accès aux données. Ce qui explique que je n'ai pas de « Table Scan », mais un « Index Scan » marqué « Ordered » à False, et la tâche de tri est réapparue dans le plan.

L'index clustered nous rend bien des services, mais je n'ai le droit d'un mettre 1 et un seul… C'est d'ailleurs bien souvent, le problème. Sur quel champ le mettre ?

On verra qu'il existe des alternatives à cette limitation d'index clustered unique…

Bonnes performances…