Tables de tas dans SQL Server

Dans le dernier article de cette série de blogs, j’ai discuté de ce qu’était un index B-Tree et expliqué brièvement son historique.

Avant de plonger dans les index utilisés par SQL Server, il est important de définir les bases des structures de données. La table la plus basique que vous pouvez créer est un tas. Un tas est une structure non triée de pages qui ne sont pas liées les unes aux autres.

Quand devriez-vous utiliser des tas?

La meilleure pratique recommandée est d’éviter les tas de SQL Server, dans la plupart des cas. Kimberly Tripp explique certaines des raisons et des débats ici, mais je couvrirai le haut niveau. Les tas, étant la structure de stockage la plus basique, manquent de fonctionnalités qui améliorent les performances des charges de travail normales (OLTP). Certains soutiennent que les tas sont meilleurs pour les opérations d’extraction, de transformation et de chargement (ETL) car il y a moins de pages à maintenir pendant les opérations d’écriture (MISE à JOUR / INSERTION / SUPPRESSION). Plus précisément, les tas peuvent être plus rapides qu’un index B-tree si des divisions de page se produisent et que de nombreux niveaux de l’index doivent être mis à jour. De plus, l’identifiant de ligne (RID), discuté plus loin, n’est que de 8 octets. Cela peut fonctionner mieux qu’un index en cluster avec beaucoup de colonnes de clés, car les index non en cluster doivent stocker la clé en cluster ou RID pour chaque ligne indexée. En règle générale, je recommande d’utiliser un index en cluster (plus sur les index en cluster au fur et à mesure que nous continuons cette série) plutôt qu’un tas mais, si vous les utilisez, ne les utilisez que lorsque vous avez prouvé qu’ils seront plus efficaces qu’un index en cluster. Après avoir discuté de la structure d’un tas, je décrirai les problèmes de performance liés à l’utilisation de tas.

Structure de tas

Un tas est un regroupement de pages non triées qui ne sont pas liées. L’anatomie des pages est hors de portée pour cette série car tous les types de tables indexées et non indexées utilisent la même structure de page, mais je vous encourage à consulter ici et ici pour en savoir plus.

Un tas est composé d’une ou plusieurs pages de carte d’allocation d’index (IAM) qui pointent vers les pages de données qui composent le tas. La seule exception à cela est lorsque vous avez une ligne qui a été mise à jour et qui ne pouvait plus tenir dans sa page. Dans ce cas, vous obtenez un pointeur de transfert vers la ligne qui a été déplacée vers une page existante avec de l’espace ou une nouvelle page. Il est possible pour vous de produire une chaîne d’enregistrements de transfert si la ligne continue de nécessiter une relocalisation par d’autres opérations.

heap-structure-1

Référence de l’image : MSDN

Un tas a une ligne dans sys.les partitions par partition et son index_id seront égaux à zéro. Dans cet enregistrement, la page first_iam_page pointe vers la première des pages de la carte d’allocation d’index (IAM). Une page IAM mappe les pages de chaque unité d’allocation et gère des morceaux de 4 Go de la table. Les unités d’allocation comprennent:

  1. Unité d’allocation IN_ROW_DATA
  2. Unité d’allocation LOB_DATA
  3. Unité d’allocation ROW_OVERFLOW_DATA

La page IAM contient l’en-tête standard de 96 octets suivi de l’en-tête IAM. L’en-tête IAM commence par huit emplacements pour les allocations d’étendue mixtes, puis une carte de bits de 8000 octets pour localiser les étendues uniformes allouées à la table.

Une étendue mixte est celle qui contient des pages de plus d’une table. Ceci est également appelé une étendue partagée. Le but d’une étendue mixte est d’économiser de l’espace avec des tables inférieures à 64 Ko. Une étendue uniforme est celle où toutes les pages de l’étendue appartiennent à cette seule table.

mixed-extent

Référence de l’image: L’étendue

Pour étudier notre page IAM, nous devons d’abord la localiser.

DBCC IND(‘demo’, ‘dbo.démo’,1)

dbcc-ind

Avec DBCC IND, nous avons 17 pages de données et 1 page IAM. Les pages IAM ne sont pas auto-référencées, par conséquent, IAMFID (Id de fichier IAM) et IAMPID (Id de page IAM) sont NULS. Cela signifie que notre page IAM se trouve dans l’ID de fichier (PageFID) 1 et est l’Id de page (PagePID) 297. La PAGE DBCC nous permettra de visualiser notre page IAM.

DBCC TRACEON(3604);

PAGE DBCC (‘démo’,1,297,3);

dbcc-page-iam

En JAUNE, il y a huit allocations d’une seule page dans notre étendue mixte. En ORANGE, les étendues uniformes sont affichées en plages.

Implications sur les performances

