Guide d’architecture des pages et des étendues

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

La page est l’unité fondamentale de stockage de données dans SQL Server. Une étendue est une collection de huit pages contiguës physiquement. Les étendues sont une aide précieuse pour la gestion des pages. Ce guide décrit les structures de données utilisées pour gérer les pages et les étendues dans toutes les versions de SQL Server. Il est essentiel de comprendre l'architecture des pages et étendues pour concevoir et développer des bases de données performantes.

Pages et étendues

L’unité fondamentale du stockage des données dans SQL Server est la page. L’espace disque alloué à un fichier de données (.mdf ou .ndf) dans une base de données est divisé logiquement en pages numérotées de 0 à n. Les opérations d'E/S disque sont effectuées au niveau page. En d’autres termes, SQL Server lit ou écrit des pages entières de données.

Les extensions sont une collection de huit pages physiques contiguës ; elles sont utilisées pour gérer les pages de manière efficace. Toutes les pages sont organisées en étendues.

Pages

Dans un livre régulier, tout le contenu est écrit sur des pages. Comme pour un livre, SQL Server écrit toutes les lignes de données sur les pages et toutes les pages de données sont de la même taille : 8 Ko. Dans un livre, la plupart des pages contiennent les données ( le contenu principal du livre) et certaines pages contiennent des métadonnées sur le contenu (par exemple, la table des matières et l’index). Là encore, SQL Server n’est pas différent : la plupart des pages contiennent des lignes de données réelles stockées par les utilisateurs ; elles sont appelées pages de données et pages texte/image (pour des cas spéciaux). Les pages d’index contiennent des références d’index sur l’emplacement où se trouvent les données. Enfin, il existe des pages système qui stockent différentes métadonnées sur l’organisation des données.

Chaque page commence par un en-tête de 96 octets qui sert à stocker les informations système relatives à la page. Ces informations sont notamment le numéro de page, le type de page, la quantité d'espace disponible sur la page et l'ID de l'unité d'allocation de l'objet auquel appartient la page.

Le tableau suivant présente les types de page utilisés dans les fichiers de données d’une base de données SQL Server.

Type de page	Sommaire
Données	Lignes de données avec toutes les données, à l’exception du texte, ntext, image, nvarchar(max), varchar(max), varbinary(max)et des données xml , lorsque le texte dans la ligne est défini sur ON.
Index	Des entrées d'index.
Texte/image	Types de données d’objet volumineux : texte, ntext, image, nvarchar(max), varchar(max), varbinary(max) et données xml. Colonnes de longueur variable lorsque la ligne de données dépasse 8 Ko : varchar, nvarchar, varbinary et sql_variant.
Pages GAM (Global Allocation Map) Pages SGAM (Shared Global Allocation Map)	Informations précisant si ces extensions sont allouées.
Page Free Space (PFS)	Informations sur l'allocation des pages et sur l'espace disponible sur ces pages.
Mappage d’allocation d’index (IAM)	Informations sur les extensions utilisées par une table ou un index par unité l'allocation.
BCM (Bulk Changed Map)	Informations sur les extensions modifiées par des opérations en bloc depuis la dernière instruction BACKUP LOG par unité d'allocation.
DCM (Differential Changed Map)	Informations sur les extensions qui ont été modifiées depuis la dernière instruction BACKUP DATABASE par unité d'allocation.

Note

Les fichiers journaux ne contiennent pas de pages. Ils contiennent une série d’enregistrements de journal qui n’ont pas de taille fixe.

Les lignes de données sont stockées sur la page en série, en commençant immédiatement après l’en-tête. Une table de décalage de lignes débute à la fin de la page et chaque table de décalage de lignes contient une entrée pour chaque ligne de la page. Chaque entrée de décalage de ligne stocke la distance entre le premier octet de la ligne et le début de la page. Par conséquent, la fonction de la table de décalage de ligne consiste à aider SQL Server à localiser rapidement les lignes d’une page. Les entrées de la table de décalage de lignes sont inversées par rapport à l'ordre des lignes sur la page.

Prise en charge des lignes volumineuses

