[SQLServer] パフォーマンス検証をする際に気を付けること

はじめに

SQLServerを使用した業務でパフォーマンス検証をする機会が増えてきたため、その際に意識していたことを備忘として記事にまとめます。

実行中クエリの確認

測定対象のクエリを実行する際に、裏で別のクエリが動いていた場合、SQLServerは並列処理として各クエリに対してメモリの領域を確保して処理します。
そのため、単独で実行した場合と並列で実行した場合で処理速度に差が出る場合があります。

パフォーマンスを測定する際は、バックアップや定常処理などが実行されないタイミングの確認をしたり、環境を占有して測定するために各所への調整を実施したりなど、単独での処理時間を計測できる環境を整えておきましょう。

逆に、実際の稼働環境で並列処理が行われている場合、単独で計測してしまうと実際の処理時間と異なるため、同じ環境を再現するために裏の処理を動かすなど調整が必要になるため注意しましょう。

実行中クエリ確認

SELECT
  sqltext.TEXT,
  req.session_id,
  req.status,
  req.command,
  req.cpu_time,
  req.total_elapsed_time
FROM
  sys.dm_exec_requests req
CROSS APPLY
  sys.dm_exec_sql_text(sql_handle) AS sqltext

実行中のクエリを停止したい場合は、上記の実行結果から取得したsession_idを指定してKILLコマンドを実行すれば停止が可能です。
※停止しても問題ないことは確認してください

クエリの停止(KILL)

KILL {session_id}

キャッシュ

SQLServerは実行されたクエリのキャッシュを保持しており、そのキャッシュを使用し実行されたクエリの実行計画を作成します。
そのため、別のキャッシュが登録されている環境でクエリを実行しても、同じパフォーマンスの結果にはならない可能性があり、実際の環境で測定する必要があります。

しかし、実際の環境でパフォーマンス測定できる機会は稀だと思います。
確認したいパフォーマンスの値とは異なりますが、最も悪い時のパフォーマンスを測定して比較するという意味で、キャッシュをクリアした状態でパフォーマンスを測定することが多いです。

キャッシュクリア

DBCC DROPCLEANBUFFERS  -- データバッファキャッシュのクリア
DBCC FREEPROCCACHE     -- メモリキャッシュのクリア

パフォーマンスを測定する前には必ずキャッシュをクリアするようにして、一定の値を測定して比較するようにしましょう。

本番環境でキャッシュをクリアしてしまうと、定常的に動作しているクエリのパフォーマンスを悪化させてしまう危険があります。
キャッシュをクリアしても問題ない環境であることを確認したうえで実施をお願いします。

統計情報

統計情報の作成

SQLServerにおける統計情報とは、テーブルの中でどのような値がどのような頻度で存在しているかをまとめた情報のことを指します。
この情報を使用して、SQLServerが最適なクエリの実行プランを算出します。(*1)
(*1)：https://learn.microsoft.com/ja-jp/sql/relational-databases/statistics/statistics

統計情報はテーブルの列ごとに作成されます。
SQL Serverは初期設定で統計情報の自動作成がONになっているため、ストアドプロシージャやビューでテーブルの項目を参照した時に、自動的に統計情報が作成されます。
明示的に作成する場合は、以下のコマンドを実行してください。

統計情報の作成

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }

1回目のクエリ実行時に参照列の統計情報が存在していない場合は、クエリが始まる前に統計情報の自動作成処理が開始されて時間を要する場合があります。
パフォーマンスを測定する場合は、2回目以降で統計情報が作成されてから実行することをお勧めします。

統計情報の更新

統計情報は常時更新されているわけではないため、テーブルに対してデータの変更処理が実行されるにつれて、統計情報と実際のデータに乖離が発生していき、パフォーマンスの悪化につながります。

更新も作成と同じように、SQLServerの初期設定で自動更新がONになっています。
更新のタイミングとしては、色々条件があり一概には言えないようですが、約20%のデータが更新された時とされています。
※私が関わったクエリでは20%以下でも更新されていたため、参考程度としてください。

そのため、基本的には手を加える必要はないですが、処理の度に更新されてしまうというケースでは、統計情報の更新がかかることによって逆に遅くなってしまうということもあると思います。
その場合は、統計情報の自動更新をオフにして、定期的に手動で更新するなどを検討してください。

統計情報の更新

UPDATE STATISTICS [ schema_name . ] table_name 

実行想定環境との環境差異の明確化

実際とは異なるの環境でパフォーマンス検証を行う場合、性能や状況にどれだけの差があるかを事前に把握しておく必要があります。
性能の低い環境ではパフォーマンスが悪く見えても実際の環境では影響がなかったり、本番環境のインデックスはテスト環境と比べて断片化が進んでいたりと、洗い出しを行わなくては正常の判断ができません。

確認すべきポイントの一例を以下にまとめます。
・サーバースペック
　- メモリ
　- CPU
・ネットワーク帯域速度
・インデックスの断片化状況
・統計情報の更新状況
・クエリキャッシュの有無
・裏で動いているクエリ