SQLServerのパフォーマンスチューニング

SQLServerのパフォーマン スチューニング 2024/11/21 クラメソさっぽろIT勉強会 (仮) #6:パフォーマンスチューニング

目次 1. 自己紹介 2. Profiler 3. インデックスのおさらい 4. パフォーマンスチューニングあれこれ 5. まとめ

1 自己紹介

1 Profiler まずは可視化の手法から。

Profiler? 以下のようなことができる公式のツールです。 ● ● ● クエリの実行状況のモニタリング パフォーマンス分析 セキュリティ監査

SQLServer Management Studio ● 現状、SSMS経由の SQLServerProfilerを利用するのが 手っ取り早い。 （設定変更少なく、機能豊富）

ただし、、 1. 2. SSMSはWindows専用ソフトウェア SQLServer Profilerは現状非推奨 a. 拡張イベントというより軽量なシステムを推奨している。 https://learn.microsoft.com/ja-jp/sql/tools/sql-server-profiler/sql-se rver-profiler?view=sql-server-ver16

• https://learn.microsoft.com/ja-jp/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver16

Azure Data Studio ● Macにも対応したツール ○ ● ● 機能面はSSMSに比べ、まだまだ少ない。 Profilerは拡張機能からインストール。 RDS on SQLServerの場合 ○ ○ 少しカスタマイズが必要。 詳しくは、Qiitaをご確認ください。

3 インデックスのおさらい ぼんやりとした知識を再確認！

実データの保存方法（ページ） ● レコード実データは８KB単位で論理的に管理 ○ ● ● １単位を「ページ」と呼びます。 インデックスがない状態では、Insert順に格納されている 初期状態では、バラバラに単語が並んだ辞書状態！ https://learn.microsoft.com/ja-jp/sql/relational-databases/pages-and -extents-architecture-guide?view=sql-server-ver16

• https://learn.microsoft.com/ja-jp/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16

インデックスがないと。。 実際に50,000レコードあるテーブルから先頭（ID=1）のデータを検索して みると、TableScan（全走査）が発生している。

インデックスの仕組み（B+Tree） ● 二分検索木 ○ ● ● 検索が早い（検索回数が対数関数的に増える）； ○ 深さ1（データ数：03件(3^1) 検索回数：1回） ○ 深さ2（データ数：09件(3^2) 検索回数：2回） ○ 深さ3（データ数：27件(3^3) 検索回数：3回） ○ 深さ4（データ数：81件(3^4) 検索回数：4回） 深さが同じ ○ ● データはリーフノードだけ どのデータを検索しても、同じ速度 範囲検索ができる ○ リードノードが繋がっている。

B+Tree(１〜9の検索例)

インデックスの種類 ● 非クラスター化インデックス ○ ○ ● 付加列インデックス カバリングインデックス クラスター化インデックス

非クラスター化インデックス ● ● ● 作り方 ○ CREATE NONCLUSTERED INDEX IX_Shain_ID ON Shain(ID); 実行計画（TableScan→IndexSeekに！） ○

RID Lookup ● ● ● ● Select結果を取得するために行なってい る操作。 非クラスター化インデックス場合、末端の リーフノードには、RID(Row ID)と呼ぶ、実 データの参照先のみが作成されている。 = オーバーヘッド！ リーフノードに必要なデータを付与する 付加列インデックスもある。 ○ が、利用は要考慮。

RID Lookup ● 非クラスター化インデックスに含まれるデータのみ取得する場合 は、RID Lookupは発生しない。 ○ カバリングインデックス

クラスター化インデックス ● ● 作り方 ○ CREATE CLUSTERED INDEX IX_Shain_ID ON Shain(ID); リーフノードが実データになったインデックス ○ ● ● ＝実データの並び順 １テーブルにつき、１つのみ作成可能。 実データの並び順になるので、再編成に時間はかかる。

4 パフォーマンスチューニングあれこれ これまで経験したすべて・・