Les lignes ne peuvent pas s’étendre sur des pages ; Toutefois, les parties de la ligne peuvent être déplacées hors de la page de la ligne, de sorte que la ligne peut être très volumineuse. La quantité maximale de données et de surcharge contenues dans une seule ligne d’une page est de 8 060 octets. Cela n’inclut pas les données stockées dans le type de page texte/image.

Cette restriction est assouplie pour les tables qui contiennent des colonnes varchar, nvarchar, varbinary ou sql_variant . Lorsque la taille totale de ligne de toutes les colonnes fixes et variables d’une table dépasse la limite de 8 060 octets, SQL Server déplace dynamiquement une ou plusieurs colonnes de longueur variable vers des pages dans l’unité d’allocation ROW_OVERFLOW_DATA, en commençant par la colonne avec la plus grande largeur.

Cette opération est réalisée chaque fois qu'une opération d'insertion ou de mise à jour augmente la taille totale de la ligne au-delà de la limite de 8 060 octets. Lorsqu'une colonne est déplacée dans une page de l'unité d'allocation ROW_OVERFLOW_DATA, un pointeur de 24 octets est conservé sur la page d'origine dans l'unité d'allocation IN_ROW_DATA. Si une opération ultérieure réduit la taille de la ligne, SQL Server redéplace de manière dynamique les colonnes dans la page de données d’origine.

Considérations relatives au dépassement de ligne

Une ligne ne peut pas résider sur plusieurs pages et peut dépasser la taille combinée des champs de type de données de longueur variable supérieure à la limite de 8060 octets. Pour illustrer, une table peut être créée avec deux colonnes : une varchar(7000) et une autre varchar (2000). Individuellement, aucune colonne ne dépasse 8 060 octets, mais combinées, elles peuvent le faire si la largeur entière de chaque colonne est remplie. SQL Server peut déplacer dynamiquement la colonne de longueur variable varchar(7000) vers des pages dans l’unité d’allocation ROW_OVERFLOW_DATA. Lorsque vous combinez des colonnes de type varchar, nvarchar, varbinary ou sql_variant ou CLR définies par l’utilisateur qui dépassent 8 060 octets par ligne, tenez compte des éléments suivants :

• Les enregistrements volumineux sont automatiquement déplacés vers une autre page dès lors que les enregistrements s'allongent suite à une opération de mise à jour. Les opérations de mise à jour qui raccourcissent les enregistrements peuvent provoquer le rapatriement d'enregistrements vers la page initiale dans l'unité d'allocation IN_ROW_DATA.

  L’interrogation et d’autres opérations de sélection, telles que les tris ou les jointures portant sur des enregistrements volumineux qui contiennent des données de dépassement de ligne, augmentent le temps de traitement car ces enregistrements sont traités de façon synchrone, et non de manière asynchrone.

  Par conséquent, lorsque vous concevez une table avec plusieurs colonnes de type varchar, nvarchar, varbinary ou sql_variant ou CLR définies par l’utilisateur, tenez compte du pourcentage de lignes susceptibles de circuler sur et de la fréquence à laquelle ces données de dépassement de capacité sont susceptibles d’être interrogées. S'il est probable qu'il y ait de fréquentes requêtes sur de nombreuses lignes de données de dépassement de ligne, pensez à normaliser la table de manière à ce que certaines colonnes soient déplacées vers une autre table. Celle-ci peut ensuite être interrogée lors d'une opération JOIN asynchrone.

• La longueur des colonnes individuelles doit toujours être comprise dans la limite de 8 000 octets pour les colonnes de type varchar, nvarchar, varbinary ou sql_variant et CLR définies par l’utilisateur. Seule la combinaison de leurs longueurs peut dépasser la limite de 8 060 octets par ligne d'une table.

• La somme des autres colonnes de type de données, y compris les données char et nchar , doit se trouver dans la limite de lignes de 8 060 octets. En outre, les données d'objet volumineux ne sont pas soumises à la limite de 8 060 octets par ligne.

