none
Auditer et Optimiser une Base de Données Sans Jointures

    Question


  • Bonjour,

    Voici un périmètre. j'ai une base de données, dont des 

    tables sans jointures, qui a atteint 400Go avec une 

    volumétrie transactionnelle de 100 Tr/s.

    La bases de données anciennement faite sur SQL SERVER 

    2000. Aujourd'hui sur SQL SERVER 2008.

    Un serveur à 20Go de RAM / Xéon... (bonne machine).

    Les developpeurs utilisent, dans le cadre d'une 

    application, des longues requêtes paramétrées. Leurs 

    exécutions atteignent jusqu'à 15 min (suite à des 

    verrous).

    Ma question est un peu générale : Comment optimiser 

    une base de données sans jointure.

    (avec le DTA, j'ai fait des indexes et des 

    statistiques)/ je trouve que nous pourrions faire 

    mieux. est ce que vous avez une idée?


    SQL SERVER MVP

    dimanche 23 septembre 2012 11:35

Réponses

  • Bonjour,

    C'est vraiment très général effectivment comme question... Voire il n'y a pas de réponse toute faite, a part ça dépend.

    On puet imaginer partitionenr certaines tables de manière à intégrer un niveau de vérouillage partition plutôt que de faire du lock escalation Row -> table.

    Pour du vérouillage, on peut aussi jouer sur les niveau d'isolation (READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISAOLATION).

    Sachant que votre BD ne peut tenir en RAM, quelle est la config disque. C'est la poitn faible de la majorité des configurations SQL Server. Si vous n'êtes pas capable de monter en IOPS, vos rq vont tourner au ralenti, que ce soit en SEL ou bien UPD,DEL ou INS. Ce sera d'autant plus flagrant si votre latence disque pour le journal de transaction est élevée. Comptez moins de 10ms pour que tout soit a peu près fluide, meêm si je préfèrerais < 5ms.

    Ensuite, si vous êtes en éditionentreprise, vous pouvez aussi compresser els données, les résultats peuvent être positifs sur vos rq lourdes.

    Comem vous le mentionnez, les index vont être extrèmement importants. Ne faites pas confiance au DTA, regardez vos requêtes, interrogez les DMVs et vous obtiendrez les index les plus importants.

    Il vous reste ensuite des possibilités en jouant sur la répartition de charge. Si certains rq sont en lecture seule, on peut imaginer des solutions a base de réplication et ainsi débrancher des rq en read-only sur ces bases répliquées.

    Une question générique appelle une multitude de réponses. Commencez par les niveau d'isolation, les index et comprendre les raisons de vos verrous (locks ou deadlocks ?).

    Cdlt

    Christophe.


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    lundi 24 septembre 2012 08:21

