Tweet
Tweet

Slide 1

Slide 1 text

SQL Server Performance Tuning Essentials Masaki Hirose

Slide 2

Slide 2 text

Chapter 1 受講の準備

Slide 3

Slide 3 text

Chapter 1 Lesson 1 本コースについて

Slide 4

Slide 4 text

本資料のパフォーマンスチューニングの定義 1. 個別最適なチューニング 特定のクエリの「実⾏時間」または「CPU使⽤時間」の削減 2. 全体最適なチューニング 特定のインスタンスで実⾏されている全クエリの「実⾏時間の 合計値」または「CPU使⽤時間の合計値」の削減

Slide 5

Slide 5 text

本資料で学べること • 個別最適の観点でひとつのクエリをチューニングするための知 識と技術 • 全体最適の観点でインスタンス全体の総実⾏時間および総CPU 使⽤時間を削減するための知識と技術

Slide 6

Slide 6 text

本資料の特徴 • 専⽤のサンプルDBを使った実践的なノウハウを知ることができる • 知識としてだけではなく、実際にデモを⾒ながらチューニングに よる速度アップを体感できる

Slide 7

Slide 7 text

対象者 • ⽇頃SQL Server / Azure SQL Databaseを使って開発・運⽤・ 管理などを⾏っており、単⼀または全体的なクエリパフォーマ ンスやサーバーのCPU⾼負荷状態に悩んでいる⼈ • CPU負荷を削減することで、クラウドで稼働中のDBをスケール ダウン／スケールインしてコストカットしたい⼈

Slide 8

Slide 8 text

前提条件 • SQLの使⽤経験 • SQL Serverの使⽤経験 • SQL ServerのPaaSの使⽤経験 (Azure/AWS/GCP) • SQL Server Management Studio (SSMS)の使⽤経験

Slide 9

Slide 9 text

チューニング⼒を上げるためのポイント • ⾃分で考える • ⾃分で⼿を動かす

Slide 10

Slide 10 text

Chapter 1 Lesson 2 サンプルDBの準備

Slide 11

Slide 11 text

サンプルDBの内容 DB名 Member レコード数 MemberEMail レコード数 MyTuningDB_small 1,000万 1,500万 MyTuningDB_middle 2,000万 3,000万 MyTuningDB_large 3,000万 4,500万

Slide 12

Slide 12 text

サンプルDBのダウンロード • https://drive.google.com/drive/u/2/folders/13xVm- alJ2kzuDQyDVvWxSyUR5i6lWx_5 • バックアップファイルを圧縮した3ファイル • 展開したあとにリストア

Slide 13

Slide 13 text

サンプルDBについて • SQL Server 2019以降でリストア可能 • ⽤途：サンプルDBを使ってクエリチューニングを体感してもらう • データはすべてランダムに⽣成したデータ • 電話番号やメールアドレスはランダムだが、実在の可能性がある ため別⽤途での使⽤は禁⽌

Slide 14

Slide 14 text

個別最適

Slide 15

Slide 15 text

Chapter 2 チューニングの流れ

Slide 16

Slide 16 text

Chapter 2 Lesson 1 パフォーマンスチューニングの定義

Slide 17

Slide 17 text

本コースにおけるパフォーマンスチューニン グの定義 1. 個別最適なチューニング 特定のクエリの「実⾏時間」または「CPU使⽤時間」の削減 2. 全体最適なチューニング 特定のインスタンスで実⾏されている全クエリの「実⾏時間の 合計値」または「CPU使⽤時間の合計値」の削減

Slide 18

Slide 18 text

Chapter 2 Lesson 2 チューニングの流れと評価⽅法

Slide 19

Slide 19 text

評価時に使うふたつの指標 • ユーザーの待ち時間に直結する「実⾏時間(duration)」 • サーバーの負荷に直結する「CPU使⽤時間(cpu)」 基本的にはチューニングすると両⽅減るが、そうでない場合も。 どちらを削減したいかあらかじめ決めておく。

Slide 20

Slide 20 text

クエリチューニングの流れ １．対象クエリと削減したい値（duration/cpu）の決定 ２．指標の計測 ① ３．チューニング実施（クエリ書き換え、インデックス作成等） ４．指標の計測 ② ５．評価

Slide 21

Slide 21 text

クエリチューニングの例 1. 実⾏時間 (duration) を削減したい 2. 指標の計測 ① 3. クエリチューニング（クエリの書き換え、インデックス作成など） 4. 指標の計測 ② 5. 評価 例：チューニングの結果、実⾏時間を1000msから10msへと99% 削減できた

Slide 22

Slide 22 text

評価するさいの注意点 • sys.dm_exec_query_statsにはコンパイルの時間は含まれない

Slide 23

Slide 23 text

ポイント：体感ではなく数値で⽐較する ×「かなり時間がかかっていたのが⼀瞬で完了するようになった」 〇「10秒かかっていたのが100 msecで完了するようになった」

Slide 24

Slide 24 text

Chapter 2 Lesson 3 set statistics time onとは

Slide 25

Slide 25 text

duration 計測⽅法① set statistics time on cpu

Slide 26

Slide 26 text

Chapter 2 Lesson 4 sys.dm_exec_query_statsとは

Slide 27

Slide 27 text

計測⽅法② sys.dm_exec_query_stats • キャッシュされたクエリプランのパフォーマンス統計を取得 • プランがキャッシュから削除されると、そのプランに紐づく 統計データも消える • 粒度はステートメント単位 → 1ストアド内で3クエリ実⾏するストアドだと3⾏取得

Slide 28

Slide 28 text

Chapter 2 Lesson 5 計測⽅法の使い分け

Slide 29

Slide 29 text

duration/cpu 計測⽅法の使い分け • リリース前：set statistics time on SSMSでの検証時に⼿動で実⾏するクエリ • リリース後：sys.dm_exec_query_stats アプリケーションが実⾏しているクエリ

Slide 30

Slide 30 text