• La clé d’un index cluster ne peut pas contenir de colonnes varchar qui possèdent des données dans l’unité d’allocation ROW_OVERFLOW_DATA. Si un index cluster est créé sur une colonne varchar et que les données existantes se trouvent dans l’unité d’allocation IN_ROW_DATA, les actions d’insertion ou de mise à jour réalisées ultérieurement sur la colonne et susceptibles d’envoyer les données hors ligne sont vouées à l’échec. Pour plus d’informations sur les unités d’allocation, consultez le guide de conception et d’architecture d’index.

• Vous pouvez inclure des colonnes qui contiennent des données de dépassement de ligne en tant que colonnes clés ou non clés d'un index non-cluster.

• La limite de taille d'enregistrement pour les tables qui utilisent des colonnes éparses est de 8 018 octets. Quand les données converties plus les données de l’enregistrement existant dépassent 8 018 octets, MSSQLSERVER ERROR 576 est retourné. Lorsque les colonnes sont converties entre les types épars et nonparse, le moteur de base de données conserve une copie des données d’enregistrement actuelles. Cela double temporairement le stockage requis pour l'enregistrement.

• Pour obtenir des informations sur les tables ou les index pouvant contenir des données de dépassement de ligne, utilisez la fonction de gestion dynamique sys.dm_db_index_physical_stats.

Étendues

Les extensions constituent l'unité de base dans laquelle l'espace est géré. Une extension est constituée de 8 pages contiguës, soit 64 Ko. Cela signifie que les bases de données SQL Server ont 16 étendues par mégaoctet.

SQL Server contient deux types d’étendues :

• Les extensions uniformes appartiennent à un objet unique ; les huit pages de l’extension ne peuvent être utilisées que par l’objet propriétaire.
• Les extensions mixtes sont partagées par huit objets au plus. Chacune des huit pages de l'extension peut être la propriété d'un objet différent.

Jusqu’à sql Server 2014 (12.x), le moteur de base de données n’alloue pas toutes les étendues aux tables avec de petites quantités de données. Une nouvelle table ou un nouvel index affecte en général des pages issues d'extensions mixtes. Lorsque la table ou l'index atteint huit pages, il bascule à l'utilisation des extensions uniformes pour les allocations suivantes. Si vous créez un index sur une table existante qui possède un nombre de lignes suffisant pour générer huit pages dans l'index, toutes les allocations à l'index se trouvent dans des extensions uniformes.

À compter de SQL Server 2016 (13.x), la valeur par défaut pour la plupart des allocations dans une base de données utilisateur est tempdb d’utiliser des étendues uniformes, à l’exception des allocations appartenant aux huit premières pages d’une chaîne IAM. Les allocations pour master, msdbet model les bases de données conservent toujours le comportement précédent.

Note

Dans SQL Server, jusqu’à SQL Server 2014 (12.x), vous pouvez utiliser l’indicateur de trace (TF) 1118 pour modifier l’allocation par défaut pour toujours utiliser des étendues uniformes. Pour plus d’informations sur cet indicateur de trace, consultez DBCC TRACEON - Indicateurs de Trace.

À compter de SQL Server 2016 (13.x), la fonctionnalité fournie par TF 1118 est automatiquement activée pour tempdb toutes les bases de données utilisateur. Pour les bases de données utilisateur, ce comportement est contrôlé par l’option SET MIXED_PAGE_ALLOCATION « ALTER DATABASE, avec la valeur par défaut définie sur OFF et TF 1118 n’a aucun effet. Pour plus d’informations, consultez Options SET d’ALTER DATABASE (Transact-SQL).

À compter de SQL Server 2012 (11.x), la sys.dm_db_database_page_allocations fonction système peut signaler des informations d’allocation de page pour une base de données, une table, un index et une partition.

Important

La fonction système sys.dm_db_database_page_allocations n’est pas documentée et est susceptible d’être modifiée. La compatibilité n'est pas garantie.

À compter de SQL Server 2019 (15.x), la fonction système sys.dm_db_page_info est disponible et retourne des informations sur une page d’une base de données. La fonction retourne une ligne qui contient les informations d’en-tête de la page, y compris le object_id, index_idet partition_id. Cette fonction rend superflue l’utilisation de DBCC PAGE dans la plupart des cas.

Gérer les allocations d’étendues et l’espace libre

Les structures de données SQL Server qui gèrent les allocations des étendues et l’espace libre ont une structure relativement simple. Cette solution offre les avantages suivants :

