先ほど Enzo SQL Baseline を使用した SQL Server / SQL Azure のベースライン作成 を投稿したのですが、タイトルにベースラインと書いている割にはベースラインにあまり触れていないことに気づきました (汗)
ということで追加投稿でベースラインの比較についてまとめてみたいと思います。
■ベースラインの比較
今回は SQL Azrure を使用しています。
まずは、ベースラインを作成するために以下のテーブルとストアドプロシージャを準備しました。
CREATE TABLE [dbo].[Table_1]( ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_Col2] CREATE PROCEDURE [dbo].[USP_INSERT_Table_1] INSERT INTO Table_1 (Col3, Col4) |
int 型の IDENTITY 列を持つテーブルとそのテーブルに対して INSERT を実行するためのストアドプロシージャになります。
Enzo SQL Baseline を使用して作成したストアドプロシージャを使用して、データを INSERT します。
# 複数のスレッドで実行することで、Col3 の SPID の値が適度にばらけてデータが挿入されます。
それではベースラインを取得したいと思います。
最初に以下の更新のクエリを実行しベースラインを取得します。
SET NOCOUNT ON |
実行回数は 2 回とし、初回実行時にクエリコンパイルが発生するかもしれないので、[Prime Database] は有効にしています。
実行が完了したら、[Comparative Analysis] タブの [Add last run to comparative analysis] をクリックして、ベースラインとして登録します。
[Execution the same run] と [Parallel Execution] の値を増やして複数回 / 複数スレッドで実行する場合は、[Comparative Analysis] の [Exclude Minimum and Maximum Data Points] は有効にした方が良いかもしれません。
この設定を有効にすることで、複数セッションの実行結果の最大と最小を集計結果から除くことができるため、瞬間的に時間がかかった / かからなかった際の結果を除いて集計をすることができるようになります。
# 1 回しか実行しなかった場合はその結果が最大かつ最小になるので、結果は 0 になってしまいます。
同じ操作を複数回実行して各クエリのベースラインを登録し、比較しておきます。
これは、そのクエリの測定値が実行のたびに大幅に変わらないことを確認するためです。
CPU の使用率は実行の度に振れ幅がありますが、[Avg Reads] [Avg Writes] に関しては、[104 前後] [470 前後] と大体同じ値になっているのが確認できます。
そのため、このクエリを実行した際の平均的な読み込みと書き込みの値としては妥当な値が取れていると思います。
# 最後の 1 回は Write が高くなっていますがそれ以外は平均しています。
最初のの一回の結果だけあれば現状のベースラインとしては使えますので、残りの結果については削除してしまおうと思います。
削除するベースラインを選択して、[Delete] をクリックすることでベースラインから削除することができます。
現在、更新対象の列としている、Col4 は非クラスター化インデックスは作成していません。
以下のクエリを実行して Col4 に非クラスター化インデックスを作成します。
更新対象の列に非クラスター化インデックスを追加することで更新のコストが増えますので、Write の値に変化があるはずです。
それでは、Enzo SQL Baseline を先ほどと同じ設定で複数回実行してベースラインを登録してみたいと思います。
Baseline に表示しているのが、非クラスター化インデックスを追加する前、Run 2 ~ Run 4 が非クラスター化インデックス追加後の Baseline となります。
Baseline (非クラスター化インデックスなし) と Run 2 ~ Run 4 の値の変化を見ることで、非クラスター化インデックス追加後の更新コストの変化を確認することができます。
Read に関しては、3,000 % 程 Up、Write 関しては、 200 ~ 600 % 程 Up しているのが確認できます。
この差が非クラスター化インデックスを追加したことによる更新コストの増加分としてとらえることができるかと思います。
更新コストが増える分、検索のコストが減りますの処理特性を考慮したうえでケースバイケースになりますが。
単純な読み込み回数だけで見てしまうと非クラスター化インデックスを付与したことで回数自体は増えることがありますので、コストを見たうえで効果を見る必要もあったりはしますが指標としてはこの値を使うことはできるかと思います。