①キャッシュを消してから確認 ● 性能確認時はキャッシュを削除してから！ ○ ● 本番系の投入は慎重に。。 プランキャッシュ ○ ○ SQLを解析・最適化し、実行計画を生成する処理部分のキャッシュ。 消し方 DBCC FREEPROCCACHE データキャッシュ ■ ● ○ ○ 二次記憶装置（HDD/SSD）から一次記憶装置（メモリ）へキャッシュ。 消し方 ■ DBCC DROPCLEANBUFFERS

②ProfilerでSQLを確認しよう！ ● ● システム経由では遅いのに、SQLクライアントから同じ（と思ってい る）SQLを実施してみても、再現しない場合がある。 セッションパラメタの差異や、大文字/小文字差異、SP利用有無 で、実行計画は別々に作られる！

ログインセッション時 ● arithabortには要注意！（SSMSではデフォON） ○ ○ 暗黙の変換と合わせて性能劣化起きたり。。 検証時は明示的にOFFにする。

sp_executesql ● ● .net利用時は、暗黙的にspが利用されたりします。 以下の同じSQLでも実行計画は別。 ○ select * from SHAIN where ID = 4567 ○ exec sp_executesql N'SELECT * FROM [SHAIN] WHERE [ID]=@1',N'@1 smallint',@1='4567'

③暗黙の変換 (Implicit Conversion) ● ● ● カラム定義の型と異なる型を指定した場合に発生 型変換のオーバーヘッドが発生！ 例： params.Add("@Name", SqlDbType.NVarChar, 100).Value = order.Name; params.Add("@Date" , SqlDbType.NVarChar, 100).Value = order.Date; ❌：datatime型をnvarchar型として定義している。（コピペ修正漏れ） ● その他よくある暗黙の変換 ○ ○ ○ 固定文字列と、可変長文字列（charとvarchar ） 非UnicodeとUnicode（varcharとN’hoge’ ） decimal/numeric（decimal(10,0)と10 ）

④ロックオプション select * from SHAIN where ID = 4567 ● トランザクション分離レベルがデフォルトのREAD COMMITTEDの 場合、上記のような単純なSQLは他のワークロードの更新処理を ブロックします。

with(nolock) select * from SHAIN with(nolock) where ID = 4567 ● ● 他の更新処理をブロックしなくなる。 ただし、ダーティリード。 ○ ファントムリード等の問題が顕著化する場合は利用できない

SNAPSHOT分離 SET TRANSACTION ISOLATION LEVEL SNAPSHOT select * from SHAIN where ID = 4567 ● ● 更新処理をブロックしないし、ダーティリードもなし。 トランザクション開始時点のコミット済みデータが取得可能 ○ ○ 一時的なデータを格納するtempdbを利用 tempdbを利用することのオーバーヘッドがあるので注意。

⑤インデックスのメンテナンス ● インデックスの作成は最小限に ○ ● 検索処理を高速にする反面、更新処理ではインデックスの更新が必要となる 為、性能劣化します。 インデックスの再構築・再編成 ○ ○ データの更新を繰り返すと、インデックスの断片化し、検索効率が徐々に低下 します。 夜間などに定期的な再構築・編成が必要。

⑥統計情報のメンテナンス ● データベース内のデータ分布を表す情報 ○ ● 実データとの乖離があると誤った実行計画が作成される場合がある ○ ○ ● 実行計画を作成するために利用されます。 常に最新のデータが反映されているわけではない。 一定条件で更新はされるが、サンプリング率が低い。。 以下のようなメンテナンスの考慮が必要 ○ ○ 周期的なタイミングで、高めのサンプリング率を指定した更新 大量のデータ更新後では手動の統計情報更新

Qiitaにまとめてます！ SQL ServerのProfiler周りに ついて調べてみた。 SQLServerのインデックスに ついての基礎の基礎。 SQLServer パフォーマンス チューニングまとめ

Thanks!!