1 / 19

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

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

tuyet
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

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

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

  6. シナリオ 1: カバーリングインデックスカバーリングインデックスの作成方法 パフォーマンスを向上させたいクエリ select a1,a2,a3 from tab1 where a1 > 10000 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 table tab1 (a1 int, a2 int, a3 nchar(2000), a4 nchar(2000)) -- クラスタ化インデックス作成 create clustered index IX_tab1_a1 on tab1(a1) go  -- データの挿入 declare @icntint set @icnt = 1 while @icnt < 20001 begin   insert into tab1 values (@icnt,@icnt,'チューニング','パフォーマンス') set @icnt = @icnt + 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 tab1where a1 > 10000 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 tab1where a1 > 10000 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 Ainner join tab3 as B on (A.a1 = B.a1) option(HASH JOIN) ハッシュ結合ヒントにより、以下の通り実行プランがマージ結合からハッシュ結合に変更されている。

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

  16. シナリオ 2:テーブルヒント – ForceSeekhintフォースシークヒントを利用するクエリ (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. 参考情報 • 実際の実行プランを表示する方法 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

  19. Ihr Potenzial. Unser Antrieb.

More Related