Skip to content

MS_SQLServerManagement

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server の管理

概要

ディスク I/O を減らす系の作業が多い。

メンテナンス プラン

インデックスの再構築・デフラグ

インデックスの再構築・デフラグ（MS_IndexRebuildAndDefrag.md）

空ページを削除し、データ ファイルを圧縮

データ ファイルの圧縮と拡張（MS_DataFileShrinkAndGrow.md）

補足（縮小は基本的に行わない）: DBCC SHRINKFILE / SHRINKDATABASE は
定常のメンテナンス プランに入れてはならないというのが現在の定説。

  • ページを末尾から先頭へ移動するため、インデックスが激しく断片化する
  • 縮小した領域は結局また拡張されるので、
    縮小 → 断片化 → 再構築 → 拡張という不毛なループになる
  • 拡張時にはファイルの初期化（ゼロ埋め）が発生し、
    その間の書き込みが待たされる

実施するのは、大量削除やアーカイブ後など
恒久的に空き領域が生じたときの単発作業に限り、
実施後は必ずインデックスを再構築する。
データ ファイルの拡張が遅い場合は、
瞬時ファイル初期化（サービス アカウントに
「ボリュームの保守タスクを実行」権限を付与）を有効にすると改善する
（ログ ファイルには適用されない）。

インデックスの統計情報を更新

SQL Server のオプティマイザ

内部一貫性チェック

SQL Server では、DBCC CHECKDB ステートメントを使用して、
指定された DB 内の、すべての DB オブジェクトの割り当てと構造的整合性を
チェック、修復することができる。

DBCC CHECKDB

  • オンラインでの実行

    • DBCC CHECKDB ステートメントは、スキーマ ロックを保持する。
    • スキーマ ロックとのロックの互換性から、
      オンライン操作（DML ステートメント）は許可されるが、
      DDL ステートメントは許可されないので注意する。

  • 処理の負荷

    • DBCC CHECKDB ステートメントは、CPU とディスクに大きな負担がかかる。

    • まず、チェックする「データ ページ」が、メモリに読み込まれなければならない。

    • その上、tempdb を使用してデータを並べ替える。

    • また、ログの読み取りが終了するまで
      「トランザクション ログ」の切り捨てをブロックするため、
      DBCC CHECKDB ステートメントの実行中にトランザクションを
      活発に実行すると、「トランザクション ログ」が増大し続ける。

  • このため、サーバの負荷が軽い時間帯に、
    DBCC CHECKDB ステートメントを実行することが推奨される。

  • オンライン処理の性能に影響を与えないための推奨事項

    • システムの使用率が低いときに実行する。

    • 他のディスク I/O 操作を実行していないことを確認する。

    • tempdb を別のディスク システム、または高速なディスク サブシステムに配置する。

    • tempdb が拡張できるように、ドライブに十分な空き領域を用意する。

    • オプション

      • ESTIMATE ONLY オプション
        tempdb に必要な空き領域のサイズを見積もる。
      • NO_INFOMSGS オプション
        処理量と tempdb の使用率を減らす。
      • PHYSICAL_ONLY オプション
        チェック内容を、DB の物理的一貫性のみの
        低オーバーヘッド チェックに変更する。

移行メモ（正誤）: ESTIMATE ONLY は正しくは
ESTIMATEONLY（スペースなし）である。

補足（DBCC CHECKDB の運用）:

  • SQL Server 2005 以降、DBCC CHECKDB は
    内部スナップショットに対して実行されるため、
    実際には長時間のスキーマ ロックを保持しない
    （WITH TABLOCK を指定した場合を除く）。
    ただし、スナップショット用の領域がデータ ファイルと
    同じボリュームに作られるため、空き容量は必要。
  • 大規模 DB では、PHYSICAL_ONLY を平日に、
    フルチェックを週末に、といった二段構えが現実的。
    さらに DBCC CHECKFILEGROUP / CHECKTABLE で分割実行する手もある。
  • REPAIR_ALLOW_DATA_LOSS は最後の手段。
    名前のとおりデータが失われる。まず
    SQL Server のバックアップからの復元を検討する。
  • 破損の多くはストレージ層に起因する。
    PAGE_VERIFY CHECKSUM（既定）を有効にしておき、
    msdb.dbo.suspect_pages とイベント ログを監視する。

バックアップ

SQL Server のバックアップ

データ圧縮

パーティション毎、異なる圧縮オプションを使用できる。

  • ページ圧縮
  • 行圧縮
    • プレフィックスの圧縮
    • 辞書の圧縮

ディスク I/O を減らすが、CPU 時間は増える。

詳細は SQL Server データ圧縮を参照。

チューニング

ディスク・コントローラの I/O 性能をフル活用する。

RAID、ディスク・レイアウトなど。

ファイル・グループに分割・配置

SQL Server のファイル・グループ

データのパーティション分割

SQL Server パーティション分割

インデックス見直し

SQL Server 問題の分析方法

スパース列として設定

スパース列として設定

監視

問題を監視する。

定常的

インシデント

その他

パーティション分割

SQL Server パーティション分割

並列クエリによる性能向上も可能だが、
主に保守・運用時のデータ・メンテナンス性能に効果を発揮。

移行メモ（誤字）: 元ページの「保守・運用時時」は「保守・運用時」の誤記。

シャーディング

補足（最新化：現在の運用の勘所）: 元ページは
「ディスク I/O を減らす」という当時の主眼で構成されているが、
現在はストレージが SSD / NVMe になり、ボトルネックの所在が変わっている。
定常運用で押さえるべき点は概ね以下。

項目 現在の指針
インデックス メンテナンス 断片化率で分岐（〜5% 何もしない / 〜30% REORGANIZE / 30%〜 REBUILD）。SSD では断片化の影響自体が小さい
統計情報 自動更新に任せつつ、大きなテーブルは明示的に UPDATE STATISTICS。SQL Server 2016 以降は既定のサンプリング閾値が改善
整合性チェック DBCC CHECKDB を定期実行し、結果を監視
バックアップ 復旧モデルに応じた完全／差分／ログのセット（SQL Server の障害復旧）
監視 待ち事象（sys.dm_os_wait_stats）とクエリ ストアを起点にする

メンテナンス プランの GUI よりも、
Ola Hallengren のメンテナンス スクリプトを使うのが実務では一般的。

Tags: 移行, データアクセス, SQL Server

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

NetDevInfraWiki

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

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally