Title of Presentation

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
int
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 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 = 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)
参考情報
実際の実行プランを表示する方法
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

Download Report