Il existe un mythe persistant dans le monde SQL Server selon lequel les commandes DROP TABLE et TRUNCATE TABLE ne sont pas consignées.
Ils ne sont pas. Ils sont tous deux entièrement connectés, mais efficacement connectés.
Vous pouvez facilement le prouver à vous-même., Exécutez le code suivant pour configurer une base de données de test et de table, et de montrer que nous avons 10 000 lignes de notre tableau:
les Résultats:
—-
10000
puis le code suivant, qui tronque la table dans une transaction et vérifie le nombre de lignes:
BEGIN TRAN;GOTRUNCATE TABLE ;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO
les Résultats:
—-
0
Maintenant que la table est vide., Mais je peux revenir à la transaction, et de mettre toutes les données de retour de nouveau:
ROLLBACK TRAN;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO
les Résultats:
—-
10000
Clairement TRUNCATE fonctionnement devez être connecté sinon reculer le fonctionnement des opérations.
alors d’où vient l’idée fausse?
Il s’agit du comportement de DROP et TRUNCATE opérations sur de grandes tables., Ils se termineront presque instantanément, et si vous regardez dans le journal des transactions en utilisant fn_dblog juste après, vous ne verrez qu’un petit nombre d’enregistrements de journal générés à partir de l’opération. Ce petit nombre ne correspond pas à la taille de la table tronquée ou supprimée, il semble donc que les opérations DROP Et TRUNCATE ne soient pas enregistrées.
Mais ils sont entièrement enregistrés, comme je l’ai démontré ci-dessus. Où sont donc les enregistrements de journal pour les opérations?,
la réponse est que les enregistrements de journal seront créés, mais pas immédiatement, par un mécanisme appelé « drop différé », qui a été ajouté dans SQL Server 2000 SP3.
Lorsqu’une table est supprimée ou tronquée, toutes les pages du fichier de données allouées à la table doivent être désallouées.,uire exclusif de l’allocation de verrouillage sur mesure
Sonde le verrouillage de page pour chaque page dans la mesure où (acquérir le verrou en mode exclusif, et immédiatement, en s’assurant que personne d’autre n’a la page verrouillé)
Ne PAS relâcher le verrou étendue, afin de garantir que personne d’autre ne peut utiliser que mesure
passer à la prochaine mesure
Fin
Comme tous les verrous de mesure ont eu lieu jusqu’à la fin de l’opération, et chaque serrure prend une petite quantité de mémoire, il était possible pour le gestionnaire de verrous à exécuter de mémoire lorsqu’un DROP ou TRUNCATE d’une très grande table s’est produite., Certains clients SQL Server ont commencé à trouver qu’ils rencontraient des conditions de mémoire insuffisante sur SQL Server 2000, car les tables devenaient très grandes et dépassaient largement la croissance de la mémoire système.
le mécanisme de dépôt différé simule l’opérationDROPouTRUNCATE se terminant immédiatement, en décrochant les allocations pour la table et en les plaçant dans la « file d’attente de dépôt différé », pour un traitement ultérieur par une tâche d’arrière-plan. Cette opération de décrochage et de transfert ne génère qu’une poignée d’enregistrements de journal., C’est l’opération qui est effectuée et annulée dans mon exemple de code ci-dessus.
la « tâche d’arrière-plan de dépôt différé » tourne toutes les quelques secondes et désalloue toutes les pages et les étendues de la file d’attente de dépôt différé en petits lots, garantissant que l’opération ne manquera pas de mémoire. Ces désallocations sont toutes entièrement consignées, mais n’oubliez pas que la désallocation d’une page pleine de données ou d’enregistrements d’index n’enregistre pas les suppressions individuelles de ces enregistrements; à la place, la page entière est simplement marquée comme désallouée dans la carte d’octets d’allocation PFS (Page Free Space) correspondante.,
à partir de SQL Server 2000 SP3, lorsque vous effectuez unDROP ouTRUNCATE d’une table, vous ne verrez que quelques enregistrements de journal générés. Si vous attendez une minute ou deux, puis regardez à nouveau dans le journal des transactions, vous verrez que des milliers d’enregistrements de journal ont été générés par l’opération de suppression différée, chaque désallocation d’une page ou d’une étendue. L’opération est entièrement et efficacement connecté.,
Voici un exemple, en utilisant le scénario que nous avons créé ci-dessus:
CHECKPOINT;GOTRUNCATE TABLE ;GOSELECT COUNT (*) AS N'LogRecCount'FROM fn_dblog (NULL, NULL);GO
résultats:
— –
25
comme vous pouvez le voir, il n’y a clairement pas d’enregistrements de journal qui désallouent les 10 000 pages, plus 1 250 étendues dans le tableau testtable.
Si j’attends quelques secondes, puis exécutez le fn_dblog nouveau code, j’obtiens:
—-
3811
vous pourriez Vous demander pourquoi il n’y a pas au moins 10 000 enregistrements de journal – un pour chaque page étant libéré., En effet, les désallocations de page sont même consignées efficacement-avec un enregistrement de journal reflétant les modifications d’allocation de page PFS pour 8 pages consécutives de fichier de données, au lieu d’un enregistrement de journal pour chaque page de fichier de données reflétant son statut d’allocation changeant dans la page PFS.
SQL Server essaie toujours de produire le moins de journal des transactions possible, tout en respectant les règles de journalisation complète ou minimale basées sur le modèle de récupération actuel., Si vous voulez regarder les enregistrements de journal réels générés par les mécanismes de décrochage et de transfert et de dépôt différé, remplacez simplement * par COUNT (*) dans le code fn_dblog ci-dessus et recherchez une transaction dont le nom de Transaction est défini sur DeferredAllocUnitDrop::Process.
dans les prochains articles, je discuterai des éléments internes qui sous-tendent d’autres mythes persistants concernant les aspects de performance du moteur de Stockage SQL Server.
Laisser un commentaire