「マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
目次 †
概要 †
- SQLのパフォーマンスを向上させるためには、
どうすれば効率のよい実行計画になるかを探る必要がある。
- DBMSはコンポーネントというコンポーネントを持っており、
クエリ (データに対する問い合わせ) を実行する最も効率的な方法を決定する。
- オプティマイザには、
- 「ルール ベース」の「オプティマイザ」（RBO）
- 「コスト ベース」の「オプティマイザ」（CBO）
という２種類がある。
- SQL Serverは、コスト ベースのオプティマイザ（CBO）を採用している。
オプティマイザの種類 †
「ルール ベース」の「オプティマイザ」（RBO） †
- 「ルール ベース」の「オプティマイザ」は、「RBO：Rule-Base-Optimizer」と呼ばれる。
- SQL文を分解して、その分解された情報から所定のルールによって最適化する。
「コスト ベース」の「オプティマイザ」（CBO） †
- 「コスト ベース」の「オプティマイザ」は、「CBO：Cost-Base-Optimizer」と呼ばれる。
- データ（インデックス）内のキー値の「選択度」と「分布」を記述した「分布統計」から、実行コスト（I/OとCPUコスト）を見積もることによって、
クエリの「実行プラン」を評価する。これにより適切な量のリソースを消費し、かつ、最も速く結果を返す「実行プラン」を選択する。
オプティマイザのトレンド †
- オプティマイザの種類としては、コストベースのオプティマイザ（CBO）が主流となっている。
- この理由は、CBOは、データが変化する環境においても定期的に統計情報の収集をするため、
データにフィットした 実行計画、 アクセスパスになるように自動的に調整されるためである。
- Oracle 10g からはルールベースのオプティマイザ（RBO）はサポートされなくなっている。
ただし、この「サポートされない」の意味は、RBOの生成する実行計画に影響を与えるクエリヒントが
将来、サポートされなくなる可能性を示唆しているだけで「実際はまだ使用可能」である。
チューニング方法 †
自動パラメータ †
Windows Serverが自動パラメータであるように、SQL ServerもCBOに基づいたチューニングを行う。
＃Sybase SQL ServerはCBOをサポートした初めて商用で成功したRDBMSでもある。
クエリのチューニング †
以下の手順にある様に、CBO（統計情報→実行プラン）の問題を確認し、
必要に応じてRBO（プラン ガイド、クエリ ヒント）を適用する。
クエリ パフォーマンス
http://msdn.microsoft.com/ja-jp/library/ms190610.aspx
CBO †
- CBOではインデックス統計を使用する。
- このため、インデックス統計は必要に応じて更新する必要がある。
- これにより。クエリの実行プランを適正化し、ディスクI/Oを減らす。
実行計画の確認 †
統計情報のメンテナンスの手順 †
「UPDATE STATISTICS」ステートメント †
テーブルまたはインデックス付きビュー内の「分布統計」を更新する。
「CREATE STATISTICS」ステートメント †
- 1 つの「非インデックス化列」、または「非インデックス化列」のセットの「分布統計」を手動で作成できる。
- 「非インデックス化列」の「分布統計」を作成すると、テーブル上で許可される249個の「非クラスタ化インデックス」の上限が減少する。
何に使う？
「AUTO_CREATE_STATISTICS」データベース オプション †
「AUTO_CREATE_STATISTICS」データベース オプションのON（既定値）に指定すると、
- クエリの最適化に必要な「分布統計」が不足している場合、
自動的に「分布統計」が作成される。
- クエリの最適化に必要な「分布統計」が現状を反映していない場合、
自動的に「分布統計」が更新される。
このデータベース オプションの設定には、
- 「ALTER DATABASE」ステートメント
- 「sp_dboption」システム ストアド プロシージャ
- 「CREATE STATISTICS」ステートメント
- 「UPDATE STATISTICS」ステートメント
を使用する。
「分布統計」の最終更新日を調べるには、STATS_DATE関数を使用する。
統計情報の自動更新・手動更新の使い分け †
統計情報の自動更新が ON の場合の手動更新の必要性 †
統計情報の自動更新が ON に設定されている場合には、統計情報を手動で更新する必要は全くないか？
UPDATE STATISTICS や sp_updatestats を実行して
明示的に統計情報を更新する必要がある場合はある。
統計情報の自動更新はいつどのように行われるのか？ †
おおよそテーブルの 20% に相当するデータが更新されると、
そのデータの統計は自動更新の対象になる。
どのような場合に手動更新が必要か？ †
- 統計情報の自動更新が実行されるためのしきい値には達しないまでも、データ分布に影響を与える量のデータ変更が行われた場合。
- 全体のデータ分布には大きな影響は与えていないが、データ参照を行う処理が、追加変更されたデータのみを参照する場合。
- 言い換えれば、データ変更後に、統計情報に含まれていないデータを対象とした処理が行われる場合。
統計情報の自動更新をOFFにする。 †
- 統計情報の自動更新がONの状態で、オンライン中に統計情報の更新が発生すると、性能的に問題が出ることがある。
- しかし、統計情報の自動更新をOFFにした場合、結局、統計情報が実データと乖離した際に問題が発生する。
- 従って、統計情報更新のOFF運用は以下のようになると考える。
- サーバ・メンテナンス時間帯に統計情報の更新をONにして統計情報を更新する。
- STATS_DATE関数で、統計の最終更新日を確認できる。
- サーバ・メンテナンス時間帯に問題（乖離）を発見して手動更新する（難しい）。
- 注意：OFFだと、新たなインデックス追加時などにも統計情報が作成されなくなる。
Missing Column Index イベントをトレースして統計情報のないIndexが通知を受け取ることができる。
参考 †
実行計画の確認、統計情報のメンテナンスの手順は以下を参照。
連載 RDBMSアーキテクチャの深層（5）
OracleとSQL Server、チューニングの違いを知る（Page 2）
http://www.atmarkit.co.jp/fdb/rensai/rdbmsarc05/rdbmsarc05_2.html
RBO †
SQL Serverには以下のRBO的な機能が残されている。
プラン ガイド †
- プラン ガイドのデザインと実装
- プラン ガイドを使用したクエリのパラメータ化動作の指定
- パラメータ化クエリのプラン ガイドの設計
- SQL Server がプラン ガイドをクエリに照合するプロセス
- SQL Server Profiler を使用したプラン ガイドの作成とテスト
クエリ ヒント †
- USE PLAN クエリ ヒントの使用
- カーソルを使用したクエリでの USE PLAN クエリ ヒントの使用
- プラン強制シナリオと例
参考 †
内部 †
外部 †
ITpro †
SE の雑記 †
都内で働くSEの技術的なひとりごと †
Microsoft SQL Server Japan Support Team Blog †
Tags: :データアクセス, :SQL Server, :障害対応, :性能
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