L’exécution de diverses opérations de langage de manipulation de données (DML) sur un tas a ces effets.

  • INSÉRER – De nouvelles lignes peuvent être placées dans la première page disponible avec suffisamment d’espace. Ainsi, chaque fois qu’une nouvelle ligne est insérée, elle sera probablement ajoutée à la dernière page.
  • MISE À JOUR – Les lignes peuvent rester sur la même page si elles s’insèrent dans la page après la mise à jour, sinon elles seront supprimées de la page en cours et placées sur la première page disponible avec suffisamment d’espace et un pointeur de transfert est écrit dans sa page d’origine.
  • DELETE – Les données ne sont pas écrasées, l’espace est simplement signalé comme disponible pour réutilisation. Cela peut faire en sorte que votre table consomme beaucoup plus d’espace disque que nécessaire.
  • SELECT – Une analyse de table sur la table entière devra être lue pour la plupart des requêtes. L’exception à cela est lorsqu’un index non clusterisé approprié est disponible. Nous discuterons des index non clusterisés plus tard dans cette série.

Lit

Sans index de support, un tas nécessite une analyse complète de la table pour toute requête. En effet, la structure physique n’est pas triée et il n’y a pas de lien de page pour prendre en charge les analyses de plage.

DÉFINISSEZ LES STATISTIQUES IO SUR

DBCC IND(‘demo’, ‘dbo.démo’,1)

SÉLECTIONNEZ COUNT(*)
DANS dbo.demo

Répéter notre commande DBCC IND d’en haut nous rappelle que nous avons 18 pages pour ce tableau. 17 pages de données et 1 page IAM.

-18 résultat de ligne, indique 18 pages.

(18 lignes affectées)
Exécution DBCC terminée. Si DBCC a imprimé des messages d’erreur, contactez votre administrateur système.

Nombre de lignes
—-
5031
(1 ligne(s) affectée(s))

Table ‘Demo’. Nombre de numérisations 1, lectures logiques 17, lectures physiques 0, lectures anticipées 0, lectures logiques de lob 0, lectures physiques de lob 0, lectures anticipées de lob 0.
(1 ligne(s) affectée(s))

Notez les 17 lectures logiques de notre requête. En effet, la page IAM n’est pas comptée dans la métrique lectures logiques et nous avons effectué une analyse complète de la table avec 18 pages.

Pointeurs d’écriture et de transfert

Comme mentionné ci-dessus, les insertions et les suppressions sont assez simples. Une INSERTION se produit sur la première page avec de l’espace disponible même si cela signifie la création d’une nouvelle page. Les suppressions sont effectuées en désallouant l’espace occupé par la ligne.

Les mises à jour sont là où les choses se dégradent pour les tas. Lorsqu’un type de données de longueur fixe est mis à jour, la mise à jour se produit en place. Si un type de données de longueur variable est mis à jour et que la longueur a augmenté, il est possible que la ligne ne rentre plus dans la même page. Lorsque la ligne ne correspond plus, un pointeur de transfert est placé dans l’espace où se trouvait la ligne d’origine et la ligne est déplacée vers une nouvelle page. Ce processus a un impact négatif sur les performances de lecture. Démontrons.

À l’origine tous nos enregistrements dans le dbo.la table de démonstration contenait des VARCHARs avec 9 chaînes de caractères.

SÉLECTIONNEZ TOP 5*
DE dbo.demo

 données de table de démonstration

Pour créer des pointeurs vers l’avant, nous mettrons à jour toutes nos lignes et augmenterons la longueur de la colonne varLen.

mise à jour dbo.demo
set varLen= replicate(‘a’,100)

Nous avons maintenant 95 pages dans notre tas car toutes nos lignes ont été déplacées vers de nouvelles pages avec des pointeurs avant laissés.

DBCC IND(‘demo’, ‘dbo.démo’,1)

dbcc-output-1

Comme vous pouvez l’imaginer, avec des tas nécessitant des analyses de table complètes, nous devrons lire beaucoup plus de pages pour le même nombre d’enregistrements maintenant.

DÉFINISSEZ LES STATISTIQUES IO SUR

SÉLECTIONNEZ COUNT(*)
DEPUIS dbo.demo
(1 ligne(s) affectée(s))

Tableau ‘Demo’. Nombre de numérisations 1, lectures logiques 4405, lectures physiques 0, lectures anticipées 0, lectures logiques de lob 0, lectures physiques de lob 0, lectures anticipées de lob 0.

Nos lectures ont considérablement augmenté car les pages d’origine devaient être lues, puis les pointeurs avant devaient être suivis. L’impact sur les performances et le gaspillage d’espace en mémoire et sur disque sont la raison pour laquelle nous avons besoin d’index en cluster.

La prochaine fois

La structure de données de tas n’est pas un index et est souvent la structure de données la moins performante pour vos tables dans SQL Server. Nous l’avons couvert aujourd’hui comme une base de compréhension qui sera utile lorsque nous discuterons des indices. Dans la partie suivante de cette série, les index en cluster, une structure arborescente B+ pour les données de table, seront couverts.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.