なぜsys.dm_exec_query_statsを使うのか？ • プロダクション環境ではさまざまなパラメータでクエリが実⾏ • パラメータによって実⾏時間が⼤幅に変化するケースもある 例）リリース前の⼿動での検証時は「where col1 = 1」で1秒だったが、リリース後は 「where col1 = 2」が指定されることが多く、この場合だと10秒かかる。 • sys.dm_exec_query_statsを使えば、プロダクション環境で指定 されるさまざまなパラメータによって実⾏された実際の duration/cpuを取得できる • プロダクション環境でのチューニングの評価⽅法として妥当

Slide 31

Slide 31 text

注意点① durationとcpuは⼤きく違う場合も • duration >> cpu ロック競合などで待たされる時間が多いと、この関係になる • duration << cpu 並列クエリの場合はcpu時間のほうが⼤きくなることがある • duration/cpuは個別に把握することが⼤事

Slide 32

Slide 32 text

• コストとは • あるスペックのマシンでそのクエリを実⾏した場合に想定さ れる実⾏時間 • コストが⾼くても「実⾏時間が⻑そうだとSQL Serverが思っ ている」ということまでしかわからない • 基本的にはコストが低いほどduration/cpuも低いが、コストが 下がってもduration/cpuが増えることもある ⇒ 指標として不適当 注意点② 評価の指標にコスト値は使わない

Slide 33

Slide 33 text

Chapter 3 インデックスをマスターする

Slide 34

Slide 34 text

Chapter 3 Lesson 1 インデックスを使う理由

Slide 35

Slide 35 text

SQLは宣⾔型⾔語 最⼤の利点は「なにを」と「どのように」を分離できること select * from Member where MemberID = 18629764 なにを取得したいか＝SQL どのように取得するか＝実⾏プラン

Slide 36

Slide 36 text

「どのように」は本来知らなくていい • 「なにを＝SQL」が書ければ、欲しいデータはとれる • 「どのように」は本来知らなくていいが、チューニングのため に踏み込む必要あり

Slide 37

Slide 37 text

duration/cpuに影響を与えるのは実⾏プラン • SQLをいくら書き換えても、実⾏プランが変化しなければ durationもcpuも同じ • 実⾏プランを変化させる有効な⼿段のひとつが「インデックス」

Slide 38

Slide 38 text

durationの差と実⾏プランの違い • インデックス効いてる • select * from [Member] where LoginName = 'HunterGreen45744363' • インデックス効いてない • select * from [Member] with(index(PK_Member)) where LoginName = 'HunterGreen45744363ʼ

Slide 39

Slide 39 text

Chapter 3 Lesson 2 テーブルとインデックスのアーキテ クチャ

Slide 40

Slide 40 text

論理的な構造 8KBの連続領域 物理的な構造 テーブルのアーキテクチャ ツリー構造

Slide 41

Slide 41 text

インデックスとは • クエリ⾼速化に有効なデータ構造 • さまざまな種類があるが、SQL Serverでもっとも⼀般的なのは ツリー構造のインデックス

Slide 42

Slide 42 text

２種類の基本的なインデックス • クラスタ化インデックス • １テーブルに１個 • テーブルの実データ（＝全カラム）が格納 • ⾮クラスタ化インデックス • １テーブルに0〜999個 • テーブルの⼀部のカラムが格納 ⇒ どちらもページを論理的につなげてツリー構造にしたもの

Slide 43

Slide 43 text

page page page page page page page 双⽅向連結リスト B-Tree (Balanced-Tree) Root Branch (中間) Leaf

Slide 44

Slide 44 text

Chapter 3 Lesson 3 クラスタ化インデックスとは

Slide 45

Slide 45 text

クラスタ化インデックスの定義 [MemberID]で並び替えられたツリー構造になっている ALTER TABLE [dbo].[Member] ADD CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED ( [MemberID] ASC )

Slide 46

Slide 46 text

クラスタ化インデックス：キーで並び替え 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

Slide 47

Slide 47 text

Chapter 3 Lesson 4 Index SeekとIndex Scan

Slide 48

Slide 48 text

ポイント インデックスは「うまく使われれば」クエリを⾼速化できる

Slide 49

Slide 49 text

ひとつずつ実⾏してみよう select * from Member where MemberID = 18629768 select * from Member where LoginName = 'Janita1317' どちらも使われるインデックスは同じ ⇒ インデックスはどう使われたのか？

Slide 50

Slide 50 text

瞬時に実⾏完了したクエリ：Index Seek select * from Member where MemberID = 18629768

Slide 51

Slide 51 text

時間がかかったクエリ：Index Scan select * from Member where LoginName = 'Janita1317'

Slide 52

Slide 52 text

⽮印の向きが違うだけなのになぜ速い/遅い？ 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

Slide 53

Slide 53 text

ツリーの深さ(4) << リーフページ数 (160350) リーフページ数：160350 ツリーの深さ：4 Level=2 Level=1 Level=0 Level=3

Slide 54

Slide 54 text

ツリーの深さ(4) << リーフページ数 (160350) リーフページ数：160350 ツリーの深さ：4 Level=2 Level=1 Level=0 Level=3 seek時の読み取りページ数 << scan時の読み取りページ 数

Slide 55

Slide 55 text

Seek = 読み取りページ数が圧倒的に⼩さい（160350 → 4) = ⾼速 リーフページ数：160350 ツリーの深さ：4 Level=2 Level=1 Level=0 Level=3

Slide 56

Slide 56 text

Chapter 3 Lesson 5 ⾮クラスタ化インデックスで ScanからSeekへ変化させる

Slide 57

Slide 57 text

