中上級編 vol. 1

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

SQL パフォーマンス チューニング中上級編 3 回シリーズ

2010 年 9 月公開 中上級編 vol. 1 カバーリングインデックス / クエリヒントの利用

2010 年 10 月公開予定 中上級編 vol. 2 プランガイドの利用

2010 年 11 月公開予定 中上級編 vol. 3 PSSDIAG/SQLNEXUS ツールの利用

2010 年 4 月から公開中 初級編 SQL パフォーマンス チューニング : パフォーマンス改善 最初の一歩

概要

パフォーマンスに関する悩みシナリオ

シナリオ 1: カバーリングインデックス シナリオ 2: クエリヒント / テーブルヒント

まとめ

パフォーマンスに関する悩み

インデックスを設定しているが、より有効な　インデックスを設定したい。 カバーリングインデックスの設定

チューニングを行った結果、特定の実行プランならばパフォーマンスが向上することが判明。特定の実行プランでクエリを実行したい。 クエリヒント / テーブルヒントの利用

シナリオ 1: カバーリングインデックス

カバーリングインデックスとは クエリで利用する全ての列を含むインデックス

効果 コストのかかる nested loop の rid lookup

や key lookup などのブックマーク参照を避ける

クエリの論理読み取り数の削減

シナリオ 1: カバーリングインデックスカバーリングインデックスの作成方法

テーブル名　tab1

a1 int

a2 int

a3 nchar(2000)

パフォーマンスを向上させたいクエリselect a1,a2,a3 from tab1where a1 > 10000 and a2 > 18000

カバーリングインデックス ( 付加列インデックス ) を作成するために、下記クエリを実行します。

create index IX_tab1_a123 on tab1(a1,a2) include (a3)

-- データベースを作成create database Scenario_1

-- テーブルを作成use Scenario_1gocreate table tab1 (a1 int, a2 int, a3 nchar(2000), a4 nchar(2000))

-- クラスタ化インデックス作成create clustered index IX_tab1_a1 on tab1(a1)go -- データの挿入declare @icnt int set @icnt = 1while @icnt < 20001begin  insert into tab1 values (@icnt,@icnt,' チューニング ',' パフォーマンス ')

set @icnt = @icnt + 1 end

シナリオ 1: カバーリングインデックス準備

シナリオ 1: カバーリングインデックスカバーリングインデックスを利用しないクエリ

-- 非クラスタ化インデックス作成 create index IX_tab1_a1a2 on tab1(a1,a2)

-- 　 Management Studio で ”実際の実行プランを表示する” を選択--   カバーリングインデックスがない場合にクエリを実行

DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE

SET STATISTICS PROFILE ONSET STATISTICS IO ONSET STATISTICS TIME ON

select a1,a2,a3 from tab1 　 where a1 > 10000 and a2 > 18000

SET STATISTICS TIME OFFSET STATISTICS IO OFFSET 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)

マージ結合が実行されていることがわかる。

--   ハッシュ結合ヒントを利用するクエリselect A.a1,A.a3 from tab2 as A inner join tab3 as B on (A.a1 = B.a1)option(HASH JOIN)

ハッシュ結合ヒントにより、以下の通り実行プランがマージ結合からハッシュ結合に変更されている。

シナリオ 2: クエリヒント – HashJoin hintハッシュ結合ヒントを利用するクエリ

--   フォースシークヒントを利用しないクエリ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 > 19000option (TABLE HINT (tab1,FORCESEEK))

シナリオ 2: テーブルヒント – ForceSeek hintフォースシークヒントを利用するクエリ (SQL Server 2008/2008 R2)

--   フォースシークヒントを利用するクエリ (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)

参考情報

実際の実行プランを表示する方法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