Partager via

Guide d’architecture de gestion de la mémoire

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Gestionnaire de mémoire virtuelle Windows

Les zones d'espace d'adressage validées sont mappées à la mémoire physique disponible par le Gestionnaire de mémoire virtuelle Windows (VMM).

Pour plus d’informations sur la quantité de mémoire physique prise en charge par les divers systèmes d’exploitation, consultez la documentation Windows sur les limites de la mémoire selon les versions de Windows.

Les systèmes de mémoire virtuelle autorisent le surengagement de la mémoire physique, de sorte que le rapport entre la mémoire virtuelle et la mémoire physique peut être supérieur à 1:1. Il est alors possible d'exécuter des programmes plus volumineux sur des ordinateurs offrant diverses configurations de la mémoire physique. Toutefois, dans la plupart des cas, l'utilisation d'une quantité de mémoire virtuelle nettement plus importante que les plages de travail moyennes combinées de tous les processus peut entraîner une détérioration des performances.

Architecture de la mémoire de SQL Server

SQL Server acquiert et libère la mémoire dynamiquement selon ses besoins. En règle générale, un administrateur n’a pas à spécifier la quantité de mémoire à allouer à SQL Server, même si cette option existe toujours et est obligatoire dans certains environnements.

L'un des objectifs principaux de tous les logiciels de base de données est de réduire les E/S disque, car les opérations de lecture et écriture sur le disque font partie des opérations les plus consommatrices de ressources. SQL Server crée un pool de mémoires tampons en mémoire afin d'y garder les pages lues à partir de la base de données. Une grande partie du code SQL Server vise à réduire au minimum le nombre d’opérations de lecture et d'écriture physiques entre le disque et le pool de mémoires tampons. SQL Server tente d’atteindre un équilibre entre deux objectifs :

  • Empêcher le pool de mémoires tampons d'atteindre une taille susceptible de priver le système de mémoire.
  • Réduire les E/S physiques vers les fichiers de la base de données en augmentant la taille du pool de mémoires tampons.

Dans un système surchargé, certaines requêtes volumineuses dont l'exécution nécessite une importante quantité de mémoire ne peuvent pas obtenir la quantité minimale de mémoire requise et reçoivent une erreur de temporisation pendant qu'elles attendent des ressources mémoire. Pour résoudre ce problème, augmentez la valeur de l'option query wait. Pour une requête parallèle, envisagez de réduire l'option max degree of parallelism (Degré maximum de parallélisme).

Dans un système où une charge très lourde pèse sur les ressources mémoire, les requêtes comportant une jointure de fusion, tri et bitmap dans le plan de requête peuvent éliminer le bitmap si elles n'obtiennent pas la mémoire minimale nécessaire pour ce bitmap. Ceci peut affecter les performances de la requête, et, si le processus de tri ne tient pas en mémoire, ceci peut accroître l'utilisation des tables de travail dans la base de données tempdb, ce qui augmente le volume de tempdb. Pour résoudre ce problème, ajoutez de la mémoire physique ou paramétrez les requêtes de façon qu'elles utilisent un autre plan de requête plus rapide.

Mémoire conventionnelle (virtuelle)

Toutes les éditions de SQL Server prennent en charge la mémoire conventionnelle sur la plateforme 64 bits. Le processus SQL Server peut accéder à l'espace d'adressage virtuel jusqu'au maximum du système d'exploitation sur l'architecture x64 (SQL Server Standard Edition prend en charge jusqu'à 128 Go). Avec l’architecture IA64, la limite était de 7 To (IA64 non prise en charge dans SQL Server 2012 (11.x) et versions ultérieures). Pour plus d’informations, consultez Limites de mémoire pour Windows.

Adressez la mémoire des extensions Windows (AWE)

En utilisant les privilège AWE (Address Windowing Extensions ) et pages de verrouillage en mémoire (LPIM) requises par AWE, vous pouvez conserver la plupart de la mémoire de processus SQL Server verrouillée dans la RAM physique dans des conditions de mémoire virtuelle faible. Cela se produit dans les allocations AWE 32 bits et 64 bits. Le verrouillage de la mémoire se produit, car la mémoire AWE ne passe pas par le Gestionnaire de mémoire virtuelle dans Windows, qui contrôle la pagination de la mémoire. L’API d’allocation de mémoire AWE nécessite le privilège Verrouiller des pages en mémoire (SeLockMemoryPrivilege) ; consultez les notes AllocateUserPhysicalPages. Par conséquent, l’avantage principal de l’utilisation de l’API AWE consiste à conserver la plupart de la mémoire résidente dans la RAM en cas de pression de mémoire sur le système. Pour plus d’informations sur la façon d’autoriser SQL Server à utiliser AWE, consultez Activer Verrouiller des pages en mémoire (Windows).

Si le privilège Verrouiller des pages en mémoire (LPIM) est accordé nous vous recommandons vivement de définir la mémoire maximale du serveur (Mo) à une valeur spécifique, plutôt que de laisser la valeur par défaut de 2 147 483 647 mégaoctets (Mo). Pour plus d’informations, consultez Mémoire du serveur Configuration du serveur : définir les options manuellement et Verrouiller les pages en mémoire (LPIM).

