Guide d’architecture de gestion de la mémoire

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 mémoire 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, bien que l’option existe toujours et soit requise 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 fortement chargé, certaines requêtes volumineuses nécessitant une grande quantité de mémoire à exécuter ne peuvent pas obtenir la quantité minimale de mémoire demandée et recevoir une erreur de délai d’attente lors de l’attente des ressources de 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 fortement chargé sous pression mémoire, les requêtes avec jointure de fusion, tri et bitmap dans le plan de requête peuvent supprimer la bitmap lorsque les requêtes n’obtiennent pas la mémoire minimale requise pour la bitmap. Cela peut affecter les performances des requêtes et si le processus de tri ne peut pas s’adapter à la mémoire, il peut augmenter l’utilisation des tables de travail dans tempdb la base de données, ce qui entraîne une tempdb croissance. Pour résoudre ce problème, ajoutez de la mémoire physique ou ajustez les requêtes pour utiliser un plan de requête différent et 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 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 Les limites de mémoire pour Windows .

Adressez la mémoire des extensions Windows (AWE)

En utilisant le privilège AWE (Address Windowing Extensions ) et les 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 Lock pages in memory (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 MÉMOIRE ram s’il y a une pression de mémoire sur le système. Pour plus d’informations sur la façon d’autoriser SQL Server à utiliser AWE, consultez l’option Activer les pages de verrouillage en mémoire.

Si LPIM est accordé, nous vous recommandons vivement de définir la mémoire maximale du serveur (Mo) sur 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 Configuration du serveur de mémoire du serveur : Définir manuellement les options et verrouiller les pages en mémoire (LPIM).

Si LPIM n’est pas activé, SQL Server bascule vers l’utilisation de la mémoire conventionnelle et, dans les cas d’épuisement de la mémoire du système d’exploitation, l’erreur 17890 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 (11.x)

Dans les versions antérieures de SQL Server, l’allocation de mémoire a été effectuée à l’aide de cinq mécanismes différents :

• Allocator monopage (SPA), y compris uniquement les allocations de mémoire inférieures ou égales à 8 Ko dans le processus 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 piles de threads 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 du tas 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édure stockée étendue, 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 monopage, les allocations multipage et les allocations CLR sont toutes consolidées dans un allocateur de page « Toute taille », et incluses dans les limites de mémoire contrôlées par les options de configuration max server memory (Mo) et min server memory (Mo). Cette modification a fourni une capacité de dimensionnement plus précise pour toutes les exigences de mémoire qui passent par le gestionnaire de mémoire SQL Server.

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) et 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

À compter de SQL Server 2012 (11.x), SQL Server peut allouer plus de mémoire que la valeur spécifiée dans le paramètre max server memory (Mo). Ce comportement peut se produire lorsque la valeur 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). S’il n’y a pas suffisamment de mémoire libre contiguë pour répondre à la demande de demandes de mémoire multipage (plus de 8 Ko) en raison de la fragmentation de la mémoire, SQL Server peut effectuer un sur-engagement au lieu de rejeter la demande de mémoire.

Dès que cette allocation est effectuée, la tâche en arrière-plan Resource Monitor commence à signaler à tous les consommateurs de mémoire de libérer la mémoire allouée et tente d’apporter la valeur mémoire totale du serveur (Ko) sous la spécification mémoire du serveur cible (Ko). Par conséquent, l’utilisation de la mémoire SQL Server peut brièvement dépasser le paramètre max server memory (Mo). Dans ce cas, le compteur de performances Mémoire totale du serveur (Ko) dépasse les paramètres max server memory (Mo) et Target Server Memory (Ko).

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

• Requêtes d’index columnstore volumineuses
• Mode batch volumineux sur les requêtes rowstore
• Columnstore index (re)builds, qui utilisent de grands volumes de mémoire pour effectuer des opérations de hachage et de tri
• Opérations de sauvegarde nécessitant des mémoires tampons volumineuses
• Opérations de suivi qui doivent stocker des paramètres d’entrée volumineux

Modifications apportées à memory_to_reserve à partir de SQL Server 2012 (11.x)

Dans les versions antérieures de SQL Server, le gestionnaire de mémoire SQL Server a réservé une partie de l’espace d’adressage virtuel de processus (VAS) à utiliser par l’allocateur multipage (MPA), l’allocateur CLR, les allocations de mémoire pour les piles de threads dans le processus SQL Server et les 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 page « n’importe quelle taille » gère également les allocations supérieures à 8 Ko, la valeur memory_to_reserve n’inclut pas les allocations à plusieurs 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 se trouve dans 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) et 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 de gestion de la mémoire par défaut du moteur de base de données SQL Server consiste à acquérir autant de mémoire qu’il en a besoin sans créer de pénurie de mémoire sur le système. Le moteur de base de données SQL Server effectue cette opération à l’aide des API de notification de mémoire dans Microsoft Windows.

