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

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

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

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

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

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

シナリオ 1: クエリヒント クエリヒントを使用したプランガイドを作成する テーブル名　tab1 a1 int a2 a3 nchar(2000) パフォーマンスを向上させたいクエリ 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))'

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

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

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

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

シナリオ 1:クエリヒント プランガイドを作成 -- プランガイド作成 -- 該当のクエリをクエリヒント"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))'; オプション設定 　 @stmt ：プランを強制させたいクエリを記載。この部分のステートメントは、実際に実行されるステートメントを厳密に指定する必要がある。例えばスペースや改行の違いによっても、プランガイドは使用されなくなるため、注意が必要。 　　@hints ：クエリヒントを記載。

シナリオ 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'

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

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

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

シナリオ 2: 特定のクエリ実行プラン 特定のクエリプランを使用するプランガイドを作成する テーブル名　tab1 a1 int a2 a3 nchar(2000) パフォーマンスを向上させたいクエリ 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>'')'

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

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

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

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

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

シナリオ 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''<ShowPlanXML xmlns・・・/BatchSequence></ShowPlanXML>'')' オプション 　@hints：プロファイラで取得したXML形式の実行プランを記載。N プレフィックスの後は、「’」（シングルクォーテーション）2つで囲む'')'

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

シナリオ ２:特定のクエリ実行プラン クエリプラン取得方法 プランガイドで指定するクエリ実行プランの取得方法は以下のいずれかで可能 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 からクエリを実行する場合、出力文字列の制限によりすべて情報が表示されない可能性があるため、プロファイラでの採取を推奨。

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

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

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

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

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

参考情報 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

参考情報 テーブル ヒント (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 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

Ihr Potenzial. Unser Antrieb. Seminar Name