Si LPIM n’est pas activé, SQL Server passe à l’utilisation de la mémoire conventionnelle et, en cas d’épuisement de la mémoire du système d’exploitation, l’erreur [MSSQLSERVER_17890] (errors-events/mssqlserver-17890-database-engine-error.md) peut être signalée dans le journal des erreurs. L'erreur ressemble à l'exemple suivant :

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

Modifications apportées à la gestion de la mémoire à partir de SQL Server 2012

Dans les anciennes versions de SQL Server, l'allocation de mémoire était effectuée à l'aide de cinq mécanismes différents :

  • Allocateur de page unique (SPA), comprenant uniquement les allocations de mémoire inférieures ou égales à 8 Ko dans le processus de SQL server. Les options de configuration Mémoire maximum du serveur (Mo) et Mémoire minimum du serveur (Mo) déterminaient les limites de la mémoire physique consommée par SPA. Le pool de tampons était aussi le mécanisme pour SPA et le plus grand consommateur d’allocations de pages uniques.
  • Allocateur de plusieurs pages (MPA), pour les allocations de mémoire demandant plus de 8 Ko.
  • Allocateur du CLR, comprenant les segments de mémoire du CLR SQL et ses allocations globales créées durant l’initialisation du CLR.
  • Allocations de mémoire pour les thread stacks dans le processus SQL Server.
  • Allocations Windows directes (DWA), pour les demandes d’allocation de mémoire apportées directement à Windows. Il s’agit notamment de l’utilisation des segments de mémoire Windows et des allocations virtuelles directes effectuées par les modules chargés dans le processus SQL Server. Les allocations à partir des DLL de procédures stockées étendues, les objets créés au moyen de procédures Automation (appels sp_OA) et les allocations à partir de fournisseurs de serveur lié sont des exemples de demandes d’allocation de mémoire.

À compter de SQL Server 2012 (11.x), les allocations de page unique, les allocations de plusieurs pages et les allocations du CLR sont toutes consolidées dans un Allocateur de pages de « toute taille » et sont incluent dans les limites de mémoire contrôlées par les options de configuration mémoire maximum du serveur (Mo) et mémoire minimum du serveur (Mo). Ce changement offre des capacités de redimensionnement plus précises pour tous les besoins en mémoire transitant par le Gestionnaire de mémoire de SQL Server.

Important

Après la mise à niveau vers SQL Server 2012 (11.x) et les versions ultérieures, vérifiez soigneusement les configurations actuelles de la mémoire maximale (Mo) et de la mémoire minimale (Mo) du serveur. En effet, à compter de SQL Server 2012 (11.x), ces configurations comprennent et représentent plus d’allocations de mémoire que celles des versions antérieures. Ces modifications s’appliquent aux versions 32 bits et 64 bits de SQL Server 2012 (11.x) et SQL Server 2014 (12.x) et 64 bits de SQL Server 2016 (13.x) et versions ultérieures.

Le tableau suivant indique si un type spécifique d’allocation de mémoire est contrôlé par les options de configuration Mémoire maximum du serveur (Mo) et Mémoire minimum du serveur (Mo) :

Type d’allocation de mémoire SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x) À compter de SQL Server 2012 (11.x)
Allocation de page unique Oui Oui, regroupées dans des allocations de pages de « toute taille »
Allocation de plusieurs pages Non Oui, regroupées dans des allocations de pages de « toute taille »
Allocation du CLR Non Oui
Mémoire de piles de threads Non Non
Allocations directes de Windows Non Non

SQL Server peut allouer plus de mémoire que la valeur du paramètre de mémoire maximum du serveur

À partir de SQL Server 2012 (11.x), SQL Server peut allouer plus de mémoire que la valeur spécifiée dans le paramètre mémoire maximale du serveur (Mo). Ce comportement peut se produire quand la valeur de Mémoire totale du serveur (Ko) a déjà atteint le paramètre Mémoire du serveur cible (Ko), comme spécifié par la Mémoire maximale du serveur (Mo). Si la mémoire libre contiguë est insuffisante pour répondre aux demandes de mémoire de plusieurs pages (plus de 8 Ko) en raison de la fragmentation de la mémoire, SQL Server peut procéder à une surallocation au lieu de rejeter la demande de mémoire.

Dès que cette allocation est effectuée, la tâche en arrière-plan du moniteur de ressource signale à tous les consommateurs de mémoire de libérer la mémoire allouée et tente de faire passer la valeur Mémoire totale du serveur (Ko) en dessous de la spécification Mémoire du serveur cible (Ko). L’utilisation de la mémoire de SQL Server peut donc dépasser brièvement le paramètre mémoire maximale du serveur (Mo). Dans ce cas, la valeur du compteur de performances Mémoire totale du serveur (Ko) dépasse les paramètres mémoire maximale du serveur (Mo) et Mémoire du serveur cible (Ko).

Ce comportement est généralement observé durant les opérations suivantes :

  • Requêtes d’index Columnstore volumineuses
  • Grandes requêtes Mode Batch sur Rowstore.
  • Constructions ou reconstructions d’index ColumnStore qui utilisent des volumes importants de mémoire pour effectuer les opérations de hachage et de tri
  • Opérations de sauvegarde qui nécessitent des mémoires tampons volumineuses
  • Traçage d’opérations qui doivent stocker des paramètres d’entrée volumineux
  • Demandes d’allocation de mémoire volumineuses

