Optimisation de l’utilisation de la mémoire par SQL Server

Microsoft SQL Server utilise de la mémoire pour mettre en cache des données et améliorer la performance du serveur. De par sa conception, SQL Server lit des pages de données du disque vers la mémoire. Il stocke le nombre de pages que vous lui indiquez et ne les supprime que lorsque cela est nécessaire. Lorsque le serveur reçoit des requêtes demandant des pages qui ont déjà été utilisées, il peut les récupérer rapidement à partir de la mémoire plutôt que de les créer à partir de zéro.

Nous pensons souvent à tort que SQL Server utilise toute la mémoire ou monopolise toutes les ressources de la mémoire. Puisque SQL Server s’efforce d’optimiser les performances, il utilise le volume maximum de mémoire qui lui est alloué. Toutefois, c’est vous qui déterminez le volume de mémoire que vous allouez à SQL Server et à d’autres applications ou au système d’exploitation.

Pour ce qui est des performances du système, trois éléments clés contribuent conjointement à garantir le fonctionnement optimal du système : le processeur, le stockage et la mémoire. Vous devez allouer la mémoire de manière efficace pour prévenir les goulots d’étranglement en termes de performances.

La tâche consistant à déterminer le volume de mémoire à allouer à SQL Server est complexe et implique la surveillance en continu de l’utilisation de la mémoire par SQL Server. En surveillant et en maximisant la mémoire allouée à SQL Server, vous pouvez améliorer les performances de SQL Server et les temps de réponse des requêtes du serveur.

Pour surveiller l’utilisation de la mémoire par SQL Server, l’administrateur de bases de données examine les mesures de l’utilisation de la mémoire sur la totalité de votre système informatique, sans se limiter à SQL Server, et utilise ces données pour allouer comme il convient vos ressources limitées en termes de mémoire.

Le principal objectif de la surveillance de l’utilisation de la mémoire par SQL Server est de déterminer le volume de mémoire à allouer à SQL Server sans nuire aux performances du système d’exploitation ou d’autres applications. Pour y parvenir, vous devez surveiller les indicateurs de performance de votre système et utiliser ces données pour procéder à un réglage plus judicieux de la mémoire allouée à SQL Server.

Plusieurs facteurs peuvent avoir une incidence sur l’utilisation de la mémoire d’un système informatique dans son ensemble. Pour surveiller et optimiser l’utilisation de la mémoire par SQL, vous devez capturer des données sur ces facteurs afin d’identifier les requêtes SQL qui nécessitent votre attention. Parmi les facteurs qui influencent l’utilisation de la mémoire, on compte les suivants :

• Espérance de vie des pages. Il s’agit d’un indicateur important pour surveiller l’utilisation de la mémoire. Les baisses de l’espérance de vie des pages indiquent que les pages ne restent pas en mémoire pour être réutilisées. On remarque habituellement ce comportement lorsque SQL Server commence à manquer de mémoire tampon.

• Taille de cache du plan
• Utilisation de la mémoire du système d’exploitation
• Taille de cache de la mémoire tampon
• Taux d’accès au cache de la mémoire tampon
• Taux de pagination de la mémoire
• Octets de journaux par seconde
• Vidages de journaux par seconde
• Taux d’accès au cache des procédures
• Compilations SQL

Pour ce qui est de l’utilisation de la mémoire par SQL Server, la surveillance nécessite l’examen du rapport de consommation de mémoire par SQL Server qui est disponible sur tous les systèmes sur lesquels SQL Server est installé. Ce rapport contient des informations classées par sections, dont les suivantes :

• Principaux composants qui consomment de la mémoire
• Distribution des pages de la mémoire tampon
• Changements affectant la mémoire au cours des sept derniers jours
• Utilisation de la mémoire par composant

En surveillant ces données, vous pouvez mieux déterminer la valeur maximale de mémoire à allouer à votre instance SQL Server et dans quelle mesure vous devez ajuster cette valeur maximale au fur et à mesure que vos besoins et votre utilisation de la mémoire changent.

Même s’il est possible de surveiller manuellement l’utilisation de la mémoire par SQL Server, il est parfois difficile d’avoir à localiser constamment les indicateurs pour pouvoir les comparer, car ils se trouvent sur des écrans et dans des outils différents. Par conséquent, nous recommandons l’utilisation d’un seul outil pour surveiller la mémoire allouée à votre instance SQL Server. Parmi ces outils, on compte SolarWinds^® Database Performance Analyzer qui est conçu pour collecter les données pertinentes, les corréler et les présenter sur un écran unique.

Il est important de surveiller l’utilisation de la mémoire par SQL Server, car elle affecte directement les performances de SQL Server. Elle a un impact considérable sur la rapidité d’exécution des requêtes.

En vous concentrant sur l’espérance de vie des pages dans le cadre de la surveillance générale de l’utilisation de la mémoire par SQL Server, vous pouvez déterminer si la mémoire tampon de SQL Server est insuffisante. Cela peut indiquer qu’il vous manque des index ou que vos instructions SQL sont mal rédigées.

Malheureusement, dans SQL Server, les paramètres par défaut d’utilisation de la mémoire sont habituellement loin d’être adaptés. Si vous ne les optimisez pas, vous penserez sans doute que la seule manière d’améliorer vos performances est d’investir dans de la mémoire supplémentaire. Cette initiative coûteuse ne résoudra toutefois pas le problème. Il ne s’agit que d’une solution de fortune qui ne sera efficace que jusqu’à ce que vous manquiez de nouveau de mémoire, auquel cas, vous devrez continuer à acheter de la mémoire.

