SQL Server Performance Tuning Essentials

Transcript

1. SQL Server Performance Tuning Essentials Masaki Hirose
2. Chapter 1 受講の準備
3. Chapter 1 Lesson 1 本コースについて
4. 本資料のパフォーマンスチューニングの定義 1. 個別最適なチューニング 特定のクエリの「実⾏時間」または「CPU使⽤時間」の削減 2. 全体最適なチューニング 特定のインスタンスで実⾏されている全クエリの「実⾏時間の 合計値」または「CPU使⽤時間の合計値」の削減
5. 本資料で学べること • 個別最適の観点でひとつのクエリをチューニングするための知 識と技術 • 全体最適の観点でインスタンス全体の総実⾏時間および総CPU 使⽤時間を削減するための知識と技術
6. 本資料の特徴 • 専⽤のサンプルDBを使った実践的なノウハウを知ることができる • 知識としてだけではなく、実際にデモを⾒ながらチューニングに よる速度アップを体感できる
7. 対象者 • ⽇頃SQL Server / Azure SQL Databaseを使って開発・運⽤・ 管理などを⾏っており、単⼀または全体的なクエリパフォーマ ンスやサーバーのCPU⾼負荷状態に悩んでいる⼈ • CPU負荷を削減することで、クラウドで稼働中のDBをスケール ダウン／スケールインしてコストカットしたい⼈
8. 前提条件 • SQLの使⽤経験 • SQL Serverの使⽤経験 • SQL ServerのPaaSの使⽤経験 (Azure/AWS/GCP) • SQL Server Management Studio (SSMS)の使⽤経験
9. チューニング⼒を上げるためのポイント • ⾃分で考える • ⾃分で⼿を動かす
10. Chapter 1 Lesson 2 サンプルDBの準備
11. サンプルDBの内容 DB名 Member レコード数 MemberEMail レコード数 MyTuningDB_small 1,000万 1,500万 MyTuningDB_middle 2,000万 3,000万 MyTuningDB_large 3,000万 4,500万
12. サンプルDBのダウンロード • https://drive.google.com/drive/u/2/folders/13xVm- alJ2kzuDQyDVvWxSyUR5i6lWx_5 • バックアップファイルを圧縮した3ファイル • 展開したあとにリストア
13. サンプルDBについて • SQL Server 2019以降でリストア可能 • ⽤途：サンプルDBを使ってクエリチューニングを体感してもらう • データはすべてランダムに⽣成したデータ • 電話番号やメールアドレスはランダムだが、実在の可能性がある ため別⽤途での使⽤は禁⽌
14. 個別最適
15. Chapter 2 チューニングの流れ
16. Chapter 2 Lesson 1 パフォーマンスチューニングの定義
17. 本コースにおけるパフォーマンスチューニン グの定義 1. 個別最適なチューニング 特定のクエリの「実⾏時間」または「CPU使⽤時間」の削減 2. 全体最適なチューニング 特定のインスタンスで実⾏されている全クエリの「実⾏時間の 合計値」または「CPU使⽤時間の合計値」の削減
18. Chapter 2 Lesson 2 チューニングの流れと評価⽅法
19. 評価時に使うふたつの指標 • ユーザーの待ち時間に直結する「実⾏時間(duration)」 • サーバーの負荷に直結する「CPU使⽤時間(cpu)」 基本的にはチューニングすると両⽅減るが、そうでない場合も。 どちらを削減したいかあらかじめ決めておく。
20. クエリチューニングの流れ １．対象クエリと削減したい値（duration/cpu）の決定 ２．指標の計測 ① ３．チューニング実施（クエリ書き換え、インデックス作成等） ４．指標の計測 ② ５．評価
21. クエリチューニングの例 1. 実⾏時間 (duration) を削減したい 2. 指標の計測 ① 3. クエリチューニング（クエリの書き換え、インデックス作成など） 4. 指標の計測 ② 5. 評価 例：チューニングの結果、実⾏時間を1000msから10msへと99% 削減できた
22. 評価するさいの注意点 • sys.dm_exec_query_statsにはコンパイルの時間は含まれない
23. ポイント：体感ではなく数値で⽐較する ×「かなり時間がかかっていたのが⼀瞬で完了するようになった」 〇「10秒かかっていたのが100 msecで完了するようになった」
24. Chapter 2 Lesson 3 set statistics time onとは
25. duration 計測⽅法① set statistics time on cpu
26. Chapter 2 Lesson 4 sys.dm_exec_query_statsとは
27. 計測⽅法② sys.dm_exec_query_stats • キャッシュされたクエリプランのパフォーマンス統計を取得 • プランがキャッシュから削除されると、そのプランに紐づく 統計データも消える • 粒度はステートメント単位 → 1ストアド内で3クエリ実⾏するストアドだと3⾏取得
28. Chapter 2 Lesson 5 計測⽅法の使い分け
29. duration/cpu 計測⽅法の使い分け • リリース前：set statistics time on SSMSでの検証時に⼿動で実⾏するクエリ • リリース後：sys.dm_exec_query_stats アプリケーションが実⾏しているクエリ
30. なぜsys.dm_exec_query_statsを使うのか？ • プロダクション環境ではさまざまなパラメータでクエリが実⾏ • パラメータによって実⾏時間が⼤幅に変化するケースもある 例）リリース前の⼿動での検証時は「where col1 = 1」で1秒だったが、リリース後は 「where col1 = 2」が指定されることが多く、この場合だと10秒かかる。 • sys.dm_exec_query_statsを使えば、プロダクション環境で指定 されるさまざまなパラメータによって実⾏された実際の duration/cpuを取得できる • プロダクション環境でのチューニングの評価⽅法として妥当
31. 注意点① durationとcpuは⼤きく違う場合も • duration >> cpu ロック競合などで待たされる時間が多いと、この関係になる • duration << cpu 並列クエリの場合はcpu時間のほうが⼤きくなることがある • duration/cpuは個別に把握することが⼤事
32. • コストとは • あるスペックのマシンでそのクエリを実⾏した場合に想定さ れる実⾏時間 • コストが⾼くても「実⾏時間が⻑そうだとSQL Serverが思っ ている」ということまでしかわからない • 基本的にはコストが低いほどduration/cpuも低いが、コストが 下がってもduration/cpuが増えることもある ⇒ 指標として不適当 注意点② 評価の指標にコスト値は使わない
33. Chapter 3 インデックスをマスターする
34. Chapter 3 Lesson 1 インデックスを使う理由
35. SQLは宣⾔型⾔語 最⼤の利点は「なにを」と「どのように」を分離できること select * from Member where MemberID = 18629764 なにを取得したいか＝SQL どのように取得するか＝実⾏プラン
36. 「どのように」は本来知らなくていい • 「なにを＝SQL」が書ければ、欲しいデータはとれる • 「どのように」は本来知らなくていいが、チューニングのため に踏み込む必要あり
37. duration/cpuに影響を与えるのは実⾏プラン • SQLをいくら書き換えても、実⾏プランが変化しなければ durationもcpuも同じ • 実⾏プランを変化させる有効な⼿段のひとつが「インデックス」
38. durationの差と実⾏プランの違い • インデックス効いてる • select * from [Member] where LoginName = 'HunterGreen45744363' • インデックス効いてない • select * from [Member] with(index(PK_Member)) where LoginName = 'HunterGreen45744363ʼ
39. Chapter 3 Lesson 2 テーブルとインデックスのアーキテ クチャ
40. 論理的な構造 8KBの連続領域 物理的な構造 テーブルのアーキテクチャ ツリー構造
41. インデックスとは • クエリ⾼速化に有効なデータ構造 • さまざまな種類があるが、SQL Serverでもっとも⼀般的なのは ツリー構造のインデックス
42. ２種類の基本的なインデックス • クラスタ化インデックス • １テーブルに１個 • テーブルの実データ（＝全カラム）が格納 • ⾮クラスタ化インデックス • １テーブルに0〜999個 • テーブルの⼀部のカラムが格納 ⇒ どちらもページを論理的につなげてツリー構造にしたもの
43. page page page page page page page 双⽅向連結リスト B-Tree (Balanced-Tree) Root Branch (中間) Leaf
44. Chapter 3 Lesson 3 クラスタ化インデックスとは
45. クラスタ化インデックスの定義 [MemberID]で並び替えられたツリー構造になっている ALTER TABLE [dbo].[Member] ADD CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED ( [MemberID] ASC )
46. クラスタ化インデックス：キーで並び替え Root Branch Leaf 実データ インデックスキーの範囲情報 ＋ 対応するページ番号 MemberID Page 1-50000 ① 50001-100000 ② ① ② MemberID Page 1-25000 ③ 25001-50000 ④ ③ ④ MemberID LoginName … RegistDate 1 a1 … 2020/10/1 2 a2 … 2020/10/2 … … … … 25000 b1 … 2020/10/3
47. Chapter 3 Lesson 4 Index SeekとIndex Scan
48. ポイント インデックスは「うまく使われれば」クエリを⾼速化できる
49. ひとつずつ実⾏してみよう select * from Member where MemberID = 18629768 select * from Member where LoginName = 'Janita1317' どちらも使われるインデックスは同じ ⇒ インデックスはどう使われたのか？
50. 瞬時に実⾏完了したクエリ：Index Seek select * from Member where MemberID = 18629768
51. 時間がかかったクエリ：Index Scan select * from Member where LoginName = 'Janita1317'
52. ⽮印の向きが違うだけなのになぜ速い/遅い？ DECLARE @DB_ID int, @Object_ID int set @DB_ID = DB_ID('MyTuningDB_small') set @Object_ID = OBJECT_ID('Member') SELECT name, index_id, index_type_desc, index_depth, index_level, page_count, record_count, avg_fragmentation_in_percent as 断⽚化率 FROM sys.dm_db_index_physical_stats (@DB_ID, @Object_ID, NULL , NULL, 'DETAILED') as A JOIN sys.objects as B with(nolock) on A.object_id = B.object_id ORDER BY index_id, index_level desc
53. ツリーの深さ(4) << リーフページ数 (160350) リーフページ数：160350 ツリーの深さ：4 Level=2 Level=1 Level=0 Level=3
54. ツリーの深さ(4) << リーフページ数 (160350) リーフページ数：160350 ツリーの深さ：4 Level=2 Level=1 Level=0 Level=3 seek時の読み取りページ数 << scan時の読み取りページ 数
55. Seek = 読み取りページ数が圧倒的に⼩さい（160350 → 4) = ⾼速 リーフページ数：160350 ツリーの深さ：4 Level=2 Level=1 Level=0 Level=3
56. Chapter 3 Lesson 5 ⾮クラスタ化インデックスで ScanからSeekへ変化させる
57. インデックスが効く(=seek)条 件 例：「インデックスの並び順」=「where句で指定した条件」 ALTER TABLE [dbo].[Member] ADD CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED ( [MemberID] ASC ) 効く：select * from Member where MemberID = 18629768 効かない：select * from Member where LoginName = 'Janita1317' インデックス≠万能薬
58. set statistics io onで読み取りページ数を確 認 seek scan
59. set statistics time, io on で両⽅確認も可能
60. 時間がかかったクエリを⾼速化するには？ select * from Member where LoginName = 'Janita1317'
61. これが
62. こうなればOK
63. そのための新しいインデックス CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ( [LoginName] ASC )
64. ⾮クラスタ化インデックス Root Branch Leaf キー＋クラスタ化インデックスキー インデックスキーの範囲情報 ＋ 対応するページ番号 LoginName Page a1-c1 ① c2-d1 ② ① ② LoginName Page a1-b1 ③ b2-c1 ④ ③ ④ LoginName MemberID a1 1 a2 2 … … b1 25000
65. Chapter 3 Lesson 6 ⾮クラスタ化インデックスの バリエーション
66. 付加列インデックス CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (RegistDate)
67. 付加列インデックスの構造 Root Branch Leaf キー＋クラスタ化インデックスキー＋付加列 インデックスキーの範囲情報 ＋ 対応するページ番号 LoginName Page a1-c1 ① c2-d1 ② ① ② LoginName Page a1-b1 ③ b2-c1 ④ ③ ④ LoginName MemberID RegistDate a1 1 2020/10/1 a2 2 2020/10/2 … … … b1 25000 2020/10/3
68. 複合インデックス CREATE NONCLUSTERED INDEX [IX_Member_Sei_PrefectureID] ON [dbo].[Member] ( [Sei] ASC, [PrefectureID] ASC ) 複合インデックス = 複数のカラムをインデックスキーに指定
69. 複合インデックス Root Branch Leaf ① ② ③ ④ Sei PrefectureI D Page Adena 40 ① Barrett 25 ② Sei PrefectureID Page Adena 40 ③ Adrian 3 ④ Sei PrefectureID MemberID Adena 40 523 Adena 42 613 作成時に指定したカラムの順序で インデックスキーが作成される
70. 複合インデックスを使⽤した検索 インデックスキーに指定したカラムの順序が検索効率に影響する SELECT COUNT(*) FROM Member WHERE Sei = 'Adena' AND PrefectureID = 1 → インデックス作成時に指定したカラムの両⽅が検索条件に含まれる SELECT COUNT(*) FROM Member WHERE Sei = 'Adenaʻ → インデックス作成時に指定した「先頭のカラム」が検索条件に含まれる SELECT COUNT(*) FROM Member WHERE PrefectureID = 1 → インデックス作成時に指定したカラムは含むが、「先頭のカラム」が検索条件に 含まれていない • Index Seekで検索されるケース • Index Seekで検索が⾏われないケース ⇒ インデックスの先頭には検索に使われる頻度の多い項⽬を指定する
71. Chapter 3 Lesson 7 キー参照と カバリングインデックス
72. キー参照 ⾮クラスタ化インデックスだけではカラムを返せないとき • ⾮クラスタ化インデックスでIndex Seek • リーフページでクラスタ化インデックスキー取得 • 取得したキーでクラスタ化インデックスをIndex Seek ( = キー参照)
73. LoginName MemberID RegistDate a1 1 2020/10/1 a2 2 2020/10/2 … … … b1 25000 2020/10/3 SELECT LoginName, RegistDate, Sei FROM Member WHERE LoginName = 'b1' リーフページに[Sei]が無い ⾮クラスタ化インデックス 実データ MemberID LoginName … Sei 1 a1 … Asai 2 a2 … Tanaka … … … … 25000 b1 … Yokota クラスタ化インデックス キー参照 (MemberID = 25000でIndex Seek)
74. カバリングインデックス • 上記ようなインデックスが作成されている場合 CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName] ASC) INCLUDE (RegistDate) キー参照が発⽣しないインデックスを「カバリングインデックス」 SELECT LoginName, RegistDate FROM Member WHERE LoginName = 'Tawny265167' インデックスキー インデックスキー INCLUDE カラム クエリに必要なカラムをカバーするインデックスになっている ＝カバリングインデックス
75. カバリングインデックス（続き） ⾮クラスタ化インデックスで完結するため、 実⾏プランにもキー参照があらわれない。
76. カバリングインデックスのポイント • カバリングかどうかはクエリごとに決まる • インデックスは下記のクエリに対して「カバリング」 • インデックスは 下記のクエリに対して「カバリングではない」 CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName] ASC) INCLUDE (RegistDate) SELECT LoginName, RegistDate FROM Member WHERE LoginName = 'Tawny265167' SELECT LoginName, RegistDate, Sei FROM Member WHERE LoginName = 'Tawny265167'
77. Chapter 3 Lesson 8 インデックスのまとめ
78. インデックスのまとめ① • クラスタ化インデックス • キーの順番で並び替えられている • 1テーブルにつき1個だけしか作れない • ⾮クラスタ化インデックス • キーの順番で並び替えられている キーの範囲情報 キーの範囲情報 実データ キー + クラスタ化インデックス キー Seek Scan Seek Scan
79. • 付加列インデックス • ⾮クラスタ化インデックスとほぼ同じ • リーフページに付加列も含まれている • 複合インデックス • ⾮クラスタ化インデックスとほぼ同じ • 複数のカラムをインデックスキーに指定 キー＋クラスタ化インデックスキー＋付加列 インデックスのまとめ② キーの範囲情報 複数列のキー の範囲情報 Seek Scan Seek Scan キー + クラスタ化インデックス キー
80. インデックスのまとめ③ カバリングインデックス • あるクエリに必要な全カラムを含むインデックス • クエリ①ではカバリングだがクエリ②ではカバリングではないという ふうに、クエリ単位でカバリングかどうかは変わってくる
81. Seek vs Scan • Seekが有利なとき • レコードを⼤幅に絞り込めるとき • Scanが有利なときやScanしか使われないとき • レコード数が少ないテーブル • count(*)などの集計処理で全件⾛査が必要なとき
82. インデックスのメリット：「データ透過性」 ロジックに影響しないので、インデックスを追加・削除しても 同じSQLなら取得できるデータは変わらない ⇒ 機能⾯のテスト不要
83. インデックスのデメリット • ディスク容量が増える • 各インデックスは物理的に独⽴している • インデックスを作成したテーブルの更新速度が落ちる • 1レコードをINSERTするときも、最⼤でインデックスの数と同じだけ 書き込みが発⽣する
84. Chapter 3 Lesson 9 インデックスの練習問題
85. 問題：インデックスを設計してください DECLARE @Tel VARCHAR(20) SET @Tel = '0292866656' SELECT MemberID FROM Member WHERE tel = @Tel
86. 回答 ※MemberIDは主キーなので⾃動的にインデックスに含まれる CREATE NONCLUSTERED INDEX [IX_Member_Tel] ON [dbo].[Member] ([Tel])
87. 問題：インデックスを設計してください DECLARE @LoginName VARCHAR(20) SET @LoginName = 'Keg River4714' SELECT MemberID ,GenderID ,PrefectureID FROM Member WHERE LoginName = @LoginName
88. 回答① CREATE NONCLUSTERED INDEX [IX_Memer_LoginName] ON [dbo].[Member] ([LoginName])
89. 回答②：付加列を追加 CREATE NONCLUSTERED INDEX [IX_Memer_LoginName] ON [dbo].[Member] ([LoginName]) INCLUDE ([GenderID], [PrefectureID])
90. Chapter 4 テーブルサイズの変化と インデックス構造
91. Chapter 4 Lesson 1 レコード数増加の影響調査
92. サービス成⻑に伴ってデータは増え続ける レコード数が増えていったときに • インデックスの構造はどう変わるかを理解する • クエリの実⾏速度はどう変わるかを理解する
93. レコード数だけが異なる3つのDB
94. 事前準備：各DBでインデックス再構築 use MyTuningDB_small alter index PK_MemberEMail on MemberEMail rebuild go use MyTuningDB_middle alter index PK_MemberEMail on MemberEMail rebuild go use MyTuningDB_large alter index PK_MemberEMail on MemberEMail rebuild go
95. 各DBごとにインデックスの構造を確認 DECLARE @OBJECT_ID int set @OBJECT_ID = OBJECT_ID('MemberEMail') SELECT index_id ,index_type_desc ,index_depth ,index_level ,page_count ,record_count FROM sys.dm_db_index_physical_stats (DB_ID(), @OBJECT_ID, NULL , NULL, 'DETAILED') as A JOIN sys.objects as B on A.object_id = B.object_id ORDER BY index_id, index_level
96. Small 1500万レコード Middle 3000万レコード Large 4500万レコード
97. レコード数増加によるインデックス構造の変化まとめ • 1000万単位のレコード数の差でもツリーの深さはほぼ同じ (index_depth = 3 or 4) • リーフノードのページ数(page_count)はレコード数にほぼ⽐例
98. Chapter 4 Lesson 2 パフォーマンスへの影響
99. パフォーマンスへの影響：Index Scanのとき
100. Small 1500万レコード Middle 3000万レコード Large 4500万レコード パフォーマンスへの影響：Index Scanのとき
101. Index Scanのポイント • レコード数が増えるほど「論理読み取り数」が増加 • リーフノードのページ数にほぼ⼀致 • レコード数が増えるほど如実に実⾏時間が増加 • 1.5秒 → 3秒 → 5秒
102. パフォーマンスへの影響：Index Seekのとき
103. Small 1500万レコード Middle 3000万レコード Large 4500万レコード パフォーマンスへの影響：Index Seekのとき
104. Index Seekのポイント • レコード数が増えても「論理読み取り数」がほぼ同じ • インデックスの階層数にほぼ⼀致 • そのためレコードが⼤幅に増加しても実⾏時間はほぼ同じ • Index Seekの強⼒な特徴
105. パフォーマンスへの影響のまとめ • データ量増加に伴うクエリ実⾏時間の変化を推定するときに • 「そのクエリの実⾏はScanなのか、Seekなのか」を 理解しておくことがとても重要 • 信頼度が低い推定 • 「データ量が増えたから遅くなった/遅くなりそう」 • 信頼度が⾼い推定 • 「データ量が増えてもSeek処理なので速度は変わらないはず」 • 「データ量が増えるとScan処理なので徐々に遅くなっていく懸念 がある」
106. Chapter 5 実⾏プランについて
107. Chapter 5 Lesson 1 実⾏プランとは
108. このチャプターの⽬標 • 実⾏プランの⾒⽅を理解する • 実⾏プランで押さえておくべき演算⼦を理解する • チューニング⽬的で実⾏プランを⾒る際のポイントを理解する
109. 実⾏プランとは • 「どのように」データを取ってくるかの計画図 • SQL Serverがプランを作成する • グラフィカルなものとテキストベースの２種類がある • 今回はグラフィカルな実⾏プランを⽤いる
110. 基本的な実⾏プランの⾒⽅ • 右から左、上から下の順番で実⾏される • ふたつのインデックスから読み取ったデータを合体させる
111. 複雑なプランでも考え⽅は同じ ③ ④ ① ② ⑤ ふたつのインデックスを読み取って合体を繰り返す
112. Chapter 5 Lesson 2 データへのアクセス⽅法
113. 実⾏プランの基本的な演算⼦まとめ • データへのアクセス⽅法 • Scan / Seek / キー参照 • 結合⽅法 • Nested Loops / Hash Match / Merge Join • 並び替え • Sort 他にもたくさんあるが、ボトルネックはこの中のどれかである場合 が多い
114. ① Index Scan
115. ① Index Scan
116. ② Index Seek
117. ② Index Seek
118. ③キー参照
119. ③キー参照
120. Chapter 5 Lesson 3 結合⽅法と並び替え
121. ① Nested Loops
122. ① Nested Loops ‒ ２重for⽂のイメー ジ Table_A 1 2 3 4 Table_B 6 D 1 B 5 C 1 A
123. ① Nested Loops ‒ ２重for⽂のイメー ジ Table_A 1 2 3 4 Table_B 6 D 1 B 5 C 1 A
124. ① Nested Loops ‒ ２重for⽂のイメー ジ Table_A 1 2 3 4 Table_B 6 D 1 B 5 C 1 A
125. ① Nested Loops ‒ ２重for⽂のイメー ジ Table_A 1 2 3 4 Table_B 6 D 1 B 5 C 1 A 1 1 B 1 1 A
126. ② Hash Match
127. ② Hash Match Table_A 1 2 3 4 ハッシュテーブルを作る Table_A ハッシュ値 1 1agsc3 2 fasd98 3 42cf89 4 fgt2cc ハッシュテーブル Table_B 6 D 1 B 5 C 3 A 35vxxv ハッシュ値計算、 マッチング
128. ② Hash Match Table_A 1 2 3 4 ハッシュテーブルを作る Table_A ハッシュ値 1 1agsc3 2 fasd98 3 42cf89 4 fgt2cc ハッシュテーブル Table_B 6 D 1 B 5 C 3 A 35vxxv 1agsc3 ハッシュ値計算、 マッチング h577v
129. ② Hash Match Table_A 1 2 3 4 ハッシュテーブルを作る Table_A ハッシュ値 1 1agsc3 2 fasd98 3 42cf89 4 fgt2cc ハッシュテーブル Table_B 6 D 1 B 5 C 3 A 35vxxv 1agsc3 h577v 42cf89 1 1 B 3 3 A ハッシュ値計算、 マッチング
130. ③ Merge Join
131. ③ Merge Join Table_B 6 D 1 B 5 C 1 A Table_A 1 2 3 5 Table_B 1 A 1 B 5 C 6 D ソート不要 ソート
132. ③ Merge Join Table_B 6 D 1 B 5 C 1 A Table_A 1 2 3 5 Table_B 1 A 1 B 5 C 6 D ソート不要 ソート
133. ③ Merge Join Table_B 6 D 1 B 5 C 1 A Table_A 1 2 3 5 Table_B 1 A 1 B 5 C 6 D ソート不要 ソート
134. ③ Merge Join Table_B 6 D 1 B 5 C 1 A Table_A 1 2 3 5 Table_B 1 A 1 B 5 C 6 D ソート不要 ソート
135. ③ Merge Join Table_B 6 D 1 B 5 C 1 A Table_A 1 2 3 5 Table_B 1 A 1 B 5 C 6 D ソート不要 1 1 A 1 1 B 5 5 C ソート
136. JOINの使い分け • 基本的にSQL Server側が最適な結合⽅法を判断する • JOINする２テーブルのサイズやインデックスの有無で最適な⽅法が違う • Nested Loops • ⼩さめのデータセットや where句でレコード数が⼤幅に絞り込めるとき向き • Merge Join / Hash Match • ⼤規模テーブル同⼠でレコード数が絞り込めないとき向き • メモリ消費⼤。場合によってはtempDBへの物理書き込み発⽣で速度低下
137. 常に最適なJOINが選択されるわけではない 「Nested Loopsが良いはずなのにHash Matchになっている」 「Hash MatchがいいはずなのにNested Loopsになっている」 といった箇所がボトルネックになる可能性はある
138. Sort：データの並び替え
139. Chapter 5 Lesson 4 実⾏プランを確認するさいの ポイント
140. 実⾏プランで⾒るべきポイント① • シーク述語 • Index Seek時にレコードを絞り込む条件 • 述語 • リーフページ⾛査時にレコードを絞り込む条件 • オブジェクト • ⾛査対象のクラスタ化インデックス/⾮クラスタ化インデックス/ヒー プ
141. 実⾏プランで⾒るべきポイント② • 予測⾏数 / 実際の⾏数 / およびその差 • 予測実⾏回数 / 実際の実⾏回数 / およびその差 • 予測と実際の乖離が⼤きく、実⾏時間が⻑い場合は他に最適なプランが 存在する可能性がある
142. 実⾏プランを⾒るさいのマインドセット • 全部を完全に理解していなくてもチューニングは可能 • 「どこがボトルネックで遅いのか」を突き⽌めるよう意識する
143. Chapter 5 Lesson 5 インデックス設計の練習問題
144. 問題：インデックスを設計してください SELECT TOP 10 * FROM MemberEMail ORDER BY Email ASC
145. 回答：ソートをカットするインデックス create index [IX_MemberEMail_Email] on [MemberEmail] ([Email] asc)
146. 問題：インデックスを設計してください SELECT TOP 10 * FROM MemberEMail ORDER BY DeleteFlag ASC ,Email DESC
147. 回答：order byとキーの並び順を同じにする create index [IX_MemberEMail_DeleteFlag_Email] on [MemberEmail] ([DeleteFlag] asc, [EMail] desc)
148. 問題：インデックスを設計してください SELECT TOP 10 * FROM MemberEMail WHERE DeleteFlag = 0 ORDER BY Email
149. 回答：クエリ実⾏順序とキーの順番を同じにする create index [IX_MemberEMail_DeleteFlag_Email] on [MemberEmail]([DeleteFlag], [EMail])
150. Chapter 6 クエリの実⾏
151. Chapter 6 Lesson 1 クエリが実⾏されるまでの流れ
152. クエリ実⾏までの流れの概略図 統計情報など クエリツリー 実⾏プラン SQL Parser Optimizer Query Executor
153. 実⾏プランがキャッシュされている場合 プランキャッシュ 実⾏プラン SQL Parser Query Executor
154. 実⾏プランを理解するためのポイント • 実⾏プランを⽣成するタイミングでは、オプティマイザは WHERE句でどれだけレコードが絞り込まれるかわからない ⇒ 推定するしかない • 絞り込まれるレコード数の推定⽅法 ＝ 基数推定アルゴリズム • 基数推定に使⽤する重要な情報が統計情報
155. Chapter 6 Lesson 2 統計情報について
156. 統計情報の概要 統計情報はカラムの値の分布をヒストグラム情報として保持
157. • インデックス作成時に対応する統計情報が ⾃動作成される • PK_Member • MemberテーブルのPKの統計情報 • _WA_Sys_*** • クエリ実⾏時に⾃動作成される場合あり 統計情報の概要
158. PK_Memberの統計情報 ① • 名前: 統計情報の名前 • 更新 : 統計情報の更新⽇時 • ⾏ : レコード数 • サンプリングされた⾏数 • ⼿順 : ヒストグラムのステップ数 • 列 : どのカラムの情報か
159. • RANGE_HI_KEY：ステップの上限キー • RANGE_ROWS：ステップ内の⾏数（上限は含まない） • EQ_ROWS：上限の値と列の値が等しい⾏数 PK_Memberの統計情報 ②
160. 0 1 3178694 1 2392063 1 4429238 1 0 1 0 select count(*) from Member where MemberID < 18629764 select count(*) from Member where MemberID = 18629764 select count(*) from Member where 18629764 < MemberID and MemberID < 23169772 select count(*) from Member where MemberID = 23169772 select count(*) from Member where 23169772 < MemberID and MemberID < 26721041 select count(*) from Member where MemberID = 26721041 select count(*) from Member where 26721041 < MemberID and MemberID < 33503451 select count(*) from Member where MemberID = 33503451 select count(*) from Member where 33503451 < MemberID and MemberID < 33503454 select count(*) from Member where 33503454 = MemberID select count(*) from Member where 33503454 < MemberID
161. ヒストグラム化するとこうなる
162. 通常はレコード数 >> サンプル 数 • 先ほどの例はサンプリング率100%（フルスキャン） update statistics member update statistics member with fullscan • with fullscan無し：レコード数に応じて⾃動でサンプリング率が決定
163. 統計情報のポイント① • 統計情報は「不完全な情報」 • 各テーブルの各レコードの全カラムの情報がわかれば、最適なプランは ⽣成しやすい • ただし全レコードを実⾏前にチェックしていると時間がかかってしまう → 最適化に時間がかかっては意味がない • そこで統計情報を利⽤する → 完全な情報ではなく、ざっくりとしたカラム値の分布で代⽤ • 基数推定アルゴリズムも完ぺきではない ＝ 誤差は⽣じる • 統計情報を使って、WHERE句の絞り込みレコード数を推定する → 統計情報の時点でざっくりとした情報なので、完璧な精度は出ない ⇒ ⾃分で「このプランは妥当なのか」を評価する⼒が重要
164. • 統計情報は常に更新されるわけではない • 統計の⾃動更新がONになっていても、全レコードの20%が更新されて はじめて統計も更新される • 実際のデータ分布と統計情報に乖離が⽣まれる場合がある • 乖離が⼤きいほど、最適な実⾏プランが⽣成されない可能性も上がる 統計情報のポイント②
165. 統計情報についてのまとめ • オプティマイザの仕事は最⾼のプランを⾒つけることではなく、 限られた時間で「良いプラン」を⽣成すること • オプティマイザは実⾏プランを⽣成する際に統計情報を利⽤ • 統計情報はざっくりとしたカラムの分布をヒストグラムで保持 • 諸条件により最適でないプランが⽣成される場合もある • 統計情報のサンプリング数 • 統計情報の更新⽇時 • 基数推定アルゴリズムの限界
166. Chapter 7 Selectivityを理解する
167. Chapter 7 Lesson 1 Selectivityとは
168. ⾼速なクエリ selectivityの良い検索述語 適切なインデックス = +
169. このレッスンで使う⽤語について • 検索述語：WHERE句の各条件のこと • selectivity (選択性)：検索述語が⾏をどれだけ絞り込めるかの指標 • selectivityが良い：少ない⾏に絞り込みができること • 適切なインデックス：作成することでIOを劇的に削減できるインデックスのこと
170. 検索述語のselectivity評価例① 検索述語：MemberID = 18629764 1,000万レコードを1⾏に絞り込み →「selectivityがもっとも良い」
171. 検索述語のselectivity評価例② 検索述語：DeleteFlag = 0 1,000万レコードを約半数に絞り込み →「selectivityが悪い」
172. 「selectivityが良い」のボーダーラインは？ • 「レコード全体の5%程度」まで絞り込めるか • 1,000万レコードのテーブルであれば、5%の50万レコードまで • ポイント：主キーやユニークキーはselectivityがもっとも良い • 「5％」の基準はディスク性能など環境によって変わる • あくまで⽬安と考えておく
173. 検索述語のselectivity評価例③ 検索述語：DeleteFlag = 0 and PrefectureID = 6 and GenderID = 2 and Sei = 'Marlinʼ 1,000万レコードを26⾏に絞り込みできるので「selectivityが良い」
174. selectivityが良いクエリにインデックスを作成 • demo⽤クエリ select * from Member where DeleteFlag = 0 and PrefectureID = 6 and GenderID = 2 and Sei = 'Marlin' create index IX_Member_1 on Member (DeleteFlag, PrefectureID, GenderID, Sei) • demo⽤インデックス
175. 「適切なインデックス」を作るポイント selectivityの良い検索述語の組み合わせでインデックスを作ること で「適切なインデックス」を作成できる select * from Member where DeleteFlag = 0 and PrefectureID = 6 and GenderID = 2 and Sei = 'Marlin' create index IX_Member_1 on Member (DeleteFlag, PrefectureID, GenderID, Sei)
176. まとめ • ⾼速なクエリ = selectivityの良い検索述語 + 適切なインデック ス • クエリチューニングの際は以下の２点を確認 • selectivityの良い検索述語があるか • 適切なインデックスが存在するか
177. Chapter 7 Lesson 2 複数テーブルを参照するクエリ のSelectivity を評価する
178. 複数テーブルJOIN時のselectivityは？
179. 各テーブルごとにselectivityを評価する • 「Memberの検索述語はselectivityが良い」 • 「MemberEmailの検索述語はselectivityが悪い」
180. 複数テーブルJOIN時のselectivity評価のポイント • 複数テーブルのJOINを含むクエリでは、selectivityが良い テーブルがひとつ以上存在すれば適切なインデックスと 組み合わせることで⾼速化が⾒込める • なぜか？
181. チューニング後の実⾏プラン
182. MemberEMail 評価時の selectivity：悪い （8552233⾏） MemberEMail 実際の selectivity：良い （1⾏）
183. MemberEMailのシーク述語は「結合条件」 select MemberID from Member where DeleteFlag = 0 and Tel = '0698903494' select MemberID from MemberEMail where MemberID = 18629764 and MainFlag = 1
184. 元クエリ SQL Serverが クエリ実⾏する際の イメージ select A.MemberID from Member A join MemberEMail B on A.MemberID = B.MemberID where A.DeleteFlag = 0 and A.Tel = '0698903494' and B.MainFlag = 1 select MemberID from Member where DeleteFlag = 0 and Tel = '0698903494' select MemberID from MemberEMail where MemberID = 18629764 and MainFlag = 1 ①まずMemberから MemberIDを取得 ②取得したMemberIDを検索 述語に追加
185. 実⾏プランのシーク述語をチェック ・Memberのシーク述語は元クエリのwhere句と同⼀ ・MemberEMailのシーク述語は元クエリのwhere句「MainFlag = 1」 ではなく、元クエリの結合条件「A.MemberID = B.MemberID」
186. select A.MemberID from Member A join MemberEMail B on A.MemberID = B.MemberID where A.DeleteFlag = 0 and A.Tel = '0698903494' and B.MainFlag = 1
187. selectivityが悪い時：後続の処理にも影響 select * from Member A join MemberEMail B on A.MemberID = B.MemberID where A.PrefectureID = 1 and A.DeleteFlag = 0 and A.Sei like 'a%' and B.MainFlag = 1
188. Chapter 7 Lesson 3 selectivityのまとめ
189. selectivityのまとめ① • JOINを含むSELECT⽂の実⾏の流れ 1. 各テーブル（正確にはインデックスまたはヒープ）ごとに データを絞り込む 2. テーブルを合体させる 3. １と２を繰り返す • 検索述語に複数テーブルのカラムが指定されていても、 基本的には最もselectivityが良い検索述語のみがシーク述語 となり、それ以外は結合条件がシーク述語となる
190. selectivityのまとめ② • もっともselectivityが良い検索述語による絞り込みレコード数は、 その後の各結合処理の実⾏回数（≒レコード数）へと 影響が伝搬していく • selectivityが良い → パフォーマンス的な好影響が伝搬していく • selectivityが悪い → パフォーマンス的な悪影響が伝搬していく
191. selectivityのまとめ③ • 複数テーブルのJOINを含むクエリでも、selectivityが良い検索 述語がひとつ以上存在すればチューニングは可能。なぜか？ ⇒クエリ実⾏時、selectivityが良い検索述語によりレコード数 が⼤幅に絞り込まれ、その後の結合時にパフォーマンス的な 好影響が伝搬していくため
192. Chapter 7 Lesson 4 インデックス設計の練習問題
193. 問題：インデックスを設計してください DECLARE @MemberID INT SET @MemberID = 18629764 SELECT * FROM Member a JOIN MemberEMail b ON a.MemberID = b.MemberID WHERE a.MemberID = @MemberID ORDER BY MainFlag DESC
194. 回答：MemberEMailのScanをSeekにしたい CREATE NONCLUSTERED INDEX [IX_MemberEmail_MemberID] ON [dbo].[MemberEMail] ([MemberID])
196. 問題：インデックスを設計してください DECLARE @Tel VARCHAR(100) SET @Tel = '09002505878' SELECT LoginName, Sei, Mei, Tel FROM Member a WHERE EXISTS ( SELECT * FROM MemberEMail b WHERE a.MemberID = b.MemberID AND MainFlag = 1 AND b.DeleteFlag = 0 ) AND Tel = @Tel
197. 回答：まずMemberのScanをSeekにする CREATE NONCLUSTERED INDEX [IX_Member_Tel] ON [dbo].[Member] ([Tel])
199. 次にMemberEMailのScanをSeekにする CREATE NONCLUSTERED INDEX [IX_MemberEMail_MemberID] ON [dbo].[MemberEMail] ([MemberID]) INCLUDE ([MainFlag], [DeleteFlag])
201. インデックス設計のポイント • とりあえず実⾏して「実際の実⾏プラン」をチェックする • 「ボトルネックはどこか」という観点で実⾏プランを⾒る • インデックスをひとつずつ作成していく • プラン確認 → インデックス作成 → プラン確認 をチューニング完了まで繰り返す
202. Chapter 8 全体最適なチューニング
203. Chapter 8 Lesson 1 全体最適なチューニングとは
204. パフォーマンスチューニングの定義 1. 個別最適なチューニング 特定のクエリの「実⾏時間」または「CPU使⽤時間」の削減 2. 全体最適なチューニング 特定のインスタンスで実⾏されている全クエリの 「実⾏時間の合計値」または「CPU使⽤時間の合計値」の削減
205. 全体最適なチューニングをするときの前提 • インスタンスでは多数のクエリが実⾏されている • 各クエリの詳細は把握していない • 各クエリの実⾏頻度も把握していない • 「どのクエリをチューニングすればいいのか」を調査すべき
206. パレートの法則（80:20の法則） • 「全体の数値の⼤部分は全体を構成するうちの⼀部の要素が ⽣み出している」という理論 • 例：ビジネスにおいて、売上の８割は全顧客の２割が⽣み出している • 例：商品の売上の８割は、全商品銘柄のうちの２割が⽣み出している
207. パレートの法則をDBサーバーに当てはめる • DBサーバー１台にかけるCPU負荷の８割は、 全クエリの２割が⽣み出している • DBサーバー１台で実⾏されるクエリの総実⾏時間の８割は、 全クエリの２割が⽣み出している • この「２割」のクエリを⾒つけ出すことが、 少ない労⼒で⼤きなCPU負荷減や総実⾏時間減につながる ※ 実際は１割以下のこともある
208. ZOZOTOWNのDBにおける全体最適なチューニング例 全体の1%のクエリをチューニングしたことで、CPU負荷が50%減
209. パレートの法則を元にした重要な問い • CPU負荷を下げたい場合 「今⾃分がチューニングしようとしている箇所は CPU負荷全体の何割を占めるのか？」 • 実⾏時間を下げたい場合 「今⾃分がチューニングしようとしている箇所は 総実⾏時間の何割を占めるのか？」
210. 全体最適なチューニングでもっとも重要なこと 「どのクエリが全体のCPU負荷の何％を占めているのか」 「どのクエリが全体の実⾏時間の何％を占めているのか」 ということについての事前調査を⾏う ⇒ そのためにはツールを使う
211. Chapter 8 Lesson 2 拡張イベントとは
212. 拡張イベントとは 軽量なパフォーマンス監視システム ⇒ SQL Server ProfilerやSQLトレースの上位互換
213. 拡張イベントの例
214. チューニング⽬的でよく使⽤するイベント • rpc_completed：リモートプロシージャコール完了 • sql_batch_completed：バッチ完了 • sp_statement_completed：ストアドプロシージャの ステートメント完了 • sql_statement_completed：ステートメント実⾏完了
215. 各イベントの関係性 cpu/durationともに rpc_completed + sql_batch_completed ≒ コンパイル時間 + sp_statement_completed + sql_statement_completed まずはrpc_completed+sql_batch_completedの粒度で調査する 必要に応じてstatement系にドリルダウンする
216. Chapter 8 Lesson 3 DMVを使ったチューニング 対象の選定⽅法
217. DMV(Dynamic Management View)とは • サーバーの情報が格納されたViewのこと。沢⼭種類がある • sys.dm_exec_query_stats ⇒ クエリの実⾏統計（cpu/durationなど）をキャッシュ • 累積値なので、２回情報を取得して差分をとれば該当時間帯の 各クエリのcpuやdurationの合計値を取得できる ⇒ 降順に並び替えると、cpu負荷をかけているクエリを⾒つけ るといったことが可能
218. Chapter 8 Lesson 4 全体最適なインデックスとは
219. テーブルに作成すべきインデックスの数は？ • ⼀般的にインデックスが増えるほど • 読み取りは⾼速になる • 更新は低速になる ⇒ 明確な答えはない • あるテーブルAに作成すべき最適なインデックスは 「テーブルAを参照する全クエリの総実⾏時間を最⼩化する インデックスの組み合わせ」
220. 総実⾏時間を最⼩化するインデックスの組み合わせ • インデックスの組み合わせで総実⾏時間や総CPU時間は変化する • 考えられるインデックス全パターンを試すのは時間的に無理 • そのため、実際には完璧な最適解は得られない
221. ではどうするか？ 各テーブルに関するワークロードの性質を理解して柔軟に対応する • write heavyならインデックスは必要最⼩限に留める • read heavyならインデックスを積極的に作成する
222. インデックス作成の基本戦略 • 必要最⼩限のインデックス設計を⼼がける • キーが同じで付加列だけ異なる等のインデックスはまとめる • 意図した使われ⽅をしているか確認する • DMVを使ってseek回数とscan回数の⽐率をチェックする
223. インデックス作成のアンチパターン 各クエリごとに最適なインデックスを作成する ⇒ 似たインデックスが⼤量に作成される CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (RegistDate) CREATE NONCLUSTERED INDEX [IX_Member_LoginName2] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (Sei, Mei) CREATE NONCLUSTERED INDEX [IX_Member_LoginName3] ON [dbo].[Member] ( [LoginName] ASC, [RegistDate] ASC ) INCLUDE (Sei, Mei) CREATE NONCLUSTERED INDEX [IX_Member_LoginName4] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (DeleteFlag)
224. CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (RegistDate) CREATE NONCLUSTERED INDEX [IX_Member_LoginName2] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (Sei, Mei) CREATE NONCLUSTERED INDEX [IX_Member_LoginName3] ON [dbo].[Member] ( [LoginName] ASC, [RegistDate] ASC ) INCLUDE (Sei, Mei) CREATE NONCLUSTERED INDEX [IX_Member_LoginName4] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (DeleteFlag) CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ( [LoginName] ASC , [RegistDate] ASC ) INCLUDE (RegistDate, Sei, Mei, DeleteFlag)
225. 意図した使われ⽅をしているか確認する declare @TableName varchar(1000) = 'Member' select OBJECT_NAME(i.object_id) as table_name ,i.name as index_name ,ps.row_count as row_count ,ps.reserved_page_count * 8.0 / 1024 as size_mb ,type_desc ,us.* from sys.dm_db_partition_stats ps left join sys.indexes i on ps.object_id = i.object_id and ps.index_id = i.index_id left join sys.dm_db_index_usage_stats us on ps.object_id = us.object_id and ps.index_id = us.index_id where OBJECT_NAME(i.object_id) = @TableName order by index_id
226. Chapter 8 Lesson 5 全体最適なインデックスの設計
227. 必要最⼩限のインデックスを設計する SELECT句でしか使わないカラムは基本的には付加列にする select Sei, Mei from Member where LoginName = 'Test' 〇 CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName]) INCLUDE ([Sei], [Mei]) × CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName], [Sei], [Mei])
228. なぜSELECT⽤カラムは付加列か？ ・付加列として加えておいた⽅がインデックスに拡張性がある select top 10 Sei, Mei from Member where LoginName like 'Te%' and DeleteFlag = 1 • 例えば、上記のクエリを使った処理を 新しくリリースした場合を考慮する
229. 付加列にしたインデックスの場合 ・既存のインデックスを⼀度DROPして作り直せばOK CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName]) INCLUDE ([Sei], [Mei]) CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName], [DeleteFlag]) INCLUDE ([Sei], [Mei]) ・ひとつのインデックスで２種類のクエリに対応可能
230. CREATE NONCLUSTERED INDEX [IX_Memaber_LoginName] ON [dbo].[Member] ([LoginName], [DeleteFlag]) INCLUDE ([Sei], [Mei]) select Sei, Mei from Member where LoginName like 'Te%' and DeleteFlag = 1 select Sei, Mei from Member where LoginName = 'Test'
231. 付加列にしないインデックスの場合 • 追加で別のインデックスを作るしかなくなる CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName], [Sei], [Mei]) CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName], [DeleteFlag], [Sei], [Mei]) • このインデックスにまとめようとするのはNG。なぜか？
232. • インデックスを修正する時点でプロダクション環境で 実⾏されている全クエリを把握するのは難しい CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName], [Sei], [Mei]) select * from Member where LoginName = 'Test' and Sei = 'aaa' and Mei = 'bbb' • この既存インデックスだけみると、下記のようなクエリが すでに実⾏されていると考えるのが妥当 ・よってインデックスを増やすしかなくなる
233. • 結果的に以下のふたつのインデックスを作成する必要が出てくる CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName], [Sei], [Mei]) CREATE NONCLUSTERED INDEX [IX_Member_LoginName2] ON [dbo].[Member] ([LoginName], [DeleteFlag], [Sei], [Mei]) • 無駄な容量や更新時のコスト増につながる
234. ・⼀般化：良いselectivityが得られる最⼩カラム構成をとる 例：MemberテーブルはLoginNameでUniqueとなる性質がある ↓これが0レコード select LoginName from Member group by LoginName having count(*) > 1 select Sei, Mei from Member where LoginName = 'Test' and DeleteFlag = 1 CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName]) INCLUDE ([Sei], [Mei], [DeleteFlag]) ・したがって、上記のクエリのインデックスは下記でOK
235. インデックスの設計タイミング • 新機能作成に伴ってテーブルが追加される場合 １．必要なクエリをすべて作成 ２．各クエリに最適なインデックスを設計する（個別最適） ３．２で設計したインデックスをできる限りまとめる（全体最適） • 既存クエリをチューニングする場合 １．チューニングしたいクエリに最適なインデックスを設計（個別最適） ２．既存インデックスとまとめられる場合はまとめる（全体最適）
236. 全体最適なインデックス設計のまとめ • 良いselectivityが得られる最⼩カラム構成をとる • SELECT句でしか使わないカラムは基本付加列にする • WHERE句で使っているカラムでも、 それ以外のカラムで良いselectivityが得られるなら付加列でOK • 理由：インデックスの拡張性を保ち、できるだけ少ない インデックスで多くのクエリに対応できるため
237. Chapter 8 Lesson 6 クエリを書くときのポイント
238. ポイント①：アドホッククエリを避ける • アドホッククエリ • where句などで値が直接指定されているクエリのこと • SQLインジェクションの危険性 • 毎回コンパイルされる可能性が⾼くCPU負荷増、実⾏時間増に つながりやすい • 1⽂字でも異なれば各クエリすべてがキャッシュされるため メモリ効率が悪い select * from Member where PrefectureID = 2
239. 開発時の基本⽅針 • パラメータ化クエリまたはストアドプロシージャを使⽤する • パラメータごとのselectivityが⼤きく異なる場合は パラメータスニッフィングによる実⾏速度の低下に注意する • クエリプランの後退にも注意する
240. パラメータごとのselectivity 例：1,000万レコードのテーブル • select * from Member where PrefectureID = 1 → 9,999,995レコード • select * from Member where PrefectureID = 2 → １レコード • select * from Member where PrefectureID = 3 → １レコード • select * from Member where PrefectureID = 4 → １レコード • select * from Member where PrefectureID = 5 → １レコード • select * from Member where PrefectureID = 6 → １レコード 「PrefectureID = 1」だけ selectivityが⼤きく異なる
241. パラメータスニッフィングとは コンパイル時に受け取ったパラメータにとって最適な実⾏プランを ⽣成する挙動のこと declare @PrefID int = 1 select top 10 * from Member where PrefectureID = @PrefID IndexScanの実⾏プランがキャッシュされ、@PrefIDで別の値が指定されても IndexScanでクエリ実⾏される @PrefID=1の場合はテーブルの99%以上のレコードが取得されるためIndexScan
242. パラメータスニッフィングによる実⾏速度の低下 • @PrefID=2の場合：１レコード取得のためIndexSeekが理想 • @PrefID=1に最適なプランがキャッシュされているため、 IndexScanで実⾏されてしまい実⾏速度が低下する
243. 実⾏速度の低下に対する対策 リコンパイルヒントを付けて毎回コンパイルする • ストアドプロシージャ：with recompile • パラメータ化クエリ：option (recompile)
244. クエリプランの後退とは 本来はもっと⾼速に実⾏できる実⾏プランがあるのに 低速な別の実⾏プランで実⾏されてしまう現象のこと ⇒ いつもは問題なく実⾏できているクエリがなにもしていないのに 突然遅くなった場合はクエリプランの後退を疑う
245. クエリプランの後退への対策 • クエリで使っている統計情報を更新する • 該当クエリをリコンパイルする • DBCC FREEPROCCACHE (plan_handle) • リコンパイルヒントを付けて毎回コンパイルする • ストアドプロシージャ：with recompile • パラメータ化クエリ：option (recompile)
246. ポイント②：インデックスが効かないケース • 暗黙の型変換 • where col1 = 1234 -- col1がchar(4)の場合はʼ1234ʼにすべき • カラムを加⼯する • where (col1*3) = 5 • where func(col1) = 5 • like ʻ%***%ʼとする
247. ポイント③：ヒント句は使わない • ヒント句は使わず、基本的にはオプティマイザに任せる • 以下のヒント句は場合によっては有効な場合もある • option (maxdop 10) : 並列クエリの多重度を変更する • with(index(index_name))：指定したインデックスの使⽤を強制 • with(forceseek)：index seekを強制
248. Chapter 8 Lesson 7 全体最適なインデックスの 練習問題
249. 問題：全体最適な観点でインデックスを再設計 してください ① CREATE INDEX [IX_Member_Tel] ON [Member] ([tel]) INCLUDE ([RegistDate]) ② CREATE INDEX [IX_Member_LoginName] ON [Member] ([LoginName]) INCLUDE ([Sei], [Mei]) ③ CREATE INDEX [IX_Member_LoginName2] ON [Member] ([LoginName]) INCLUDE ([GenderID], [PrefectureID]) ④ CREATE INDEX [IX_Member_DeleteFlag_RegistDate] ON [Member] ([DeleteFlag], [RegistDate]) ⑤ CREATE INDEX [IX_Member_LoginName_HashedPassword] ON [Member] ([LoginName], [HashedPassword]) INCLUDE ([DeleteFlag]) ⑥
250. この問題の考え⽅ 各インデックスが使っているクエリの速度を落とさずに できるだけインデックスの数を減らす
251. 解説① ① + ⑥ CREATE INDEX [IX_Member_Tel_DeleteFlag] ON [Member] ([tel], [DeleteFlag]) INCLUDE ([RegistDate]) ① CREATE INDEX [IX_Member_Tel] ON [Member] ([tel]) INCLUDE ([RegistDate]) ⑥ CREATE INDEX [IX_Member_Tel_DeleteFlag] ON[Member] ([tel], [DeleteFlag])
252. 解説② ② + ③ + ⑤ CREATE INDEX [IX_Member_LoginName] ON [Member] ([LoginName], [HashedPassword]) INCLUDE ([Sei], [Mei], [GenderID], [PrefectureID], [DeleteFlag]) ② CREATE INDEX [IX_Member_LoginName] ON [Member] ([LoginName]) INCLUDE ([Sei], [Mei]) ③ CREATE INDEX [IX_Member_LoginName2] ON [Member] ([LoginName]) INCLUDE ([GenderID], [PrefectureID]) ⑤ CREATE INDEX [IX_Member_LoginName_HashedPassword] ON [Member] ([LoginName], [HashedPassword]) INCLUDE ([DeleteFlag])
253. 最終的な回答 ① + ⑥ CREATE INDEX [IX_Member_Tel_DeleteFlag] ON [Member] ([tel], [DeleteFlag]) INCLUDE ([RegistDate]) ② + ③ + ⑤ CREATE INDEX [IX_Member_LoginName] ON [Member] ([LoginName], [HashedPassword]) INCLUDE ([Sei], [Mei], [GenderID], [PrefectureID], [DeleteFlag]) ④ CREATE INDEX [IX_Member_DeleteFlag_RegistDate] ON [Member] ([DeleteFlag], [RegistDate])