Si vous observez fréquemment ce comportement, envisagez d’utiliser l’indicateur de trace 8121 dans SQL Server 2019 (15.x) pour permettre au moniteur de ressources de nettoyer plus rapidement. À compter de SQL Server 2022 (16.x), cette fonctionnalité est activée par défaut et l’indicateur de trace n’a aucun effet.

Modifications apportées à memory_to_reserve à partir de SQL Server 2012

Dans les versions antérieures de SQL Server, le Gestionnaire de mémoire de SQL Server réservait une partie de l’espace d’adressage virtuel (VAS) des processus à l’allocateur multipage (MPA), à l’allocateur CLR, aux allocations de mémoire pour les piles de threads dans le processus SQL Server et aux allocations Windows directes (DWA). Cette partie de l’espace d’adressage virtuel est également appelée « Mem-To-Leave » ou « pool non-tampon ».

L’espace d’adressage virtuel réservé pour ces allocations est déterminé par l’option de configuration memory_to_reserve. La valeur par défaut utilisée par SQL Server est de 256 Mo.

Étant donné que l'allocateur de pages « toute taille » gère également les allocations supérieures à 8 Ko, la valeur de memory_to_reserve n'inclut pas les allocations multi-pages. À l’exception de ce changement, cette option de configuration est la même.

Le tableau suivant indique si un type spécifique d’allocation de mémoire appartient à la région memory_to_reserve de l’espace d’adressage virtuel pour le processus SQL Server :

Type d’allocation de mémoire SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x) À compter de SQL Server 2012 (11.x)
Allocation de page unique Non Non, regroupées dans des allocations de pages de « toute taille »
Allocation de plusieurs pages Oui Non, regroupées dans des allocations de pages de « toute taille »
Allocation du CLR Oui Oui
Mémoire de piles de threads Oui Oui
Allocations directes de Windows Oui Oui

Gestion dynamique de la mémoire

Le comportement par défaut de la gestion de la mémoire du moteur de base de données Microsoft SQL Server consiste à acquérir autant de mémoire que nécessaire sans causer d'insuffisance de mémoire sur le système. Pour ce faire, le moteur de base de données SQL Server utilise les API de notification de mémoire de Microsoft Windows.

Lorsque SQL Server utilise dynamiquement la mémoire, il interroge régulièrement le système afin de déterminer la mémoire physique disponible. La conservation de cette mémoire libre empêche le système d'exploitation de paginer. S'il y a moins de mémoire, SQL Server en libère pour le système d'exploitation. S’il y a plus de mémoire disponible, SQL Server peut allouer davantage de mémoire. SQL Server n’ajoute de la mémoire que lorsque sa charge de travail en requiert davantage. Un serveur au repos n’augmente pas la taille de son espace d’adressage virtuel. Si vous remarquez que le Gestionnaire des tâches et Analyseur de performances affichent une diminution constante de la mémoire disponible lorsque SQL Server utilise la gestion dynamique de la mémoire, il s’agit du comportement par défaut et ne doit pas être perçu comme une fuite de mémoire.

Options de configuration de la mémoire du serveur contrôle l’allocation de mémoire de SQL Server, la mémoire de compilation, tous les caches (notamment le pool de mémoires tampons), les allocations de mémoire d’exécution des requêtes, la mémoire du gestionnaire de verrous et la mémoire du CLR1 (essentiellement les régisseurs de mémoire se trouvant dans sys.dm_os_memory_clerks).

1 La mémoire du CLR est gérée dans le cadre des allocations max_server_memory à partir de SQL Server 2012 (11.x).

La requête suivante retourne des informations sur la mémoire allouée actuellement :

SELECT physical_memory_in_use_kb / 1024 AS sql_physical_memory_in_use_MB,
       large_page_allocations_kb / 1024 AS sql_large_page_allocations_MB,
       locked_page_allocations_kb / 1024 AS sql_locked_page_allocations_MB,
       virtual_address_space_reserved_kb / 1024 AS sql_VAS_reserved_MB,
       virtual_address_space_committed_kb / 1024 AS sql_VAS_committed_MB,
       virtual_address_space_available_kb / 1024 AS sql_VAS_available_MB,
       page_fault_count AS sql_page_fault_count,
       memory_utilization_percentage AS sql_memory_utilization_percentage,
       process_physical_memory_low AS sql_process_physical_memory_low,
       process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

Tailles de la pile

La mémoire pour les piles de threads 1, CLR 2, les fichiers .dll de procédure étendue, les fournisseurs OLE DB référencés par les requêtes distribuées, les objets d'automatisation référencés dans les instructions Transact-SQL, et toute mémoire allouée par une DLL non SQL Server, ne sont pas contrôlés par la mémoire maximale du serveur (Mo).

1 Reportez-vous à l'article sur la configuration des threads de travail maximum (option de configuration du serveur), pour obtenir des informations sur les threads de travail par défaut calculés pour un nombre donné de CPU affinitaires dans l'hôte actuel. Les tailles de pile SQL Server sont les suivantes :

Architecture de SQL Server Architecture du système d’exploitation Taille de la pile
x86 (32 bits) x86 (32 bits) 512 Ko
x86 (32 bits) x64 (64 bits) 768 Ko
x64 (64 bits) x64 (64 bits) 2 048 Ko
IA64 (Itanium) IA64 (Itanium) 4 096 Ko