Quand SQL Server utilise la mémoire dynamiquement, il interroge régulièrement le système pour déterminer la quantité de mémoire libre. La conservation de cette mémoire libre empêche le système d'exploitation de paginer. Si moins de mémoire est libre, SQL Server libère de la mémoire sur le système d’exploitation. Si plus de mémoire est libre, SQL Server peut allouer plus de mémoire. SQL Server ajoute de la mémoire uniquement lorsque sa charge de travail nécessite plus de mémoire ; un serveur au repos n’augmente pas la taille de son espace d’adressage virtuel. Si vous remarquez que Task Manager et Performance Monitor montrent une diminution constante de la mémoire disponible lorsque SQL Server utilise la gestion de la mémoire dynamique, il s’agit du comportement par défaut et ne doit pas être perçu comme une fuite de mémoire.

La mémoire maximale du serveur contrôle l’allocation de mémoire SQL Server, la mémoire de compilation, tous les caches (y compris 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 CLR¹ (essentiellement tout commis de mémoire trouvé dans sys.dm_os_memory_clerks).

¹ mémoire CLR est gérée sous max_server_memory allocations à 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 pile

Mémoire pour les piles de threads 1^{, CLR 2, fichiers .dll de procédure étendue, les fournisseurs OLE DB référencés par les requêtes distribuées, les objets automation 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) .

¹ Reportez-vous à l’article sur la configuration maximale des threads de travail, pour plus d’informations sur les threads de travail par défaut calculés pour un nombre donné de processeurs affinités dans l’hôte actuel. Les tailles de pile SQL Server sont les suivantes :

Architecture 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

^{La mémoire CLR 2} est gérée sous max_server_memory allocations à partir de SQL Server 2012 (11.x).

SQL Server utilise l’API de notification de mémoire QueryMemoryResourceNotification pour déterminer quand le gestionnaire de mémoire SQL Server peut allouer de la mémoire et libérer de la mémoire.

Au démarrage de SQL Server, il calcule la taille de l’espace d’adressage virtuel pour le pool de mémoires tampons en fonction de plusieurs paramètres, tels que la quantité de mémoire physique sur le système, le nombre de threads de serveur et différents paramètres 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. À mesure que d’autres utilisateurs se connectent et exécutent des requêtes, SQL Server acquiert plus de mémoire physique à 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 du serveur (Mo) ou que le système d’exploitation indique qu’il n’y a plus d’excès de mémoire libre ; il libère de la mémoire lorsqu’elle a plus que le paramètre min server memory et le système d’exploitation indique qu’il y a une pénurie 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 minimale de la mémoire du serveur et de la mémoire maximale du serveur établissent des limites supérieures et inférieures à 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’acquiert pas immédiatement la quantité de mémoire spécifiée en mémoire minimale du serveur. En effet, il commence seulement avec la mémoire nécessaire à l'initialisation. À mesure que la charge de travail du moteur de base de données SQL Server augmente, elle continue d’acquérir la mémoire nécessaire pour prendre en charge la charge de travail. Le pool de mémoires tampons ne libère aucune de la mémoire acquise tant qu’elle n’atteint pas la quantité spécifiée en mémoire minimale du serveur. 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 est que le pool de mémoires tampons ne supprime jamais son allocation de mémoire au-dessous du niveau spécifié en mémoire minimale du serveur, et n’acquiert jamais plus de mémoire que le niveau spécifié dans la mémoire maximale du serveur (Mo) .

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 sur l’instance. Une instance SQL Server qui ne traite pas de nombreuses requêtes peut ne jamais atteindre la mémoire minimale du serveur.

Si la même valeur est spécifiée pour la mémoire minimale du serveur et la mémoire maximale du serveur (Mo), une fois que la mémoire allouée au moteur de base de données SQL Server atteint cette valeur, le moteur de base de données SQL Server cesse de libérer dynamiquement et d’acquérir de la mémoire pour le pool de mémoires tampons.

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. Dans ces cas, vous pouvez utiliser les options min server memory et max server memory (Mo) pour contrôler la quantité de mémoire que SQL Server peut utiliser. 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 * network_packet_size + 94 Ko)

La taille des paquets réseau est la taille des paquets TDS (Tabulaire Data Stream) utilisés pour communiquer entre les 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 plusieurs jeux de résultats actifs (MARS) sont activés, la connexion utilisateur est d’environ (3 + 3 * num_logical_connections) * network_packet_size + 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 cumulé dans ce scénario est RESOURCE_SEMAPHORE.

Considérations sur l’allocation de mémoire

