Utilisation de la ressource de versioning de ligne
La structure de gestion des versions de ligne prend en charge les fonctionnalités suivantes dans SQL Server :
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 dans 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.
Lorsque tempdb n'a plus d'espace, 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, vous devrez peut-être allouer un espace disque suffisant 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. Par exemple, si la longueur de ligne moyenne est de 100 octets, les 14 octets supplémentaires peuvent provoquer une augmentation de 14 pour cent de la table existante.
La réduction du facteur de remplissage ms177459(v=sql.100).md peut permettre d'empêcher ou de réduire la fragmentation des pages d'index. Pour afficher les paramètres de fragmentation des données et des index de la table ou vue spécifiée, vous pouvez utiliser DBCC SHOWCONTIG.
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) au 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 la gestion des versions de ligne lorsqu'une base de données est mise à niveau vers SQL Server à 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 de la banque des versions
SQL Server fournit des outils pour le contrôle du versioning de ligne, de la banque des 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 sur la banque des 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 (Transact-SQL).
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 (Transact-SQL).
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 (Transact-SQL).
sys.dm_tran_top_version_generators. Renvoie une table virtuelle pour les objets générant la majorité des versions dans la banque des versions. Agrégation des 256 premières longueurs d'enregistrement selon database_id et rowset_id. Utilisez cette fonction pour rechercher les clients les plus volumineux de la banque des versions. Pour plus d'informations, consultez sys.dm_tran_top_version_generators (Transact-SQL).
sys.dm_tran_version_store. Renvoie une table virtuelle qui affiche tous les enregistrements de version dans la banque des versions commune. Pour plus d'informations, consultez sys.dm_tran_version_store (Transact-SQL).
[!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 la banque des versions entière, qui peut s'avérer très volumineuse.
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 (Transact-SQL).
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 (Transact-SQL).
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 (Transact-SQL).
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 (Transact-SQL).
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 la banque des 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 kilo-octets (Ko), d'espace libre dans la base de données tempdb. tempdb doit disposer d'un espace libre suffisant pour gérer la banque des versions prenant en charge l'isolement de capture instantanée.
La formule ci-dessous vous donne une estimation grossière de la taille de la banque des versions. Pour estimer la taille maximale de la banque des 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 de la banque des versions commune] = 2 * [données de la banque des versions générées par minute] * [délai le plus long d'exécution (en minutes) de la 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 une autre banque des versions. La taille approximative de la banque des versions utilisée 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 de la banque des versions (Ko). Contrôle la taille en Ko de toutes les banques des versions. Cette information permet de déterminer la quantité d'espace nécessaire dans la base de données tempdb pour la banque des 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 toutes les banques des versions.
Taux de nettoyage de version (Ko/s). Contrôle le taux de nettoyage de version en Ko par seconde pour toutes les banques des 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 la banque des versions. Contrôle le nombre d'unités dans la banque des versions.
Création d'unité dans la banque des versions. Contrôle le nombre total d'unités créées dans la banque des versions pour le stockage des versions de lignes depuis le démarrage de l'instance.
Troncation d'unité dans la banque des versions. Contrôle le nombre total d'unités tronquées dans la banque des versions depuis le démarrage de l'instance. Une unité de banque des versions est tronquée lorsque SQL Server spécifie qu'aucune des lignes de versions stockées dans l'unité de la banque des 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