Fragmentation d'un index clusterisé [Résolu]

[Roubachof]

Chers développeurs,

J'ai une table de positions gps de 9Go qui possède donc un grand nombre de lignes.
Cette table a comme index clusterisé

GpsVehicleID bigint,
GpsTime dateTime

Il y a beaucoup d'insertions (plus de 100 par secondes). En règle générale, les lignes insérées se suivent plus ou moins selon la colonne GpsTime.
Le nombre total de véhicules est lui peut élevé (à peu près 5000).

Au départ mon cluster était inversé, le temps étant en premier.
Mais nous nous sommes alors aperçu que toutes les requêtes faites étaient d'abord filtrées par le GpsVehicleID puis par le temps.
Nous avons donc décidé d'inverser le cluster afin d'économiser la maintenance d'un index non clusterisé sur une table aussi obèse.

Cependant, aujourd'hui j'ai un doute. Est-ce que cela ne créerait pas plus rapidement de la fragmentation dans le cluster de ne pas mettre en premier la colonne sensée augmenter de manière monotone ?

[SQLpro]

évidemment oui !

Mais pour la meilleure configuration il nous faudrait connaître le SGBDR. Par exemple pour SQL Server je vous aurait conseillé de rajouter une clef auto incrémentée de type BIGINT et de créer l'index cluster dessus, puis de créer une contrainte d'unicité sur vos colonnes avec un FILL factor de l'ordre de 90%

A +

[Luc Orient]

Envoyé par SQLpro

... pour SQL Server je vous aurait conseillé de rajouter une clef auto incrémentée de type BIGINT et de créer l'index cluster dessus, puis de créer une contrainte d'unicité sur vos colonnes avec un FILL factor de l'ordre de 90% ...

Donc deux index au lieu d'un ... Si c'est le cas, l'optimisation proposée m'échappe quelque peu ...

[Roubachof]

Je ne comprends pas bien l'intérêt de créer un index cluster sur une colonne que je devrai rajouter, et qui ne me sera d'aucune utilité...
Et comme l'a souligné Luc, cela me rajoute un index en plus.

Ma question portait plus sur l'intérêt du cluster regroupé par VehiculeID et non par le temps gps. Je me demande si ça vaut le coup d'économiser un index non cluster grâce à ce cluster (rajouter ce type d'index fait grossir la table de 25%, non négligeable, avec tous les traitements à rajouter au niveau de la mise à jour de l'index), sachant qu'apparemment selon vous et comme je le soupçonnais cela va créer de la fragmentation plus rapidement qu'un cluster sur le temps gps.

Donc ma question est plutôt qu'est-ce qui a le plus d'impact sur les performances entre ces deux possibilité ?

Merci.

[SQLpro]

Je n'ai pas le temps de faire une explication de 3 heures sur le sujet.

Commencez par lire ceci : http://sqlpro.developpez.com/optimis...ntenanceIndex/
Il vous faut comprendre comment sont structurées les pages de données et d'index dans un SGBDR

Ensuite comprenez que :
sans index cluster, la ligne d'une table est repéré à l'aide d'une clef physique comportant 3 éléments :

• le n° de fichier
• le N° de page dans le fichier
• et enfin le N° de slot de ligne dans une page

Tous les index créés doivent référencer la ligne de la table pour qu'après recherche dans l'index on puisse revenir sur le table.
Si vous utilisez un index cluster, alors la ligne de la table n'est plus repérée par cet identifiant physique, mais par l'indentifiant logique que constitue la valeur de la clef cluster. Tous les index secondaire utiliseront donc cet info pour retrouver la ligne

Si votre clef de cluster est longue ou composé de plusieurs parties, alors elle devient moins intéressante que de ne pas clusteriser.
C'est pourquoi l'index cluster doit être le plus court possible.
Or à l'évidence DATETIME + BIGINT = 2 colonnes et 16 octets.
Alors que BIGINT d'auto incrément = 1 colonne et 8 octets.
Qu'est ce qui sera plus optimisé selon vous : que tous vos index secondaires ait à faire référence à la ligne par 16 octets en 2 colonnes ou 8 octets en 1 colonne ?

A +

[Roubachof]