• Les informations sur l'espace libre sont très compactes, d'où un nombre de pages d'informations relativement faible.

  Cela augmente la vitesse en réduisant le nombre de lectures de disque requises pour récupérer les informations d’allocation. et à l'augmentation de la possibilité de garder en mémoire l'affectation des pages, ce qui réduit encore le nombre de lectures.

• La plupart des informations d’allocation ne sont pas chaînées. ce qui simplifie leur gestion.

  Chaque allocation ou désallocation de page peut être effectuée rapidement, ce qui diminue les problèmes de contention entre les tâches simultanées nécessitant une allocation ou une désallocation de pages.

Gérer les allocations d’étendues

SQL Server utilise deux types de tables d'allocation pour enregistrer l'allocation des extensions :

• Pages GAM (Global Allocation Map)

  Les pages GAM enregistrent les extensions qui ont été allouées. Chaque page GAM couvre 64 000 extensions, soit près de 4 gigaoctets (Go) de données. La gam a 1 bits pour chaque étendue dans l’intervalle qu’elle couvre. Si le bit est 1, l’étendue est libre ; si le bit est 0, l’étendue est allouée.

• Pages SGAM (Shared Global Allocation Map)

  Les pages SGAM enregistrent les extensions actuellement utilisées comme extensions mixtes et possédant au moins une page inutilisée. Chaque table SGAM prend en charge 64 000 étendues, soit près de 4 Go de données. Le SGAM a 1 bits pour chaque étendue dans l’intervalle qu’il couvre. Si le bit est 1, l’extension est utilisée comme une étendue mixte et a une page libre. Si le bit est 0, l’étendue n’est pas utilisée comme étendue mixte, ou il s’agit d’une étendue mixte et toutes ses pages sont utilisées.

Chaque extension possède les schémas de bits suivants dans les tables GAM et SGAM, en fonction de son utilisation actuelle.

Utilisation actuelle de l'extension	Valeur du bit GAM	Valeur du bit SGAM
Libre, inutilisée	1	0
Extension uniforme ou extension mixte complète	0	0
Extension mixte avec pages libres	0	1

Ceci se traduit par des algorithmes simples de gestion des extensions.

• Pour allouer une étendue uniforme, le moteur de base de données recherche le gam un 1 peu et le 0définit sur .
• Pour trouver une étendue mixte avec des pages libres, le moteur de base de données recherche le SGAM pour un 1 peu.
• Pour allouer une étendue mixte, le moteur de base de données recherche le GAM pour un 1 bit, le 0définit sur , puis définit le bit correspondant dans le SGAM sur 1.
• Pour libérer une extension, le moteur de base de données s’assure que le bit GAM est défini 1sur , et que le bit SGAM est défini sur 0.

Les algorithmes utilisés en interne par le moteur de base de données sont plus sophistiqués que ce qui est décrit dans cet article, car le moteur de base de données distribue les données uniformément dans une base de données. Toutefois, même les algorithmes réels sont simplifiés afin de ne plus devoir gérer les chaînes d'informations d'allocation des extensions.

Suivre l’espace libre

Les pages PFS (Page Free Space) enregistrent quand une page individuelle a été allouée, le statut d’allocation et la quantité d’espace libre de chaque page. Le PFS a 1 octet pour chaque page, enregistrant si la page est allouée, et, si c’est le cas, s’il est vide, 1 à 50 % plein, 51 à 80 % plein, 81 à 95 % plein ou 96 à 100 % plein.

Une fois une étendue allouée à un objet, le moteur de base de données utilise les pages PFS pour enregistrer les pages de l’étendue qui sont allouées ou libres. Ces informations sont alors utilisées par le moteur de base de données pour l’allocation de toute nouvelle page. La quantité d’espace libre dans une page est conservée uniquement pour les pages de tas et de texte/image. Ces informations sont exploitées par le moteur de base de données pour rechercher une page disposant de suffisamment d’espace libre pour accueillir une nouvelle ligne. Les index ne nécessitent pas que l’espace libre de la page soit suivi, car le point auquel insérer une nouvelle ligne est défini par les valeurs de clé d’index.

