SQL Server: 変更の追跡を導入することのオーバーヘッドについて調べてみた

12871 ワード
SQLServer DB 検証 SQLServer テキストリンク

背景

こちらの記事で変更の追跡について検証しましたが、より詳細にオーバーヘッドについて調べてみたので結果を共有します。

調査方法

調査クエリ

use [master]
GO
CREATE DATABASE [TESTDB]
GO

-- DB レベルで変更の追跡を有効化
ALTER DATABASE [TESTDB] SET CHANGE_TRACKING = ON
GO

USE [TESTDB]
-- ①変更の追跡を有効化するテーブルを作成
CREATE TABLE ChangeTrackingTBL(
    C1 int identity primary key,
    C2 int,
    C3 varchar(36),
    C4 datetime2(0) DEFAULT (GETDATE())
)
CREATE INDEX  NCIX_ChangeTrackingTBL ON ChangeTrackingTBL (C2)

-- 変更の追跡の有効化 (テーブルレベル)
ALTER TABLE ChangeTrackingTBL ENABLE CHANGE_TRACKING

-- ②変更の追跡を有効化しないテーブルを作成
CREATE TABLE NOTrackingTBL(
    C1 int identity primary key,
    C2 int,
    C3 varchar(36),
    C4 datetime2(0) DEFAULT (GETDATE())
)
CREATE INDEX  NCIX_NOTrackingTBL ON NOTrackingTBL (C2)

-- ③変更の追跡を有効化せず、インデックスを一つ多くもつテーブルを作成
CREATE TABLE IDX_NoTrackingTBL(
    C1 int identity primary key,
    C2 int,
    C3 varchar(36),
    C4 datetime2(0) DEFAULT (GETDATE())
)
CREATE INDEX  NCIX_IDX_NoTrackingTBL ON IDX_NoTrackingTBL (C2)
CREATE INDEX  NCIX_IDX_NoTrackingTBL2 ON IDX_NoTrackingTBL (C3)

テスト用DBを作成し、作成したDBで変更の追跡を有効化します。
つぎに、3パターンのテーブルを作成します。
①変更の追跡を有効化するテーブルを作成
②変更の追跡を有効化しないテーブルを作成
③変更の追跡を有効化せず、インデックスを一つ多くもつテーブルを作成

負荷をかけるクエリの実行

各テーブルにたいして1行単位のINSERT、UPDATE、DELETEをそれぞれ1万回実行しました。

評価クエリ

キャッシュから該当クエリの実行時間を確認することで、各テーブルの更新処理性能について評価します。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100
  DB_NAME(qt.dbid) as database_name
  --query text
  ,qt.TEXT as parent_query
  ,SUBSTRING(qt.TEXT, qs.statement_start_offset / 2, (
      CASE
        WHEN qs.statement_end_offset = - 1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
        ELSE qs.statement_end_offset
      END - qs.statement_start_offset
   ) / 2) as statement
  -- average
  ,total_worker_time / qs.execution_count / 1000 as average_CPU_time_ms
  ,total_elapsed_time / qs.execution_count / 1000 as average_duration_ms
  ,total_physical_reads / qs.execution_count / 1000 as average_physical_reads
  -- execution count
  ,qs.execution_count as execution_count
  -- creation / execution time
  ,last_execution_time
  ,creation_time
  -- total
  ,total_worker_time / 1000 as total_CPU_time_ms
  ,total_elapsed_time / 1000 as total_duration_ms
  ,total_physical_reads / 1000 as total_physical_reads
  -- query plan
  ,qp.query_plan  -- プランもみたいときはコメント外す
  ,qs.plan_handle
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt -- クエリテキスト用
OUTER apply sys.dm_exec_query_plan(plan_handle) as qp -- プランプラン用
WHERE
   qt.TEXT like '%TrackingTBL%'
order by execution_count desc
option (recompile)

ここで、「dm_exec_query_stastの実行時間に変更の追跡テーブルへのINSERT時間の時間ものっかってくるか」については確認が必要だったため、実行プランをチェックしました。

実行プランを見ると変更の追跡用テーブルへのINSERTも入っていることがわかります。
したがって、この方法で評価した結果は妥当であると言えそうです。

調査結果

正確な時間を公開することは禁止されていたはずなので、比率で公開したいと思います。

変更の追跡なし 変更の追跡あり 変更の追跡なし/追加インデックス一つ作成
INSERT CPU時間 1 1.33 1.30
UPDATE CPU時間 1 2.02 34.2
DELETE CPU時間 1 0.84 0.97
変更の追跡なし 変更の追跡あり 変更の追跡なし/追加インデックス一つ作成
INSERT 実行時間 1 1.33 1.29
UPDATE 実行時間 1 2.02 34.2
DELETE 実行時間 1 0.85 0.97

以上の結果から、以下のことが分かりました。

  • INSERTについてはインデックスを1つ作成するのと同じくらいの負荷であるとの認識でよさそう
  • UPDATEについてはインデックスを１つ作成するよりはオーバーヘッドが少なく、変更の追跡を入れないよりは多い、という中間くらいの負荷との認識でOK
    • 今回の検証では、インデックス追加するとUPDATEコストが飛躍的に上昇しました
  • DELETEの処理はどれも変わらなそう
    • 変更の追跡ありのほうが負荷が低いとの値になったが、それはありえないはずなので誤差とみる

まとめ

変更の追跡を導入することのオーバーヘッドは、導入しない状態と比較すると大きくなるが、インデックスを1つ追加することによる影響よりは小さい、と結論づけました。

※その他のオーバーヘッド

定期的に変更の追跡をクリーンアップするジョブの負荷が発生します。
ただ、last page insertしてるはずなので、変更の追跡テーブルへのINSERTと、期限切れのデータを変更の追跡テーブルから削除する処理とのブロッキングは発生しないはずと考えています。