Tout d'abord merci pour le lien!
Celui-ci a répondu de manière concrète à ma question principale.
En effet, avec un GpsVehiculeID en première colonne de cluster, cela va créer un nombre important de split de pages, et donc de fragmentation... Dans mon index cluster.

Par contre, et malgré la lecture de votre article, je ne vois toujours pas l'avantage du cluster sur clé auto-incrémentée.
Avec cette solution, je dois créer un index non cluster en plus, donc je me retrouve à gérer l'index non-cluster sur (GpsVehiculeID, GpsTime) ET l'index cluster sur la clé auto-incrémentée.

Lorsqu'une recherche sera faite dans cet index, cela me coutera le coût du parcours de l'index + le lien vers l'index cluster.
Sa taille sera aussi grossie par ce nouvel index cluster.

Alors que si je crée un seul index, cluster, sur (GpsVehiculeID, GpsTime), j'accède directement à mes lignes, sans indirection.
Je paye certes la taille du cluster sur deux colonnes, mais j'économise quand même par rapport à un cluster + un index non cluster sur ces mêmes deux colonnes.

Maintenant je dis ça bien humblement, c'est juste qu'il y a quelque chose qui doit m'échapper. Si vous pouviez m'éclairer cela me permettrait de progresser dans ces méandres...

[Luc Orient]

Mais quel SGBDR vous utilisez ?

[Roubachof]

Pardon, SQL Server 2008.

[SQLpro]

Tout dépend le nombre d'index secondaire que vous avez. La notion de volume global n'est pas d'un grand intérêt. Une base non indexée est moins volumineuse... Est-elle plus rapide ???

A +

[Roubachof]

Tout fait, il n'y a pas forcément de corrélation entre ces deux dimensions.
Votre remarque m'interpelle particulièrement, car dans mon cas, je possède en effet un autre index non-cluster pointant sur (GpsVehicleUserID, GpsTime), permettant de récupérer des positions pour un conducteur donné. Je dois donc prendre en compte que le nombre d'E/S sera sensiblement augmenté par l'agrandissement du row locator.

Cependant, pour revenir à mon cas, je viens de tomber sur un passage du livre "SQL Server query performance tuning distilled" par Sajal Dam.

Il met en avant un impact important des index cluster: leur contiguïté physique sur le disque. Comme précisé, mon index cluster a été construit en vu d'optimiser la récupération de positions pour un véhicule à un temps donné (requête critique de l'application). Ainsi lors de la récupération pour un véhicule, d'une plage de positions, la tête de lecture du disque n'aura pas à faire des aller-retour entre les différentes positions pointées par l'index non-cluster vers l'index cluster auto-incrémentées. Celles-ci sont contigües et seront donc récupérées d'un traite, économisant un nombre d'E/S conséquent.

Résumons donc:

Solution A

cluster (GpsVehicleID, GpsTime).
index non cluster (GpsVehicleUserID, GpsTime).

Avantage:
+Performance "optimum" pour les requêtes indéxées selon le cluster.

Désavantages:
-Fragmentation à surveiller (nombre page split possiblement élevé)
-Index non cluster moins efficace que solution B.

Solution B

cluster (GpsPositionID) (clé auto-incrémentée)
index non cluster (GpsVehicleID, GpsTime).
index non cluster (GpsVehicleUserID, GpsTime).

Avantages:
+Index non cluster sur GpsVehicleUserID plus efficace que solution A
+Pas de fragmentation sur le cluster.

Désavantages:
-Fragmentation de l'index non cluster (GpsVehicleID, GpsTime)
-Performance sur l'index non cluster (GpsVehicleID, GpsTime) moindre que solution A

Mon analyse vous parait-elle pertinente ?

[SQLpro]

Il n'est pas possible de continuer de discuter de votre problématique sans savoir
1) la structure EXACTE et COMPLÈTE de votre table (colonne, type, index, contraintes...)
2) avoir une idée de la distribution des données
3) de connaître la nature des opérations transactionnelles qui y sont effectuées.

prenez ce petit exemple que je donne en cours d'optimisation chez Orsys :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
USE master
GO
 
IF EXISTS(SELECT *
          FROM   sys.DATABASES
          WHERE  name = 'DB_ORSYS')
   DROP DATABASE DB_ORSYS
GO
 
CREATE DATABASE DB_ORSYS
GO
 
USE DB_ORSYS
GO
 