2 La mémoire du CLR est gérée dans le cadre des allocations max_server_memory à partir de SQL Server 2012 (11.x).

SQL Server utilise l'API de notification de mémoire QueryMemoryResourceNotification pour déterminer le moment où le Gestionnaire de mémoire de SQL Server peut allouer et libérer de la mémoire.

Lorsque SQL Server démarre, il calcule la taille de l'espace d'adressage virtuel pour le pool de mémoires tampons d'après plusieurs paramètres, dont la quantité de mémoire physique du système, le nombre de threads serveur et diverses options de démarrage. SQL Server réserve la quantité ainsi calculée de son espace d'adressage virtuel de processus pour le pool de mémoires tampons, mais il acquiert (valide) uniquement la quantité nécessaire de mémoire physique pour la charge actuelle.

L'instance continue alors à acquérir de la mémoire comme l'exige la prise en charge de la charge de travail. Au fur et à mesure que des utilisateurs se connectent et exécutent des requêtes, SQL Server acquiert la mémoire physique supplémentaire à la demande. Une instance SQL Server continue d'acquérir de la mémoire physique jusqu'à ce qu'elle atteigne sa cible d'allocation maximale de mémoire serveur (Mo) ou que le système d'exploitation indique qu'il n'y a plus d'excédent de mémoire libre ; elle libère de la mémoire lorsqu'elle est supérieure à la valeur minimale de la mémoire serveur et que le système d'exploitation indique qu'il y a un déficit de mémoire libre.

Étant donné que d'autres applications sont démarrées sur l'ordinateur exécutant une instance de SQL Server, elles consomment de la mémoire et la quantité de mémoire physique disponible descend en dessous de la cible de SQL Server. L'instance de SQL Server ajuste sa consommation de mémoire. Si une autre application est arrêtée, la mémoire disponible est augmentée, et l’instance de SQL Server augmente la taille de son allocation de mémoire. SQL Server peut libérer et acquérir plusieurs mégaoctets de mémoire chaque seconde, ce qui lui permet de s’adapter rapidement aux changements d’allocation de mémoire.

Effets des options de configuration « min server memory » et « max server memory »

Les options de configuration mémoire serveur min et mémoire serveur max permettent d’établir les limites supérieure et inférieure de la quantité de mémoire utilisée par le pool de mémoires tampons et d’autres caches du moteur de base de données. Le pool de mémoires tampons n'obtient pas immédiatement la quantité de mémoire spécifiée dans mémoire serveur min. En effet, il commence seulement avec la mémoire nécessaire à l'initialisation. Au fur et à mesure que la charge de travail du moteur de base de données SQL Server augmente, il acquiert la mémoire nécessaire à la prise en charge de la charge de travail. Le pool de mémoires tampons ne libère aucune partie de la mémoire acquise avant d'atteindre la valeur spécifiée dans mémoire serveur min. Dès lors que la quantité spécifiée dans min server memory est atteinte, le pool de mémoires tampons utilise l'algorithme standard pour acquérir et libérer la mémoire en fonction des besoins. La seule différence réside dans le fait que le pool de mémoires tampons ne diminue jamais son allocation de mémoire en dessous de la valeur spécifiée dans mémoire serveur min et n'obtient jamais plus de mémoire que le niveau spécifié dans mémoire serveur max (Mo).

Remarque

En tant que processus, SQL Server acquiert plus de mémoire qu'indiqué par l'option mémoire serveur max (Mo). Les composants internes et externes peuvent allouer de la mémoire en dehors du pool de mémoires tampons, qui consomme un supplément de mémoire, mais la mémoire allouée au pool de mémoires tampons représente généralement la plus grande part de la mémoire consommée par SQL Server.

La quantité de mémoire acquise par le moteur de base de données SQL Server dépend entièrement de la charge de travail placée dans l'instance. Une instance de SQL Server qui ne traite pas beaucoup de requêtes risque de ne jamais atteindre la valeur spécifiée par min server memory.

Si la valeur spécifiée pour mémoire serveur min et pour mémoire serveur max (Mo). est identique, le moteur de base de données cesse de libérer et d'acquérir de la mémoire de façon dynamique pour le pool de mémoires tampons une fois que la mémoire allouée au moteur de base de données SQL Server a atteint cette valeur.

Si une instance SQL Server fonctionne sur un ordinateur sur lequel d'autres applications sont régulièrement arrêtées ou démarrées, l'allocation et la désallocation de mémoire par l'instance SQL Server peut ralentir le démarrage de ces applications. De même, si SQL Server est une application serveur parmi d'autres exécutées sur un seul ordinateur, l'administrateur système doit éventuellement contrôler la quantité de mémoire allouée à SQL Server. Pour ce faire, il peut utiliser les options mémoire serveur min et mémoire serveur max (Mo). pour contrôler la quantité de mémoire utilisable par SQL Server. Les options min server memory et max server memory sont spécifiées en mégaoctets. Pour plus d’informations, notamment des suggestions sur la définition de ces configurations de mémoire, consultez Options de configuration de la mémoire du serveur.

Mémoire utilisée par les spécifications d’objets SQL Server

La liste suivante décrit la quantité estimée de mémoire utilisée par différents objets dans SQL Server. Les quantités indiquées sont des estimations. Elles peuvent varier en fonction de l’environnement et de la manière dont les objets sont créés :

  • Verrou (tel qu’il est géré par le Gestionnaire de verrous) : 64 octets + 32 octets par propriétaire
  • Connexion utilisateur : environ (3 * taille_paquet_réseau + 94 Ko)