インデックスが効く(=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' インデックス≠万能薬

Slide 58

Slide 58 text

set statistics io onで読み取りページ数を確 認 seek scan

Slide 59

Slide 59 text

set statistics time, io on で両⽅確認も可能

Slide 60

Slide 60 text

時間がかかったクエリを⾼速化するには？ select * from Member where LoginName = 'Janita1317'

Slide 61

Slide 61 text

これが

Slide 62

Slide 62 text

こうなればOK

Slide 63

Slide 63 text

そのための新しいインデックス CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ( [LoginName] ASC )

Slide 64

Slide 64 text

⾮クラスタ化インデックス Root Branch Leaf キー＋クラスタ化インデックスキー インデックスキーの範囲情報 ＋ 対応するページ番号 LoginName Page a1-c1 ① c2-d1 ② ① ② LoginName Page a1-b1 ③ b2-c1 ④ ③ ④ LoginName MemberID a1 1 a2 2 … … b1 25000

Slide 65

Slide 65 text

Chapter 3 Lesson 6 ⾮クラスタ化インデックスの バリエーション

Slide 66

Slide 66 text

付加列インデックス CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ( [LoginName] ASC ) INCLUDE (RegistDate)

Slide 67

Slide 67 text

付加列インデックスの構造 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

Slide 68

Slide 68 text

複合インデックス CREATE NONCLUSTERED INDEX [IX_Member_Sei_PrefectureID] ON [dbo].[Member] ( [Sei] ASC, [PrefectureID] ASC ) 複合インデックス = 複数のカラムをインデックスキーに指定

Slide 69

Slide 69 text

複合インデックス 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 作成時に指定したカラムの順序で インデックスキーが作成される

Slide 70

Slide 70 text

複合インデックスを使⽤した検索 インデックスキーに指定したカラムの順序が検索効率に影響する 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で検索が⾏われないケース ⇒ インデックスの先頭には検索に使われる頻度の多い項⽬を指定する

Slide 71

Slide 71 text

Chapter 3 Lesson 7 キー参照と カバリングインデックス

Slide 72

Slide 72 text

キー参照 ⾮クラスタ化インデックスだけではカラムを返せないとき • ⾮クラスタ化インデックスでIndex Seek • リーフページでクラスタ化インデックスキー取得 • 取得したキーでクラスタ化インデックスをIndex Seek ( = キー参照)

Slide 73

Slide 73 text

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)

Slide 74

Slide 74 text

カバリングインデックス • 上記ようなインデックスが作成されている場合 CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName] ASC) INCLUDE (RegistDate) キー参照が発⽣しないインデックスを「カバリングインデックス」 SELECT LoginName, RegistDate FROM Member WHERE LoginName = 'Tawny265167' インデックスキー インデックスキー INCLUDE カラム クエリに必要なカラムをカバーするインデックスになっている ＝カバリングインデックス

Slide 75

Slide 75 text

カバリングインデックス（続き） ⾮クラスタ化インデックスで完結するため、 実⾏プランにもキー参照があらわれない。

Slide 76

Slide 76 text

カバリングインデックスのポイント • カバリングかどうかはクエリごとに決まる • インデックスは下記のクエリに対して「カバリング」 • インデックスは 下記のクエリに対して「カバリングではない」 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'

Slide 77

Slide 77 text

Chapter 3 Lesson 8 インデックスのまとめ

Slide 78

Slide 78 text

インデックスのまとめ① • クラスタ化インデックス • キーの順番で並び替えられている • 1テーブルにつき1個だけしか作れない • ⾮クラスタ化インデックス • キーの順番で並び替えられている キーの範囲情報 キーの範囲情報 実データ キー + クラスタ化インデックス キー Seek Scan Seek Scan

Slide 79

Slide 79 text

• 付加列インデックス • ⾮クラスタ化インデックスとほぼ同じ • リーフページに付加列も含まれている • 複合インデックス • ⾮クラスタ化インデックスとほぼ同じ • 複数のカラムをインデックスキーに指定 キー＋クラスタ化インデックスキー＋付加列 インデックスのまとめ② キーの範囲情報 複数列のキー の範囲情報 Seek Scan Seek Scan キー + クラスタ化インデックス キー

Slide 80

Slide 80 text

インデックスのまとめ③ カバリングインデックス • あるクエリに必要な全カラムを含むインデックス • クエリ①ではカバリングだがクエリ②ではカバリングではないという ふうに、クエリ単位でカバリングかどうかは変わってくる

Slide 81

Slide 81 text

Seek vs Scan • Seekが有利なとき • レコードを⼤幅に絞り込めるとき • Scanが有利なときやScanしか使われないとき • レコード数が少ないテーブル • count(*)などの集計処理で全件⾛査が必要なとき

Slide 82

Slide 82 text

インデックスのメリット：「データ透過性」 ロジックに影響しないので、インデックスを追加・削除しても 同じSQLなら取得できるデータは変わらない ⇒ 機能⾯のテスト不要

Slide 83

Slide 83 text

インデックスのデメリット • ディスク容量が増える • 各インデックスは物理的に独⽴している • インデックスを作成したテーブルの更新速度が落ちる • 1レコードをINSERTするときも、最⼤でインデックスの数と同じだけ 書き込みが発⽣する

Slide 84

Slide 84 text

Chapter 3 Lesson 9 インデックスの練習問題

Slide 85

Slide 85 text

問題：インデックスを設計してください DECLARE @Tel VARCHAR(20) SET @Tel = '0292866656' SELECT MemberID FROM Member WHERE tel = @Tel

Slide 86

Slide 86 text

回答 ※MemberIDは主キーなので⾃動的にインデックスに含まれる CREATE NONCLUSTERED INDEX [IX_Member_Tel] ON [dbo].[Member] ([Tel])

Slide 87

Slide 87 text

問題：インデックスを設計してください DECLARE @LoginName VARCHAR(20) SET @LoginName = 'Keg River4714' SELECT MemberID ,GenderID ,PrefectureID FROM Member WHERE LoginName = @LoginName

Slide 88

Slide 88 text

回答① CREATE NONCLUSTERED INDEX [IX_Memer_LoginName] ON [dbo].[Member] ([LoginName])

Slide 89

Slide 89 text

回答②：付加列を追加 CREATE NONCLUSTERED INDEX [IX_Memer_LoginName] ON [dbo].[Member] ([LoginName]) INCLUDE ([GenderID], [PrefectureID])

Slide 90

Slide 90 text

Chapter 4 テーブルサイズの変化と インデックス構造

Slide 91

Slide 91 text

Chapter 4 Lesson 1 レコード数増加の影響調査

Slide 92

Slide 92 text

サービス成⻑に伴ってデータは増え続ける レコード数が増えていったときに • インデックスの構造はどう変わるかを理解する • クエリの実⾏速度はどう変わるかを理解する

Slide 93

Slide 93 text

