SQL Server では、様々な情報を DMV (動的管理ビュー) で取得することができます。
SQL Database v12 では様々な DMV にアクセスできるようになっているので基本似たような情報が取れるかと。
本番と開発環境のスペックが大きく離れている場合は、クエリの実行時間等の情報が参考になるかは一考の余地がありますが、それほど離れていない場合には、開発段階で各種情報を取得しておくと有益な情報が取得することができます。
自分でとるのはちょっと！！ という方は SQL Server 現状診断サービスのご紹介 もご検討いただければ幸いです。
はい。察しの言い方は気づかれたかもしれませんが、本投稿は宣伝です。
弊社診断サービスでは、簡易診断という形で各種情報を取得しながらディスカッション形式で、サーバーの状態を診断する作業についても実施させていただいていますので、「報告書の作成は不要だが、現状のサーバーの状態がどうなっているかを確認したい」というようなご要望がありましたら、こちらのサービスのご利用も検討いただければと思います。
Contents
■クエリの情報
クエリの情報については、
- 実行中のクエリ
- キャッシュされているクエリ
から取得することができます。
実行中のクエリは sys.dm_exec_requests から取得することができます。
こちらについては、現在実行されているクエリから情報を取得しますので、「サーバーに負荷がかかっている状態」で情報を取得する際に利用するのが一般的になるかと。
キャッシュされているクエリについては、sys.dm_exec_query_stats から取得することができます。
メモリ上に載っているクエリのみが取得できますので、大量のアドホッククエリが実行されている環境などでは、取得したいクエリの情報がキャッシュアウトされている可能性がありますのでご注意いただければと。
実行に時間がかかっているクエリについては、本番環境でも実行に時間がかかる可能性があります。
# ハードのスペックである程度はごまかせますが。
- CPU 使用時間
- 実行時間
- I/O の発生状況
から、これらの情報が本番環境でも影響を与えることがないかは一考の余地があるのではと思います。
開発環境と本番環境のスペックにさほど差がない場合には、時間がかかっているクエリは、どの環境でも時間がかかりますので。
キャッシュアウトをされても、クエリの実行情報を残しておきたいという場合には、
の利用を検討していただければと思います。
データコレクションは SQL Server 2008 以降 の機能となりますが、クエリストアについては SQL Server 2016 / SQL Database v12 で使用することができます。
拡張イベントを使用できる場合は、sql_batch_completed / sql_statement_completed / sp_statement_completed / rpc_completed で時間がかかっているクエリの情報を取得することでスロークエリのログを取得できますのでこちらも開発または、本番環境で設定しておくと便利かと思います。
■インデックスの情報
開発段階でインデックスの設定をきちんと行っていればよいのですが、初期はプライマリーキーのクラスター化インデックスの情報のみ設定しているケースもあるのではないでしょうか。
インデックスの使用状況については、
- スキャン/シーク回数
- 不足しているインデックスのアドバイス情報
を確認することができます。
適切なインデックスが設定されていない場合は、過度なスキャンが発生しているケースがあります。
このような状況が起きているかは sys.dm_db_index_usage_stats から取得することができます。
また、SQL Server ではオプティバイザーがインデックスが不足しているのではと判断した場合、欠落インデックスの情報として取得がされます。
欠落したインデックスの情報については sys.dm_db_missing_index_details から取得することができます。
欠落したインデックスの情報は更新系のコストは考慮されずに、検索の効率化のためのアドバイスとなりますので、すべてを設定してよいかは判断が必要となりますが、考慮のベースとして使用する情報としては十分に使用できます。
不足しているインデックスと、スキャン状況を比較することで、どのインデックスを設定すると効果的かの判断をするための材料とすることができます。
開発環境と本番環境でインデックスの設定状況が同じ場合、開発環境で発生しているスキャン操作は、実行プランが大きく異ならない限りは、本番環境でも発生するはずですので。
最近、SQL Database v12 で SQL Database Index Advisor が導入されましたが、ドキュメントを見る限りは、欠落インデックスの情報を定期的に収集し、ポータルから見れるようにした機能なのかと思います。
■ロック情報の取得
ロックの情報ですが、
- 現状、ロック競合が発生しているか
- ロックにより待ちが発生したか
- デッドロックの発生状況
の 3 種類を確認するのがよいのではないでしょうか。
現状のロック競合については DMV から見ることもできますが、sp_who / sp_who2 を使うのが楽かと思います。
ロックの競合が発生している場合は、どのセッション間で競合しているのかを確認することができます。
ロックによる待ち、待ち事象の DMV から取得することもできますが、sys.dm_db_index_operational_stats から取得することで、ロックによる待ちが発生したインデックスの情報を取得することができます。
デッドロックについては、デッドロックの検出と終了 で触れらているようなトレースフラグを設定することによる主語句がありますが、SQL Server 2008 以降では システムヘルスの情報 でデッドロック発生時に自動でデッドロックグラフを取得しています。直近の情報にはなりますが、ここから取得すると後追いをすることもできるかと。
SQL Database の場合は、sys.event_log から取得できるのですが、
このビューのデータは、時間の経過と共に累積されます。 通常、データは集計の間隔が開始してから 1 時間以内に累積されますが、すべてのデータがビューに表示されるまでに最大で 24 時間かかることがあります。 その間に、1 つの行内の情報が定期的に更新されることがあります。
という仕様がありますので、すぐに確認することはできないかと。
ロック競合については複数のセッションからクエリを実行した場合に発生しますので、開発環境を少人数で使用している場合は、発生頻度が少ないかもしれませんが、確認をしておくのは無駄にはならないかと。
ほかにも、blocked process threshold サーバー構成オプション の利用がありますので、拡張イベントと組み合わせることで、ロック競合が発生した場合のブロックプロセスレポートを取得してもよいかと思います。