Toutes les réponses


  • Bonjour,

    Voici un périmètre. j'ai une base de données, dont des 

    tables sans jointures, qui a atteint 400Go avec une 

    volumétrie transactionnelle de 100 Tr/s.

    La bases de données anciennement faite sur SQL SERVER 

    2000. Aujourd'hui sur SQL SERVER 2008.

    Un serveur à 20Go de RAM / Xéon... (bonne machine).

    Les developpeurs utilisent, dans le cadre d'une 

    application, des longues requêtes paramétrées. Leurs 

    exécutions atteignent jusqu'à 15 min (suite à des 

    verrous).

    Ma question est un peu générale : Comment optimiser 

    une base de données sans jointure.

    (avec le DTA, j'ai fait des indexes et des 

    statistiques)/ je trouve que nous pourrions faire 

    mieux. est ce que vous avez une idée?



    SQL SERVER MVP

    dimanche 23 septembre 2012 11:34

  • Bonjour,

    Voici un périmètre. j'ai une base de données, dont des 

    tables sans jointures, qui a atteint 400Go avec une 

    volumétrie transactionnelle de 100 Tr/s.

    La bases de données anciennement faite sur SQL SERVER 

    2000. Aujourd'hui sur SQL SERVER 2008.

    Un serveur à 20Go de RAM / Xéon... (bonne machine).

    Les developpeurs utilisent, dans le cadre d'une 

    application, des longues requêtes paramétrées. Leurs 

    exécutions atteignent jusqu'à 15 min (suite à des 

    verrous).

    Ma question est un peu générale : Comment optimiser 

    une base de données sans jointure.

    (avec le DTA, j'ai fait des indexes et des 

    statistiques)/ je trouve que nous pourrions faire 

    mieux. est ce que vous avez une idée?


    SQL SERVER MVP

    Bonjour,

    Essayez en augmentant la taille des clusters du disque dur.

    Je pense que vous allez gagner du temps. En tout cas j'ai fait l'essai sur SQL 2005 lors d'une utilisation de Microsoft Navision.

    Informez moi du résultat SVP. J'aimerais savoir si c'est aussi le cas pour vous.

     

    Cordialement


    Merci de valider par "Proposer comme réponse" si celle-ci répond a votre demande !

    dimanche 23 septembre 2012 20:39
  • Bonjour,

    C'est vraiment très général effectivment comme question... Voire il n'y a pas de réponse toute faite, a part ça dépend.

    On puet imaginer partitionenr certaines tables de manière à intégrer un niveau de vérouillage partition plutôt que de faire du lock escalation Row -> table.

    Pour du vérouillage, on peut aussi jouer sur les niveau d'isolation (READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISAOLATION).

    Sachant que votre BD ne peut tenir en RAM, quelle est la config disque. C'est la poitn faible de la majorité des configurations SQL Server. Si vous n'êtes pas capable de monter en IOPS, vos rq vont tourner au ralenti, que ce soit en SEL ou bien UPD,DEL ou INS. Ce sera d'autant plus flagrant si votre latence disque pour le journal de transaction est élevée. Comptez moins de 10ms pour que tout soit a peu près fluide, meêm si je préfèrerais < 5ms.

    Ensuite, si vous êtes en éditionentreprise, vous pouvez aussi compresser els données, les résultats peuvent être positifs sur vos rq lourdes.

    Comem vous le mentionnez, les index vont être extrèmement importants. Ne faites pas confiance au DTA, regardez vos requêtes, interrogez les DMVs et vous obtiendrez les index les plus importants.

    Il vous reste ensuite des possibilités en jouant sur la répartition de charge. Si certains rq sont en lecture seule, on peut imaginer des solutions a base de réplication et ainsi débrancher des rq en read-only sur ces bases répliquées.

    Une question générique appelle une multitude de réponses. Commencez par les niveau d'isolation, les index et comprendre les raisons de vos verrous (locks ou deadlocks ?).

    Cdlt

    Christophe.


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    lundi 24 septembre 2012 08:21
  • Merci Christophe pour ton aide omni présente.

    PS : la base est sur un RAID 5.

    Ta démarche rythme avec mes soupçons.

    Il y a aussi la possibilité de voir les cluster du disk (comme propose :LyamineNat System).

    je continu a cerner le périmètre et revenir vers vous.

    il sera un cas d’école...


    SQL SERVER MVP

    lundi 24 septembre 2012 08:53
  • Bonjour,

    Ce n'est pas une très bonne idée, on s'expose aux :

      •Dirty Reads
      •Lost Updates
      •Phantom Reads
      •Non-repeatable Reads

      La taille des clusters disque est effectivemnt une recommendation (64K), tout simplement pour aligner els IO fait par SQL Server avec la partie physique. Si ces IOs ne sont pas alignés, tu risque seulement de provoquer plus d'activité côté matériel (1 IO demandé par le système sur un fichier de données => 16 IOs sur le disque si tu as gardé le formatage par défaut de 4K de windows). Tu va de fait dimnuer ta bande passante, donc la quantité de données par seconde que ton disque va remonter vers ton buffer pool. cela se traduit généralement pas du ASYNC_IO_COMPLETION, IO_COMPLETION et PAGEIOLATCH.

      Cela ne va rien changer à ta problématique de vérouillage, mais si tu as bcp d'accès disques (physical reads au niveau de tes requetes) alors, ca va augmenter un peu les performances. Mis a part l'ordre RESTORE DATABASE, tous les ordres SQL travaillent en mémoire. Donc la tailel de cluster ne va intervenir que sur els lectures physiques. Tu peux consulter sys.dm_exec_query_stats pour voir si il y a bcp de lecture physiques ou seulement de la lecture logique (dans ton buffer pool).

      Pendant que vos requetes passernt (vous avez le temps sur 15 minutes ...) regardez dans sys.dm_tran_locks, vous aurez déjà bcp de réponses ...

      Christophe


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    mardi 25 septembre 2012 13:39
  • Bonjour,

    Est-ce que vous avez testé les solutions proposées ? Merci de partager avec nous les résultats, afin que d'autres personnes avec le même problème puissent profiter de cette solution.

    Cordialement,

    Aurel


    Aurel BERA, Microsoft
    Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.

    jeudi 27 septembre 2012 08:03
  • Je suis en train de tester :

    • READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION sur une base de 385 Go plus de 3h
    • J'évaluerai après le possibilité de faire une compression Page. En calculant sur une Table de 2 Millions de Lignes 19 Go le résultat finale Table à 6 Go!!! . maintenant, est ce que je gagnerai en performance IO, vu que je compresse les Pages, reste à voir?
    • je travail un script pour exécuter le changement de mode compression sur toutes les tables.
    • Mes feedbacks se font un peu tard , c'est que je travail sur le serveur directement - donc au moment des creux.

    A+

    Cordialement


    SQL SERVER MVP

    jeudi 27 septembre 2012 12:29
  • Je suis en train de tester :

    • READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION sur une base de 385 Go plus de 3h
    • J'évaluerai après le possibilité de faire une compression Page. En calculant sur une Table de 2 Millions de Lignes 19 Go le résultat finale Table à 6 Go!!! . maintenant, est ce que je gagnerai en performance IO, vu que je compresse les Pages, reste à voir?
    • je travail un script pour exécuter le changement de mode compression sur toutes les tables.
    • Mes feedbacks se font un peu tard , c'est que je travail sur le serveur directement - donc au moment des creux.

    A+

    Cordialement


    SQL SERVER MVP

    Bonsoir,

    Avez-vous testé cette solution : http://www.youtube.com/watch?v=gjT8wL92mqE

    Ajouter un NONCLUSTERED INDEX.

    A part la répartition de charge ou passer par du Big Table, personnellement je ne vois pas quoi ajouter :/

     

    Cordialement


    Merci de valider par "Proposer comme réponse" si celle-ci répond a votre demande !


    • Modifié Lyamine jeudi 27 septembre 2012 20:26
    jeudi 27 septembre 2012 20:25
  • Bonjour,

    Pouvons-nous considérer que vous avez résolu votre problème avec les scénarios proposés ? Dans l'affirmative, pourriez-vous partager avec nous la solution, afin que d'autres personnes avec le même problème puissent profiter de cette solution ?

    Désormais, nous marquons les solutions proposées. N'hésitez pas à revenir et supprimer la réponse marquée si la solution n’est pas correcte. Merci !

    Cordialement,


    Aurel BERA, Microsoft
    Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.

    vendredi 28 septembre 2012 07:08
  • Merci pour l’intérêt que vous portez à cette question.

    Mes recommandations seront comme suit (chronologiquement):

      • Mettre la Tempdb.mdf sur un autre volume, repartir la base sur 2 Volumes - SAN avec 2 contrôleurs
      • Changer le mode READ COMMITTED par READ_COMMITTED_SNAPSHOT 
      • Utilisation de la fonction de Compresse Page vs Compresse Row sur les grosses Tables
      • Reconstruire les Indexes puis refaire les statistiques
      • Après pour mieux finaliser, le DTA (sans se fier à ses recommandation; Il faut tjrs avoir un oeil critique)

    PS: la base est de 400Go en plein production, l'application des ces recommandations sera en soiré et Weekend.

    Voila un séquencement proposé, merci de porter votre avis sur.

    Cordialement


    SQL SERVER MVP

    vendredi 28 septembre 2012 08:30
  • Il faut tout de même faire attention avec la compression de données. Si les données restent volatiles il faudra voir si le compromis économie d'IO / overhead CPU est rentable. Les pages de données étant compressés sur disque et en mémoire il faudra les décompresser pour les lire et les recompresser après écriture.

    ++


    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1

    vendredi 28 septembre 2012 16:00
  • Re Bonjour

    Oui, il y a ce risque. je choisirai que quelques tables. j'ajouterai un peu de CPU certe (+3% à 7%) mais je gagnerai en IOps disk. Je testerai pour cette fois et vous donnerez un Feedback.

    Cordialement


    SQL SERVER MVP

    vendredi 28 septembre 2012 16:19
  • Bonjour

    J'ajouterai avant le Partitionnement des grosses tables sur 2 contrôleurs distincts.

    Qlqs suggestions?


    SQL SERVER MVP

    lundi 1 octobre 2012 10:04