レコード数だけが異なる3つのDB

Slide 94

Slide 94 text

事前準備：各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

Slide 95

Slide 95 text

各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

Slide 96

Slide 96 text

Small 1500万レコード Middle 3000万レコード Large 4500万レコード

Slide 97

Slide 97 text

レコード数増加によるインデックス構造の変化まとめ • 1000万単位のレコード数の差でもツリーの深さはほぼ同じ (index_depth = 3 or 4) • リーフノードのページ数(page_count)はレコード数にほぼ⽐例

Slide 98

Slide 98 text

Chapter 4 Lesson 2 パフォーマンスへの影響

Slide 99

Slide 99 text

パフォーマンスへの影響：Index Scanのとき

Slide 100

Slide 100 text

Small 1500万レコード Middle 3000万レコード Large 4500万レコード パフォーマンスへの影響：Index Scanのとき

Slide 101

Slide 101 text

Index Scanのポイント • レコード数が増えるほど「論理読み取り数」が増加 • リーフノードのページ数にほぼ⼀致 • レコード数が増えるほど如実に実⾏時間が増加 • 1.5秒 → 3秒 → 5秒

Slide 102

Slide 102 text

パフォーマンスへの影響：Index Seekのとき

Slide 103

Slide 103 text

Small 1500万レコード Middle 3000万レコード Large 4500万レコード パフォーマンスへの影響：Index Seekのとき

Slide 104

Slide 104 text

Index Seekのポイント • レコード数が増えても「論理読み取り数」がほぼ同じ • インデックスの階層数にほぼ⼀致 • そのためレコードが⼤幅に増加しても実⾏時間はほぼ同じ • Index Seekの強⼒な特徴

Slide 105

Slide 105 text

パフォーマンスへの影響のまとめ • データ量増加に伴うクエリ実⾏時間の変化を推定するときに • 「そのクエリの実⾏はScanなのか、Seekなのか」を 理解しておくことがとても重要 • 信頼度が低い推定 • 「データ量が増えたから遅くなった/遅くなりそう」 • 信頼度が⾼い推定 • 「データ量が増えてもSeek処理なので速度は変わらないはず」 • 「データ量が増えるとScan処理なので徐々に遅くなっていく懸念 がある」

Slide 106

Slide 106 text

Chapter 5 実⾏プランについて

Slide 107

Slide 107 text

Chapter 5 Lesson 1 実⾏プランとは

Slide 108

Slide 108 text

このチャプターの⽬標 • 実⾏プランの⾒⽅を理解する • 実⾏プランで押さえておくべき演算⼦を理解する • チューニング⽬的で実⾏プランを⾒る際のポイントを理解する

Slide 109

Slide 109 text

実⾏プランとは • 「どのように」データを取ってくるかの計画図 • SQL Serverがプランを作成する • グラフィカルなものとテキストベースの２種類がある • 今回はグラフィカルな実⾏プランを⽤いる

Slide 110

Slide 110 text

基本的な実⾏プランの⾒⽅ • 右から左、上から下の順番で実⾏される • ふたつのインデックスから読み取ったデータを合体させる

Slide 111

Slide 111 text

複雑なプランでも考え⽅は同じ ③ ④ ① ② ⑤ ふたつのインデックスを読み取って合体を繰り返す

Slide 112

Slide 112 text

Chapter 5 Lesson 2 データへのアクセス⽅法

Slide 113

Slide 113 text

実⾏プランの基本的な演算⼦まとめ • データへのアクセス⽅法 • Scan / Seek / キー参照 • 結合⽅法 • Nested Loops / Hash Match / Merge Join • 並び替え • Sort 他にもたくさんあるが、ボトルネックはこの中のどれかである場合 が多い

Slide 114

Slide 114 text

① Index Scan

Slide 115

Slide 115 text

① Index Scan

Slide 116

Slide 116 text

② Index Seek

Slide 117

Slide 117 text

② Index Seek

Slide 118

Slide 118 text

③キー参照

Slide 119

Slide 119 text

③キー参照

Slide 120

Slide 120 text

Chapter 5 Lesson 3 結合⽅法と並び替え

Slide 121

Slide 121 text

① Nested Loops

Slide 122

Slide 122 text

① Nested Loops ‒ ２重for⽂のイメー ジ Table_A 1 2 3 4 Table_B 6 D 1 B 5 C 1 A

Slide 123

Slide 123 text

① Nested Loops ‒ ２重for⽂のイメー ジ Table_A 1 2 3 4 Table_B 6 D 1 B 5 C 1 A

Slide 124

Slide 124 text

① Nested Loops ‒ ２重for⽂のイメー ジ Table_A 1 2 3 4 Table_B 6 D 1 B 5 C 1 A

Slide 125

Slide 125 text

① 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

Slide 126

Slide 126 text

② Hash Match

Slide 127

Slide 127 text

② 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 ハッシュ値計算、 マッチング

Slide 128

Slide 128 text

② 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

Slide 129

Slide 129 text

② 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 ハッシュ値計算、 マッチング

Slide 130

Slide 130 text

③ Merge Join

Slide 131

Slide 131 text

③ 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 ソート不要 ソート

Slide 132

Slide 132 text

③ 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 ソート不要 ソート

Slide 133

Slide 133 text

③ 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 ソート不要 ソート

Slide 134

Slide 134 text

③ 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 ソート不要 ソート

Slide 135

Slide 135 text

③ 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 ソート

Slide 136

Slide 136 text

JOINの使い分け • 基本的にSQL Server側が最適な結合⽅法を判断する • JOINする２テーブルのサイズやインデックスの有無で最適な⽅法が違う • Nested Loops • ⼩さめのデータセットや where句でレコード数が⼤幅に絞り込めるとき向き • Merge Join / Hash Match • ⼤規模テーブル同⼠でレコード数が絞り込めないとき向き • メモリ消費⼤。場合によってはtempDBへの物理書き込み発⽣で速度低下

Slide 137

Slide 137 text

常に最適なJOINが選択されるわけではない 「Nested Loopsが良いはずなのにHash Matchになっている」 「Hash MatchがいいはずなのにNested Loopsになっている」 といった箇所がボトルネックになる可能性はある

