Utilisation de la ressource de versioning de ligne

Mis à jour : 5 décembre 2005

La structure de versioning de ligne prend en charge les fonctionnalités suivantes dans MicrosoftSQL Server 2005 :

• Déclencheurs
• MARS (Multiple Active Results Sets)
• Indexation en ligne

La structure de versioning de ligne prend également en charge les niveaux d'isolement des transactions basé sur le versioning de ligne, qui sont désactivés par défaut :

• Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est activée (ON), les transactions READ_COMMITTED permettent une lecture cohérente au niveau des instructions grâce au versioning de ligne.
• Lorsque l'option de base de données ALLOW_SNAPSHOT_ISOLATION est activée (ON), les transactions SNAPSHOT permettent une lecture cohérente au niveau des transactions grâce au versioning de ligne.

Les niveaux d'isolement basé sur le versioning de ligne réduisent le nombre de verrous obtenus par la transaction en supprimant l'utilisation des verrous partagés dans les opérations de lecture. Les performances système sont ainsi accrues et les ressources nécessaires à la gestion des verrous diminuées. La réduction des blocages d'une transaction par des verrous obtenus par d'autres transactions permet également d'augmenter les performances.

Les niveaux d'isolement basé sur le versioning de ligne augmentent les ressources nécessaires pour la modification de données. L'activation de ces options induit automatiquement le versioning de toutes les modifications apportées aux données de la base de données. Une copie des données avant modification est stockée dans tempdb, même s'il n'existe aucune transaction active utilisant l'isolement basé sur le versioning de ligne. Les données modifiées contiennent un pointeur vers les données de version stockées dans tempdb. En ce qui concerne les objets volumineux, seule la partie de l'objet ayant été modifiée est copiée dans tempdb.

Espace occupé dans tempdb

Pour toute instance du moteur de base de données, tempdb doit disposer d'un espace suffisant pour conserver les versions de ligne générées pour chaque base de données de l'instance. L'administrateur de base de données doit s'assurer que tempdb dispose de suffisamment d'espace pour la prise en charge de la banque des versions. tempdb intègre deux banques des versions :

• la banque des versions de construction d'index en ligne, utilisée pour les constructions d'index en ligne dans l'ensemble des bases de données ;
• la banque des versions commune, utilisée pour toutes les autres opérations de modification des données dans l'ensemble des bases de données.

Les versions de ligne doivent être stockées pour toute la durée au cours de laquelle une transaction active doit être accessible. Chaque minute, un thread d'arrière-plan supprime les versions de ligne qui ne sont plus nécessaires et libère de l'espace dans tempdb. Une transaction longue empêche la libération d'espace dans une banque des versions si l'une des conditions suivantes est remplie :

• elle utilise l'isolement basé sur le versioning de ligne ;
• elle utilise des déclencheurs, des jeux MARS ou des opérations de construction d'index en ligne ;
• elle génère des versions de ligne.

Remarque :

Quand un déclencheur est appelé au sein d'une transaction, les versions de ligne créées par le déclencheur sont conservées jusqu'à la fin de la transaction, même si les versions de ligne ne sont plus nécessaires après l'exécution du déclencheur. Ce point s'applique aussi aux transactions à lecture validée qui utilisent le contrôle de version de ligne. Dans ce type de transaction, une vue cohérente sur le plan transactionnel de la base de données n'est nécessaire que pour chaque instruction de la transaction. Cela signifie que les versions de ligne créées pour une instruction de la transaction ne sont plus nécessaires une fois l'instruction exécutée. Cependant, les versions de ligne créées par chaque instruction de la transaction sont conservées jusqu'à la fin de la transaction.

En cas d'espace insuffisant pour tempdb, le moteur de base de données force la réduction des banques des versions. Lors de ce processus de réduction, les transactions les plus longues n'ayant pas encore généré de versions de ligne sont marquées comme victimes. Le message 3967 est inscrit dans le journal d'erreurs pour chaque transaction victime. Toute transaction marquée comme victime ne peut plus lire les versions de ligne de la banque des versions. En cas de tentative de lecture des versions de ligne, le message 3966 est généré et la transaction est restaurée. En cas de réussite du processus de réduction, l'espace est disponible dans tempdb. Dans le cas contraire, l'espace de tempdb devient insuffisant, avec les conséquences suivantes :

