こんにちは、KiYOです。
システムエンジニア歴20年オーバー。製造現場に特化したシステム開発を主にやってます。
新しい物好きで色々なIT技術を勉強しては、仕事に使って行ってます。
KiYO
今回は、Microsoft SQL Serverのパフォーマンスチューニングについて解説していきます。
目次
インデックスを使用したパフォーマンスチューニング
インデックスとは、データベース内のテーブル検索を高速にするための機能です。本の目次的な役割に似ていて、目的の場所(データ)に直ぐに辿り着けるようになります。
インデックスがない場合、テーブルスキャンが実行され、全てのデータを先頭から探していくため時間がかかります。
SQLServerで使用される基本的なテーブルの構成
ヒープ
データページの集合のことをヒープと呼びます。ヒープには1つ以上の非クラスタ化インデックスを作成することができます。
インデックスの作成されていない通常のテーブルをヒープ表と呼びます。 ヒープ表では、挿入されたデータはハードディスクの様々な位置に格納されます。
非クラスタ化インデックス
実データを含むヒープ、またはクラスター化インデックスをポイントしています。
複数個作成できるのが非クラスタ化インデックスです。 非クラスタ化インデックスのリーフノードには、実データへの参照が格納されています。 このRIDをもとに、実データへアクセスしデータを取得する仕組みが非クラスタ化インデックスです。 この実データへのアクセスをページジャンプといいます。
メリット
キーに対応するレコードの検索は高速です 1つのテーブルに複数のインデックスを作成できます。
デメリット
範囲検索(between, <, > 演算)ではクラスター化インデックスと比べると速度が遅いです。 データが並び替えられて格納されていないため、インデックスの列でソートをした場合、クラスター化インデックスと比べると速度が遅いです。
クラスタ化インデックス
クラスタ化インデックスを使用すると、インデックス内にデータを含みます。クラスタ化インデックスにおいてデータはインデックスに指定したキーの値によってソートされてディスクに格納されます。
クラスタ化インデックスはPrimary Keyの値で作成されます。
メリット
データが並び替えられて保存されているため、インデックス列のソートが高速です。 データが並び替えられているため、インデックス列の範囲検索 (between, <, > 演算)が高速です。
デメリット
テーブル1つに対して1つしかクラスター化インデックスを作成できません。
パフォーマンスを考慮したSQL記述
必要なデータだけを取得するようにする
- 全ての列を取得するSELECT * の使用は避ける
- WHERE句を使用して行をフィルターする
- 検索演算子<>の使用など避ける
例)
WHERE DATEDIFF(yy, hirdate,GETDATE()) >= 8; | NG |
WHERE hiredate <= DATEDIFF(yy,-8,GETDATE()); | OK |
WHERE句の検索性能を向上させる
- 否定の条件や列値を操作する式は避ける
- LIKE演算子使用時は先頭のワイルドカードの使用を避ける
- 先頭に「%」や「_」などのワイルドカードは使用しない
その他
- 一時テーブルまたはテーブル変数の使用を控える
- ユーザ定義関数の使用はパフォーマンスの問題を生じる可能性があるので注意する
- フィルター、結合、ソートをサポートする適切なインデックスを作成する
ツールを使用したインデックスのカスタマイズ
SQL Server Profiler
各イベントに関するデータをキャプチャし、ファイルやテーブルに保存して、後で分析することができる。
できること
- 問題の原因を特定するため、問題の発生したクエリを順次実行する
- 実行速度の遅いクエリを検出し、その原因を診断する
- トレースファイルに保存された内容をテストサーバ上に再生する
- データベースエンジンチューニングアドバイザ(DTA)への入力に使用する「ワークロードファイル」の作成
- トレースとWindowsパフォーマンスログデータの関連付け
データベースエンジンチューニングアドバイザー(DTA)
パフォーマンスを向上させるインデックスや統計について推奨設定を提案する
リンク
Udemyというオンライン講座でもデータベースの学習はできます。 再生速度を早めてみたり、何度も見返すことができるのでおすすめです! 私も色々なデータベースをここで学習しています。
インデックスについて詳しく解説してくれて、理解しやすいと思います。
SQLServerのインデックスを理解して検索を速くする方法 その他のパフォーマンスチューニングはこちらをご覧ください。
あわせて読みたい Microsoft SQL Serverの9つのパフォーマンスチューニング こんにちは、KiYOです。 システムエンジニア歴20年オーバー。製造現場に特化したシステム開発を主にやってます。 新しい物好きで色々なIT技術を勉強しては、仕事に使…
コメント