Slide 138

Slide 138 text

Sort：データの並び替え

Slide 139

Slide 139 text

Chapter 5 Lesson 4 実⾏プランを確認するさいの ポイント

Slide 140

Slide 140 text

実⾏プランで⾒るべきポイント① • シーク述語 • Index Seek時にレコードを絞り込む条件 • 述語 • リーフページ⾛査時にレコードを絞り込む条件 • オブジェクト • ⾛査対象のクラスタ化インデックス/⾮クラスタ化インデックス/ヒー プ

Slide 141

Slide 141 text

実⾏プランで⾒るべきポイント② • 予測⾏数 / 実際の⾏数 / およびその差 • 予測実⾏回数 / 実際の実⾏回数 / およびその差 • 予測と実際の乖離が⼤きく、実⾏時間が⻑い場合は他に最適なプランが 存在する可能性がある

Slide 142

Slide 142 text

実⾏プランを⾒るさいのマインドセット • 全部を完全に理解していなくてもチューニングは可能 • 「どこがボトルネックで遅いのか」を突き⽌めるよう意識する

Slide 143

Slide 143 text

Chapter 5 Lesson 5 インデックス設計の練習問題

Slide 144

Slide 144 text

問題：インデックスを設計してください SELECT TOP 10 * FROM MemberEMail ORDER BY Email ASC

Slide 145

Slide 145 text

回答：ソートをカットするインデックス create index [IX_MemberEMail_Email] on [MemberEmail] ([Email] asc)

Slide 146

Slide 146 text

問題：インデックスを設計してください SELECT TOP 10 * FROM MemberEMail ORDER BY DeleteFlag ASC ,Email DESC

Slide 147

Slide 147 text

回答：order byとキーの並び順を同じにする create index [IX_MemberEMail_DeleteFlag_Email] on [MemberEmail] ([DeleteFlag] asc, [EMail] desc)

Slide 148

Slide 148 text

問題：インデックスを設計してください SELECT TOP 10 * FROM MemberEMail WHERE DeleteFlag = 0 ORDER BY Email

Slide 149

Slide 149 text

回答：クエリ実⾏順序とキーの順番を同じにする create index [IX_MemberEMail_DeleteFlag_Email] on [MemberEmail]([DeleteFlag], [EMail])

Slide 150

Slide 150 text

Chapter 6 クエリの実⾏

Slide 151

Slide 151 text

Chapter 6 Lesson 1 クエリが実⾏されるまでの流れ

Slide 152

Slide 152 text

クエリ実⾏までの流れの概略図 統計情報など クエリツリー 実⾏プラン SQL Parser Optimizer Query Executor

Slide 153

Slide 153 text

実⾏プランがキャッシュされている場合 プランキャッシュ 実⾏プラン SQL Parser Query Executor

Slide 154

Slide 154 text

実⾏プランを理解するためのポイント • 実⾏プランを⽣成するタイミングでは、オプティマイザは WHERE句でどれだけレコードが絞り込まれるかわからない ⇒ 推定するしかない • 絞り込まれるレコード数の推定⽅法 ＝ 基数推定アルゴリズム • 基数推定に使⽤する重要な情報が統計情報

Slide 155

Slide 155 text

Chapter 6 Lesson 2 統計情報について

Slide 156

Slide 156 text

統計情報の概要 統計情報はカラムの値の分布をヒストグラム情報として保持

Slide 157

Slide 157 text

• インデックス作成時に対応する統計情報が ⾃動作成される • PK_Member • MemberテーブルのPKの統計情報 • _WA_Sys_*** • クエリ実⾏時に⾃動作成される場合あり 統計情報の概要

Slide 158

Slide 158 text

PK_Memberの統計情報 ① • 名前: 統計情報の名前 • 更新 : 統計情報の更新⽇時 • ⾏ : レコード数 • サンプリングされた⾏数 • ⼿順 : ヒストグラムのステップ数 • 列 : どのカラムの情報か

Slide 159

Slide 159 text

• RANGE_HI_KEY：ステップの上限キー • RANGE_ROWS：ステップ内の⾏数（上限は含まない） • EQ_ROWS：上限の値と列の値が等しい⾏数 PK_Memberの統計情報 ②

Slide 160

Slide 160 text

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

Slide 161

Slide 161 text

ヒストグラム化するとこうなる

Slide 162

Slide 162 text

通常はレコード数 >> サンプル 数 • 先ほどの例はサンプリング率100%（フルスキャン） update statistics member update statistics member with fullscan • with fullscan無し：レコード数に応じて⾃動でサンプリング率が決定

Slide 163

Slide 163 text

統計情報のポイント① • 統計情報は「不完全な情報」 • 各テーブルの各レコードの全カラムの情報がわかれば、最適なプランは ⽣成しやすい • ただし全レコードを実⾏前にチェックしていると時間がかかってしまう → 最適化に時間がかかっては意味がない • そこで統計情報を利⽤する → 完全な情報ではなく、ざっくりとしたカラム値の分布で代⽤ • 基数推定アルゴリズムも完ぺきではない ＝ 誤差は⽣じる • 統計情報を使って、WHERE句の絞り込みレコード数を推定する → 統計情報の時点でざっくりとした情報なので、完璧な精度は出ない ⇒ ⾃分で「このプランは妥当なのか」を評価する⼒が重要

Slide 164

Slide 164 text

• 統計情報は常に更新されるわけではない • 統計の⾃動更新がONになっていても、全レコードの20%が更新されて はじめて統計も更新される • 実際のデータ分布と統計情報に乖離が⽣まれる場合がある • 乖離が⼤きいほど、最適な実⾏プランが⽣成されない可能性も上がる 統計情報のポイント②

Slide 165

Slide 165 text

統計情報についてのまとめ • オプティマイザの仕事は最⾼のプランを⾒つけることではなく、 限られた時間で「良いプラン」を⽣成すること • オプティマイザは実⾏プランを⽣成する際に統計情報を利⽤ • 統計情報はざっくりとしたカラムの分布をヒストグラムで保持 • 諸条件により最適でないプランが⽣成される場合もある • 統計情報のサンプリング数 • 統計情報の更新⽇時 • 基数推定アルゴリズムの限界