Pour l’exécution du mode ligne, l’allocation de mémoire initiale ne peut pas être dépassée sous aucune condition. Si plus de mémoire que l’allocation initiale est nécessaire pour exécuter des opérations de hachage ou de tri, leur dépassement est transféré sur le disque. Une opération de hachage qui se déverse est prise en charge par un Fichier de travail, tempdbtandis qu’une opération de tri qui se déverse est prise en charge par un Worktable.

Un déversement qui se produit pendant une opération de tri est appelé avertissement de tri. Les avertissements de tri indiquent que les opérations de tri ne tiennent pas en mémoire. Cela n’inclut pas les opérations de tri impliquant la création d’index, seules les opérations de tri dans une requête (par exemple, une ORDER BY clause utilisée dans une SELECT instruction).

Un déversement qui se produit pendant une opération de hachage est appelé 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.

• La récursivité du hachage se produit lorsque l’entrée de build ne s’intègre pas dans la mémoire disponible, ce qui entraîne le fractionnement de l’entrée en plusieurs partitions traitées séparément. Si l’une de ces partitions ne tient toujours pas dans la mémoire disponible, elle est divisée en sous-parties, 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 en mémoire, celles-ci se déversent 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.

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.

Au démarrage de SQL Server, il calcule la taille de l’espace d’adressage virtuel pour le cache de mémoire tampon en fonction de plusieurs paramètres, tels que la quantité de mémoire physique sur le système, le nombre configuré de threads de serveur maximum et différents paramètres 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 seule demande de lecture de page de 8 Ko remplit une seule page de mémoire tampon. 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 Le Guide de l’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.
• Gestionnaire de base de données et système d’exploitation SQL Server (SQLOS) pour les opérations d’E/S de fichiers de bas niveau.
• 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.

  Note

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

• Le gestionnaire de mémoires tampons fournit des diagnostics supplémentaires qui sont exposés via des vues de gestion dynamique. Vous pouvez utiliser ces vues pour surveiller 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.

E/S disque

Le gestionnaire de tampons n'effectue que des lectures et des écritures dans la base de données. Les autres opérations de base de données et de fichier comme les opérations d'ouverture, de fermeture, d'élargissement et de compactage sont prises en charge par les composants du gestionnaire de base de données et du gestionnaire de fichiers.

Les opérations d'E/S disque effectuées par le gestionnaire de tampons présentent les caractéristiques suivantes :

• Toutes les opérations d'E/S s'effectuent de manière asynchrone, ce qui permet au thread appelant de continuer le traitement durant l'opération d'E/S en arrière-plan.
• Toutes les opérations d'E/S ont lieu dans des threads appelants, sauf si l'option affinity I/O est utilisée. L’option affinity I/O mask lie les E/S disque de SQL Server à un sous-ensemble de processeurs spécifié. Dans les environnements de traitement transactionnel en ligne (OLTP) SQL Server haut de gamme, cette extension permet d'améliorer les performances des threads SQL Server émettant des E/S.
• Les E/S de pages multiples s'effectuent à l'aide d'E/S par fragmentation-rassemblement, ce qui permet le transfert des données dans des zones contiguës de la mémoire ou hors de celles-ci. Cela signifie que SQL Server peut remplir ou vider rapidement le cache des tampons tout en évitant les demandes d'E/S physiques multiples.

Longues demandes d'E/S

Le gestionnaire de tampons signale les demandes d'E/S en suspens pendant un délai minimum de 15 secondes. L'administrateur système peut ainsi distinguer entre les problèmes liés à SQL Server et les problèmes au niveau du sous-système d'E/S. Le message d'erreur 833 est rapporté et consigné dans le journal des erreurs SQL Server comme suit :

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

Une longue E/S peut être une lecture ou une écriture ; il n’est pas indiqué actuellement dans le message. Les messages d'opérations d'E/S longues sont des avertissements pas des erreurs. Ils n’indiquent pas de problèmes avec SQL Server, mais avec le système d’E/S sous-jacent. Les messages permettent à l'administrateur de détecter la cause des temps de réponse SQL Server médiocres plus rapidement et de distinguer les problèmes qui ne dépendent pas de SQL Server. Par conséquent, elles ne nécessitent aucune action, mais l’administrateur système doit examiner la raison pour laquelle la demande d’E/S a pris si longtemps et si le temps est justifiable.

Causes des demandes d’E/S longues

Un message d’E/S long peut indiquer qu’une E/S est définitivement bloquée et ne sera jamais terminée (connue sous le nom d’E/S perdues), ou simplement qu’elle n’a pas encore terminé. Il n’est pas possible d’indiquer à partir du message quel scénario est le cas, bien qu’une E/S perdue entraîne souvent un délai d’expiration du verrou.

Une opération d'E/S longue indique souvent une charge de travail SQL Server trop intense pour le sous-système de disque. Un sous-système de disque inapproprié peut se manifester dans les cas suivants :

