Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Discussion: Gestion du cache

  1. #1
    Nouveau membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    163
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 163
    Points : 39
    Points
    39

    Par défaut Gestion du cache

    Bonjour à tous,
    Je vous prie, tout d'abord, d'excuser ma naïveté informatique qui me pousse à considérer certains fonctionnements comme de la magie.
    Je débute sur SQLServer et je me pose la question de la gestion du cache.
    Voilà quelque chose qui, pour moi, relève de la magie.
    Je ne dis pas que cela ne fonctionne pas bien, mais je me pose juste la question des limites optimales de cette gestion du cache.
    Elle augmente clairement les performances, mais ne devient-elle pas contre productive au bout d'un certain volume de transaction ?

    D'où ma question :
    Les vétérans de cet outil ont-ils un process "d'hygiène fonctionnelle" permettant de s'en assurer un fonctionnement optimal ?

    Merci d'avance et meilleurs voeux
    Pas changer assiettes pour fromage.

  2. #2
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    août 2005
    Messages
    5 192
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : août 2005
    Messages : 5 192
    Points : 12 057
    Points
    12 057

    Par défaut

    Hello,

    Disons qu'il n'y a pas vraiment de lien direct entre volume de transactions et taille du cache sur SQL Server. Sur SQL Server (comme d'autres SGBDRs) il y a plusieurs caches.

    => L'efficacité du cache (buffer cache) réside dans le fait de pouvoir mettre en mémoire les pages de données des objets de la table (table, indexes etc ...). Pour rester simple, le problème se créé lorsque la taille du cache ne permet pas de stocker l'ensemble des objets sollicités par l'application (les transactions). Après on peut commencer à entrer dans des considérations d'optimisations (requête bien écrites, index manquants etc...) qui permettent d'avoir une utilisation plus efficiente de cet espace mémoire.

    => L'efficacité du cache de plan de requête n'est également pas lié au volume transactionnel mais à la façon dont l'application l'utilise. Je peux avoir un volume transactionnel important avec une activité object compilé (procédure stockée, requête préparée ... ) mais qui va au final réutiliser les plans de requêtes déjà dans le cache. A l'inverse je peux avoir un volume transactionnel plus faible avec une activité orientée requête adhoc et qui viendra polluer ce cache car aucune vraie possibilité de réutiliser les plans stockés en cache. Là aussi on peut rentrer dans des considérations d'optimisation pour maximiser l'utilisation du cache.

    ++

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 971
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 971
    Points : 44 567
    Points
    44 567

    Par défaut

    Il existe une segmentation des caches (environ une trentaine) parmi lesquels le cache des données (buffer cache) est le plus important, suivi du cache des procédures (contenant des plans de requêtes réutilisable).

    L'algorithme de gestion des caches est basé sur le LRU (Least Recently Used); Autrement dit, plus récemment l'objet a été utilisé et plus il persistera en cache (ou placez-vous vos chaussettes et slips par rapport à vos affaire de ski ou de natation ?).

    Mais les données mise à jour en mémoire vont empêcher de permettre la mise en cache de nouvelles données si toute la RAM est utilisée.... Pour palier à cet inconvénient, le moteur enregistre les données mise à jour (appelées "pages sales") dans les fichier de données de manière asynchrone et cyclique.

    Ceci libère donc les pages sales qui deviennent des pages ordinaire et peuvent sortir du cache pour laisser la place à de nouvelles en cas de besoin.

    Tout ceci est effectué par différents mécanisme en tâche de fond, indépendamment du service des requêtes qui ne doit pas souffrir du moindre ralentissement !

    La conception de tout ces mécanismes est décrit dans l'ouvrage suivant :
    https://www.amazon.com/Readings-Data.../dp/1558605231

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

  4. #4
    Nouveau membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    163
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 163
    Points : 39
    Points
    39

    Par défaut

    Salut mikedavem et merci beaucoup pour ton topo.
    Et justement, dans ce que tu écris, il y a quelque chose que je vais essayer de creuser :

    Citation Envoyé par mikedavem Voir le message
    A l'inverse je peux avoir un volume transactionnel plus faible avec une activité orientée requête adhoc et qui viendra polluer ce cache car aucune vraie possibilité de réutiliser les plans stockés en cache. Là aussi on peut rentrer dans des considérations d'optimisation pour maximiser l'utilisation du cache.
    ++
    J'ai l'impression que cela correspond bien à mon inquiétude, fondée ou non. Mais j'aimerais beaucoup mettre en place une façon de faire ou un process permettant d'éliminer ce risque de pollution.
    Qui permettrait, au-moins dans cette circonstance, de conserver l'optimisation des performances de la base.
    Quelque chose entre "supprimer le cache systématiquement" et "ne pas toucher au cache".
    Pas changer assiettes pour fromage.

  5. #5
    Nouveau membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    163
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 163
    Points : 39
    Points
    39

    Par défaut

    Merci SQLPro, je vais lire et digérer tout cela
    Pas changer assiettes pour fromage.

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 971
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 971
    Points : 44 567
    Points
    44 567

    Par défaut

    Citation Envoyé par cestpasmoinonplus Voir le message
    ... j'aimerais beaucoup mettre en place une façon de faire ou un process permettant d'éliminer ce risque de pollution.
    Qui permettrait, au-moins dans cette circonstance, de conserver l'optimisation des performances de la base.
    Quelque chose entre "supprimer le cache systématiquement" et "ne pas toucher au cache".
    Ceci est paramétrable dans SQL Server à différents niveaux :
    1) au niveau serveur =>
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    EXEC sp_configure 'optimize for ad hoc workloads', 1
    2) au niveau base de données :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED
    A +

    A lire (les deux personnes qui vous ont répondues - Mike et SQLpro - ont co écrit cet ouvrage avec quelques autres...) : Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 42