La taille des paquets réseau représente la taille des paquets TDS (Tabular Data Scheme) utilisés pour la communication entre des applications et le moteur de base de données. La taille par défaut s'élève à 4 Ko ; elle est contrôlée par l'option de configuration Taille du paquet réseau.

Lorsque la fonctionnalité MARS (Multiple Active Result Sets) est activée, la connexion utilisateur est environ (3 + 3 * nombre_connexions_logiques) * taille_paquet_réseau + 94 Ko.

Effets de min memory per query

L’option de configuration min memory per query spécifie la quantité minimale de mémoire (en kilo-octets) allouée pour l’exécution d’une requête. Cela est également appelé « allocation de mémoire minimale ». Avant de commencer à s’exécuter, toutes les requêtes doivent attendre jusqu’à ce que la mémoire minimale demandée soit sécurisée, ou jusqu’à ce que la valeur spécifiée dans l’option de configuration du serveur Attente de la requête soit dépassée. Le type d’attente qui est accumulé dans ce scénario est RESOURCE_SEMAPHORE.

Important

Ne définissez pas l'option de configuration Mémoire minimale par serveur de requêtes à un niveau trop élevé, en particulier sur les systèmes très occupés, car cela pourrait entraîner :

  • Une concurrence accrue pour les ressources mémoire.
  • Une concurrence réduite en augmentant la quantité de mémoire pour chaque requête individuelle, même si la mémoire nécessaire à l’exécution est inférieure à cette configuration.

Pour obtenir des recommandations sur l’utilisation de cette configuration, consultez Configurer l’option de configuration de serveur min memory per query.

Considérations sur l’allocation de mémoire

Pour l’exécution en mode ligne, l’allocation de mémoire initiale ne peut être dépassée en aucune circonstance. Si plus de mémoire que l’allocation initiale est nécessaire pour exécuter des opérations de hachage ou de tri, les opérations sont transférées sur le disque. Une opération de hachage qui connaît un dépassement est prise en charge par un fichier de travail dans tempdb, tandis qu’une opération de tri avec dépassement est prise en charge par une table de travail.

Un dépassement qui se produit pendant une opération de tri est appelé classe d'événement d’avertissement de tri. Les avertissements de tri indiquent que les opérations de tri ne peuvent pas être effectuées en mémoire. Ceci n’inclut pas les opérations de tri impliquant la création d’index, mais seulement les opérations de tri effectuées dans une requête (comme une clause ORDER BY utilisée dans une instruction SELECT).

Un dépassement qui se produit pendant une opération de hachage est appelé classe d'événement d’avertissement de hachage. Ceci se produit quand une récurrence de hachage ou une cessation de hachage (interruption de hachage) s’est produite pendant une opération de hachage.

  • Une récurrence de hachage se produit lorsque l'entrée de construction n'est pas adaptée à la mémoire disponible, ce qui entraîne une fragmentation de l'entrée en plusieurs parties traitées séparément. Si une de ces partitions n’est toujours pas adaptée à la mémoire disponible, elle est à nouveau fragmentée en sous-partitions, qui sont également traitées séparément. Ce processus de fragmentation se poursuit jusqu’à ce que toutes les partitions soient adaptées à la mémoire disponible ou jusqu’à ce que le niveau maximal de récursivité soit atteint.
  • L'interruption de hachage a lieu lorsqu'une opération de hachage atteint son niveau maximal de récursivité et se décale à un plan auxiliaire pour traiter les données partitionnées restantes. Ces événements peuvent entraîner une baisse des performances de votre serveur.

Pour l’exécution en mode batch, l’allocation de mémoire initiale peut augmenter dynamiquement jusqu’à un certain seuil interne par défaut. Ce mécanisme d’allocation de mémoire dynamique est conçu pour permettre l’exécution en mémoire des opérations de hachage ou de tri qui s’exécutent en mode batch. Si ces opérations ne tiennent toujours pas dans la mémoire, elles sont transférées sur le disque.

Pour plus d’informations sur les modes d’exécution, consultez le Guide d’architecture de traitement des requêtes.

Gestion des tampons

L'objectif principal d'une base de données SQL Server est de stocker et de récupérer les données, l'utilisation intensive d'E/S sur disque est donc une caractéristique centrale du moteur de base de données. Étant donné que les opérations d'E/S disque peuvent consommer beaucoup de ressources et durent relativement longtemps, SQL Server s'attache à rendre ces opérations efficaces. La gestion des tampons joue un rôle essentiel pour parvenir à cette efficacité. Le composant de gestion des tampons comprend deux mécanismes : le gestionnaire de tampons qui permet d’accéder et de mettre à jour les pages de la base de données, et le cache des tampons (également appelé pool de tampons), qui permet de réduire les opérations d’E/S du fichier de la base de données.

Pour obtenir une explication détaillée des E/S de disque dans SQL Server, consultez Notions de base des E/S de SQL Server.

Fonctionnement de la gestion des tampons