• L'exécution des opérations d'écriture se poursuit, mais sans génération de versions. Un message d'information (3959) apparaît dans le journal d'erreurs. La transaction d'écriture des données n'en est pas affectée.
• Les transactions qui tentent d'accéder aux versions de ligne n'ayant pas été générées à cause d'une restauration complète dans tempdb se terminent sur l'erreur 3958.

Espace occupé dans les lignes de données

Chaque ligne de base de données peut, à des fins d'informations sur le versioning de ligne, utiliser un maximum de 14 octets en fin de ligne. Les informations sur le versioning de ligne contiennent le numéro de séquence de la transaction ayant validé la version et le pointeur vers la ligne avec version. Ces 14 octets sont ajoutés lors de la première modification de la ligne ou lors de l'insertion d'une nouvelle ligne, pour autant que l'une des conditions suivantes soit remplie :

• l'option READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION est activée (ON) ;
• la table comporte un déclencheur ;
• des jeux MARS (Multiple Active Results Sets) sont en cours d'utilisation ;
• des opérations de construction d'index en ligne sont en cours d'exécution dans la table.

Ces 14 octets sont supprimés de la ligne de base de données lors de la première modification de la ligne, pour autant que toutes les conditions suivantes soient remplies :

• les options READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION sont désactivées (OFF) ;
• le déclencheur n'existe plus dans la table ;
• les jeux MARS ne sont pas en cours d'utilisation ;
• aucune opération de construction d'index en ligne n'est en cours d'exécution.

En cas d'utilisation de l'une des fonctionnalités de versioning de ligne, un espace disque suffisant doit être alloué pour permettre les 14 octets nécessaires par ligne de base de données. L'ajout d'informations sur le versioning de ligne peut entraîner le fractionnement des pages d'index ou l'allocation d'une nouvelle page de données en cas d'insuffisance d'espace disponible sur la page actuelle.

Espace occupé dans les objets volumineux

Le moteur de base de données SQL Server prend en charge six types de données pouvant contenir des chaînes volumineuses d'une longueur de 2 gigaoctets (Go) maximum : nvarchar(max), varchar(max), varbinary(max), ntext, text et image. Les chaînes volumineuses stockées à l'aide de ces types de données sont stockées dans une série de fragments de données associés à la ligne de données. Les informations sur le versioning de ligne sont stockées dans chaque fragment utilisé pour le stockage des chaînes volumineuses. Les fragments de données sont un ensemble de pages dédiées aux objets volumineux d'une table.

Lorsque des valeurs importantes sont ajoutées dans une base de données, elles sont allouées avec un maximum de 8 040 octets de données par fragment. Les versions antérieures du moteur de base de données pouvaient stocker jusqu'à 8 080 octets de données ntext, text ou image par fragment.

Les données des objets volumineux (LOB) ntext, text et image existants ne sont pas mises à jour pour libérer de l'espace pour les informations sur le versioning de ligne en cas de mise à niveau d'une base de données à SQL Server 2005 à partir d'une version antérieure de SQL Server. Cependant, lors de leur première modification, les données LOB sont mises à niveau de manière dynamique pour permettre le stockage des informations sur le versioning, même si des versions de lignes sont générées. Une fois la mise à niveau des données LOB terminée, le nombre maximum d'octets stockés par fragment passe de 8 080 à 8 040. Le processus de mise à niveau équivaut à supprimer la valeur LOB et à réinsérer la même valeur. Les données LOB sont mises à niveau même en cas de modification d'un seul octet. Cette opération est unique pour chaque colonne ntext, text ou image Chaque opération peut néanmoins générer une quantité importante d'allocations de pages et d'activité E/S selon la taille des données LOB, ainsi qu'une activité importante d'écriture dans le journal si la modification doit être écrite en entier dans le journal. Les opérations WRITETEXT et UPDATETEXT sont écrites dans le journal de façon minimale si le mode de récupération de la base de données n'est pas défini sur FULL.

