Skip to content

MS_SQLServerOptimizer

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

SQL Server のオプティマイザ

概要

  • SQL のパフォーマンスを向上させるためには、
    どうすれば効率のよい実行計画になるかを探る必要がある。

  • DBMS はオプティマイザというコンポーネントを持っており、
    クエリ（データに対する問い合わせ）を実行する最も効率的な方法を決定する。

移行メモ（正誤）: 元ページの「DBMS はコンポーネントというコンポーネントを持っており」は
「オプティマイザというコンポーネントを持っており」の誤記と思われるため修正した。

  • オプティマイザの種類 (CBO、RBO)
    　- オラクル・Oracle をマスターするための基本と仕組み
    http://www.shift-the-oracle.com/inside/optimizer.html

  • オプティマイザには、

    • 「ルール ベース」の「オプティマイザ」（RBO）
    • 「コスト ベース」の「オプティマイザ」（CBO）

    という 2 種類がある。

  • SQL Server は、コスト ベースのオプティマイザ（CBO）を採用している。

オプティマイザの種類

「ルール ベース」の「オプティマイザ」（RBO）

  • 「ルール ベース」の「オプティマイザ」は、「RBO：Rule-Base-Optimizer」と呼ばれる。
  • SQL 文を分解して、その分解された情報から所定のルールによって最適化する。

「コスト ベース」の「オプティマイザ」（CBO）

  • 「コスト ベース」の「オプティマイザ」は、「CBO：Cost-Base-Optimizer」と呼ばれる。
  • データ（インデックス）内のキー値の「選択度」と「分布」を記述した「分布統計」から、
    実行コスト（I/O と CPU コスト）を見積もることによって、
    クエリの「実行プラン」を評価する。
    これにより適切な量のリソースを消費し、かつ、
    最も速く結果を返す「実行プラン」を選択する。

補足（「コスト」は時間ではない）: 実行プランに表示されるコストは
1990 年代のあるマシンでの実行時間を基準にした無次元の推定値であり、
秒でもミリ秒でもない。
cost threshold for parallelism の既定値 5 が現在では小さすぎるのも
この基準が古いままだからである
（SQL Server のファイル・グループ参照）。

オプティマイザのトレンド

  • オプティマイザの種類としては、コストベースのオプティマイザ（CBO）が主流となっている。

  • この理由は、CBO は、データが変化する環境においても定期的に統計情報の収集をするため、
    データにフィットした実行計画、アクセスパスになるように自動的に調整されるためである。

  • Oracle 10g からはルールベースのオプティマイザ（RBO）はサポートされなくなっている。
    ただし、この「サポートされない」の意味は、RBO の生成する実行計画に影響を与える
    クエリヒントが将来、サポートされなくなる可能性を示唆しているだけで
    **「実際はまだ使用可能」**である。

チューニング方法

自動パラメータ

Windows Server が自動パラメータであるように、SQL Server も CBO に基づいたチューニングを行う。
＃Sybase SQL Server は CBO をサポートした初めて商用で成功した RDBMS でもある。

  • Windows 自動パラメタとチューニング（MS_WindowsAutoTuning.md）

クエリのチューニング

以下の手順にある様に、CBO（統計情報→実行プラン）の問題を確認し、
必要に応じて RBO（プラン ガイド、クエリ ヒント）を適用する。

クエリ パフォーマンス
https://learn.microsoft.com/ja-jp/sql/relational-databases/performance/performance-center-for-sql-server-database-engine

CBO

  • CBO ではインデックス統計を使用する。
  • このため、インデックス統計は必要に応じて更新する必要がある。
  • これにより、クエリの実行プランを適正化し、ディスク I/O を減らす。

実行計画の確認

実行プランのグラフィカル表示を参照。

統計情報のメンテナンスの手順

「UPDATE STATISTICS」ステートメント

テーブルまたはインデックス付きビュー内の「分布統計」を更新する。

「CREATE STATISTICS」ステートメント

  • 1 つの「非インデックス化列」、または「非インデックス化列」のセットの
    「分布統計」を手動で作成できる。
  • 「非インデックス化列」の「分布統計」を作成すると、
    テーブル上で許可される 249 個の「非クラスタ化インデックス」の上限が減少する。

何に使う？

補足（CREATE STATISTICS の用途）: 「何に使う？」への回答としては、
複数列にまたがる相関をオプティマイザに教えるのが主用途である。