Une nouvelle page PFS, GAM ou SGAM est ajoutée au fichier de données pour chaque plage supplémentaire dont elle effectue le suivi. Ainsi, il y a une nouvelle page PFS 8 088 pages après la première page PFS, et des pages PFS supplémentaires toutes les 8 088 pages. À titre d’illustration, l’ID de page 1 est une page PFS, l’ID de page 8088 est une page PFS, l’ID de page 16176 est une page PFS, et ainsi de suite.

Il y a une nouvelle page GAM 64 000 étendues après la première page GAM, qui effectue le suivi des 64 000 étendues qui la suivent. La séquence continue toutes les 64 000 étendues. De même, il y a une nouvelle page SGAM 64 000 étendues après la première page SGAM et des pages SGAM supplémentaires toutes les 64 000 étendues.

L’illustration suivante indique l’ordre des pages utilisées par le moteur de base de données pour l’allocation et la gestion des étendues.

Gérer l’espace utilisé par les objets

Une page IAM (Index Allocation Map) mappe les étendues d’une portion de 4 Go d’un fichier de base de données utilisées par une unité d’allocation. Une unité d'allocation peut être de trois types :

• IN_ROW_DATA

  Contient une partition d'un segment ou d'un index.

• LOB_DATA

  Contient des types de données d’objet volumineux (LOB), tels que xml, varbinary(max)et varchar(max).

• ROW_OVERFLOW_DATA

  Contient les données de longueur variable stockées dans varchar, nvarchar, varbinary ou sql_variant colonnes qui dépassent la limite de taille de ligne de 8 060 octets.

Chaque partition d'un segment ou d'un index contient au moins une unité d'allocation IN_ROW_DATA. Elle peut aussi contenir une unité d'allocation LOB_DATA ou ROW_OVERFLOW_DATA, selon le schéma de segment ou d'index.

Une page IAM couvre une plage de 4 Go dans un fichier, comme une page GAM ou SGAM. Si l'unité d'allocation contient des étendues provenant de plusieurs fichiers, ou plusieurs plages de 4 Go dans un fichier, il y aura plusieurs pages IAM liées entre elles dans une chaîne IAM. Ainsi, chaque unité d'allocation contient au moins une page IAM pour chaque fichier dans lequel elle possède des étendues. Un fichier peut aussi contenir plusieurs pages IAM si la plage d'étendues du fichier allouée à l'unité d'allocation dépasse la plage que peut enregistrer une page IAM unique.

Les pages IAM sont allouées au fur et à mesure des besoins pour chaque unité d'allocation et elles sont placées aléatoirement dans le fichier. La vue système sys.system_internals_allocation_units pointe vers la première page IAM d’une unité d’allocation. Toutes les pages IAM de cette unité d'allocation sont liées entre elles et forment une chaîne IAM.

Important

La vue système sys.system_internals_allocation_units est destinée exclusivement à un usage interne et elle est susceptible de changer. La compatibilité n'est pas garantie. Cette vue n’est pas disponible dans Azure SQL Database.

Une page IAM a un en-tête qui indique l’étendue de départ de la plage d’étendues mappées par la page IAM. La page IAM contient aussi une grande image dans laquelle chaque bit représente une étendue. Le premier bit représente la première étendue de la plage, le second la deuxième étendue, et ainsi de suite. Si un bit est 0, l’étendue qu’elle représente n’est pas allouée à l’unité d’allocation propriétaire de l’IAM. Si le bit est 1, l’étendue qu’elle représente est allouée à l’unité d’allocation propriétaire de la page IAM.

Lorsque le moteur de base de données doit insérer une nouvelle ligne et qu’aucun espace n’est disponible dans la page active, il utilise les pages IAM et PFS pour rechercher une page à allouer, ou, pour un tas ou une page texte/image, une page avec suffisamment d’espace pour contenir la ligne. Le moteur de base de données utilise les pages IAM pour rechercher les étendues allouées à l’unité d’allocation. Pour chaque étendue, le moteur de base de données recherche les pages PFS afin de vérifier si l’une d’elles peut être utilisée. Chaque page IAM et PFS couvre de nombreuses pages de données. Il existe donc peu de pages IAM et PFS dans une base de données. C’est pourquoi elles se trouvent en général dans la mémoire du pool de mémoires tampons de SQL Server, d’où il est possible de les rechercher plus rapidement. Pour les index, le point d’insertion d’une nouvelle ligne est défini par la clé d’index, mais quand une nouvelle page est nécessaire, le processus décrite précédemment se produit.

