SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Enzo SQL Baseline を使用したベースラインの比較

leave a comment

先ほど Enzo SQL Baseline を使用した SQL Server / SQL Azure のベースライン作成 を投稿したのですが、タイトルにベースラインと書いている割にはベースラインにあまり触れていないことに気づきました (汗)

ということで追加投稿でベースラインの比較についてまとめてみたいと思います。

■ベースラインの比較


今回は SQL Azrure を使用しています。

まずは、ベースラインを作成するために以下のテーブルとストアドプロシージャを準備しました。

CREATE TABLE [dbo].[Table_1](
    [Col1] int IDENTITY NOT NULL,
    [Col2] [datetime] NULL,
    [Col3] int NULL,
    [Col4] [nchar](250) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
([Col1] ASC))

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_Col2] 
DEFAULT (getdate()) FOR [Col2]
GO

CREATE PROCEDURE [dbo].[USP_INSERT_Table_1]
    @Col4 nchar(250)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Table_1 (Col3, Col4)
    VALUES (@@SPID, @Col4)
END

int 型の IDENTITY 列を持つテーブルとそのテーブルに対して INSERT を実行するためのストアドプロシージャになります。

Enzo SQL Baseline を使用して作成したストアドプロシージャを使用して、データを INSERT します。
# 複数のスレッドで実行することで、Col3 の SPID の値が適度にばらけてデータが挿入されます。
image

image

それではベースラインを取得したいと思います。

最初に以下の更新のクエリを実行しベースラインを取得します。

SET NOCOUNT ON
UPDATE Table_1 SET Col4 = RAND()
WHERE Col3 BETWEEN 210 AND 220

実行回数は 2 回とし、初回実行時にクエリコンパイルが発生するかもしれないので、[Prime Database] は有効にしています。

image

実行が完了したら、[Comparative Analysis] タブの [Add last run to comparative analysis] をクリックして、ベースラインとして登録します。
image

[Execution the same run] と [Parallel Execution] の値を増やして複数回 / 複数スレッドで実行する場合は、[Comparative Analysis] の [Exclude Minimum and Maximum Data Points] は有効にした方が良いかもしれません。
この設定を有効にすることで、複数セッションの実行結果の最大と最小を集計結果から除くことができるため、瞬間的に時間がかかった / かからなかった際の結果を除いて集計をすることができるようになります。
# 1 回しか実行しなかった場合はその結果が最大かつ最小になるので、結果は 0 になってしまいます。

 

同じ操作を複数回実行して各クエリのベースラインを登録し、比較しておきます。
image

これは、そのクエリの測定値が実行のたびに大幅に変わらないことを確認するためです。

CPU の使用率は実行の度に振れ幅がありますが、[Avg Reads] [Avg Writes] に関しては、[104 前後] [470 前後] と大体同じ値になっているのが確認できます。
そのため、このクエリを実行した際の平均的な読み込みと書き込みの値としては妥当な値が取れていると思います。
# 最後の 1 回は Write が高くなっていますがそれ以外は平均しています。
image

最初のの一回の結果だけあれば現状のベースラインとしては使えますので、残りの結果については削除してしまおうと思います。
削除するベースラインを選択して、[Delete] をクリックすることでベースラインから削除することができます。
image

現在、更新対象の列としている、Col4 は非クラスター化インデックスは作成していません。

以下のクエリを実行して Col4 に非クラスター化インデックスを作成します。

CREATE INDEX NCL_Table_1_Col4
ON Table_1
(
    Col4
)

更新対象の列に非クラスター化インデックスを追加することで更新のコストが増えますので、Write の値に変化があるはずです。

それでは、Enzo SQL Baseline を先ほどと同じ設定で複数回実行してベースラインを登録してみたいと思います。
image

Baseline に表示しているのが、非クラスター化インデックスを追加する前、Run 2 ~ Run 4 が非クラスター化インデックス追加後の Baseline となります。

Baseline (非クラスター化インデックスなし) と Run 2 ~ Run 4 の値の変化を見ることで、非クラスター化インデックス追加後の更新コストの変化を確認することができます。

Read に関しては、3,000 % 程 Up、Write 関しては、 200 ~ 600 % 程 Up しているのが確認できます。

この差が非クラスター化インデックスを追加したことによる更新コストの増加分としてとらえることができるかと思います。
更新コストが増える分、検索のコストが減りますの処理特性を考慮したうえでケースバイケースになりますが。

 

 

単純な読み込み回数だけで見てしまうと非クラスター化インデックスを付与したことで回数自体は増えることがありますので、コストを見たうえで効果を見る必要もあったりはしますが指標としてはこの値を使うことはできるかと思います。

Share

Written by Masayuki.Ozawa

6月 12th, 2011 at 6:56 pm

Posted in SQL Azure,SQL Server

Leave a Reply