• plusieurs opérations d'E/S longues dans le journal d'erreurs au cours d'une charge de travail SQL Server importante ;
• Les compteurs de l’Analyseur de performances affichent des latences de disque longues, des files d’attente de disque longues ou aucun temps d’inactivité du disque.

Les opérations d'E/S longues sont aussi parfois causées par un composant dans le chemin d'accès d'E/S (un pilote, un contrôleur, un microprogramme, entre autres) qui retardent continuellement une demande d'E/S antérieure pour traiter des demandes plus récentes dont la position actuelle est plus proche de la tête de disque. La technique courante de traitement des demandes en priorité en fonction de laquelle les requêtes sont les plus proches de la position actuelle de la tête de lecture/écriture est connue sous le nom de « recherche d’ascenseur ». Cela peut être difficile à corroborer avec l’outil Analyseur de performances, car la plupart des E/S sont rapidement mises en service. Les opérations d'E/S longues peuvent se compliquer en raison de charges de travail impliquant de grandes quantités d'E/S séquentielle, parmi lesquelles figurent les opérations de sauvegarde et de restauration, les analyses de table, les tris, les créations d'index, les chargements en masse et les réinitialisations de fichiers.

Les E/S longues isolées qui n’apparaissent pas liées à l’une des conditions précédentes peuvent être causées par un problème matériel ou de pilote. Le journal d'événements système peut contenir un événement connexe qui permet de diagnostiquer le problème.

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 à découper 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 :

• Réponse à la pression de la mémoire externe lorsque le moteur de base de données SQL Server définit des limites d’utilisation de mémoire inférieures.
• 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 pression de la mémoire, dans le cadre de sa gestion dynamique de la mémoire. 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 facultatifs qui permettent de garantir l’intégrité de la page à partir du moment où elle est écrite sur le disque jusqu’à ce qu’elle soit réécrite : protection de page endommagée et protection de 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 lecture échouent, la commande échoue elle aussi avec le message d'erreur 824.

Le type de protection de page utilisé est un attribut de la base de données qui contient la page. La protection de 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 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 ALTER DATABASE SETde . Vous pouvez déterminer le paramètre de protection de page actuel en interrogeant la page_verify_option colonne dans la vue catalogue sys.databases ou la IsTornPageDetectionEnabled propriété de la fonction DATABASEPROPERTYEX .

Protection de page déchirée

La protection des pages endommagées, introduite dans SQL Server 2000 (8.x), est principalement un moyen de détecter les altérations de page en raison de 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 binaire 01 et 10 avec chaque écriture. Il est donc toujours possible de savoir quand seule une partie des secteurs l’a rendue sur disque : si un peu est dans un état incorrect lorsque la page est plus tard lue, la page a été écrite de manière incorrecte et une page déchirée est détectée. La détection de page déchirée utilise des ressources minimales ; toutefois, il ne détecte pas toutes les erreurs causées par les défaillances matérielles de disque. Pour plus d’informations sur la définition de la détection de page déchirée, 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 plus forte 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).

Comprendre l’accès à la mémoire non uniforme

SQL Server prend en charge l’accès à la mémoire non uniforme (NUMA) et fonctionne correctement sur le matériel NUMA 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 en savoir plus, référez-vous à Procédure : configurer SQL Server pour utiliser soft-NUMA.

Partition dynamique d’objets mémoire

Les allocateurs de tas, appelés 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.

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és (PMO) et chaque partition est représentée par un seul objet CMemThread. Le partitionnement d’un objet mémoire est défini de manière statique 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 partition unique suffit, mais dans certains scénarios, cela peut entraîner une contention, qui peut être empêchée uniquement avec un objet mémoire hautement partitionné. Il n’est pas souhaitable de partitionner chaque objet mémoire, car d’autres partitions peuvent entraîner d’autres inefficacités et augmenter la fragmentation de la mémoire.

À compter 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 CMemThread spécifique et promouvoir l’objet vers un nœud ou une implémentation basée sur l’UC. Une fois promu, le PMO reste promu jusqu’à ce que le processus SQL Server soit redémarré. La contention CMemThread peut être détectée par la présence d’attentes CMEMTHREAD élevées dans la vue DMV sys.dm_os_wait_stats, et en observant les colonnes contention_factorsys.dm_os_memory_objects DMV , partition_type, exclusive_allocations_countet waiting_tasks_count.

Étapes suivantes

• server memory (options de configuration de serveur)
• Lecture de pages
• Écriture de pages
• Procédure : configurer SQL Server pour utiliser Soft-NUMA
• Exigences liées à l’utilisation des tables à mémoire optimisée
• Résoudre les problèmes de mémoire insuffisante ou de mémoire insuffisante dans SQL Server
• Résoudre les problèmes de mémoire insuffisante à l’aide de tables à mémoire optimisée