Modes de récupération et journal de transactions, épisode 2

Jeudi, décembre 11, 2008
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [70 article(s)]

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:

  1. SELECT INTO
  2. BCP IN / BULK INSERT
  3. ALTER INDEX … REBUILD
  4. 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 :

  1. Un SELECT INTO d’une table de 2,6 millions de lignes dans une table d’archive.
  2. Le rebuild d’un index clusterisé sur une table de 2,6 millions de lignes.

Dans chaque cas, nous allons mesurer plusieurs choses :

  1. Le temps d’exécution de l’opération.
  2. L’impact sur le journal de transactions (en Mb).
  3. 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, stats

Ensuite 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          6400

Et 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             0

Quasiment 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        328272
LOP_MODIFY_ROW                  1570        138852
LOP_COUNT_DELTA                 33          6864
LOP_BUF_WRITE                   31          59512
LOP_INSERT_ROWS                 27          4068
LOP_ROOT_CHANGE                 5           480
LOP_BEGIN_XACT                  4           432
LOP_HOBT_DELTA                  4           256
LOP_COMMIT_XACT                 4           208
LOP_LOCK_XACT                   3           120
LOP_HOBT_DDL                    2           72
LOP_PREP_XACT                   1           68
LOP_END_CKPT                    1           136
LOP_FORMAT_PAGE                 1           84
LOP_BEGIN_CKPT                  1           96
LOP_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 :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: ,

Leave a Reply