Slide 166

Slide 166 text

Chapter 7 Selectivityを理解する

Slide 167

Slide 167 text

Chapter 7 Lesson 1 Selectivityとは

Slide 168

Slide 168 text

⾼速なクエリ selectivityの良い検索述語 適切なインデックス = +

Slide 169

Slide 169 text

このレッスンで使う⽤語について • 検索述語：WHERE句の各条件のこと • selectivity (選択性)：検索述語が⾏をどれだけ絞り込めるかの指標 • selectivityが良い：少ない⾏に絞り込みができること • 適切なインデックス：作成することでIOを劇的に削減できるインデックスのこと

Slide 170

Slide 170 text

検索述語のselectivity評価例① 検索述語：MemberID = 18629764 1,000万レコードを1⾏に絞り込み →「selectivityがもっとも良い」

Slide 171

Slide 171 text

検索述語のselectivity評価例② 検索述語：DeleteFlag = 0 1,000万レコードを約半数に絞り込み →「selectivityが悪い」

Slide 172

Slide 172 text

「selectivityが良い」のボーダーラインは？ • 「レコード全体の5%程度」まで絞り込めるか • 1,000万レコードのテーブルであれば、5%の50万レコードまで • ポイント：主キーやユニークキーはselectivityがもっとも良い • 「5％」の基準はディスク性能など環境によって変わる • あくまで⽬安と考えておく

Slide 173

Slide 173 text

検索述語のselectivity評価例③ 検索述語：DeleteFlag = 0 and PrefectureID = 6 and GenderID = 2 and Sei = 'Marlinʼ 1,000万レコードを26⾏に絞り込みできるので「selectivityが良い」

Slide 174

Slide 174 text

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⽤インデックス

Slide 175

Slide 175 text

「適切なインデックス」を作るポイント 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)

Slide 176

Slide 176 text

まとめ • ⾼速なクエリ = selectivityの良い検索述語 + 適切なインデック ス • クエリチューニングの際は以下の２点を確認 • selectivityの良い検索述語があるか • 適切なインデックスが存在するか

Slide 177

Slide 177 text

Chapter 7 Lesson 2 複数テーブルを参照するクエリ のSelectivity を評価する

Slide 178

Slide 178 text

複数テーブルJOIN時のselectivityは？

Slide 179

Slide 179 text

各テーブルごとにselectivityを評価する • 「Memberの検索述語はselectivityが良い」 • 「MemberEmailの検索述語はselectivityが悪い」

Slide 180

Slide 180 text

複数テーブルJOIN時のselectivity評価のポイント • 複数テーブルのJOINを含むクエリでは、selectivityが良い テーブルがひとつ以上存在すれば適切なインデックスと 組み合わせることで⾼速化が⾒込める • なぜか？

Slide 181

Slide 181 text

チューニング後の実⾏プラン

Slide 182

Slide 182 text

MemberEMail 評価時の selectivity：悪い （8552233⾏） MemberEMail 実際の selectivity：良い （1⾏）

Slide 183

Slide 183 text

MemberEMailのシーク述語は「結合条件」 select MemberID from Member where DeleteFlag = 0 and Tel = '0698903494' select MemberID from MemberEMail where MemberID = 18629764 and MainFlag = 1

Slide 184

Slide 184 text

元クエリ 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を検索 述語に追加

Slide 185

Slide 185 text

実⾏プランのシーク述語をチェック ・Memberのシーク述語は元クエリのwhere句と同⼀ ・MemberEMailのシーク述語は元クエリのwhere句「MainFlag = 1」 ではなく、元クエリの結合条件「A.MemberID = B.MemberID」

Slide 186

Slide 186 text

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

Slide 187

Slide 187 text

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

Slide 188

Slide 188 text

Chapter 7 Lesson 3 selectivityのまとめ

Slide 189

Slide 189 text

selectivityのまとめ① • JOINを含むSELECT⽂の実⾏の流れ 1. 各テーブル（正確にはインデックスまたはヒープ）ごとに データを絞り込む 2. テーブルを合体させる 3. １と２を繰り返す • 検索述語に複数テーブルのカラムが指定されていても、 基本的には最もselectivityが良い検索述語のみがシーク述語 となり、それ以外は結合条件がシーク述語となる

Slide 190

Slide 190 text

selectivityのまとめ② • もっともselectivityが良い検索述語による絞り込みレコード数は、 その後の各結合処理の実⾏回数（≒レコード数）へと 影響が伝搬していく • selectivityが良い → パフォーマンス的な好影響が伝搬していく • selectivityが悪い → パフォーマンス的な悪影響が伝搬していく

Slide 191

Slide 191 text

selectivityのまとめ③ • 複数テーブルのJOINを含むクエリでも、selectivityが良い検索 述語がひとつ以上存在すればチューニングは可能。なぜか？ ⇒クエリ実⾏時、selectivityが良い検索述語によりレコード数 が⼤幅に絞り込まれ、その後の結合時にパフォーマンス的な 好影響が伝搬していくため

Slide 192

Slide 192 text

Chapter 7 Lesson 4 インデックス設計の練習問題

Slide 193

Slide 193 text

問題：インデックスを設計してください 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

Slide 194

Slide 194 text

回答：MemberEMailのScanをSeekにしたい CREATE NONCLUSTERED INDEX [IX_MemberEmail_MemberID] ON [dbo].[MemberEMail] ([MemberID])

Slide 195

Slide 195 text

No content

Slide 196

Slide 196 text

問題：インデックスを設計してください 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

Slide 197

Slide 197 text

回答：まずMemberのScanをSeekにする CREATE NONCLUSTERED INDEX [IX_Member_Tel] ON [dbo].[Member] ([Tel])

Slide 198

Slide 198 text

No content

Slide 199

Slide 199 text

次にMemberEMailのScanをSeekにする CREATE NONCLUSTERED INDEX [IX_MemberEMail_MemberID] ON [dbo].[MemberEMail] ([MemberID]) INCLUDE ([MainFlag], [DeleteFlag])

Slide 200

Slide 200 text

No content