Un tampon est une page de 8 Ko en mémoire dont la taille est similaire à une page d'index ou de données. Ainsi, le cache des tampons est divisé en pages de 8 Ko. Le gestionnaire des tampons gère les fonctions de lecture des pages d'index ou de données à partir des fichiers de disque de base de données dans le cache de tampons ainsi que la réécriture sur le disque des pages modifiées. Une page reste dans le cache des tampons jusqu'à ce que le gestionnaire de tampons ait besoin de la zone de mémoire tampon pour lire davantage de données. Les données ne sont réécrites sur le disque que si elles sont modifiées. Les données dans le cache de tampons peuvent être modifiées plusieurs fois avant leur réécriture sur le disque. Pour plus d’informations, consultez Lecture de pages et Écriture de pages.

Lorsque SQL Server démarre, il calcule la taille de l'espace d'adressage virtuel pour le cache des tampons d'après plusieurs paramètres, dont la quantité de mémoire physique du système, le nombre de threads serveur maximum configuré et diverses options de démarrage. SQL Server réserve la quantité ainsi calculée de son espace d’adressage virtuel de processus (appelée cible mémoire) pour le cache des tampons, mais il acquiert (valide) uniquement la quantité de mémoire physique nécessaire pour la charge actuelle. Vous pouvez interroger les colonnes committed_target_kb et committed_kb dans la vue du catalogue sys.dm_os_sys_info pour retourner le nombre de pages réservées comme cible mémoire et le nombre de pages actuellement réservées dans le cache de la mémoire tampon, respectivement.

L'intervalle entre le démarrage de SQL Server et le moment où le cache des tampons obtient sa cible de mémoire s'appelle l’accélération. Au cours de cette opération, les tampons se remplissent de demandes de lecture selon les besoins. Par exemple, une demande de lecture d’une page de 8 Ko unique remplit une page de tampon unique. Cela signifie que l'accélération dépend du nombre et du type des demandes clientes. L’accélération s’effectue par la transformation des demandes de lecture de page unique en demandes de huit pages alignées (ce qui constitue une étendue). Cette opération permet au processus d'accélération de s'achever plus rapidement en particulier sur les ordinateurs possédant beaucoup de mémoire. Pour plus d’informations sur les pages et les étendues, consultez Guide d’architecture des pages et des étendues.

Comme le gestionnaire de tampons consomme l'essentiel de la mémoire dans les processus SQL Server, il collabore avec le gestionnaire de mémoire afin de permettre aux autres composants d'utiliser ses tampons. Le gestionnaire de tampons interagit essentiellement avec les composants suivants :

  • le gestionnaire de ressources pour contrôler l'utilisation de l'ensemble de la mémoire et, sur les plateformes 32 bits, pour contrôler l'utilisation de l'espace d'adressage.
  • le gestionnaire de base de données et le système d'exploitation SQL Server (SQLOS) pour les opérations d'E/S de fichier peu importantes ;
  • le gestionnaire du journal pour la journalisation WAL (write-ahead log).

Fonctionnalités prises en charge

Le gestionnaire de tampons prend en charge les fonctionnalités suivantes.

  • Le gestionnaire de tampons est compatible avec la technologie NUMA (Non-Uniform Memory Access). Les pages de cache des tampons sont réparties sur les nœuds NUMA matériels, ce qui permet à un thread d'accéder à une page de tampons allouée sur le nœud NUMA local au lieu de la mémoire étrangère.

  • Le gestionnaire de tampons prend en charge l’ajout de mémoire à chaud, ce qui permet aux utilisateurs d’ajouter de la mémoire physique sans redémarrer le serveur.

  • Le gestionnaire de tampons prend en charge les grandes pages sur les plateformes 64 bits. La taille de la page est spécifique à la version de Windows.

    Remarque

    Avant SQL Server 2012 (11.x), l’activation de grandes pages dans SQL Server nécessite l’indicateur de trace 834.

  • Le gestionnaire de tampons fournit les diagnostics supplémentaires exposés par le biais des vues de gestion dynamique. Vous pouvez utiliser ces affichages pour contrôler différentes ressources du système d'exploitation spécifiques à SQL Server. Par exemple, vous pouvez utiliser la vue sys.dm_os_buffer_descriptors pour surveiller les pages dans le cache des tampons.

Détection de la forte sollicitation de la mémoire

La forte sollicitation de la mémoire est une situation résultant d’un manque de mémoire et peut entraîner les effets suivants :

  • E/S supplémentaires (par exemple un thread d’arrière-plan d’écriture différée très actif)
  • Taux de recompilation plus élevé
  • Exécution plus longue des requêtes (dans le cas où des allocations de mémoire sont en attente)
  • Cycles d’UC supplémentaires

Cette situation peut être déclenchée pour des raisons externes ou internes. Les raisons externes peuvent être les suivantes :

  • La mémoire physique (RAM) disponible est faible. Ceci fait que le système raccourcit les plages de travail des processus actuellement en cours d’exécution, ce qui peut aboutir à un ralentissement global. SQL Server peut réduire la cible de validation du pool de mémoires tampons et commencer à réduire les caches internes plus souvent.
  • La mémoire système globale disponible (qui inclut le fichier d’échange système) est faible. Ceci peut entraîner l’échec des allocations de mémoire par le système, car il ne peut pas paginer la mémoire actuellement allouée.

