1 / 33

SQL パフォーマンス チューニング ～ プランガイドの利用～

SQL パフォーマンス チューニング ～ プランガイドの利用～. 中上級編 vol. 2 前編：クエリヒント. SQL パフォーマンス チューニング 中上級編 3 回シリーズ. 2010 年 9 月公開 中上級編 vol. 1 カバーリングインデックス / クエリヒントの利用 2010 年 10 月 公開 中上級編 vol. 2 プランガイドの利用 2010 年 11 月公開 予定 中上級編 vol. 3 PSSDIAG/SQLNEXUS ツールの利用. 概要. パフォーマンス に関する 悩み シナリオ シナリオ 1: クエリヒント

ismail
Download Presentation

SQL パフォーマンス チューニング ～ プランガイドの利用～

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL パフォーマンス チューニング～ プランガイドの利用～ 中上級編 vol. 2 前編：クエリヒント

  2. SQL パフォーマンス チューニング中上級編3回シリーズ • 2010年9月公開中上級編 vol. 1 • カバーリングインデックス/クエリヒントの利用 • 2010年10月公開中上級編 vol. 2 • プランガイドの利用 • 2010年11月公開予定 中上級編 vol. 3 • PSSDIAG/SQLNEXUS ツールの利用

  3. 概要 パフォーマンスに関する悩み • シナリオ • シナリオ 1: クエリヒント • シナリオ 2: 特定のクエリ実行プラン • プランガイドの利用確認 • まとめ 前編 後編

  4. パフォーマンスに関する悩み • プランガイドが有効な状況 • クエリヒントやテーブルヒントの追加、もしくは、特定のクエリ実行プランにてパフォーマンスが向上できることが分かっている • 既存のクエリ内容が変更できない (サード・パーティ製アプリケーションなど)

  5. プランガイドとは • クエリ内容を変更せず、クエリヒントや特定のクエリ実行プランを使用することが可能 • 指定するクエリヒントや特定のクエリ実行プランを、特定のクエリに紐付け、指定したヒントやクエリ実行プランを利用して特定のクエリを実行することが可能 • SQL Server 2005 以降より利用可能

  6. シナリオ 1: クエリヒント • 問題 • クエリヒント（“FORCESEEK”）を該当クエリに追加することにより、処理時間が改善されることが確認できているが、既存のクエリ内容を変更することができない • 対処方法 • クエリヒントを使用したプランガイドを利用する

  7. シナリオ 1: クエリヒントクエリヒントを使用したプランガイドを作成する パフォーマンスを向上させたいクエリ select a1,a2,a3 from tab1 where a1 > 18000 クエリヒント“FORCESEEK”を使用するプランガイドを作成するために、下記クエリを実行する EXEC sp_create_plan_guide @name = N'PLANGUIDE1', @stmt = N'select a1,a2,a3 from tab1 where a1 > 18000', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT (tab1,FORCESEEK))'

  8. シナリオ 1:クエリヒント準備１ -- データベースを作成 create database Scenario_2 go ALTER DATABASE [Scenario_2] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT go -- テーブルを作成 use Scenario_2 go create table tab1 (a1 int, a2 int, a3 nchar(2000)) Go

  9. シナリオ 1:クエリヒント準備２ -- インデックス作成 create index IX_tab1_a1 on tab1(a1) go --不適切なインデックスを使用するよう、あえて統計情報の自動更新をOFFに設定 EXEC sp_autostats tab1,'OFF' go  -- データの挿入 declare @icntint set @icnt=1 while @icnt < 20001 begin insert into tab1 values(@icnt,@icnt,N'チューニング') set @icnt = @icnt + 1 end

  10. シナリオ 1:クエリヒントプランガイドを利用しないクエリ --Management Studio で ”実際の実行プランを表示する” を選択 -- プランガイドを利用しない場合のクエリを実行 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS PROFILE ON SET STATISTICS IO ON SET STATISTICS TIME ON select a1,a2,a3 from tab1where a1 > 18000 SET STATISTICS TIME OFF SET STATISTICS IO OFF SET STATISTICS PROFILE OFF

  11. シナリオ 1:クエリヒントプランガイドを利用しないクエリの実行結果 実行結果の一部 テーブル 'tab1'。スキャン回数 1、 論理読み取り数10000、物理読み取り数80 SQL Server 実行時間: CPU 時間 = 78 ミリ秒、経過時間 = 1061 ミリ秒

  12. シナリオ 1:クエリヒントプランガイドを作成 -- プランガイド作成 -- 該当のクエリをクエリヒント"FORCESEEK" を指定したうえで、実行プランを作成 EXEC sp_create_plan_guide @name = N'PLANGUIDE1', @stmt = N‘selecta1,a2,a3 from tab1 where a1 > 18000’, @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (TABLE HINT (tab1,FORCESEEK))'; オプション設定 @stmt：プランを強制させたいクエリを記載。この部分のステートメントは、実際に実行されるステートメントを厳密に指定する必要がある。例えばスペースや改行の違いによっても、プランガイドは使用されなくなるため、注意が必要。 @hints ：クエリヒントを記載。

  13. シナリオ 1:クエリヒントプランガイドの確認 -- 作成したプランガイドを確認 select plan_guide_id, name,is_disabled,scope_type, query_text, hints from sys.plan_guides -- 無効化 (is_disabled 列が 1）となっている場合、作成したプランガイドを有効化 exec sp_control_plan_guide @operation = N'ENABLE', @name = N'PLANGUIDE1'

  14. シナリオ 1:クエリヒントプランガイドを利用するクエリの実行結果確認 実行結果の一部 テーブル 'tab1'。スキャン回数 1、 論理読み取り数 2017、物理読み取り数２ SQL Server 実行時間: CPU 時間 = 78ミリ秒、経過時間 = 283 ミリ秒

  15. SQL パフォーマンス チューニング～ プランガイドの利用～ 中上級編 vol. 2 後編：特定のクエリ実行プラン

  16. シナリオ 2: 特定のクエリ実行プラン • 問題 • 特定のクエリ実行プランでは、処理時間が改善されることが確認できているが、既存のクエリ内容を変更することができない • 対処策 • 処理時間の速いクエリ実行プランを使用してプランガイドを作成する

  17. シナリオ 2: 特定のクエリ実行プラン特定のクエリプランを使用するプランガイドを作成する パフォーマンスを向上させたいクエリ select a1,a2,a3 from tab1 where a1 > 18000 ヒント付きクエリや、処理時間が速いと確認できている状況にて、プロファイラでクエリ実行プランを採取し、その特定のクエリ実行プランを使用するプランガイドを作成するために、下記クエリを実行する。 EXEC sp_create_plan_guide @name = N'PLANGuide2', @stmt = N'select a1,a2,a3 from tab1 where a1 > 18000', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N‘OPTION (USE PLAN N’‘<ShowPlanXML・・・・・ BatchSequence></ShowPlanXML>'')'

  18. シナリオ 2: 特定のクエリ実行プランプランガイドを利用しないクエリ 実行結果の一部 テーブル 'tab1'。スキャン回数 1、 論理読み取り数10000、物理読み取り数80 SQL Server 実行時間: CPU 時間 = 78 ミリ秒、経過時間 = 1061 ミリ秒

  19. シナリオ 2: 特定のクエリ実行プラン特定のクエリプランをXML形式で採取１ -- プロファイラを使用して、XML 形式のクエリプランを採取する。 1) メニューから Microsoft SQL Server 2008（2005）- パフォーマンスツール - SQL Server Profiler を起動し、 [ファイル]-[新しいトレース]を選択。 2) 該当のインスタンスに接続。 3) 「全般」 タブにて、使用するテンプレートに 「Standard」 が選択されていることを確認。 4) [イベントの選択]タブで、「すべてのイベントを表示する」をチェック。

  20. シナリオ 2: 特定のクエリ実行プラン特定のクエリプランをXML形式で採取２ 5) 続けて、以下のイベントを追加。 - Performance Showplan XML - TSQL SQL:StmtStarting SQL:StmtCompleted ※対象クエリが ストアドプロシージャ の場合は、以下を選択。 - Performance Showplan XML - Stored Procedure SP:StmtStarting SP:StmtCompleted

  21. シナリオ 2: 特定のクエリ実行プラン特定のクエリプランをXML形式で採取3 6) 「実行」ボタンをクリック。トレース採取が開始される。 7) トレースを採取している状態で、対象クエリを実行。 クエリ実行が完了したことを確認し、トレースの採取を停止。 8) プロファイラにて、採取したトレースを確認。 ※ 下記は該当部分のみの抜粋です。 SQL:StmtStarting, SQL:StmtCompletedイベントの間に出力されているShowplan XMLが取得対象の実行プランです。

  22. シナリオ 2: 特定のクエリ実行プラン特定のクエリプランをXML形式で採取4 9) 該当のShowplan XML イベントの行を右クリックし、[イベント データの抽出] を選択。 保存先を指定して、任意のファイルとして保存。

  23. シナリオ 2: 特定のクエリ実行プランプランガイドを作成 -- 該当のクエリをXML形式で採取したクエリプランを指定し、プランガイドを作成 EXEC sp_create_plan_guide @name = N'PLANGUIDE2', @stmt = N'select a1,a2,a3 from tab1 where a1 > 18000', @module_or_batch = NULL, @params = NULL, @hints =N‘OPTION (USE PLAN N''<ShowPlanXMLxmlns・・・/BatchSequence></ShowPlanXML>'')' オプション @hints：プロファイラで取得したXML形式の実行プランを記載。N プレフィックスの後は、「’」（シングルクォーテーション）2つで囲む'')'

  24. シナリオ ２:特定のクエリ実行プランプランガイドを利用するクエリの実行結果確認 実行結果の一部 テーブル 'tab1'。スキャン回数 1、 論理読み取り数2017、物理読み取り数0 SQL Server 実行時間: CPU 時間 = 62ミリ秒、経過時間 = 226 ミリ秒

  25. シナリオ ２:特定のクエリ実行プランクエリプラン取得方法 プランガイドで指定するクエリ実行プランの取得方法は以下のいずれかで可能 • SET SHOWPLAN_XML • SET STATISTICS XML • sys.dm_exec_query_plan動的管理関数のquery_plan列へのクエリ • SQL Server Profiler のShowplan XML イベント クラス、Showplan XML Statistics Profile イベント クラス、およびShowplan XML For Query Compile イベント クラス（[イベント データの抽出] を実行して XML プランをファイルに保存） ※SQL Server Management Studio からクエリを実行する場合、出力文字列の制限によりすべて情報が表示されない可能性があるため、プロファイラでの採取を推奨。

  26. プランガイドの利用確認指定したクエリ実行プランが使用されているか確認1プランガイドの利用確認指定したクエリ実行プランが使用されているか確認1 -- プロファイラを使用して、指定したクエリ実行プランが使用されているか確認 1) メニューから Microsoft SQL Server 2008（2005） - パフォーマンスツール - SQL Server Profiler を起動し、 [ファイル]-[新しいトレース]を選択。 2) 該当のインスタンスに接続。 3) 「全般」 タブにて、使用するテンプレートに 「Standard」 が選択されていることを確認。 4) [イベントの選択]タブで、「すべてのイベントを表示する」をチェック。

  27. プランガイドの利用確認指定したクエリ実行プランが使用されているか確認２プランガイドの利用確認指定したクエリ実行プランが使用されているか確認２ 5) 以下のイベントを追加。 - Performance Showplan XML - TSQL SQL:StmtStarting SQL:StmtCompleted ※対象クエリがストアドプロシージャの場合は、以下を選択。 - Performance Showplan XML - Stored Procedure SP:StmtStarting SP:StmtCompleted

  28. プランガイドの利用確認指定したクエリ実行プランが使用されているか確認３プランガイドの利用確認指定したクエリ実行プランが使用されているか確認３ 6) 「実行」ボタンをクリック。トレース採取が開始。 7) トレースを採取している状態で、以下のクエリを実行。クエリ実行が完了したことを確認して、トレースの採取を停止。 －－－－－－－－－－－ dbccfreeproccache go 対象クエリ go －－－－－－－－－－－ ※クエリ実行前にdbccfreeproccacheを実行。本コマンドにより、プロシージャキャッシュをクリアし、キャッシュされているクエリ実行プランが使用されることを防止。

  29. プランガイドの利用確認指定したクエリ実行プランが使用されているか確認4プランガイドの利用確認指定したクエリ実行プランが使用されているか確認4 8) プロファイラにて、採取したトレースを確認。 対象クエリのShowplan XML イベントの行を右クリックし、[イベント データの抽出] を選択。保存先を指定して、任意のファイルとして保存。 9) 8) で、保存したファイルを開き、以下が含まれているかを確認。 <PlanGuideDB="データベース名" PlanGuideName="ガイド名"> 上記が含まれている場合、プランガイドで設定したプランが使用されていると判断可能。

  30. まとめ • プランガイド(SQL Server 2005/2008/2008R2) • ヒント句 • 特定のクエリ実行プラン

  31. 参考情報 sp_create_plan_guide (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms179880.aspx sys.plan_guides (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms178010.aspx sp_control_plan_guide (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms188733.aspx クエリ ヒント (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms181714.aspx クエリ ヒント (Transact-SQL) : SQL Server 2005 http://msdn.microsoft.com/ja-jp/library/ms181714(SQL.90).aspx

  32. 参考情報 テーブル ヒント (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms187373.aspx テーブル ヒント (Transact-SQL) : SQL Server 2005 http://msdn.microsoft.com/ja-jp/library/ms187373(SQL.90).aspx OPTION 句 (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms190322.aspx OPTION 句 (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms190322(SQL.90).aspx プラン ガイドを使用した配置済みアプリケーションのクエリの最適化 http://msdn.microsoft.com/ja-jp/library/ms187032.aspx プラン ガイドを使用した配置済みアプリケーションのクエリの最適化 : SQL Server 2005 http://msdn.microsoft.com/ja-jp/library/ms187032(SQL.90).aspx

  33. Ihr Potenzial. Unser Antrieb.

More Related