Slide 201

Slide 201 text

インデックス設計のポイント • とりあえず実⾏して「実際の実⾏プラン」をチェックする • 「ボトルネックはどこか」という観点で実⾏プランを⾒る • インデックスをひとつずつ作成していく • プラン確認 → インデックス作成 → プラン確認 をチューニング完了まで繰り返す

Slide 202

Slide 202 text

Chapter 8 全体最適なチューニング

Slide 203

Slide 203 text

Chapter 8 Lesson 1 全体最適なチューニングとは

Slide 204

Slide 204 text

パフォーマンスチューニングの定義 1. 個別最適なチューニング 特定のクエリの「実⾏時間」または「CPU使⽤時間」の削減 2. 全体最適なチューニング 特定のインスタンスで実⾏されている全クエリの 「実⾏時間の合計値」または「CPU使⽤時間の合計値」の削減

Slide 205

Slide 205 text

全体最適なチューニングをするときの前提 • インスタンスでは多数のクエリが実⾏されている • 各クエリの詳細は把握していない • 各クエリの実⾏頻度も把握していない • 「どのクエリをチューニングすればいいのか」を調査すべき

Slide 206

Slide 206 text

パレートの法則（80:20の法則） • 「全体の数値の⼤部分は全体を構成するうちの⼀部の要素が ⽣み出している」という理論 • 例：ビジネスにおいて、売上の８割は全顧客の２割が⽣み出している • 例：商品の売上の８割は、全商品銘柄のうちの２割が⽣み出している

Slide 207

Slide 207 text

パレートの法則をDBサーバーに当てはめる • DBサーバー１台にかけるCPU負荷の８割は、 全クエリの２割が⽣み出している • DBサーバー１台で実⾏されるクエリの総実⾏時間の８割は、 全クエリの２割が⽣み出している • この「２割」のクエリを⾒つけ出すことが、 少ない労⼒で⼤きなCPU負荷減や総実⾏時間減につながる ※ 実際は１割以下のこともある

Slide 208

Slide 208 text

ZOZOTOWNのDBにおける全体最適なチューニング例 全体の1%のクエリをチューニングしたことで、CPU負荷が50%減

Slide 209

Slide 209 text

パレートの法則を元にした重要な問い • CPU負荷を下げたい場合 「今⾃分がチューニングしようとしている箇所は CPU負荷全体の何割を占めるのか？」 • 実⾏時間を下げたい場合 「今⾃分がチューニングしようとしている箇所は 総実⾏時間の何割を占めるのか？」

Slide 210

Slide 210 text

全体最適なチューニングでもっとも重要なこと 「どのクエリが全体のCPU負荷の何％を占めているのか」 「どのクエリが全体の実⾏時間の何％を占めているのか」 ということについての事前調査を⾏う ⇒ そのためにはツールを使う

Slide 211

Slide 211 text

Chapter 8 Lesson 2 拡張イベントとは

Slide 212

Slide 212 text

拡張イベントとは 軽量なパフォーマンス監視システム ⇒ SQL Server ProfilerやSQLトレースの上位互換

Slide 213

Slide 213 text

拡張イベントの例

Slide 214

Slide 214 text

チューニング⽬的でよく使⽤するイベント • rpc_completed：リモートプロシージャコール完了 • sql_batch_completed：バッチ完了 • sp_statement_completed：ストアドプロシージャの ステートメント完了 • sql_statement_completed：ステートメント実⾏完了

Slide 215

Slide 215 text

各イベントの関係性 cpu/durationともに rpc_completed + sql_batch_completed ≒ コンパイル時間 + sp_statement_completed + sql_statement_completed まずはrpc_completed+sql_batch_completedの粒度で調査する 必要に応じてstatement系にドリルダウンする

Slide 216

Slide 216 text

Chapter 8 Lesson 3 DMVを使ったチューニング 対象の選定⽅法

Slide 217

Slide 217 text

DMV(Dynamic Management View)とは • サーバーの情報が格納されたViewのこと。沢⼭種類がある • sys.dm_exec_query_stats ⇒ クエリの実⾏統計（cpu/durationなど）をキャッシュ • 累積値なので、２回情報を取得して差分をとれば該当時間帯の 各クエリのcpuやdurationの合計値を取得できる ⇒ 降順に並び替えると、cpu負荷をかけているクエリを⾒つけ るといったことが可能

Slide 218

Slide 218 text

Chapter 8 Lesson 4 全体最適なインデックスとは

Slide 219

Slide 219 text

テーブルに作成すべきインデックスの数は？ • ⼀般的にインデックスが増えるほど • 読み取りは⾼速になる • 更新は低速になる ⇒ 明確な答えはない • あるテーブルAに作成すべき最適なインデックスは 「テーブルAを参照する全クエリの総実⾏時間を最⼩化する インデックスの組み合わせ」

Slide 220

Slide 220 text

総実⾏時間を最⼩化するインデックスの組み合わせ • インデックスの組み合わせで総実⾏時間や総CPU時間は変化する • 考えられるインデックス全パターンを試すのは時間的に無理 • そのため、実際には完璧な最適解は得られない

Slide 221

Slide 221 text

ではどうするか？ 各テーブルに関するワークロードの性質を理解して柔軟に対応する • write heavyならインデックスは必要最⼩限に留める • read heavyならインデックスを積極的に作成する

Slide 222

Slide 222 text

インデックス作成の基本戦略 • 必要最⼩限のインデックス設計を⼼がける • キーが同じで付加列だけ異なる等のインデックスはまとめる • 意図した使われ⽅をしているか確認する • DMVを使ってseek回数とscan回数の⽐率をチェックする

Slide 223

Slide 223 text

インデックス作成のアンチパターン 各クエリごとに最適なインデックスを作成する ⇒ 似たインデックスが⼤量に作成される 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)

Slide 224

Slide 224 text

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)

Slide 225

Slide 225 text

意図した使われ⽅をしているか確認する 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

Slide 226

Slide 226 text

Chapter 8 Lesson 5 全体最適なインデックスの設計

Slide 227

Slide 227 text

必要最⼩限のインデックスを設計する 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])

Slide 228

Slide 228 text

