行きの通勤電車では DELETE の基本動作を見てみましたので帰りの電車では TRUNCATE の基本動作を見てみたいと思います。
■DELETE と TRUNCATE のログの比較
DELETE も TRUNCATE も対象テーブルからレコードを削除するという操作を行う DML (データ操作言語) になります。
DELETE の場合は WHERE によって条件を指定することができますが、TRUNCATE はテーブル単位でしか実施することができません。
DELETE と TRUNCATE ではパフォーマンスに大きな差が出てきます。
この差がなぜ起きるのかをログを確認しながら見ていきたいと思います。
今回は 10 件のレコードが格納されているテーブルで比較をしています。
まずは DELETE について見ていきたいと思います。
使用したクエリがこちらです。
|
BEGIN TRAN DELETE_TRAN SELECT |
トランザクション名を指定してテーブルの全件 DELETE を実施し、そのトランザクションで出力されたログを取得しています。
実行結果がこちら。 
10 件のレコードをゴーストレコードとして処理しているのが確認できますね。
それでは、同様の処理を TRUNCATE を使用して実施します。
|
BEGIN TRAN TRUNCATE_TRAN SELECT |
実行結果がこちらになります。 
TRUNCATE TABLE もトランザクションとして処理ができますのでトランザクション名から対象のログを取得することが可能です。
DELETE のときとは異なるログが出力されているのが確認できますね。
DELETE の場合は削除対象のレコードを 1 件ずつ削除していきますが TRUNCATE の場合には対象のオブジェクトに割り当てられているページを解除することによりデータの削除を行います。
そのため操作するページは IAM / PFS / GAM というようなシステムアロケーションページとなります。
# システムアロケーションページを操作し、オブジェクトに割り当てられているページを解除します。
今回は 10 件という大変少ないテーブルで実行していますので、ログレコードの量には大差が無いですがデータサイズが増えるにつれこの差はどんどん大きくなってきます。
また、ロックの数に関しても差が出てきます。
DELETE は行単位で削除をしますのでロックに関しても基本は行単位となります。
TRUNCATE の場合はテーブルの内容をすべて削除しますのでロックの基本はテーブル / ページとなります。
この割り当ての解除はページヘッダーからも確認ができます。
TRUNCATE する前のページヘッダーはこのようになっています。
|
Metadata: ObjectId = 373576369 m_prevPage = (0:0) m_nextPage = (0:0) Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL |
実行後の結果がこちら。
|
Metadata: ObjectId = 373576369 m_prevPage = (0:0) m_nextPage Allocation Status GAM (1:2) = NOT ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x0 0_PCT_FULL |
GAM / PFS が [ALLOCATED] の状態ではなくなっていることが確認できます。
ただし、この状態では [m_slotCnt = 10] となっていますので実際のデータはまだ存在します。
これらのページに関しては GHOST CLEANUP で解放されるというわけではなく、割り当て可能なページとして認識がされるので、必要に応じて再利用されるというイメージ見たいですね。
クリーンアップタスクやサービスの再起動をしても大賞のページにはデータは残った状態となっていました。
# LOP_FORMAT_PAGE 操作でページを使用するときにフォーマットされているのだと思いますが。
ログを確認することで普段何気なく使用している操作についての理解を深めることができると思います。
他の操作に関しても時間があるときに少しずつまとめていきたいですね。
[…] SQL Server の DELETE の基本動作を見てみる SQL Server の TRUNCATE TABLE の基本動作を見てみる […]
SQL Server のゴーストクリーンアップタスクの基本動作を見てみる その 1 « SE の雑記
7 4月 11 at 00:01