Dans l’article précédent, nous avons vu quels sont les principaux modes de récupération SIMPLE et FULL, et leur effet respectif sur le journal des transactions.
Ici nous allons aborder le troisième mode BULK-LOGGED, ses avantages, inconvénients et contextes d’utilisation. Quelques rappels du post précédent:
BULK-LOGGED: c’est un mode à combiner avec le mode FULL. Dans ce mode, les opérations de modification de masse ne sont pas complètement journalisées, et l’impact de telles opérations sur le journal est limité. Le fait de changer de FULL à BULK-LOGGED ne modifie en rien ma façon de sauvegarder mes bases. En mode BULK-LOGGED, mes sauvegardes transactionnelles horaires continuent de s’exécuter sans problème. Très utile lorsqu’on lance notre réorganisation des indexes hebdomadaire car on gagne un temps fou.
Quelles sont ces opérations de modification de masse:
- SELECT INTO
- BCP IN / BULK INSERT
- ALTER INDEX … REBUILD
- Manipulation de champs de type TEXT:
On les appelle parfois par abus de langage opérations non loggées, le terme plus juste est minimallement loggées.
Lorsqu’une de ces opérations minimalement loggée est exécutée, seules les allocations de pages sont journalisées, mais elles seules ne suffisent pas à restaurer l’intégralité de mon reindex ou de mon select into en cas de crash. C’est pourquoi pour permettre de restaurer ces données modifiées, la sauvegarde embarque avec elle les pages de données allouées lors de l’opération.
Pour le vérifier, on se propose de comparer sous les deux modes de récupération FULL et BULK_LOGGED, deux opérations minimalement loggées :
- Un SELECT INTO d’une table de 2,6 millions de lignes dans une table d’archive.
- Le rebuild d’un index clusterisé sur une table de 2,6 millions de lignes.
Dans chaque cas, nous allons mesurer plusieurs choses :
- Le temps d’exécution de l’opération.
- L’impact sur le journal de transactions (en Mb).
- L’impact sur la taille de ma sauvegarde de transaction (en Mb).
1) En mode de récupération FULL :
Pour pouvoir exécuter la sauvegarde du journal à l’issue de chaque opération, il faut commencer par exécuter une sauvegarde complète (voir article précédent) :
ALTER DATABASE CAPDATA SET RECOVERY FULL
BACKUP DATABASE CAPDATA TO DISK='C:\YUKON\BACKUP\CAPDATA.bak'
WITH init, statsEnsuite on va vérifier que l’on part sur un journal vide :
dbcc sqlperf(logspace) Database Name Log Size (MB) Log Space Used (%) Status ---------------------------------------------------------------------------------------------- CAPDATA 49,99219 2,951633 0
Le journal fait 50Mb, il est vide (2%). On peut donc lancer le SELECT INTO :
set statistics time on
select * into PIECEAPPARTEMENT2 from PIECEAPPARTEMENT
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 48 ms. SQL Server Execution Times: CPU time = 7641 ms, elapsed time = 57673 ms. (2644317 row(s) affected)Quelle est la taille du journal ?
dbcc sqlperf(logspace)
Database Name Log Size (MB) Log Space Used (%) Status
----------------------------------------------------------------------------------------------
CAPDATA 97,55469 93,43568 0
Il est passé de 50 à 97 Mb, et il est plein. Sauvegardons la transaction pour le vider :
BACKUP LOG CAPDATA TO DISK='C:\YUKON\BACKUP\CAPDATA.trn' with init, stats
(…)BACKUP LOG successfully processed 11592 pages in 25.525 seconds (3.720 MB/sec).11592 * 8192 ~= 93 Mb, soit la taille de ma transaction dans mon journal. Normal. Une fois la sauvegarde effectuée, on repart sur un journal vide. On peut donc lancer immédiatement le REBUILD de l’index sur PIECEAPPARTEMENT :
ALTER INDEX PK_PIECEAPPARTEMENT ON PIECEAPPARTEMENT REBUILD WITH (SORT_IN_TEMPDB = ON)
SQL Server Execution Times: CPU time = 11828 ms, elapsed time = 66788 ms.A nouveau le journal est plein et si je le sauvegarde encore, la taille du fichier sera égale à la taille de ma transaction.
dbcc sqlperf(logspace)
Database Name Log Size (MB) Log Space Used (%) Status----------------------------------------------------------------------------------------------CAPDATA 97,55469 92,42763 0
2) En mode de récupération BULK_LOGGED :
Même scénario mais cette fois la base est passée en mode de récupération BULK_LOGGED, et mon journal est vide. Pour partir sur la même enveloppe initiale, et ne pas fausser les résultats pour le SELECT INTO, je vais réduire la taille de mon journal à sa taille de départ (50Mb) :
dbcc shrinkfile('CAPDATA_log01',50)
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages------ ----------- ----------- ----------- ----------- --------------6 2 6400 6400 6400 6400Et je lance le SELECT INTO :
SQL Server Execution Times: CPU time = 8235 ms, elapsed time = 24353 ms. (2644317 row(s) affected)Déjà, il a mis deux fois moins de temps. Voyons voir la situation du journal :
dbcc sqlperf(logspace)
Database Name Log Size (MB) Log Space Used (%) Status----------------------------------------------------------------------------------------------CAPDATA 49,99219 3,073722 0Quasiment rien n’a été utilisé !! Non seulement l’enveloppe n’a pas bougé mais en plus mon journal est toujours quasiment vide. J’en profite pour regarder ce qui se trouve dans ces 3% de journal utilisé:
select Operation, COUNT(*) nr_records, SUM([Log Record Length]) totalsize
from ::fn_dblog(null,null)
group by Operation order by COUNT(*) desc
Operation nr_records totalsize------------------------------- ----------- -----------LOP_SET_FREE_SPACE 11111 577772
LOP_SET_BITS 5650 328272LOP_MODIFY_ROW 1570 138852LOP_COUNT_DELTA 33 6864LOP_BUF_WRITE 31 59512LOP_INSERT_ROWS 27 4068LOP_ROOT_CHANGE 5 480LOP_BEGIN_XACT 4 432LOP_HOBT_DELTA 4 256LOP_COMMIT_XACT 4 208LOP_LOCK_XACT 3 120LOP_HOBT_DDL 2 72LOP_PREP_XACT 1 68LOP_END_CKPT 1 136LOP_FORMAT_PAGE 1 84LOP_BEGIN_CKPT 1 96LOP_CREATE_ALLOCCHAIN 1 40
L’opérateur logique LOP_SET_FREE_SPACE est typiquement un opérateur d’allocation, qui met à jour la page PFS du fichier de donnée et indique quels sont les pages qui ne sont plus libres pour de futures allocations car elles ont été utilisées par le SELECT INTO. Si je lance la sauvegarde de mon journal :
Processed 149 pages for database 'CAPDATA', file 'CAPDATA_log01' on file 1.BACKUP LOG successfully processed 11277 pages in 28.242 seconds (3.270 MB/sec).La sauvegarde fait 90Mb alors qu’il n’y a que 1,5Mb de transactions dans mon journal, parce qu’elle embarque les pages de données allouées par le SELECT INTO.
Exécutons maintenant le rebuild de l’index :
ALTER INDEX PK_PIECEAPPARTEMENT ON PIECEAPPARTEMENT REBUILD WITH (SORT_IN_TEMPDB = ON)
SQL Server Execution Times: CPU time = 11594 ms, elapsed time = 48860 ms.
48 secondes au lieu de 66, et dans le journal:
dbcc sqlperf(logspace)
Database Name Log Size (MB) Log Space Used (%) Status----------------------------------------------------------------------------------------------CAPDATA 49,99219 5,8515 0
Et la sauvegarde de journal fait 91 Mb, loin des 5% utilisés. Là encore, les pages de données créées lors du rebuild sont embarquées dans la sauvegarde.
Conclusion: en mode BULK_LOGGED, pour pouvoir restaurer une opération minimalement loggée, le BACKUP LOG est obligé de prendre avec lui les pages de données allouées dans l’opération. Ici, on ne voit pas de différence de taille des sauvegardes de journal entre les modes FULL et BULK_LOGGED, mais si en plus des opérations minimalement loggées, j’ai des ordres de modifications standards type INSERT/DELETE,UPDATE, la taille de ma sauvegarde va croître (et le temps de sauvegarde avec) en BULK_LOGGED et dépasser la taille d’une sauvegarde en mode FULL. Donc attention à l’espace disponible sur les disques de sauvegardes le week end pendant la reorg !
David B.
Continuez votre lecture sur le blog :
- Modes de récupération et journal de transactions, épisode 1 (David Baffaleuf) [SQL ServerVintage]
- How-To: réduire la taille du journal de transactions sur disque (David Baffaleuf) [SQL Server]
- Suppression accidentelle de ligne : comment retrouver le coupable ? (David Baffaleuf) [SQL Server]
- Récupérer l’espace consommé par le versionning de lignes (David Baffaleuf) [SQL Server]
- Point-in-time recovery et fn_dump_dblog() (David Baffaleuf) [SQL Server]