SQL Server では TRUNCATE TABLE を実行する際に「WITH (PARTITIONS())」を使用することでパーティション単位で TRUNCATE TABLE を実行することができ、これにより特定のパーティションのデータを削除することが可能です。
パーティション単位に TRUNCATE TABLE を実行しているため、同時実行性の低下は局所的 (削除対象のパーティションのみに得今日する) ではないかと期待することがあるかもしませんが、動作としてはそのようにはなっていませんので注意点をまとめておきます。
Contents
TRUNCATE TABLE はパーティション指定の有無にかかわらずSCH-M のロックを取得
見出しのとおりですが、SQL Server では TRUNCATE TABLE を実行する際に、TRUNCATE 対象のパーティションの指定有無にかかわらず SCH-M のロックがテーブルに対して取得されます。
tpch の LINEITEM をパーティショニングしたテーブルに対してパーティションで TRUNCATE TABLE を実行した場合、次のようなロックが取得されます。
SELECT OBJECT_NAME(object_id) object_name, index_id, partition_number , reserved_page_count
FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('LINEITEM2')
BEGIN TRAN
TRUNCATE TABLE LINEITEM2 WITH (PARTITIONS(3))
SELECT resource_type, OBJECT_NAME(resource_associated_entity_id) AS object_name, request_mode,resource_description, resource_lock_partition
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_type = 'OBJECT'
ORDER BY resource_lock_partition
ROLLBACK TRAN
「resource_lock_partition」はロック管理のための内部パーティションとなるため、この値はテーブルのパーティションとは関連はありません。(ロックのパーティション化ができる種類のロックは、CPU のスケジューラー単位にロックを取得するという動作が行われますが、これはテーブルのパーティションとは関係なく、あくまでのロック管理のためのパーティションとなります)
今回、パーティション単位の TRUNCATE TABLE を実行していますが、オブジェクトに対して Sch-M が取得されています。
そのため、TRUNCATE を実行しようとしているパーティションだけでなく、テーブル全体にロックが取得されていることになります。
SQL Server のロック競合のついては ロックの互換性 から確認できますが、SCH-M のロックは様々なロックと競合します。
テーブルの操作を行う際には、SCH-S や IS のロックが取得されることが多いのですが、これらのロックと SCH-M は競合しますので、TRUNCATE TABLE の実行は様々な操作と競合が発生し、同時実行性の低下につながる可能性があります。
「TRUNCATE TABLE はパーティション単位で実行してもテーブルに対して SCH-M のロックを取得する」ということが重要なポイントとなります。
TRUNCATE TABLE による同時実行性低下を抑えるには？
前述のとおり TRUNCATE TABLE は SCH-M のロックが取得されます。
TRUNCATE TABLE 自体は瞬時に実行されるため、通常の実行時には影響を与える可能性は低いのですが、「TRUNCATE TABLE 実行時に長時間実行されるクエリが同時に実行されている可能性がある」場合は、同時実行性の低下は気を付ける必要があります。
「レポーティング用の SELECT」や「大量のデータ洗い替え (BULK INSERT / UPDATE)」が実行されている最中に TRUNCATE TABLE を実行すると、SCH-M のロック待機が発生し、この待機が解消しないと後続のセッションから実行されたクエリが、大量にブロックされる可能性があります。
そのため、TRUNCATE TABLE を実行するタイミングでは極力データへのアクセスが発生していないことが望ましい状態となります。
SQL Server では一部の操作 (インデックス操作 / SHRINK FILE / 非同期統計自動更新) では、LOW PRIORITY を設定することができ、これらの操作を実行している際に取得されるロックを低優先にすることで同時実行性の低下を抑えることができる機能があるのですが、残念ながら TRUNCATE TABLE はこのオプションをサポートしていません。
そのため、TRUNCATE TABLE が長時間 SCH-M を取得しないように制御を行うためには、SET LOCK＿TIMEOUT の利用を検討します。
TRUNCATE TABLE を実行する前に、SET LOCK_TIMEOUT を実行しておくことで、ロック競合が発生した際に、TRUNCATE TABLE をタイムアウトさせ、SCH-M を取得している時間を短くすることができます。
-- デフォルトは LOCK_TIMEOUT は -1 のため、クエリタイムアウトが発生するまではロックを要求を保持したままとなる SET LOCK_TIMEOUT 500 -- 500ms TRUNCATE TABLE LINEITEM2 WITH (PARTITIONS(3))
上記のようなクエリで実行することで、ロックタイムアウトを 500ms とすることができ、SCH-M のロックを取得しようとした際に、他のクエリが実行されておりロックが取得できない場合には 500ms 経過後に 1222 のエラーが発生します。
これにより、TRUNCATE TABLE が SCH-M を取得しようとする時間を短時間にし、SCH-M の取得ができない場合の同時実行性の低下が発生する時間を短くすることが可能となります。
同時実行性の低下が懸念されるが、LOW_PRIORITY が使用することができない操作については、このように LOCK_TIMEOUT と併用することを検討するのが良いかと。
今回の TRUNCATE TABLE はエラー時にはトランザクションが残った状態にはなっていませんでしたが、操作によっては内部的にトランザクションが複数設定されており、エラーが発生した後にトランザクションがオープンされた状態になってしまうものもあります。
そのため、トランザクションの状態が気になる操作 (ストアドプロシージャとして操作が提供されているもので発生する可能性が多いです) については、エラーが発生した後に @@TRANCOUNT を実行して、トランザクションカウントが 0 になっているかを確認し、0 以外の場合は ROLLBACK を実行sる / SET XACT_ABORT ON を実行して、トランザクションの全体の自動ロールバックを実行するというようなことも考慮も必要となります。
ロックエスカレーションのレベルをパーティション単位にしていても動作は変わらないか？
SQL Server では、ロックエスカレーションのデフォルトのレベルはテーブルとなっていますが、パーティショニングしているテーブルについては、設定を変更することでロックエスカレーションの単位をパーティションとすることができます。
この設定は ALTER TABLE で変更することができ、「SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )」で変更することができます。
デフォルトは TABLE となっていますが、AUTO にすることで、ロックエスカレーションのレベルをパーティションレベルで許可することができます。
この設定を変更していても残念ながら TRUNCATE TABLE の SCH-M はテーブルに対して取得され、パーティション単位では取得されません。
ただし、この設定は DELETE では有効に動作します。
DELETE でパーティションの範囲を指定して、データ削除を実行した場合、パーティションレベルのロックエスカレーションを許可 (SET (LOCK_ESCALATION=AUTO) されているのであれば、DELETE で大量のデータを削除しようとした際のロックエスカレーションのレベルはパーティションとして実行されます。
データを削除しようとすると削除対象のデータに X のロックが取得され、大量のデータを削除しようとするとロックエスカレーションが発生します。
デフォルトの設定では特定のパーティションの範囲のデータを削除しようとした場合に、ロックのしきい値を超えた場合は、テーブルに対しての X の取得にロックがエスカレーションされます、
パーティションレベルのロックエスカレーションを許可している場合は、エスカレーションした場合はパーティションレベルの X になります。
これにより、操作しているパーティション以外のデータは操作ができる状態でデータの削除を行うことができます。
TRUNCATE TABLE と比較すると、DELETE は処理に時間がかかり、ログの記録量も多くなりますが、「LOCK_TIMEOUT で指定した短時間でも TRUNCATE TABLE による SCH-M の取得が許可できない」という場合には、TRUNCATE TABLE ではなく、パーてょしょんレベルのロックエスカレーション + DELETE を使用するということも検討するkとができるのではないでしょうか。
まとめ
TRUNCATE TABLE はパーティションの指定有無にかかわらず、テーブルに対しての SCH-M のロックが取得されます。
LOCK_TIMEOUT と併用することで、SCH-M の取得を試行する時間を調整することができます。
TRUNCATE TABLE 実行時に該当のテーブルを長時間使用するクエリが存在している可能性がある場合は、LOCK_TIME と TRUNCATE TABLEを併用して、SCH-M が取得できなかった場合の同時実行性の低下を短時間で抑えることも重要です。
パーティションレベルのロックエスカレーション + DELETE の利用のほうが同時時刻性の低下を抑えることができる可能性もあるため、処理時間とロック競合のトレードオフで DELETE を使用することも検討できます。