Les raisons internes peuvent être les suivantes :

  • En réponse à une forte sollicitation de la mémoire externe, quand le moteur de base de données SQL Server définit des limites plus basses d’utilisation de la mémoire.
  • Les paramètres mémoire ont été abaissés manuellement via une réduction de la configuration de max server memory.
  • Des modifications de la distribution en mémoire de composants internes entre les différents caches.

Le moteur de base de données SQL Server implémente une infrastructure dédiée à la détection et à la gestion de la forte sollicitation de la mémoire dans le cadre de sa gestion de la mémoire dynamique. Cette infrastructure inclut la tâche d’arrière-plan appelée Moniteur de ressource. La tâche Moniteur de ressource surveille l’état des indicateurs de la mémoire interne et externe. Une fois qu’un de ces indicateurs change d’état, il calcule la notification correspondante et la diffuse. Ces notifications sont des messages internes provenant de chacun des composants du moteur et elles sont stockées dans des mémoires tampons en anneau.

Deux mémoires tampons en anneau contiennent des informations relatives à la gestion de la mémoire dynamique :

  • La mémoire tampon en anneau Moniteur de ressource, qui assure le suivi de l’activité du Moniteur de ressource, par exemple si une forte sollicitation de la mémoire a été ou non signalée. Cette mémoire tampon en anneau a des informations d’état en fonction de la condition actuelle de RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADYou RESOURCE_MEMVIRTUAL_LOW.
  • La mémoire tampon Gestionnaire d’allocation mémoire, qui contient des enregistrements des notifications de mémoire pour chaque pool de ressources de Resource Governor. Comme une forte sollicitation de la mémoire interne est détectée, une notification de mémoire insuffisante est activée pour les composants qui allouent de la mémoire, de façon à déclencher des actions destinées à équilibrer la mémoire entre les caches.

Les gestionnaires d’allocation mémoire surveillent la consommation de la demande de mémoire par chaque composant et, en fonction des informations collectées, ils calculent une valeur optimale de mémoire pour chacun de ces composants. Il existe un ensemble de gestionnaires pour chaque pool de ressources du Resource Governor. Ces informations sont diffusées auprès de chacun des composants, qui augmente ou diminue son utilisation en fonction de ce qui lui est indiqué.

Pour plus d’informations sur les gestionnaires d’allocation mémoire, consultez sys.dm_os_memory_brokers.

Détection d'erreurs

Les pages de base de données peuvent utiliser l'un des deux mécanismes optionnels qui permettent de garantir l'intégrité de la page, depuis son écriture sur le disque jusqu'à sa relecture : la protection par page endommagée et la protection par somme de contrôle. Ces mécanismes offrent une méthode indépendante de vérification de l'exactitude du stockage des données ainsi que des composants matériels tels que les contrôleurs, les pilotes, les câbles et même le système d'exploitation. La protection est ajoutée à la page juste avant l'écriture sur le disque, puis elle est vérifiée après sa lecture sur le disque.

SQL Server procède à quatre nouvelles tentatives pour une lecture qui échoue avec une erreur de somme de contrôle, de page endommagée ou d'E/S disque. Si la lecture réussit lors d'une de ces tentatives, un message est écrit dans le journal des erreurs et l'exécution de la commande qui a déclenché la lecture se poursuit. Si les tentatives de nouvelle tentative échouent, la commande échoue avec l’erreur MSSQLSERVER_824.

Le type de protection de page utilisé est un attribut de la base de données qui contient la page. La protection de la somme de contrôle est la protection par défaut pour les bases de données créées dans SQL Server 2005 (9.x) et les versions ultérieures. Le mécanisme de protection de page est spécifié au moment de la création de la base de données et peut être modifié à l'aide de ALTER DATABASE SET. Vous pouvez déterminer le paramètre actuel de protection des pages en interrogeant la colonne page_verify_option dans la vue du catalogue sys.databases ou la propriété IsTornPageDetectionEnabled de la fonction DATABASEPROPERTYEX.

Remarque

En cas de modification du paramètre de protection de page, le nouveau paramètre ne prend pas immédiatement effet dans l'ensemble de la base de données. Par contre, les pages adoptent le niveau de protection en cours de la base de données lors de leur écriture ultérieure. Cela signifie que la base de données peut contenir des pages utilisant différents types de protection.

Protection de page endommagée

La Protection de page endommagée, introduite dans SQL Server 2000 (8.x), est essentiellement une méthode de détection des pages endommagées causées par les pannes d'alimentation. Par exemple, une panne d'alimentation inattendue peut n'entraîner que l'écriture partielle d'une page sur le disque. Quand la protection contre les pages endommagées est utilisée, un modèle de signature 2 bits spécifique pour chaque secteur de 512 octets d’une page de base de données de 8 kilo-octets (Ko) est stocké dans l’en-tête de la page quand celle-ci est écrite sur le disque.

Lorsque la page est ensuite lue à partir du disque, les bits endommagés stockés dans l'en-tête de la page sont comparés aux informations réelles du secteur concerné. Le modèle de signature alterne entre des binaires 01 et 10 à chaque opération d’écriture. Il est donc toujours possible de savoir si seule une partie des secteurs a été écrite sur le disque : si un bit est dans le mauvais état lors de la lecture ultérieure de la page, celle-ci a été écrite de manière incorrecte et une page endommagée est détectée. Ce type de détection fait appel à des ressources minimales ; cependant, elle ne détecte pas toutes les erreurs causées par les pannes de matériel des disques. Pour plus d’informations sur la définition de la détection des pages endommagées, consultez Options ALTER DATABASE SET (Transact-SQL).

