SQL Server ボトルネック箇所特定とチューニングの実演 / SQL Server analyze bottleneck and tuning

第17回 SQL Server 2019勉強会
https://sqlserver.connpass.com/event/125485/

Transcript

1. SQL Server ボトルネック箇所特定と チューニングの実演 Twitter@_itito_
2. 最近見たサメ映画
3. 今日の話の流れ • 簡単な構造のデータベースを2種類用意 • クエリを動かすと同じ結果なのに片方のパ フォーマンスが悪い • パフォーマンスが悪い原因を見てみる
4. 使用するデータベース • 公開されているStackOverFlowのデータベースを 改造して使用 – https://www.brentozar.com/archive/2015/10/how-to- download-the-stack-overflow-database-via-bittorrent/ • Large: current 312GB database as of 2018/12 • Medium: 50GB database as of 2014 • Small: 10GB database as of 2010 ←今回はこれを使用
5. • 次のクエリを含むストアド[PerformanceTest] – 2009/01/01より前に登録したユーザーの数を抽 出するクエリ – 8月31日に登録した人のコメントを抽出するクエリ – ユーザーID30000の人のコメントを抽出するクエリ – 上記の合計実行時間を出すクエリ 使用するクエリ
6. 実演 実際に2つのDBで実行してみる • ApexSqlCompleteでタブを色付けしてます – 緑：問題ないDB – 赤：問題あるDB (プレゼン用に入れてみましたが これ入れると少し重くなりますね)
7. 今回の調べ方 • とりあえずクエリストアを見てみよう • クエリストア – SQL Server 2016から使える機能 – パフォーマンスチェックやクエリプランの矯正が可能 – 事前に有効化しておく必要あり
8. 実演 クエリストアを確認 • 実行時間で並び替えるとPerformanceTestの ストアドの中で「 8月31日に登録した人のコメ ントを抽出するクエリ」が突出している • 実行計画に大きな違いがある？
9. パフォーマンスに違いのあるクエリ -- 8月31日に登録した人のコメントを抽出するクエリ SELECT c.* FROM [Users] AS u INNER JOIN [Comments] AS c ON c.[UserId] = u.[Id] WHERE u.[CreationDate] >= '2008/08/31' AND u.[CreationDate] < '2008/09/01' ;
10. 実行計画見るときに使えるツール • ApexSQL Plan – https://www.apexsql.com/sql-tools-plan.aspx – 第15回 SQL Server 2019勉強会で紹介されていました • SentryOne Plan Explorer – https://www.sentryone.com/plan-explorer – 第16回 SQL Server 2019勉強会で紹介されていました – 私が今日使っているのはこっち
11. 実行計画を確認したところ、 問題ないDBではLOOP JOIN 問題あるDBではHASH JOIN 問題ないDB 問題あるDB
12. 基本的なJOIN(LOOP, MERGE, HASH)の動きを 確認してみる
13. NESTED LOOP JOIN （インデックスがない場合） UserId 主キー UserId 3 1 3 2 2 3 1 1 外部表(駆動表) 内部表
14. NESTED LOOP JOIN （インデックスがない場合） UserId 主キー UserId 3 1 3 2 2 3 1 1 内部表 外部表(駆動表) 動きとしてはプログラムのfor文の動き 非常に効率が悪い
15. NESTED LOOP JOIN （インデックスがある場合） UserId 主キー UserId 3 1 3 2 2 3 1 1 内部表 外部表(駆動表) インデックスを辿って結合処理が行われる
16. MERGE JOIN UserId 主キー UserId 1 1 1 2 2 3 3 3 1. IDでソートする UserId 主キー UserId 1 1 1 2 2 3 3 3 2. 上から突き合わせていく
17. HASH JOIN UserId 主キー UserId 3 1 3 2 2 3 1 1 1. Hashテーブルを作成 0xB6589FC 0x356A192 0xDA4B923 2. Hash値と突き合わせる
18. 読み込む行数だけに注目してみると NESTED LOOP JOIN（インデックスなし） NESTED LOOP JOIN（インデックスあり） MERGE JOIN HASH JOIN m行 m行 m行 m行 n行 m×n行 外部表は全行、内部表はインデックスによる m+am行 最低m+n行 最低m+n行 n行 n行 n行 各表を上から突き合わせる、場合によってソートあり 各表を上から突き合わせる
19. 読み込む行数だけに注目してみると NESTED LOOP JOIN（インデックスなし） NESTED LOOP JOIN（インデックスあり） MERGE JOIN HASH JOIN m行 m行 m行 m行 n行 m×n行 外部表は全行、内部表はインデックスによる m+am行 最低m+n行 最低m+n行 n行 n行 n行 各表を上から突き合わせる、場合によってソートあり 各表を上から突き合わせる もしも外部表が内部表と比べて小さく、インデッ クスで大きく絞ることができる状況であれば、 内部表の量に影響されない NESTED LOOP JOINが大きな効果を発揮する
20. ヒントを使い、 LOOP JOINを強制してみると 387万行読み込んでいたのが、 最大で3751行の読み込みとなる
21. 強制は基本的には避けるべきであり 自然に適したJOINを選んでほしい JOINの種類が異なった原因は 何か？
22. クエリの実行フロー オプティ マイザ クエリ 実行
23. クエリの実行フロー オプティ マイザ クエリ 実行 推定実行計画 実際の実行計画
24. パフォーマンスの悪いDBの 推定実行計画と実際の実行計画を 比較してみる
25. 問題あるDBでLOOP JOINを強制したときの実行計画 推定実行計画 実際の実行計画
26. 問題あるDBでLOOP JOINを強制したときの実行計画 推定実行計画 実際の実行計画 推定実行計画ではLOOP JOIN時にインデックスを使ったとしてもそれぞ れで約9091行読まれる想定となっており、 107×9091で約97万行が読まれる想定となっている その97万行に対してSortやKeyLookupが発生するよりは HASH JOINしたほうが良いとオプティマイザに判断されたと考えられる
27. インデックスを使った時の 推定行数が多くなってしまったのは なぜか ↓ 統計情報
28. 統計情報とは • 「テーブルやインデックス内で、どんな値が、 どんな頻度で出現するのかをまとめた情報」 (https://www.atmarkit.co.jp/ait/articles/1703 /13/news001.html)
29. 4/1に入会して4/31に退会した人を 知りたい 入会日のイン デックス 退会日のイン デックス ２種類のインデックスが存在しているとする 例えば
30. 4/1に入会して4/31に退会した人を 知りたい 入会日のイン デックス 退会日のイン デックス どちらのインデックスを使えばいいか判断がつかない 何も情報がない場合
31. 4/1に入会して4/31に退会した人を 知りたい 入会日のイン デックス 退会日のイン デックス 4/1に1000人くらい 入会したという情報 4/31に30人くらい退 会したという情報 退会日から絞り込んだほうが効率が良い
32. 統計情報を見る方法 • A. SSMSから確認 – https://docs.microsoft.com/ja-jp/sql/relational- databases/statistics/view-statistics- properties?view=sql-server-2017 • B. DBCC SHOW_STATISTICS
33. 実際に見てみる
34. • RANGE_HI_KEY = 3043に注目して見てみると – RANGE_ROWSはユーザーIDが2509より上で3043未満のレコード数が 19336行であることを表す – EQ_ROWSはユーザーID3043と一致するレコード数を表す – DISTINCT_RANGE_ROWSはユーザーIDが2509より上で3043未満でレ コードが存在しているIDの行数を表す – AVG_RANGE_ROWSは(RANGE_ROWS / DISTINCT_RANGE_ROWS) 問題ないDBのCommentsテーブルのユーザーIDインデックスの統計情報
35. 問題ないDBのCommentsテーブルのユーザーIDのインデックスの統計情報 ざっくりユーザーID500行につきComments１万～２万行が存在
36. 問題ないDBのCommentsテーブルのユーザーIDのインデックスの統計情報 問題あるDBのCommentsテーブルのユーザーIDのインデックスの統計情報 ユーザーID2900より上で3000未満のレコードは存在せず、 3000と一致するレコードの数が約4万行存在しているという変な統計情報 ざっくりユーザーID500行につきComments１万～２万行が存在 ざっくりユーザーID100行につきComments３万～４万行が存在
37. 問題ないDBのCommentsテーブルのユーザーIDのインデックスの統計情報 問題あるDBのCommentsテーブルのユーザーIDのインデックスの統計情報 ユーザーID2900より上で3000未満のレコードは存在せず、 3000と一致するレコードの数が約4万行存在しているという変な統計情報 取得される行数がかなり多い見込み 問題のあるDBではユーザー1行あたりの Commentsのレコード数がかなり多い ざっくりユーザーID500行につきComments１万～２万行が存在 ざっくりユーザーID100行につきComments３万～４万行が存在
38. 統計情報の更新 • コマンド – UPDATE STATISTICS • https://docs.microsoft.com/ja-jp/sql/t- sql/statements/update-statistics-transact-sql?view=sql- server-2017 – sp_updatestats • 経験則としては、UPDATE STATISTICSのほうが、サ ンプリング率を明示的にFULLにできるので、問題 が起きにくい
39. 統計情報を更新し直して動かして みる
40. 統計情報を更新し直した結果 問題のないDBと同じような統計情報に変わりました 実行計画が改善され、速度も改善されました
41. やったことのまとめ 1. クエリストアを確認した 2. 実行計画を見たところ大量の行数が読まれていた 3. bestではないjoinがされていた 4. 推定実行計画の推定行数がおかしかった 5. 統計情報が正しくなかった 6. 統計情報を更新しなおすことで改善が行われた
42. パフォーマンスの 悪い部分を探す クエリストア 実行計画 拡張イベント パフォーマンスモニタ 動的管理ビュー … 原因を探す 実行計画が想定 と異なる IOでwaitが発生している ロックされている n+1問題 … 修正する クエリを 書き換える 統計情報を更新する SQL Serverの設定を 変更する インデックスを 追加する …
43. パフォーマンスの 悪い部分を探す クエリストア 実行計画 拡張イベント パフォーマンスモニタ 動的管理ビュー … 原因を探す 実行計画が想定 と異なる IOでwaitが発生している ロックされている n+1問題 … 修正する クエリを 書き換える 統計情報を更新する SQL Serverの設定を 変更する インデックスを 追加する … パフォーマンスが悪くなる多種多様の原因がありますが、 SQL Serverでは原因を探すための様々な機能がデフォルトで用意されています。 基本的なものはそこから順を追っていけば解決することが可能です。
44. 以上です ご清聴ありがとうございました