SQL パフォーマンス チューニング ～ カバーリングインデックス/クエリヒントの利用～ 中上級編 vol. 1

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

概要 パフォーマンスに関する悩み シナリオ シナリオ 1: カバーリングインデックス シナリオ 2: クエリヒント/テーブルヒント まとめ

パフォーマンスに関する悩み インデックスを設定しているが、より有効な インデックスを設定したい。 インデックスを設定しているが、より有効な　インデックスを設定したい。 カバーリングインデックスの設定 チューニングを行った結果、特定の実行プランならばパフォーマンスが向上することが判明。特定の実行プランでクエリを実行したい。 クエリヒント/テーブルヒントの利用

シナリオ 1: カバーリングインデックス カバーリングインデックスとは 効果 クエリで利用する全ての列を含むインデックス コストのかかる nested loop の rid lookup や key lookup などのブックマーク参照を避ける クエリの論理読み取り数の削減

シナリオ 1: カバーリングインデックス カバーリングインデックスの作成方法 テーブル名　tab1 a1 int a2 a3 nchar(2000) パフォーマンスを向上させたいクエリ select a1,a2,a3 from tab1 where a1 > 10000 and a2 > 18000 カバーリングインデックス(付加列インデックス)を作成するために、下記クエリを実行します。 create index IX_tab1_a123 on tab1(a1,a2) include (a3)

シナリオ 1: カバーリングインデックス 準備 create database Scenario_1 use Scenario_1 go -- データベースを作成 create database Scenario_1 -- テーブルを作成 use Scenario_1 go create table tab1 (a1 int, a2 int, a3 nchar(2000), a4 nchar(2000)) -- クラスタ化インデックス作成 create clustered index IX_tab1_a1 on tab1(a1)  -- データの挿入 declare @icnt int  set @icnt = 1 while @icnt < 20001 begin   insert into tab1 values (@icnt,@icnt,'チューニング','パフォーマンス') set @icnt = @icnt + 1  end

シナリオ 1: カバーリングインデックス カバーリングインデックスを利用しないクエリ -- 非クラスタ化インデックス作成  create index IX_tab1_a1a2 on tab1(a1,a2) --　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 > 10000 and a2 > 18000 SET STATISTICS TIME OFF SET STATISTICS IO OFF SET STATISTICS PROFILE OFF

シナリオ 1: カバーリングインデックス カバーリングインデックスを利用ないクエリの実行結果 実行結果の一部 テーブル 'tab1'。スキャン回数 1、 論理読み取り数 24823、物理読み取り数 188 SQL Server 実行時間: CPU 時間 = 141 ミリ秒、経過時間 = 3776 ミリ秒

シナリオ 1: カバーリングインデックス カバーリングインデックスを利用するクエリ -- 作成済みの非クラスタ化インデックスの削除 drop index IX_tab1_a1a2 on tab1   --  カバーリングインデックス(付加列インデックス)を作成 create index IX_tab1_a123 on tab1(a1,a2) include (a3) --  カバーリングインデックスがある場合にクエリを実行 select a1,a2,a3 from tab1 where a1 > 10000 and a2 > 18000

シナリオ 1: カバーリングインデックス カバーリングインデックスを利用するクエリの実行結果 実行結果の一部 テーブル 'tab1'。スキャン回数 1、 論理読み取り数 5016、物理読み取り数 0 SQL Server 実行時間: CPU 時間 = 110 ミリ秒、経過時間 = 281 ミリ秒

シナリオ 2: クエリヒント クエリヒント/テーブルヒント クエリヒント/テーブルヒントとは オプティマイザに対して、明示的に指定した動作を実現するために、実行プランを作成させるようにする機能。 効果 目的に合わせて実行プランを変更することが出来る。 今回紹介する例 ハッシュ結合ヒント (SQL Server 2005/2008/2008R2) フォースシークヒント (SQL Server 2008/2008R2)

シナリオ 2:クエリヒント – HashJoin hint ハッシュ結合ヒントを利用しないクエリ --  ハッシュ結合ヒントを利用しないクエリ select A.a1,A.a3 from tab2 as A inner join tab3 as B on (A.a1 = B.a1) マージ結合が実行されていることがわかる。

シナリオ 2:クエリヒント – HashJoin hint ハッシュ結合ヒントを利用するクエリ --  ハッシュ結合ヒントを利用するクエリ select A.a1,A.a3 from tab2 as A inner join tab3 as B on (A.a1 = B.a1) option(HASH JOIN) ハッシュ結合ヒントにより、以下の通り実行プランがマージ結合からハッシュ結合に変更されている。

シナリオ 2:テーブルヒント – ForceSeek hint フォースシークヒントを利用しないクエリ (SQL Server 2008/2008 R2) --  フォースシークヒントを利用しないクエリ select a1,a2,a3 from tab1 where a1 > 19000 テーブルスキャンが実行されていることがわかる。

シナリオ 2:テーブルヒント – ForceSeek hint フォースシークヒントを利用するクエリ (SQL Server 2008/2008 R2) --  フォースシークヒントを利用するクエリ (1) select a1,a2,a3 from tab1 where a1 > 19000 option (TABLE HINT (tab1,FORCESEEK)) --  フォースシークヒントを利用するクエリ (2) select a1,a2,a3 from tab1(FORCESEEK) where a1 > 19000 フォースシークヒントにより、以下の通り実行プランが テーブルスキャンから、インデックスシークに変更されている。

まとめ カバーリングインデックス ハッシュ結合ヒント (SQL Server 2005/2008/2008R2) option(HASH JOIN) フォースシークヒント (SQL Server 2008/2008R2) option (TABLE HINT (tab1,FORCESEEK)) テーブル名 (FORCESEEK)

参考情報 実際の実行プランを表示する方法 付加列インデックス クエリ ヒント (Transact-SQL) http://technet.microsoft.com/ja-jp/library/ms189562.aspx 付加列インデックス http://msdn.microsoft.com/ja-jp/library/ms190806.aspx クエリ ヒント (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms181714.aspx テーブル ヒント(Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms187373.aspx クエリ チューニングの高度な概念 msdn. http://microsoft.com/ja-jp/library/ms191426.aspx FORCESEEK テーブル ヒントの使用 http://msdn.microsoft.com/ja-jp/library/bb510478.aspx

Ihr Potenzial. Unser Antrieb.