En surveillant l’utilisation de la mémoire par SQL Server et en exploitant les informations qui en résultent pour intervenir, vous pouvez attaquer de front vos problèmes de performance. L’optimisation de l’utilisation de la mémoire permet également d’améliorer les performances de SQL Server sans dépenser en vain davantage d’argent pour acquérir de la mémoire supplémentaire.

Un outil de surveillance de l’utilisation de la mémoire par SQL Server vous donne les informations sur votre système dont vous avez besoin pour optimiser les performances de SQL Server et améliorer l’utilisation de la mémoire du serveur. Ces outils vous permettent non seulement d’afficher des informations sur les divers facteurs qui influencent l’utilisation de la mémoire, mais ils vous donnent également de la visibilité sur les indicateurs de performance nécessaires pour optimiser les requêtes incorrectes ou coûteuses.

Les outils de surveillance de l’utilisation de la mémoire par SQL Server aident les administrateurs de bases de données à identifier les requêtes SQL sur lesquelles ils doivent concentrer leurs efforts en extrayant et en assemblant des données toutes les secondes à partir de sessions actives. Ils classent également les instructions SQL en fonction de leur temps de réponse, pour vous permettre de mieux identifier les problèmes.

Grâce à un outil de surveillance, vous pouvez voir la corrélation directe entre le temps de réponse et les ressources du système, telles que le processeur, le stockage et la mémoire, le tout à partir d’une interface unique. En garantissant de la visibilité sur les indicateurs de performance liés notamment au temps d’exécution moyen, à la durée des requêtes, au temps processeur et aux lectures logiques/physiques, etc., les outils de surveillance vous permettent de rassembler des informations, d’établir des références et de conserver un historique des méthodes d’exécution les plus adaptées pour optimiser efficacement les requêtes SQL.

Pour ce qui est de la mémoire, le principal avantage que présente l’utilisation d’un outil de surveillance de SQL Server est qu’il collecte constamment des informations sur l’espérance de vie des pages, la taille de cache du plan, et bien plus.

L’espérance de vie des pages correspond au nombre de secondes pendant lesquelles une page reste dans le pool de mémoires tampons, sans références. Plus la page reste longtemps en mémoire, plus elle a de chances d’être réutilisée. Les baisses de l’espérance de vie des pages indiquent que les pages ne restent pas en mémoire pour être réutilisées. On remarque habituellement ce comportement lorsque SQL Server commence à manquer de mémoire tampon. En général, les instructions SQL mal rédigées ou les index manquants peuvent entraîner une surutilisation de la mémoire par SQL Server, car la requête lit trop de données pour renvoyer un ensemble de résultats très limité. Vous pouvez optimiser l’utilisation de la mémoire et limiter la pression à laquelle elle est soumise en procédant à une indexation correcte des requêtes et en les rédigeant comme il convient.

Le cache du plan tient compte de la taille du pool de mémoires tampons et indique la taille de la mémoire configurée. Chaque fois qu’une instruction SQL est exécutée, le moteur vérifie si le plan de l’instruction SQL existe déjà. Si c’est le cas, le moteur l’utilise au lieu de perdre du temps à compiler un nouveau plan. C’est ainsi que le cache du plan améliore l’efficacité du moteur de SQL Server. Lorsque vous stockez le plan de requête d’une instruction exécutée une seule fois, on le considère comme un plan à usage unique. Un nombre excessif de plans à usage unique peut épuiser la mémoire allouée à SQL Server et affaiblir les performances de SQL Server.

En surveillant systématiquement ces deux indicateurs et en fournissant régulièrement des données détaillées les concernant, un outil de surveillance de l’utilisation de la mémoire par SQL Server communique aux administrateurs de bases de données les informations dont ils ont besoin pour régler et optimiser l’allocation de mémoire à SQL Server, et les requêtes SQL en général, et ce, de manière efficace.

SolarWinds SQL Sentry propose des outils complets et une fonction de surveillance en temps réel pour aider les administrateurs de bases de données à optimiser l’utilisation de la mémoire par SQL Server. Il collecte des données sur divers facteurs liés à la mémoire, identifie les requêtes problématiques, vous permet de définir des références en matière de performance et rationalise l’analyse des performances. En outre, il étend la surveillance aux environnements VMware pour permettre une gestion proactive et offrir une vue globale de l’intégrité des bases de données au-delà des indicateurs liés à la mémoire. Les fonctionnalités de SQL Sentry aident à garantir des performances optimales et la stabilité des environnements SQL Server.

• Mémoire allouée à SQL Server : taille de cache de la mémoire tampon
• Mémoire allouée à SQL Server : taille de cache du plan (SQL)
• Mémoire allouée à SQL Server : taille de cache du plan (objets)
• Mémoire allouée à SQL Server : autre taille de cache
• Mémoire allouée à SQL Server : OLTP en mémoire
• Mémoire allouée à SQL Server : allocation pour les requêtes
• Mémoire allouée à SQL Server : columnstore
• Mémoire allouée à SQL Server : taux d’accès au cache du plan (SQL)
• Mémoire allouée à SQL Server : taux d’accès au cache du plan (objets)
• Mémoire allouée à SQL Server : mémoire volée à la mémoire tampon
• Mémoire allouée à SQL Server : lectures de pages
• Mémoire allouée à SQL Server : écritures sur les pages
• Mémoire allouée à SQL Server : espérance de vie des pages
• Mémoire allouée à SQL Server : allocations de mémoire en attente
• Mémoire système : mémoire par instance SQL Server/SSAS
• Mémoire système : mémoire d’autres processus
• Mémoire système : mémoire de cache de fichiers
• Mémoire système : mémoire gonflée**
• Mémoire système : mémoire disponible
• Mémoire système : erreurs de lecture
• Mémoire système : erreurs d’écriture
• Mémoire système : utilisation des fichiers de pagination

Vous trouverez la liste complète dans la documentation SolarWinds.