MS_SQLServerOptimizer - NetDevInfraWGinOSSConsortium/NetDevInfraWiki GitHub Wiki

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 でもある。

ク゚リのチュヌニング

以䞋の手順にある様に、CBO統蚈情報→実行プランの問題を確認し、
必芁に応じお RBOプラン ガむド、ク゚リ ヒントを適甚する。

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

  • ク゚リのチュヌニング
    https://learn.microsoft.com/ja-jp/sql/relational-databases/performance/monitor-and-tune-for-performance\ 　SQL Server デヌタベヌス ゚ンゞンのプラン衚瀺機胜を䜿甚しお、
    　ク゚リ プランを衚瀺し、分析する方法に぀いお説明したす。

  • プラン ガむドを䜿甚した配眮枈みアプリケヌションのク゚リの最適化
    https://learn.microsoft.com/ja-jp/sql/relational-databases/performance/plan-guides\ 　ク゚リのテキストを倉曎できない堎合に、プラン ガむドを䜿甚しお、
    　ク゚リ パフォヌマンスを最適化する方法に぀いお説明したす。

  • プラン匷制の䜿甚によるク゚リ プランの指定
    https://learn.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-query\ 　USE PLAN ク゚リ ヒントを䜿甚しお、ク゚リ オプティマむザがあるク゚リに察しお
    　特定のク゚リ プランを䜿甚するように蚭定する方法に぀いお説明したす。

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コン゜ヌシアム 開発基盀郚䌚」によっお運営されおいたす。

⚠ **GitHub.com Fallback** ⚠