Les types de données nvarchar(max), varchar(max) et varbinary(max) ne sont pas disponibles dans les versions antérieures de SQL Server. Vous ne rencontrerez pas conséquent aucun problème de mise à niveau.

Un espace disque suffisant doit être alloué pour satisfaire à cette exigence.

Contrôle du versioning de ligne et du magasin de versions

SQL Server fournit des outils pour le contrôle du versioning de ligne, du magasin de versions et des processus d'isolement de capture instantanée : les vues DMV (Dynamic Management Views) et les compteurs de performances dans le Moniteur système Windows.

Vues DMV

Les vues DMV suivantes fournissent des informations sur l'état système actuel de tempdb et du magasin de versions, ainsi que sur les transactions utilisant le versioning de ligne.

sys.dm_db_file_space_usage. Renvoie les informations sur l'espace occupé pour chaque fichier de la base de données. Pour plus d'informations, consultez sys.dm_db_file_space_usage.

sys.dm_db_session_space_usage. Renvoie les activités d'allocation ou de désallocation des pages par session de la base de données. Pour plus d'informations, consultez sys.dm_db_session_space_usage.

sys.dm_db_task_space_usage. Renvoie les activités d'allocation ou de désallocation des pages par tâche de la base de données. Pour plus d'informations, consultez sys.dm_db_task_space_usage.

sys.dm_tran_top_version_generators. Renvoie une table virtuelle pour les objets générant la majorité des versions d'un magasin de versions. Elle regroupe les 256 premières longueurs d'enregistrements agrégés par database_id et rowset_id. Utilisez cette fonction pour rechercher les clients les plus volumineux du magasin de versions. Pour plus d'informations, consultez sys.dm_tran_top_version_generators.

sys.dm_tran_version_store. Renvoie une table virtuelle qui affiche tous les enregistrements de version du magasin de versions commun. Pour plus d'informations, consultez sys.dm_tran_version_store.

Remarque :
sys.dm_tran_top_version_generators et sys.dm_tran_version_store sont des fonctions potentiellement très compliquées à exécuter dans la mesure où elles interrogent toutes deux le magasin de versions entier, qui peut s'avérer très volumineux.

sys.dm_tran_active_snapshot_database_transactions. Retourne une table virtuelle pour toutes les transactions actives dans l'ensemble des bases de données d'une instance de SQL Server utilisant le versioning de ligne. Les transactions système n'apparaissent pas dans cette vue DMV. Pour plus d'informations, consultez sys.dm_tran_active_snapshot_database_transactions.

sys.dm_tran_transactions_snapshot. Renvoie une table virtuelle qui affiche les captures instantanées prises par chaque transaction. La capture instantanée contient le numéro de séquence des transactions actives utilisant le versioning de ligne. Pour plus d'informations, consultez sys.dm_tran_transactions_snapshot.

sys.dm_tran_current_transaction. Renvoie une ligne unique affichant des informations sur l'état du versioning de ligne pour la transaction de la session en cours. Pour plus d'informations, consultez sys.dm_tran_current_transaction.

sys.dm_tran_current_snapshot. Retourne une table virtuelle affichant toutes les transactions actives au début de la transaction d'isolement de capture instantanée. Si la transaction actuelle utilise l'isolement de capture instantanée, cette fonction ne renvoie aucune ligne. sys.dm_tran_current_snapshot est similaire à sys.dm_tran_transactions_snapshot, mis à part qu'elle renvoie uniquement les transactions actives pour la capture instantanée actuelle. Pour plus d'informations, consultez sys.dm_tran_current_snapshot.

Compteurs de performances

Les compteurs de performances de SQL Server fournissent des informations sur les performances système affectées par les processus de SQL Server. Les compteurs de performances suivants contrôlent tempdb et le magasin de versions, ainsi que les transactions utilisant le versioning de ligne. Les compteurs de performances se trouvent dans l'objet de performances SQLServer:Transactions.

Espace disponible dans tempdb (Ko). Contrôle la quantité, en kilooctets (Ko), d'espace libre dans la base de données tempdb. tempdb doit disposer d'un espace libre suffisant pour gérer le magasin de versions prenant en charge l'isolement de capture instantanée.