CREATE TABLE T_PROSPECT_PSP
(PSP_ID          INT NOT NULL PRIMARY KEY IDENTITY,
 PSP_NOM         VARCHAR(32),
 PSP_PRENOM      VARCHAR(25))
GO
 
DECLARE @I INT
SET @I = 1
WHILE @I <= 10000
BEGIN
   INSERT INTO T_PROSPECT_PSP VALUES ('DUPONT', 'Paul')
   SET @I = @I + 1
END
GO
 
sp_spaceused 'T_PROSPECT_PSP'
GO
 
/********************************************************************************************************
name             rows        reserved           data               index_size         unused             
---------------- ----------- ------------------ ------------------ ------------------ ------------------ 
T_PROSPECT_PSP   9940        328 KB             288 KB             16 KB              24 KB
********************************************************************************************************/
 
DBCC SHOWCONTIG ('T_PROSPECT_PSP')
GO
 
/********************************************************************************************************
DBCC SHOWCONTIG analyse la table 'T_PROSPECT_PSP'...
Table : 'T_PROSPECT_PSP' (1977058079); index ID = 1, base de données ID = 17
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 36
- extensions analysées...........................: 6
- extensions commutées..........................: 5
- Moy des pages par extension...............: 6.0
- Densité d'analyse [meilleure valeur du compte réel].......: 83.33% [5:6]
- Fragmentation d'analyse logique..: 0.00%
- Fragmentation d'analyse d'extension..: 16.67%
- Moy octets libres par page.....................: 41.3
- Densité de page moy (pleine)...........: 99.49%
********************************************************************************************************/
 
-- mise à jour de 10% des lignes de la table, ajout de 9 caractères aux noms
UPDATE T_PROSPECT_PSP
SET    PSP_NOM = 'DUPONT-DUMOULIN'
WHERE  PSP_ID % 10 = 0
GO
 
-- mise à jour de 10% des mêmes lignes de la table, ajout de 5 caractères aux prénoms
UPDATE T_PROSPECT_PSP
SET    PSP_PRENOM = 'Jean-Paul'
WHERE  PSP_ID % 10 = 0
GO
 
-- 10% des lignes ont une taille qui est passé de 10 à 24 caractères
-- soit une augmentation de 140% pour les lignes concernées
-- soit une augmentation de 14% pour la table...
 
sp_spaceused 'T_PROSPECT_PSP'
GO
 
/********************************************************************************************************
name             rows        reserved           data               index_size         unused             
---------------- ----------- ------------------ ------------------ ------------------ ------------------ 
T_PROSPECT_PSP   9999        584 KB             568 KB             16 KB              0 KB
********************************************************************************************************/
 
-- la différence est de 97% d'augmentation de volume et non 14% !!!
 
DBCC SHOWCONTIG ('T_PROSPECT_PSP')
GO
 
/********************************************************************************************************
DBCC SHOWCONTIG analyse la table 'T_PROSPECT_PSP'...
Table : 'T_PROSPECT_PSP' (1977058079); index ID = 1, base de données ID = 17
Analyse du niveau TABLE effectuée.
- Pages analysées................................: 71
- extensions analysées...........................: 10
- extensions commutées..........................: 70
- Moy des pages par extension...............: 7.1
- Densité d'analyse [meilleure valeur du compte réel].......: 12.68% [9:71]
- Fragmentation d'analyse logique..: 49.30%
- Fragmentation d'analyse d'extension..: 10.00%
- Moy octets libres par page.....................: 3814.9
- Densité de page moy (pleine)...........: 52.87%
********************************************************************************************************/
 
-- le nombre de "sauts" d'une extension à l'autre est passé de 5 à 70, soit une aumentation de 1400 %...

Réitérez la même chose maintenant en utilisant du CHAR à la place du VARCHAR dans la table et dites moi ce que vous en pensez !

A +

[Roubachof]

Bon ce n'est pas grave pour ma pseudo-synthèse. Je pense avoir compris le problème induit par les index cluster: celui du key lookup.

Cet article m'y a bien aidé.

C'est à moi de trancher au niveau de mes requêtes...

Pour ce qui est de votre exemple je pense avoir compris que remplacer le varchar par du char réduira les sauts. La mise à jour de ces champs variables amène un grand nombre de déplacement de ligne.

[SQLpro]

CQFD

A +