なぜSELECT⽤カラムは付加列か？ ・付加列として加えておいた⽅がインデックスに拡張性がある select top 10 Sei, Mei from Member where LoginName like 'Te%' and DeleteFlag = 1 • 例えば、上記のクエリを使った処理を 新しくリリースした場合を考慮する

Slide 229

Slide 229 text

付加列にしたインデックスの場合 ・既存のインデックスを⼀度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]) ・ひとつのインデックスで２種類のクエリに対応可能

Slide 230

Slide 230 text

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'

Slide 231

Slide 231 text

付加列にしないインデックスの場合 • 追加で別のインデックスを作るしかなくなる 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。なぜか？

Slide 232

Slide 232 text

• インデックスを修正する時点でプロダクション環境で 実⾏されている全クエリを把握するのは難しい CREATE NONCLUSTERED INDEX [IX_Member_LoginName] ON [dbo].[Member] ([LoginName], [Sei], [Mei]) select * from Member where LoginName = 'Test' and Sei = 'aaa' and Mei = 'bbb' • この既存インデックスだけみると、下記のようなクエリが すでに実⾏されていると考えるのが妥当 ・よってインデックスを増やすしかなくなる

Slide 233

Slide 233 text

• 結果的に以下のふたつのインデックスを作成する必要が出てくる 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]) • 無駄な容量や更新時のコスト増につながる

Slide 234

Slide 234 text

・⼀般化：良い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

Slide 235

Slide 235 text

インデックスの設計タイミング • 新機能作成に伴ってテーブルが追加される場合 １．必要なクエリをすべて作成 ２．各クエリに最適なインデックスを設計する（個別最適） ３．２で設計したインデックスをできる限りまとめる（全体最適） • 既存クエリをチューニングする場合 １．チューニングしたいクエリに最適なインデックスを設計（個別最適） ２．既存インデックスとまとめられる場合はまとめる（全体最適）

Slide 236

Slide 236 text

全体最適なインデックス設計のまとめ • 良いselectivityが得られる最⼩カラム構成をとる • SELECT句でしか使わないカラムは基本付加列にする • WHERE句で使っているカラムでも、 それ以外のカラムで良いselectivityが得られるなら付加列でOK • 理由：インデックスの拡張性を保ち、できるだけ少ない インデックスで多くのクエリに対応できるため

Slide 237

Slide 237 text

Chapter 8 Lesson 6 クエリを書くときのポイント

Slide 238

Slide 238 text

ポイント①：アドホッククエリを避ける • アドホッククエリ • where句などで値が直接指定されているクエリのこと • SQLインジェクションの危険性 • 毎回コンパイルされる可能性が⾼くCPU負荷増、実⾏時間増に つながりやすい • 1⽂字でも異なれば各クエリすべてがキャッシュされるため メモリ効率が悪い select * from Member where PrefectureID = 2

Slide 239

Slide 239 text

開発時の基本⽅針 • パラメータ化クエリまたはストアドプロシージャを使⽤する • パラメータごとのselectivityが⼤きく異なる場合は パラメータスニッフィングによる実⾏速度の低下に注意する • クエリプランの後退にも注意する

Slide 240

Slide 240 text

パラメータごとの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が⼤きく異なる

Slide 241

Slide 241 text

パラメータスニッフィングとは コンパイル時に受け取ったパラメータにとって最適な実⾏プランを ⽣成する挙動のこと declare @PrefID int = 1 select top 10 * from Member where PrefectureID = @PrefID IndexScanの実⾏プランがキャッシュされ、@PrefIDで別の値が指定されても IndexScanでクエリ実⾏される @PrefID=1の場合はテーブルの99%以上のレコードが取得されるためIndexScan

Slide 242

Slide 242 text

パラメータスニッフィングによる実⾏速度の低下 • @PrefID=2の場合：１レコード取得のためIndexSeekが理想 • @PrefID=1に最適なプランがキャッシュされているため、 IndexScanで実⾏されてしまい実⾏速度が低下する

Slide 243

Slide 243 text

実⾏速度の低下に対する対策 リコンパイルヒントを付けて毎回コンパイルする • ストアドプロシージャ：with recompile • パラメータ化クエリ：option (recompile)

Slide 244

Slide 244 text

クエリプランの後退とは 本来はもっと⾼速に実⾏できる実⾏プランがあるのに 低速な別の実⾏プランで実⾏されてしまう現象のこと ⇒ いつもは問題なく実⾏できているクエリがなにもしていないのに 突然遅くなった場合はクエリプランの後退を疑う

Slide 245

Slide 245 text

クエリプランの後退への対策 • クエリで使っている統計情報を更新する • 該当クエリをリコンパイルする • DBCC FREEPROCCACHE (plan_handle) • リコンパイルヒントを付けて毎回コンパイルする • ストアドプロシージャ：with recompile • パラメータ化クエリ：option (recompile)

Slide 246

Slide 246 text

ポイント②：インデックスが効かないケース • 暗黙の型変換 • where col1 = 1234 -- col1がchar(4)の場合はʼ1234ʼにすべき • カラムを加⼯する • where (col1*3) = 5 • where func(col1) = 5 • like ʻ%***%ʼとする

Slide 247

Slide 247 text

ポイント③：ヒント句は使わない • ヒント句は使わず、基本的にはオプティマイザに任せる • 以下のヒント句は場合によっては有効な場合もある • option (maxdop 10) : 並列クエリの多重度を変更する • with(index(index_name))：指定したインデックスの使⽤を強制 • with(forceseek)：index seekを強制

Slide 248

Slide 248 text

Chapter 8 Lesson 7 全体最適なインデックスの 練習問題

Slide 249

Slide 249 text

問題：全体最適な観点でインデックスを再設計 してください ① 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]) ⑥

Slide 250

Slide 250 text

この問題の考え⽅ 各インデックスが使っているクエリの速度を落とさずに できるだけインデックスの数を減らす

Slide 251

Slide 251 text

解説① ① + ⑥ 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])

Slide 252

Slide 252 text

解説② ② + ③ + ⑤ 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])

Slide 253

Slide 253 text

最終的な回答 ① + ⑥ 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])