![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。 -[[戻る>SQL Server]] * 目次 [#r2bd7430] #contents *概要 [#dd722b34] -SQLのパフォーマンスを向上させるためには、~ どうすれば効率のよい実行計画になるかを探る必要がある。 -DBMSはコンポーネントというコンポーネントを持っており、~ クエリ (データに対する問い合わせ) を実行する最も効率的な方法を決定する。 -オプティマイザの種類 (CBO、RBO)~ - オラクル・Oracleをマスターするための基本と仕組み~ http://www.shift-the-oracle.com/inside/optimizer.html -オプティマイザには、 --「ルール ベース」の「オプティマイザ」（RBO） --「コスト ベース」の「オプティマイザ」（CBO） >という２種類がある。 -SQL Serverは、コスト ベースのオプティマイザ（CBO）を採用している。 *オプティマイザの種類 [#u18a8ef8] **「ルール ベース」の「オプティマイザ」（RBO） [#wf9bf08d] -「ルール ベース」の「オプティマイザ」は、「RBO：Rule-Base-Optimizer」と呼ばれる。 -SQL文を分解して、その分解された情報から所定のルールによって最適化する。 **「コスト ベース」の「オプティマイザ」（CBO） [#r0144fb1] -「コスト ベース」の「オプティマイザ」は、「CBO：Cost-Base-Optimizer」と呼ばれる。 -データ（インデックス）内のキー値の「選択度」と「分布」を記述した「分布統計」から、実行コスト（I/OとCPUコスト）を見積もることによって、~ クエリの「実行プラン」を評価する。これにより適切な量のリソースを消費し、かつ、最も速く結果を返す「実行プラン」を選択する。 **オプティマイザのトレンド [#la196573] -オプティマイザの種類としては、コストベースのオプティマイザ（CBO）が主流となっている。 -この理由は、CBOは、データが変化する環境においても定期的に統計情報の収集をするため、~ データにフィットした 実行計画、 アクセスパスになるように自動的に調整されるためである。 -Oracle 10g からはルールベースのオプティマイザ（RBO）はサポートされなくなっている。~ ただし、この「サポートされない」の意味は、RBOの生成する実行計画に影響を与えるクエリヒントが~ 将来、サポートされなくなる可能性を示唆しているだけで''「実際はまだ使用可能」''である。 *チューニング方法 [#u07ef8c5] **自動パラメータ [#adbfe6b9] Windows Serverが自動パラメータであるように、SQL ServerもCBOに基づいたチューニングを行う。~ ＃Sybase SQL ServerはCBOをサポートした初めて商用で成功したRDBMSでもある。 -[[Windows 自動パラメタとチューニング]] **クエリのチューニング [#w8f896dd] 以下の手順にある様に、CBO（統計情報→実行プラン）の問題を確認し、~ 必要に応じてRBO（プラン ガイド、クエリ ヒント）を適用する。 クエリ パフォーマンス~ http://msdn.microsoft.com/ja-jp/library/ms190610.aspx -クエリのチューニング~ http://msdn.microsoft.com/ja-jp/library/ms176005.aspx~ SQL Server データベース エンジンのプラン表示機能を使用して、~ クエリ プランを表示し、分析する方法について説明します。 -プラン ガイドを使用した配置済みアプリケーションのクエリの最適化~ http://msdn.microsoft.com/ja-jp/library/ms187032.aspx~ クエリのテキストを変更できない場合に、プラン ガイドを使用して、~ クエリ パフォーマンスを最適化する方法について説明します。 -プラン強制の使用によるクエリ プランの指定~ http://msdn.microsoft.com/ja-jp/library/ms190727.aspx~ USE PLAN クエリ ヒントを使用して、クエリ オプティマイザがあるクエリに対して~ 特定のクエリ プランを使用するように設定する方法について説明します。 *CBO [#e611b387] -CBOではインデックス統計を使用する。 -このため、インデックス統計は必要に応じて更新する必要がある。 -これにより。クエリの実行プランを適正化し、ディスクI/Oを減らす。 **実行計画の確認 [#gac96b63] **統計情報のメンテナンスの手順 [#t1fff22a] ***「UPDATE STATISTICS」ステートメント [#n3c3df92] テーブルまたはインデックス付きビュー内の「分布統計」を更新する。 ***「CREATE STATISTICS」ステートメント [#aee9209c] -1 つの「非インデックス化列」、または「非インデックス化列」のセットの「分布統計」を手動で作成できる。 -「非インデックス化列」の「分布統計」を作成すると、テーブル上で許可される249個の「非クラスタ化インデックス」の上限が減少する。 何に使う？ ***「AUTO_CREATE_STATISTICS」データベース オプション [#ffa6acdd] 「AUTO_CREATE_STATISTICS」データベース オプションのON（既定値）に指定すると、 -クエリの最適化に必要な「分布統計」が不足している場合、~ 自動的に「分布統計」が作成される。 -クエリの最適化に必要な「分布統計」が現状を反映していない場合、~ 自動的に「分布統計」が更新される。 このデータベース オプションの設定には、 -「ALTER DATABASE」ステートメント -「sp_dboption」システム ストアド プロシージャ -「CREATE STATISTICS」ステートメント -「UPDATE STATISTICS」ステートメント を使用する。 「分布統計」の最終更新日を調べるには、STATS_DATE関数を使用する。 **統計情報の自動更新・手動更新の使い分け [#b4304d99] ***統計情報の自動更新が ON の場合の手動更新の必要性 [#fc9d846d] 統計情報の自動更新が ON に設定されている場合には、統計情報を手動で更新する必要は全くないか？ UPDATE STATISTICS や sp_updatestats を実行して~ 明示的に統計情報を更新する必要がある場合はある。 ***統計情報の自動更新はいつどのように行われるのか？ [#pd6e9a3e] おおよそテーブルの 20% に相当するデータが更新されると、~ そのデータの統計は自動更新の対象になる。 ***どのような場合に手動更新が必要か？ [#o1c0502f] -統計情報の自動更新が実行されるためのしきい値には達しないまでも、データ分布に影響を与える量のデータ変更が行われた場合。 -全体のデータ分布には大きな影響は与えていないが、データ参照を行う処理が、追加変更されたデータのみを参照する場合。 -言い換えれば、データ変更後に、統計情報に含まれていないデータを対象とした処理が行われる場合。 ***統計情報の自動更新をOFFにする。 [#dae9c38e] -統計情報の自動更新がONの状態で、オンライン中に統計情報の更新が発生すると、性能的に問題が出ることがある。 -しかし、統計情報の自動更新をOFFにした場合、結局、統計情報が実データと乖離した際に問題が発生する。 -従って、統計情報更新のOFF運用は以下のようになると考える。 --サーバ・メンテナンス時間帯に統計情報の更新をONにして統計情報を更新する。 ---STATS_DATE関数で、統計の最終更新日を確認できる。 --サーバ・メンテナンス時間帯に問題（乖離）を発見して手動更新する（難しい）。 ---参考：【SQL Server】統計情報のヒストグラムと実行プランの予測行数 - 小物SEのメモ帳~ http://memorandom-nishi.hatenablog.jp/entry/2017/02/18/021834 --注意：OFFだと、新たなインデックス追加時などにも統計情報が作成されなくなる。~ Missing Column Index イベントをトレースして統計情報のないIndexが通知を受け取ることができる。 **参考 [#e7148501] 実行計画の確認、統計情報のメンテナンスの手順は以下を参照。 連載 RDBMSアーキテクチャの深層（5）~ OracleとSQL Server、チューニングの違いを知る（Page 2）~ http://www.atmarkit.co.jp/fdb/rensai/rdbmsarc05/rdbmsarc05_2.html -オプティマイザと統計情報 --SQL実行計画の確認手順 --統計情報のメンテナンス *RBO [#l48a6c26] SQL Serverには以下のRBO的な機能が残されている。 -プラン ガイド -クエリ ヒント **プラン ガイド [#ia0b214f] -プラン ガイドを使用した配置済みアプリケーションのクエリの最適化~ http://msdn.microsoft.com/ja-jp/library/ms187032.aspx --プラン ガイド~ http://msdn.microsoft.com/ja-jp/library/ms190417.aspx >>実際のクエリのテキストを直接変更することが不可能な場合や望ましくない場合に、~ プラン ガイドを使用してクエリのパフォーマンスを最適化することができます。 ~ ~ プラン ガイドは、クエリ ヒントまたは固定クエリ プランを~ クエリにアタッチすることにより、クエリの最適化を促します。~ ~ プラン ガイドは、サード パーティ ベンダーが提供する~ データベース アプリケーションのクエリの小さなサブセットで、~ 期待どおりのパフォーマンスが得られない場合に役に立ちます。 --プラン ガイドのデザインと実装 --プラン ガイドを使用したクエリのパラメータ化動作の指定 --パラメータ化クエリのプラン ガイドの設計 --SQL Server がプラン ガイドをクエリに照合するプロセス --SQL Server Profiler を使用したプラン ガイドの作成とテスト **クエリ ヒント [#qe267d06] -プラン強制の使用によるクエリ プランの指定~ http://msdn.microsoft.com/ja-jp/library/ms190727.aspx --プランの適用について~ http://msdn.microsoft.com/ja-jp/library/ms186343.aspx >>USE PLAN クエリ ヒントを使用すると、クエリ オプティマイザが~ クエリに対して指定のクエリ プランを強制的に適用するように設定できます。~ ~ USE PLAN クエリ ヒントは、引数として~ XML 形式のクエリ プランを受け取ることによって機能します。~ ~ USE PLAN は、実行時間の長いプランを使用するクエリに、~ より優れたプランが存在することがわかっている場合に使用できます。~ --USE PLAN クエリ ヒントの使用 --カーソルを使用したクエリでの USE PLAN クエリ ヒントの使用 --プラン強制シナリオと例 *参考 [#j229e731] **[[SQL Server アドホック クエリ問題の監視]] [#k53df512] **[[SQL Server 大量データ処理時の性能問題]] [#if9244d6] **[[実行プランのグラフィカル表示]] [#h492531a] **内部 [#x84702d4] ***[[SQL Server アドホック クエリ問題の監視]] [#k53df512] ***[[SQL Server 大量データ処理時の性能問題]] [#if9244d6] ***[[実行プランのグラフィカル表示]] [#h492531a] **ITpro [#c224ce70] **外部 [#y22383d9] ***ITpro [#c224ce70] -特集基礎から理解するデータベースのしくみ：ITpro~ http://itpro.nikkeibp.co.jp/article/COLUMN/20060127/228070/?ST=develop --Part1：SQL文はどのように実行されるのか ---基礎から理解するデータベースのしくみ（2） ---基礎から理解するデータベースのしくみ（3） ---基礎から理解するデータベースのしくみ（4） ---基礎から理解するデータベースのしくみ（5） **SE の雑記 [#da78aba2] ***SE の雑記 [#da78aba2] -統計情報の更新状況の確認~ https://blog.engineer-memo.com/2012/04/19/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%81%AE%E6%9B%B4%E6%96%B0%E7%8A%B6%E6%B3%81%E3%81%AE%E7%A2%BA%E8%AA%8D/ -統計情報の自動更新に関する考察~ https://blog.engineer-memo.com/2012/04/28/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%81%AE%E8%87%AA%E5%8B%95%E6%9B%B4%E6%96%B0%E3%81%AB%E9%96%A2%E3%81%99%E3%82%8B%E8%80%83%E5%AF%9F/ **都内で働くSEの技術的なひとりごと [#rc001470] ***都内で働くSEの技術的なひとりごと [#rc001470] -統計情報について簡単に説明してみる~ http://ryuchan.hatenablog.com/entry/2015/04/11/192331 -SQL Server の統計情報作成について書いてみた、~ ちょっとだけマジメにまとめてみた~ http://ryuchan.hatenablog.com/entry/2014/03/23/184242 **Microsoft SQL Server Japan Support Team Blog [#ue740574] ***Microsoft SQL Server Japan Support Team Blog [#ue740574] -どうする？ SQL Server のクエリ パフォーマンスが低下した！~ https://blogs.msdn.microsoft.com/jpsql/2013/09/03/sql-server-5/ -統計情報の自動更新が ON の時には統計情報を手動で更新する必要はない？~ https://blogs.msdn.microsoft.com/jpsql/2012/04/18/on-12/ ---- Tags: [[:データアクセス]], [[:SQL Server]], [[:障害対応]], [[:性能]]
