Discussion :

[cestpasmoinonplus]

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

[mikedavem]

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.

++

[SQLpro]

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 +

[cestpasmoinonplus]

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 :

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

[cestpasmoinonplus]

Merci SQLPro, je vais lire et digérer tout cela

[SQLpro]

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

[cestpasmoinonplus]

Tout bien noté. Merci à tous

[StringBuilder]

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.