Skip to content

MS_SQLServerBulkDataPerformance

Jump to bottom
nishi_74322014 edited this page Aug 18, 2026 · 2 revisions

SQL Server 大量データ処理時の性能問題

概要

大量データの追加・更新に伴って発生する性能問題と、その対処について。

詳細

先ず、統計情報の更新

大量データ処理時の性能問題は、以下で解決する可能性があります。

補足（なぜ真っ先に統計情報なのか）: 自動更新のしきい値に達していても、
統計の更新は次にそのテーブルを参照するクエリのコンパイル時に走る。
つまり、大量投入直後の最初のバッチは
投入前の統計で作られたプランを使ってしまう可能性がある。
「昨日まで数分で終わっていた夜間バッチが、
大量データ投入日だけ何時間もかかる」の典型的な原因である。

投入処理の末尾に明示的な更新を入れておくのが確実。

UPDATE STATISTICS dbo.LargeTable WITH FULLSCAN;

詳細はSQL Server のオプティマイザを参照。

トランザクション ログ モード

完全復旧モデル

全てのトランザクションが、「トランザクション ログ」に記録されるため遅い。

一括ログ 復旧モデル

  • 特定の大規模な操作を除いた、全てのトランザクションが記録される。
  • 「特定の大規模な操作」とは「bcp ユーティリティ、BULK INSERT・SELECT INTO、Index 系」
    などになる。

単純復旧モデル

  • トランザクションが記録されない
    （正確には、チェックポイント後に不要なログは削除される）。
  • 「ログバックアップ不可 → ポイントインタイム復旧不可 → データ損失リスク大」
    と言うデメリットがある。

補足（一括ログ復旧モデルの正しい使い方）: 「最小ログ記録」が効くのは
復旧モデルだけでなく、操作側の条件も満たす必要がある。

  • BULK INSERT / bcp / INSERT ... SELECT に
    TABLOCK を指定していること
  • 対象テーブルがレプリケーションの対象でないこと
  • クラスタ化インデックスがある場合は、
    空のテーブルであるか、SQL Server 2008 以降のトレース フラグ 610 相当の条件を満たすこと

また、一括ログ復旧モデルには重大な副作用がある。

論点 内容
ポイントインタイム復旧不可 最小ログ記録された操作を含むログ バックアップには、その区間の任意時点への復旧ができない
ログ バックアップのサイズ ログ自体は小さくなるが、ログ バックアップには変更されたエクステントが含まれるため小さくならない

このため実務では、

  1. 一括処理の直前にログ バックアップを取得
  2. 復旧モデルを一括ログに切り替え
  3. 一括処理を実行
  4. 完全復旧モデルに戻す
  5. すぐにログ バックアップを取得（ログの鎖をつなぎ直す）

という手順を踏む。詳細は
SQL Server の障害復旧、
SQL Server のバックアップを参照。

データベースの拡張

データベースの自動拡張によって一時的な性能劣化が発生する可能性があります。

トランザクション ログ ファイル

  • 使用可能なログ領域を超える領域を必要とするトランザクションを実行し、
    そのデータベースのトランザクション ログの自動拡張オプションを有効にしている場合、

    • トランザクションの完了までにかかる時間には、構成されたサイズずつ
      トランザクション ログを拡張するのにかかる時間が含まれます。

    • 増加量が多い場合、または長時間かかる他の要因が存在する場合、
      タイムアウト エラーが原因で、トランザクションを開くクエリが失敗することがあります。

データ ファイル

  • データベースのデータの一部の自動拡張が原因で同様の問題が発生する可能性があります。

tempdb

  • 必要に応じて、tempdb ファイルの自動拡張を許可します。
    これによって、ディスクがいっぱいになるまで、ファイルを拡張できるようになります。

    注：自動拡張操作の間に発生する可能性があるアプリケーションのタイムアウトを
    許容できない運用環境の場合、予測されるワークロードを許容するための領域を
    事前に割り当てます。

  • tempdb データベース ファイルの拡張単位が小さすぎることのないように、
    ファイル拡張の増分値を妥当なサイズに設定します。

  • tempdb に書き込まれたデータ量と比較してファイルの拡張単位が小さすぎると、
    tempdb を頻繁に拡張する必要が生じる場合があります。

  • このことは、パフォーマンスに影響します。

補足（ログ ファイルの拡張は特に重い）: データ ファイルの拡張は
瞬時ファイル初期化（サービス アカウントに
「ボリュームの保守タスクを実行」権限を付与）によって高速化できるが、
トランザクション ログ ファイルには適用されない。
ログの拡張は必ずゼロ埋めを伴うため、
拡張中は書き込みが待たされる。

また、ログの自動拡張を繰り返すと
VLF（仮想ログ ファイル）が大量に生成され、
復旧時間やログ バックアップの性能が悪化する。
ログは小刻みに拡張させず、
必要サイズを見積もって事前に確保するのが定石である。

結論

  • 自動拡張は有効にして（拡張されないと読み取り専用になる）、
  • 拡張単位は割合ではなく（頻繁に拡張されない）適切な値を設定する。
  • 可能であれば、使用状況を監視して、これらを事前に拡張する様に運用する。

補足（最新化：大量データ処理そのものの設計）: 上記は
「大量処理を行った結果として起きる問題」への対処だが、
大量処理の設計自体で押さえるべき点も挙げておく。

論点 指針
バッチ分割 1 トランザクションで数百万行を扱わない。数千〜数万行ごとにコミットし、ログの肥大とロック エスカレーションを防ぐ（SQL Server のロックのエスカレーション）
一括挿入 行ごとの INSERT ではなく SqlBulkCopy / BULK INSERT / テーブル値パラメータを使う
インデックス 大量投入前に非クラスタ化インデックスを無効化し、投入後に再構築するほうが速いことが多い
削除 全件削除なら DELETE より TRUNCATE TABLE。条件付きならパーティションの切り替え（SQL Server パーティション分割）
一括更新・削除 EF Core 7 以降の ExecuteUpdate / ExecuteDelete、または生 SQL（Entity Framework）

特にパーティションの切り替え（ALTER TABLE ... SWITCH）は、
メタデータ操作だけで完了するため、
大量データの投入・削除をほぼ瞬時に行える。

参考

参考

Tags: 移行, データアクセス, SQL Server, 障害対応, 性能

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally