SQL パフォーマンス チューニング ～ カバーリングインデックス/クエリヒントの利用～

Presentation on theme: "SQL パフォーマンス チューニング ～ カバーリングインデックス/クエリヒントの利用～"— Presentation transcript:

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

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

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

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

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

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

7 シナリオ 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)  -- データの挿入 int = 1 < 20001 begin   insert into tab1 values + 1  end

8 シナリオ 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 > and a2 > 18000 SET STATISTICS TIME OFF SET STATISTICS IO OFF SET STATISTICS PROFILE OFF

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

10 シナリオ 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 > and a2 > 18000

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

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

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

14 シナリオ 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) ハッシュ結合ヒントにより、以下の通り実行プランがマージ結合からハッシュ結合に変更されている。

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

16 シナリオ 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 フォースシークヒントにより、以下の通り実行プランが テーブルスキャンから、インデックスシークに変更されている。

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

18 参考情報 実際の実行プランを表示する方法 付加列インデックス クエリ ヒント (Transact-SQL)
付加列インデックス クエリ ヒント (Transact-SQL) テーブル ヒント(Transact-SQL) クエリ チューニングの高度な概念 msdn. FORCESEEK テーブル ヒントの使用

19 Ihr Potenzial. Unser Antrieb.