La formule ci-dessous vous donne une estimation grossière de la taille du magasin de versions. Pour estimer la taille du magasin de versions en ce qui concerne les transactions longues, il peut s'avérer utile de contrôler les taux de génération et de nettoyage.

[taille du magasin de versions commune] = 2 * [données de magasin de versions générées par minute] * [délai le plus long d'exécution (en minutes) de transaction]

Le délai le plus long d'exécution de transaction ne doit pas inclure les constructions d'un index en ligne. Étant donné que ces dernières opérations peuvent prendre un certain temps pour les tables volumineuses, elles utilisent un autre magasin de versions. La taille approximative du magasin de versions utilisé pour les constructions d'un index en ligne équivaut à la quantité de données modifiées dans la table, y compris tous les index, pendant toute la durée d'activité de la construction de l'index en ligne.

Taille du magasin de versions (Ko). Contrôle la taille en Ko de tous les magasins de versions. Cette information permet de déterminer la quantité d'espace nécessaire dans la base de données tempdb pour le magasin de versions. Le contrôle de ce compteur sur une période de temps fournit une estimation utile de l'espace supplémentaire requis pour tempdb.

Taux de génération de version (Ko/s). Contrôle le taux de génération de version en Ko par seconde pour tous les magasins de versions.

Taux de nettoyage de version (Ko/s). Contrôle le taux de nettoyage de version en Ko par seconde pour tous les magasins de versions.

Remarque :
Les informations obtenues à l'aide des compteurs Taux de génération de version (Ko/s) et Taux de nettoyage de version (Ko/s) permettent de prévoir les exigences requises par tempdb en terme d'espace.

Nombre d'unités dans le magasin de versions. Contrôle le nombre d'unités dans le magasin de versions.

Création d'unité dans le magasin de versions. Contrôle le nombre total d'unités créées dans le magasin de versions pour le stockage des versions de lignes depuis le démarrage de l'instance.

Troncation d'unité dans le magasin de versions. Contrôle le nombre total d'unités tronquées dans le magasin de versions depuis le démarrage de l'instance. Une unité de magasin de versions est tronquée lorsque SQL Server spécifie qu'aucune des lignes de versions stockées dans l'unité du magasin de versions n'est requise pour l'exécution des transactions actives.

Proportion de conflits de mise à jour. Contrôle la proportion de transactions de captures instantanées de mise à jour présentant des conflits de mise à jour par rapport au nombre total de transactions de captures instantanées de mise à jour.

Délai le plus long d'exécution de transaction. Contrôle le délai le plus long (en secondes) d'exécution de toute transaction utilisant le versioning de ligne. Ce compteur permet de déterminer si l'exécution de l'une des transactions est trop longue.

Transactions. Contrôle le nombre total de transactions actives. Les transactions système ne sont pas prises en compte.

Transactions de captures instantanées. Contrôle le nombre total de transactions de captures instantanées actives.

Transactions de captures instantanées de mise à jour. Contrôle le nombre total de transactions de captures instantanées effectuant des opérations de mise à jour.

Transactions de versions non liées à des captures instantanées. Contrôle le nombre total de transactions actives non liées à des captures instantanées générant des enregistrements de versions.

Remarque :

La somme des compteurs Transactions de captures instantanées de mise à jour et Transactions de versions non liées à des captures instantanées représente le nombre total de transactions participant à la génération d'une version. La différence entre les compteurs Transactions de captures instantanées et Transactions de captures instantanées de mise à jour indique le nombre de transactions de captures instantanées en lecture seule.

Voir aussi

Tâches

Résolution des problèmes d'espace disque insuffisant dans tempdb

Concepts

Présentation des niveaux d'isolement basés sur le versioning de ligne
Choix des niveaux d'isolement selon le versioning de ligne
Utilisation de niveaux d'isolement basés sur la gestion de la version des lignes
Utilisation de types de données de valeur élevée
Utilisation des données de type text et image

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version	Historique
5 décembre 2005	Contenu modifié : Ajout d'une remarque sur la conservation de la version de ligne pour les déclencheurs et les transactions à lecture validée qui utilisent le contrôle de version de ligne.