Protection de la somme de contrôle

La protection de la somme de contrôle, introduite dans SQL Server 2005 (9.x), fournit une vérification renforcée de l'intégrité des données. Une somme de contrôle est calculée pour les données de chaque page écrite, elle est stockée dans l'en-tête de page. À chaque lecture d'une page contenant une somme de contrôle stockée sur le disque, le moteur de la base de données recalcule la somme de contrôle pour les données de la page et renvoie l'erreur 824 si la nouvelle somme de contrôle n'est pas identique à la somme de contrôle stockée. La protection de la somme de contrôle peut détecter un plus grand nombre d'erreurs que la protection de page endommagée car celle-ci est affectée par chaque octet de la page, elle utilise toutefois peu de ressources.

Lorsque la somme de contrôle est activée, les erreurs causées par les pannes d'alimentation et du matériel ou des microprogrammes défectueux sont détectables à chaque lecture d'une page sur le disque par le gestionnaire de tampons. Pour plus d’informations sur la définition de la somme de contrôle, consultez Options ALTER DATABASE SET (Transact-SQL).

Important

Lorsqu’une base de données utilisateur ou système est mise à niveau vers SQL Server 2005 (9.x) ou une version ultérieure, la valeur PAGE_VERIFY (NONE ou TORN_PAGE_DETECTION) est conservée. Nous vous recommandons fortement d'utiliser CHECKSUM. TORN_PAGE_DETECTION peut utiliser moins de ressources, mais fournit un sous-ensemble minimal de la protectionCHECKSUM.

Présentation de l'accès NUMA (Non-uniform Memory Access)

Microsoft SQL Server est compatible avec la technologie NUMA (Non-Uniform Memory Access) et fonctionne correctement avec l'accès NUMA matériel sans configuration particulière. À mesure que la vitesse et le nombre de processeurs augmentent, il devient de plus en plus difficile de réduire le temps de réponse de la mémoire requis pour exploiter cette puissance de traitement supplémentaire. Pour contourner ce problème, les fournisseurs de matériel proposent des caches L3 de grande capacité, mais cette solution présente des limites. L’architecture NUMA fournit une solution évolutive à ce problème.

SQL Server a été conçu pour tirer parti des ordinateurs reposant sur la technologie NUMA sans qu'il soit nécessaire d'apporter des modifications aux applications. Pour plus d’informations, consultez Soft-NUMA (SQL Server).

Partition dynamique d’objets mémoire

Les allocateurs de tas, connus sous le nom d'objets mémoire dans SQL Server, permettent au moteur de base de données d'allouer de la mémoire à partir du tas. Celles-ci peuvent être suivies à l’aide de la vue de gestion dynamique sys.dm_os_memory_objects.

CMemThread est un type d’objet mémoire thread-safe qui autorise les allocations de mémoire simultanées à partir de plusieurs threads. Pour un suivi correct, les objets CMemThread s’appuient sur des constructions de synchronisation (un mutex) pour s’assurer qu’un seul thread met à jour des éléments d’information critiques à la fois.

Remarque

Le type d’objet CMemThread est utilisé dans tout la base de code du moteur de base de données pour de nombreuses allocations, et peut être partitionné globalement, par nœud ou par UC.

Toutefois, l’utilisation de mutex peut entraîner une contention si de nombreux threads sont alloués à partir du même objet mémoire d’une manière hautement simultanée. Par conséquent, SQL Server a le concept d’objets de mémoire partitionnée (PMO) et chaque partition est représentée par un objet CMemThread unique. Le partitionnement d’un objet mémoire est défini statiquement et ne peut pas être modifié après la création. Comme les modèles d’allocation de mémoire varient largement en fonction des aspects tels que l’utilisation du matériel et de la mémoire, il est impossible de trouver le modèle de partitionnement parfait au préalable.

Dans la plupart des cas, l'utilisation d'une seule partition suffit, mais dans certains scénarios, cela peut entraîner des conflits, qui ne peuvent être évités qu'avec un objet mémoire fortement partitionné. Il n'est pas souhaitable de partitionner chaque objet mémoire, car un plus grand nombre de partitions peut entraîner d'autres inefficacités et augmenter la fragmentation de la mémoire.

Remarque

Avant SQL Server 2016 (13.x),, l’indicateur de trace 8048 peut être utilisé pour forcer un PMO basé sur des nœuds à devenir un PMO basé sur le processeur. À partir de SQL Server 2014 (12.x) SP2 et SQL Server 2016 (13.x), ce comportement est dynamique et contrôlé par le moteur.

À partir de SQL Server 2014 (12.x) SP2 et SQL Server 2016 (13.x), le moteur de base de données peut détecter dynamiquement la contention sur un objet spécifique CMemThread et promouvoir l'objet vers une implémentation par nœud ou par CPU. Une fois promu, le PMO reste promu jusqu’au redémarrage du processus de SQL Server. La contention CMemThread peut être détectée par la présence d’attentes élevées CMEMTHREAD dans la DMV sys.dm_os_wait_stats, et en observant les colonnes DMV sys.dm_os_memory_objects contention_factor, partition_type, exclusive_allocations_count et waiting_tasks_count.

Contenu connexe