Taille : 105,0 Ko
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  7. #7
    Nouveau membre du Club
    Homme Profil pro
    Développeur Delphi
    Inscrit en
    janvier 2019
    Messages
    163
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur Delphi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2019
    Messages : 163
    Points : 39
    Points
    39

    Par défaut

    Tout bien noté. Merci à tous
    Pas changer assiettes pour fromage.

  8. #8
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    3 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 3 564
    Points : 5 960
    Points
    5 960
    Billets dans le blog
    1

    Par défaut

    Si vous avez la main sur les requête exécutées, utilisez des requêtes paramétrées plutôt que des requêtes littérales.

    Ça évite de devoir activer l'optimisation ad hoc en permettant d'avoir un plan unique pour chaque requête quels qu'en soient les paramètres.

    Evitez :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    select nom, prenom from personne where id = 1;
    select nom, prenom from personne where id = 2;
    select nom, prenom from personne where id = 3;
    select nom, prenom from personne where id = 4;
    => Le moteur est obligé de compiler 4 requêtes différentes, et stocker en cache leur plan (au cas où elles soient réutilisés plus tard)

    Préférez :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    select nom, prenom from personne where id = @id; { @id = 1 }
    select nom, prenom from personne where id = @id; { @id = 2 }
    select nom, prenom from personne where id = @id; { @id = 3 }
    select nom, prenom from personne where id = @id; { @id = 4 }
    => Le moteur compile une et une seule fois la requête, puis rejoue simplement le plan mis en cache avec un paramètre différent.

    Attention cependant, car en fonction des statistiques et des paramètres, parfois le plan calculé pour le premier paramètre peut s'avérer non optimal pour d'autres valeurs de paramètres.
    Je pense que c'est cependant un risque qu'il vaut mieux prendre.
    On ne jouit bien que de ce qu’on partage.

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

Discussions similaires

  1. Gestion du cache en Load Balancing
    Par loic_86 dans le forum ASP.NET
    Réponses: 1
    Dernier message: 10/09/2007, 10h00
  2. [cache] Gestion du cache en général
    Par Maxoo dans le forum Balisage (X)HTML et validation W3C
    Réponses: 7
    Dernier message: 15/12/2006, 10h21
  3. Gestion du cache d'un ResulSet
    Par skunkies dans le forum JDBC
    Réponses: 1
    Dernier message: 30/10/2006, 18h12
  4. [Sécurité] Gestion du cache / cookies
    Par dug dans le forum Sessions
    Réponses: 4
    Dernier message: 25/01/2006, 21h17
  5. [Xml][Memoire] gestion du cache
    Par tatou42 dans le forum XML
    Réponses: 11
    Dernier message: 21/09/2005, 17h48

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo