SQL Server: 動的管理オブジェクトを使用する
動的管理オブジェクトは、パフォーマンス チューニングで必要となる SQL Server のワークロードの詳細を管理および監視するのに役立ちます。
出典: 『SQL Server DMV Starter Pack』(Red Gate Books、2010 年)
Glenn Berry、Louis Davidson、Tim Ford
動的管理オブジェクト (DMO) は、システム スキーマに格納されている一連の SQL Server オブジェクトです。このオブジェクトを使用すると、さまざまな SQL Server インスタンスで実行されているアクティビティとそのアクティビティで使用されているリソースを把握できます。
つまり、DMO では、データベース インスタンスに対して実行される接続、トランザクション、SQL ステートメント、およびプロセス、これらの処理によってサーバーで生成されるワークロード、そのワークロードの分散方法、負荷がかかる場所などに関する役立つ情報が公開されます。ボトルネックや負荷がかかる場所を特定すると、適切な手順を踏んで (おそらくクエリをチューニングしたり、インデックスを追加したり、または単純にブロックしているセッションを強制終了したりして) 問題を緩和することができます。
"動的" という用語は、DMO に格納されている情報が、幅広いインストルメンテーション ポイントから動的に生成されることを表しています。これらのインストルメンテーション ポイントは、SQL Server エンジンのインメモリ構造です。このデータは、システム データベース スキーマで表形式を用いてビューまたはテーブル値関数で表示されます。前者は、動的管理ビュー (DMV) と呼ばれ、後者は、動的管理関数 (DMF) と呼ばれます。
DMV と DMF は、実質的にはシステム ビューとシステム関数なので、SQL Server の他のビューや関数と同じように使用します (クエリしたり、結合したり、パラメーターを渡したり、SQL Server インスタンスの状態や正常性に関する特定の問題を調べるのに必要なデータを含む 1 つの結果セットを最終的な結果として返したりすることができます)。
DMV を使用したパフォーマンス チューニング
DMO では、時折目まいがするような一連の情報が公開されます。元の sysprocesses システム ビューは、基本的に非正規化されており、多数の新しい DMO が追加されているため、クエリでは、多くの新しいデータ列を使用できるようになりました。データベース エンジンが適切にインストルメント化されると、それに比例して、エンジンおよびエンジンの処理作業に関するデータの量が増加します。
どの列をどこで公開するかという悩ましい最初の検討事項に加えて、一連の異なる DMO のデータを結合するという複雑さにより、DMO のクエリを神呪の収集になぞらえるデータベース管理者もいるほどです。
しかし、DMO から返されるデータは、非正規化処理によって、さまざまな意味で分析および理解しやすくなりました。独自のスクリプトを記述し始めると、同じ手法や類似した結合パターンが繰り返し使用されていることを確認できます。そのため中核となるスクリプトは、比較的少数の方がすぐに適応して、多数の要件を満たすことができます。
ある意味では、診断データを取得するために DMO を調べることは、下位層を調べるプロセスだと言えます。上位層では、SQL Server インスタンスに接続しているユーザーとその接続方法、そのユーザーに対して実行中のセッション、およびそれらのセッションで処理されている要求を確認できます。また、このような要求によって実行されている SQL ステートメントの詳細、その SQL ステートメントを実行するのに使用されているクエリ プランなども確認することができます。
1 つ下の層は、トランザクション レベルです。トランザクションの結果として保持されているロックを確認したり、潜在的なブロックを調べたりすることができます。さらに 1 つ下の階層では、送信された要求が表すワークロードが OS でどのように実際の処理に変換されているのかを確認できます。たとえば、次のようなことを特定できます。
- 要求を満たすために実行されている実際の処理 (スレッド)
- 実行されている処理 (I/O、CPU、およびメモリの使用率)
- さまざまなファイルに I/O を分散させている方法
- スレッドが処理を続行できない状態で待機している時間の長さとその理由
さまざまな階層のデータをまとめて、システムで発生している特定の問題を明らかにするのに必要な結果を提供するのが、データベース管理者の任務です。
特定の時点のデータと累積データ
先ほど述べたように、DMO で保持されているデータは、他のテーブル、ビュー、または関数と同じようにクエリできます。しかし、表示されているデータは、本来 "動的" なものであることを忘れないでください。DOM のデータは、データベース エンジンのさまざまな構造から収集され、DMO をクエリしたときにサーバーで発生していたアクティビティのその時点の "スナップショット" です。
場合によっては、これが望ましい動作となることもあります。たとえば、パフォーマンスに問題があり、その原因と考えられるサーバーで実行中のクエリを特定する必要がある場合です。ただし、その問題が簡単に "解明される" ことを期待していると、特定の時点の DMO のデータをクエリすることが非常に難しいと感じる場合があります。
たとえば、パフォーマンスに問題があって "異常な" ロック パターンがあるかどうかを確認する必要がある場合は、"select <列> from <ロックしている DMV>" ステートメントが役立つことはあまりありません。ただし、システムの "正常な" ロック パターンについて詳しい知識がある場合は、このステートメントを実行すると簡単に異常を見つけられます。
サーバーの状態は変化するので、特定の時点のデータは、クエリするたびに高い確率で変化する可能性があることに注意してください。異常な (一般的ではない) 結果が表示される場合があることを予期しておく必要があります。また、インスタンスで実行されているアクティビティの実態を確認するのに、スクリプトを何度も実行しなければならない場合もあります。
それ以外の場合、DMO のデータは累積されます。つまり、特定の列のデータは累積データであり、イベントが発生するたびにインクリメントされます。たとえば、リソースが使用可能な状態になるまでセッションが待機すると、そのたびに sys.dm_os_wait_stats 動的管理ビューの列にデータが記録されます。このような DMV をクエリすると、SQL Server が起動または再起動されてから開始された全セッションにおけるリソースの総待機時間が表示されます (ただし、データベースの整合性チェック (DBCC) コマンドが実行され、格納されている統計データが手動で削除されている場合は除きます)。
この情報により、(長時間にわたる) 待機時間が発生する状況の概要は把握できますが、詳細な部分はわかりにくくなります。データベースに対する特定の変更 (新しいインデックスなど) による影響を測定する場合は、ベースラインの測定を行い、データベースに変更を加えた後で差分を測定する必要があります。
最後に、このような DMO のデータの大部分は、多数のセッション、要求、およびトランザクションから収集された集計データであることに注意してください。たとえば、先ほど説明した wait_stats 動的管理ビューでは、SQL Server で発生した待機時間が全セッションにわたって収集され、インスタンス レベルで表示されます。分離された環境のサーバーで作業をしていない限り、セッション レベルで待機時間を追跡することはできません。
.png)
.png)
.png)
Glenn Berry は、米国コロラド州デンバーの NewsGator Technologies でデータベース アーキテクトを務めています。彼は SQL Server の MVP ですが、MCITP、MCDBA、MCSE、MCSD、MCAD、MCTS など全種類のマイクロソフト認定資格を保有しており、試験好きであることの証明になっています。
Louis Davidson は、データベースの社内開発者およびアーキテクトとして、IT 業界に 16 年間携わっています。彼は、SQL Server の MVP を 6 年連続で受賞しており、データベース設計についての書籍を 4 冊執筆しています。現在は、米国のバージニア州バージニア ビーチとテネシー州ナッシュビルにある企業をサポートする Christian Broadcasting Network のデータ アーキテクトとして働いていますが、データベース管理者として任務に当たることもあります。
Timothy Ford は、SQL Server の MVP であり、SQL Server に 10 年以上携わっています。彼は、Spectrum Health の SQL Server プラットフォームにおける主力データベース管理者で、非常に詳しい知識を持っています。また、2007 年からさまざまな Web サイトでテクノロジについての記事を執筆しており、自身のブログ (thesqlagentman.com、英語) では、SQL Server、在宅勤務、および高度な開発のトピックを扱っています。
『SQL Server DMV Starter Pack』の詳細については、red-gate.com/our-company/about/book-store/ (英語) を参照してください。