Diagnostiquer les enregistrements redirigés des heaps SQL

Un heap peut convenir à des données temporaires et se comporter autrement après des mois de modifications. Si une ligne variable s'élargit, sa page initiale peut manquer de place. SQL Server peut déplacer la ligne et conserver une redirection à son ancien emplacement.

Reproduire l'élargissement dans une table de test

Un heap ne possède pas d'index cluster, mais peut avoir des index non cluster. L'exemple définit donc explicitement une clé primaire non cluster pour ne pas changer involontairement la structure. Utilisez une base d'exercice où le nom est libre.

CREATE TABLE dbo.HeapForwardDemo
(
    RowId int NOT NULL PRIMARY KEY NONCLUSTERED,
    Payload varchar(1000) NOT NULL
);
;WITH N AS
(
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.HeapForwardDemo (RowId, Payload)
SELECT n, REPLICATE('a', 100) FROM N;

UPDATE dbo.HeapForwardDemo
SET Payload = REPLICATE('b', 900)
WHERE RowId % 2 = 0;

La charge utile initiale est courte, puis la moitié des lignes s'élargit fortement. Le nombre exact de redirections dépend du placement et de l'espace disponible. Mesurez-le au lieu d'attendre un pourcentage fixe.

SELECT
    index_id, partition_number, alloc_unit_type_desc,
    page_count, record_count,
    forwarded_record_count,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(), OBJECT_ID(N'dbo.HeapForwardDemo'), 0, NULL, 'DETAILED'
)
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

L'inspection cible un seul heap en mode DETAILED. La collecte physique peut effectuer beaucoup d'I/O et exige des droits adaptés. Ne remplacez pas le filtre d'objet par NULL sur une grande production simplement pour obtenir un rapport global.

forwarded_record_count témoigne des déplacements. page_count et l'occupation moyenne des pages complètent la description. Une métrique NULL dans un mode moins détaillé n'est pas zéro. Conservez mode, partition et unité d'allocation avec les mesures.

Relier la structure au travail observé

Les index non cluster d'un heap utilisent des localisateurs pouvant mener à l'ancien emplacement puis à la ligne redirigée. Cette navigation supplémentaire peut coûter, mais son nombre seul ne mesure pas la latence applicative. Chemin d'accès, cache et fréquence de lecture comptent.

Identifiez les requêtes concernées et relevez lectures logiques, CPU, durée et plans réels avec des paramètres représentatifs. Comparez une charge similaire. Si les lignes deviennent aussi neuf fois plus larges, le nombre de pages et leur densité expliquent une partie du changement indépendamment des redirections.

Un index couvrant peut éviter certains accès au heap pour une requête, mais ajoute stockage et maintenance des écritures. D'autres requêtes continueront de lire les lignes de base. Choisissez les index selon les accès utiles, pas pour cacher une structure inexpliquée.

Distinguez également redirections et fragmentation logique d'un B-tree. Un script fondé uniquement sur avg_fragmentation_in_percent peut manquer le problème réel. Un heap demande une interprétation spécifique et des preuves liées à la charge.

Adapter la correction au cycle de vie

Reconstruire le heap peut supprimer les redirections existantes sans empêcher celles des futurs élargissements. Ces commandes ne concernent que l'objet de test. Relancez l'inspection après reconstruction avant de le supprimer.

ALTER TABLE dbo.HeapForwardDemo REBUILD;
-- Run the inspection query again before removing the test table.
DROP TABLE dbo.HeapForwardDemo;

En production, planifiez durée, capacité du journal, espace, verrous et effets sur les index non cluster. Reconstruire seulement un index non cluster ne réorganise pas les lignes de base du heap. Un seuil arbitraire ne justifie pas automatiquement une opération volumineuse.

Un index cluster bien choisi peut convenir à une table durable fréquemment lue par clé et modifiée. Il change toutefois les localisateurs et introduit ses propres compromis de divisions de pages et de largeur de clé. Choisissez une clé stable selon la charge.

Pour des données de staging chargées, traitées puis supprimées, conserver le heap peut être raisonnable. La maintenance suit alors le cycle du lot. Pour des lignes persistantes qui grossissent, examinez la nécessité de ces mises à jour.

Reliez enfin la mesure suivante aux requêtes et à la réapparition du phénomène. Si les redirections reviennent vite sans amélioration de latence après reconstruction, la routine traite surtout un symptôme. Gardez aussi l'âge du lot et le volume de modifications pour choisir une stratégie conforme à la vie réelle de la table.

Références techniques: Microsoft Learn: Heaps · Microsoft Learn: Index physical statistics · Microsoft Learn: ALTER TABLE.