Le moteur de base de données alloue une nouvelle étendue à une unité d’allocation uniquement lorsqu’il ne peut pas trouver rapidement une page dans une étendue existante avec suffisamment d’espace pour contenir la ligne insérée.

Allocation de remplissage proportionnelle

Le moteur de base de données alloue des étendues de ceux disponibles dans le groupe de fichiers à l’aide d’un algorithme d’allocation de remplissage proportionnel. Dans le même groupe de fichiers avec deux fichiers, si un fichier a double l’espace libre que l’autre, deux pages seront allouées à partir du fichier avec l’espace disponible pour chaque page allouée à partir de l’autre fichier. Cela signifie que chaque fichier d'un groupe doit avoir un pourcentage identique d'espace utilisé.

Suivre les étendues modifiées

SQL Server utilise deux structures de données internes pour suivre les étendues modifiées par les opérations de copie en bloc et les étendues modifiées depuis la dernière sauvegarde complète. Ces structures de données accélèrent considérablement les sauvegardes différentielles. Elles accélèrent également l'enregistrement des opérations de copie en bloc dans le journal lorsqu'une base de données utilise le mode de récupération utilisant les journaux de transactions. Comme les pages GAM et SGAM, ces structures sont des bitmaps dans lesquelles chaque bit représente une seule étendue.

• DCM (Differential Changed Map)

  Ces pages suivent les extensions qui ont été modifiées depuis la dernière instruction BACKUP DATABASE. Si le bit pour une extension est 1, l’étendue a été modifiée depuis la dernière BACKUP DATABASE instruction. Si le bit est 0, l’étendue n’a pas été modifiée.

  Les sauvegardes différentielles lisent uniquement les pages DCM pour déterminer les extensions qui ont été modifiées. Cela réduit considérablement le nombre de pages qu'une sauvegarde différentielle doit analyser. La durée pendant laquelle une sauvegarde différentielle s’exécute est proportionnelle au nombre d’étendues modifiées depuis la dernière BACKUP DATABASE instruction et non à la taille globale de la base de données.

• BCM (Bulk Changed Map)

  Cela suit les étendues qui ont été modifiées par les opérations journalisées en bloc depuis la dernière BACKUP LOG instruction. Si le bit pour une extension est 1, l’extension a été modifiée par une opération journalisée en bloc après la dernière BACKUP LOG instruction. Si le bit est 0, l’étendue n’a pas été modifiée par les opérations journalisées en bloc.

  Bien que les pages BCM existent dans toutes les bases de données, elles sont uniquement significatives lorsque la base de données emploie le mode de récupération utilisant les journaux de transactions. Dans ce mode de récupération, quand une procédure BACKUP LOG est effectuée, le processus de sauvegarde analyse les pages BCM pour identifier les extensions qui ont été modifiées. Il inclut ensuite ces extensions dans la sauvegarde du journal. Cela récupère les opérations journalisées en bloc si la base de données est restaurée à partir d’une sauvegarde de base de données et d’une séquence de sauvegardes de journal des transactions. Les pages BCM ne sont pas pertinentes dans une base de données qui utilise le modèle de récupération simple, car aucune opération journalisée en bloc n’est enregistrée. Ils ne sont pas pertinents dans une base de données qui utilise le modèle de récupération complète, car ce modèle de récupération traite les opérations journalisées en bloc comme des opérations entièrement journalisées.

L'intervalle entre les pages DCM et les pages BCM est le même que l'intervalle entre les pages GAM et SGAM : 64 000 extensions. Les pages DCM et BCM se trouvent derrière les pages GAM et SGAM dans un fichier physique comme suit :

Voir aussi

• sys.allocation_units (Transact-SQL)
• Segments (tables sans index cluster)
• sys.dm_db_page_info
• Lecture de pages
• Écriture de pages