自動作成される統計は単一列に限られるため、
例えば「都道府県 = 東京都」かつ「市区町村 = 千代田区」のような
強く相関する列の組み合わせでは、
単独の選択率を掛け合わせた結果、行数を過小評価してしまう。
この場合に複数列の統計を明示的に作ると、見積り精度が改善する。

CREATE STATISTICS ST_Address_Pref_City
    ON dbo.Address (Prefecture, City);

なお、上限に関する記述は SQL Server 2005 当時のもので、
SQL Server 2008 以降は 1 テーブルあたり 999 個の
非クラスタ化インデックスを作成できる。
統計情報の上限は別枠（10,000 個）なので、
現在は統計を作ってインデックス数が減ることを心配する必要はない。

「AUTO_CREATE_STATISTICS」データベース オプション

AUTO_CREATE_STATISTICS データベース オプションを ON（既定値）に指定すると、

  • クエリの最適化に必要な「分布統計」が不足している場合、
    自動的に「分布統計」が作成される。
  • クエリの最適化に必要な「分布統計」が現状を反映していない場合、
    自動的に「分布統計」が更新される。

このデータベース オプションの設定には、

  • ALTER DATABASE ステートメント
  • sp_dboption システム ストアド プロシージャ
  • CREATE STATISTICS ステートメント
  • UPDATE STATISTICS ステートメント

を使用する。

「分布統計」の最終更新日を調べるには、STATS_DATE 関数を使用する。

移行メモ（正誤）: 自動作成は AUTO_CREATE_STATISTICS、
自動更新は AUTO_UPDATE_STATISTICS という別のオプションである
（どちらも既定 ON）。上記の 2 番目の項目は後者の説明。
また、sp_dboption は SQL Server 2005 で非推奨となり
2012 で削除されているため、現在は ALTER DATABASE ... SET を使用する。

補足（統計の状態を調べる）: 更新日だけでなく、
前回更新以降の変更行数まで見られる DMF がある。

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id)        AS table_name,
    s.name                          AS stats_name,
    sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

rows に対して rows_sampled が極端に小さい場合は、
サンプリング率が低く見積りが荒くなっている可能性がある
（WITH FULLSCAN や PERSIST_SAMPLE_PERCENT を検討）。

統計情報の自動更新・手動更新の使い分け

統計情報の自動更新が ON の場合の手動更新の必要性

統計情報の自動更新が ON に設定されている場合には、
統計情報を手動で更新する必要は全くないか？

UPDATE STATISTICS や sp_updatestats を実行して
明示的に統計情報を更新する必要がある場合はある。

統計情報の自動更新はいつどのように行われるのか？

おおよそテーブルの 20% に相当するデータが更新されると、
そのデータの統計は自動更新の対象になる。

補足（最新化：しきい値の変更）: この「20%」は
「500 行 + テーブル行数の 20%」という古いしきい値。
大きなテーブルほど更新されにくくなるという弱点があった
（1 億行なら 2,000 万行の変更が必要）。

SQL Server 2016 以降（互換性レベル 130 以上）では、
行数の平方根に比例する動的なしきい値が既定になり、
大規模テーブルでも統計が更新されやすくなっている
（旧来はトレース フラグ 2371 で有効化していた挙動）。

なお、統計の自動更新はクエリのコンパイル時に同期的に走るため、
大きなテーブルでは最初の 1 本が長く待たされる。
これを避けたい場合は
ALTER DATABASE ... SET AUTO_UPDATE_STATISTICS_ASYNC ON
で非同期更新にできる（初回は古い統計でコンパイルされる）。

どのような場合に手動更新が必要か？

  • 統計情報の自動更新が実行されるためのしきい値には達しないまでも、
    データ分布に影響を与える量のデータ変更が行われた場合。

  • 全体のデータ分布には大きな影響は与えていないが、
    データ参照を行う処理が、追加変更されたデータのみを参照する場合。

  • 言い換えれば、データ変更後に、統計情報に含まれていないデータを対象とした
    処理が行われる場合。

補足（昇順キー問題）: 2 番目・3 番目のケースは、
実務では **「日時列や ID 列など単調増加するキーの直近データ」**として現れる。
ヒストグラムの最終ステップより後の値は「該当 0 行」と見積もられ、
「今日登録されたデータを検索するバッチだけが極端に遅い」
という症状になる。

SQL Server 2014 以降の新カーディナリティ推定機能では
この点が改善されているが、
夜間バッチの前に対象テーブルの統計を明示更新する運用は
現在でも有効な対策である。

