Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 13 sur 13
  1. #1
    Invité de passage
    Inscrit en
    mars 2010
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : mars 2010
    Messages : 6
    Points : 1
    Points
    1

    Par défaut Fragmentation d'un index clusterisé

    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 ?

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 421
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 421
    Points : 27 585
    Points
    27 585

    Par défaut

    é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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Membre Expert

    Homme Profil pro François Durand
    Ingénieur Exploitation Mainframe
    Inscrit en
    octobre 2005
    Messages
    1 222
    Détails du profil
    Informations personnelles :
    Nom : Homme François Durand
    Âge : 55
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Ingénieur Exploitation Mainframe
    Secteur : Finance

    Informations forums :
    Inscription : octobre 2005
    Messages : 1 222
    Points : 2 097
    Points
    2 097

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    ... 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 ...

  4. #4
    Invité de passage
    Inscrit en
    mars 2010
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : mars 2010
    Messages : 6
    Points : 1
    Points
    1

    Par défaut

    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.

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 421
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 421
    Points : 27 585
    Points
    27 585

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  6. #6
    Invité de passage
    Inscrit en
    mars 2010
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : mars 2010
    Messages : 6
    Points : 1
    Points
    1

    Par défaut

    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...

  7. #7
    Membre Expert

    Homme Profil pro François Durand
    Ingénieur Exploitation Mainframe
    Inscrit en
    octobre 2005
    Messages
    1 222
    Détails du profil
    Informations personnelles :
    Nom : Homme François Durand
    Âge : 55
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Ingénieur Exploitation Mainframe
    Secteur : Finance

    Informations forums :
    Inscription : octobre 2005
    Messages : 1 222
    Points : 2 097
    Points
    2 097

    Par défaut

    Mais quel SGBDR vous utilisez ?

  8. #8
    Invité de passage
    Inscrit en
    mars 2010
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : mars 2010
    Messages : 6
    Points : 1
    Points
    1

    Par défaut

    Pardon, SQL Server 2008.

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 421
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 421
    Points : 27 585
    Points
    27 585

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  10. #10
    Invité de passage
    Inscrit en
    mars 2010
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : mars 2010
    Messages : 6
    Points : 1
    Points
    1

    Par défaut

    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 ?

  11. #11
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 421
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 421
    Points : 27 585
    Points
    27 585

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  12. #12
    Invité de passage
    Inscrit en
    mars 2010
    Messages
    6
    Détails du profil
    Informations forums :
    Inscription : mars 2010
    Messages : 6
    Points : 1
    Points
    1

    Par défaut

    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.

  13. #13
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 421
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 421
    Points : 27 585
    Points
    27 585

    Par défaut

    CQFD

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •