SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server の TRUNCATE TABLE の基本動作を見てみる

one comment

行きの通勤電車では DELETE の基本動作を見てみましたので帰りの電車では TRUNCATE の基本動作を見てみたいと思います。

■DELETE と TRUNCATE のログの比較


DELETE も TRUNCATE も対象テーブルからレコードを削除するという操作を行う DML (データ操作言語) になります。
DELETE の場合は WHERE によって条件を指定することができますが、TRUNCATE はテーブル単位でしか実施することができません。

DELETE と TRUNCATE ではパフォーマンスに大きな差が出てきます。
この差がなぜ起きるのかをログを確認しながら見ていきたいと思います。

今回は 10 件のレコードが格納されているテーブルで比較をしています。

 

まずは DELETE について見ていきたいと思います。
使用したクエリがこちらです。

BEGIN TRAN DELETE_TRAN
DELETE FROM Table_1
COMMIT TRAN DELETE_TRAN

SELECT
    [Current LSN],[Page ID], [Operation], [Context],
    [Transaction ID], [Transaction Name],
    [Log Record Fixed Length],[Log Record Length]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] =
    (SELECT [Transaction ID] FROM fn_dblog(NULL, NULL)
    WHERE [Transaction Name] = ‘DELETE_TRAN’)

トランザクション名を指定してテーブルの全件 DELETE を実施し、そのトランザクションで出力されたログを取得しています。

実行結果がこちら。
image

10 件のレコードをゴーストレコードとして処理しているのが確認できますね。

それでは、同様の処理を TRUNCATE を使用して実施します。

BEGIN TRAN TRUNCATE_TRAN
TRUNCATE TABLE Table_1
COMMIT TRAN TRUNCATE_TRAN

SELECT
    [Current LSN],[Page ID], [Operation], [Context],
    [Transaction ID], [Transaction Name],
    [Log Record Fixed Length],[Log Record Length]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] =
    (SELECT [Transaction ID] FROM fn_dblog(NULL, NULL)
    WHERE [Transaction Name] = ‘TRUNCATE_TRAN’)

 

実行結果がこちらになります。
image

TRUNCATE TABLE もトランザクションとして処理ができますのでトランザクション名から対象のログを取得することが可能です。

DELETE のときとは異なるログが出力されているのが確認できますね。

DELETE の場合は削除対象のレコードを 1 件ずつ削除していきますが TRUNCATE の場合には対象のオブジェクトに割り当てられているページを解除することによりデータの削除を行います。
そのため操作するページは IAM / PFS / GAM というようなシステムアロケーションページとなります。
# システムアロケーションページを操作し、オブジェクトに割り当てられているページを解除します。

今回は 10 件という大変少ないテーブルで実行していますので、ログレコードの量には大差が無いですがデータサイズが増えるにつれこの差はどんどん大きくなってきます。

また、ロックの数に関しても差が出てきます。
DELETE は行単位で削除をしますのでロックに関しても基本は行単位となります。
TRUNCATE の場合はテーブルの内容をすべて削除しますのでロックの基本はテーブル / ページとなります。

この割り当ての解除はページヘッダーからも確認ができます。
TRUNCATE する前のページヘッダーはこのようになっています。

Metadata: ObjectId = 373576369      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 36                        m_slotCnt = 10                      m_freeCnt = 7686
m_freeData = 486                    m_reservedCnt = 0                   m_lsn = (55:410:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 912887262              DB Frag ID = 1                     

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED 

 

実行後の結果がこちら。

Metadata: ObjectId = 373576369      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 36                        m_slotCnt = 10                      m_freeCnt = 7686
m_freeData = 486                    m_reservedCnt = 0                   m_lsn = (55:410:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 912887262              DB Frag ID = 1                     

Allocation Status

GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x0   0_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED    

GAM / PFS が [ALLOCATED] の状態ではなくなっていることが確認できます。
ただし、この状態では [m_slotCnt = 10] となっていますので実際のデータはまだ存在します。

これらのページに関しては GHOST CLEANUP で解放されるというわけではなく、割り当て可能なページとして認識がされるので、必要に応じて再利用されるというイメージ見たいですね。
クリーンアップタスクやサービスの再起動をしても大賞のページにはデータは残った状態となっていました。
# LOP_FORMAT_PAGE 操作でページを使用するときにフォーマットされているのだと思いますが。

ログを確認することで普段何気なく使用している操作についての理解を深めることができると思います。
他の操作に関しても時間があるときに少しずつまとめていきたいですね。

Written by masayuki.ozawa

4月 5th, 2011 at 9:28 pm

Posted in SQL Server

Tagged with

One Response to 'SQL Server の TRUNCATE TABLE の基本動作を見てみる'

Subscribe to comments with RSS or TrackBack to 'SQL Server の TRUNCATE TABLE の基本動作を見てみる'.

  1. […] SQL Server の DELETE の基本動作を見てみる SQL Server の TRUNCATE TABLE の基本動作を見てみる […]

Leave a Reply

*