統計情報の自動更新をOFFにする。

  • 統計情報の自動更新が ON の状態で、オンライン中に統計情報の更新が発生すると、
    性能的に問題が出ることがある。

  • しかし、統計情報の自動更新を OFF にした場合、
    結局、統計情報が実データと乖離した際に問題が発生する。

  • 従って、統計情報更新の OFF 運用は以下のようになると考える。

    • サーバ・メンテナンス時間帯に統計情報の更新を ON にして統計情報を更新する。

      • STATS_DATE 関数で、統計の最終更新日を確認できる。

    • サーバ・メンテナンス時間帯に問題（乖離）を発見して手動更新する（難しい）。

    • 注意：OFF だと、新たなインデックス追加時などにも統計情報が作成されなくなる。
      Missing Column Index イベントをトレースして統計情報のない Index が通知を受け取ることができる。

補足（OFF より非同期）: 「オンライン中の同期更新が重い」という理由なら、
自動更新を OFF にするより
AUTO_UPDATE_STATISTICS_ASYNC ON にするほうが
副作用が小さく、現在の第一選択である。
自動更新を OFF にする構成は、
統計を完全に自前で管理する覚悟がある場合に限る。

参考

実行計画の確認、統計情報のメンテナンスの手順は以下を参照。

連載 RDBMS アーキテクチャの深層（5）
Oracle と SQL Server、チューニングの違いを知る（Page 2）
http://www.atmarkit.co.jp/fdb/rensai/rdbmsarc05/rdbmsarc05_2.html

  • オプティマイザと統計情報
    • SQL 実行計画の確認手順
    • 統計情報のメンテナンス

RBO

SQL Server には以下の RBO 的な機能が残されている。

  • プラン ガイド
  • クエリ ヒント

プラン ガイド

  • プラン ガイドを使用した配置済みアプリケーションのクエリの最適化
    https://learn.microsoft.com/ja-jp/sql/relational-databases/performance/plan-guides

    • プラン ガイド

      実際のクエリのテキストを直接変更することが不可能な場合や望ましくない場合に、
      プラン ガイドを使用してクエリのパフォーマンスを最適化することができます。

      プラン ガイドは、クエリ ヒントまたは固定クエリ プランを
      クエリにアタッチすることにより、クエリの最適化を促します。

      プラン ガイドは、サード パーティ ベンダーが提供する
      データベース アプリケーションのクエリの小さなサブセットで、
      期待どおりのパフォーマンスが得られない場合に役に立ちます。

    • プラン ガイドのデザインと実装

    • プラン ガイドを使用したクエリのパラメータ化動作の指定

    • パラメータ化クエリのプラン ガイドの設計

    • SQL Server がプラン ガイドをクエリに照合するプロセス

    • SQL Server Profiler を使用したプラン ガイドの作成とテスト

クエリ ヒント

  • プラン強制の使用によるクエリ プランの指定
    https://learn.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-query

    • プランの適用について

      USE PLAN クエリ ヒントを使用すると、クエリ オプティマイザが
      クエリに対して指定のクエリ プランを強制的に適用するように設定できます。

      USE PLAN クエリ ヒントは、引数として
      XML 形式のクエリ プランを受け取ることによって機能します。

      USE PLAN は、実行時間の長いプランを使用するクエリに、
      より優れたプランが存在することがわかっている場合に使用できます。

    • USE PLAN クエリ ヒントの使用

    • カーソルを使用したクエリでの USE PLAN クエリ ヒントの使用

    • プラン強制シナリオと例

補足（最新化：クエリ ストアによるプラン強制）: プラン ガイドや
USE PLAN は XML プランを手で持ち回る必要があり扱いが難しかった。
SQL Server 2016 以降はクエリ ストアが、

  • 過去に使われた実行プランを自動で保持し
  • SSMS の GUI または sp_query_store_force_plan でワンステップで強制でき
  • 「強制したプランがなぜ効かなかったか」の理由まで記録する

ため、プラン回帰への対処はクエリ ストアで行うのが現在の標準。
SQL Server 2017 以降の自動プラン修正（AUTOMATIC_TUNING）を
有効にすれば、回帰を検出して自動で以前のプランに戻すこともできる。

ただし、いずれも対症療法である点は RBO 的機能と変わらない。
本来は統計情報の鮮度、インデックス設計
（SQL Server のインデックス）、
パラメータ スニッフィングの回避（OPTIMIZE FOR、RECOMPILE）といった
原因側を先に確認すること。

参考

内部

外部

ITpro

SE の雑記

都内で働くSEの技術的なひとりごと

Microsoft SQL Server Japan Support Team Blog

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

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

NetDevInfraWiki

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

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally