SQL Server 2005 でのパフォーマンス問題のトラブルシューティング
| このページはアーカイブです。記載されている内容は情報提供のみを目的としており、ページ内のリンクは有効でない可能性がありますが、これらの情報についてマイクロソフトはいかなる責任も負わないものとします。 |
公開日: 2005年10月1日
ライター : Sunil Agarwal、Boris Baryshnikov、Tom Davidson、Keith Elmore、Denzil Ribeiro、Juergen Thomas
適用対象 : SQL Server 2005
概要 : SQL Server データベースで速度低下を経験することは、珍しいことではありません。この種類のパフォーマンス問題の原因となる理由は多数ありますが、適切に設計されていないデータベースや作業負荷に合うように設定されていないシステムなどが考えられます。管理者は積極的に問題の予防または低減に努め、問題が発生したときには原因を診断して、問題を修正するための是正措置をとる必要があります。このホワイト ペーパーでは、SQL Server Profiler、システム モニタ、SQL Server 2005 の新しい動的管理ビューなど、公開されているツールを使用して、一般的なパフォーマンス問題を診断およびトラブルシューティングするためのガイドラインを説明します。
ダウンロード
.gif "Cc966540.icon_word(ja-jp,TechNet.10).gif"){kind=icon}
SQL Server 2005 でのパフォーマンス問題のトラブルシューティング
トピック
はじめに
目標
方法
リソースのボトルネック
CPU のボトルネック
メモリのボトルネック
I/O ボトルネック
Tempdb
実行が遅いクエリ
まとめ
付録 A: DBCC MEMORYSTATUS の説明
付録 B: ブロック スクリプト
はじめに
SQL Server データベースで速度低下を経験する顧客は少なくありません。この理由は、不適切に設計されたデータベースから、作業負荷に合うように設定されていないシステムまでさまざまです。管理者は、積極的に問題の予防または低減に努め、問題が発生したときは原因を診断して、可能であれば問題を修正するための是正措置をとる必要があります。考えられるすべての問題を徹底的に分析することは不可能なので、このホワイト ペーパーでは、Microsoft® Corporation の Customer Support Services (CSS または PSS) でよく見られる問題に範囲を限定して説明します。ここでは、SQL Server Profiler、システム モニタ (Perfmon)、Microsoft SQL Server™ 2005 の新しい動的管理ビューなど、公開されているツールを使用して、一般的なパフォーマンス問題を診断およびトラブルシューティングするためのガイドラインを説明します。
目標
このホワイト ペーパーの主な目標は、公開されているツールを使用して、一般的な顧客シナリオで SQL Server のパフォーマンス問題を診断およびトラブルシューティングする一般的な方法を提供することです。
SQL Server 2005 のサポート性は大きく進歩しました。カーネル層 (SQL-OS) はアーキテクチャが再設計されて、内部構造と統計データが、動的管理ビュー (DMV) を通じてリレーショナル行セットとして公開されるようになりました。SQL Server 2000 では、この情報の一部が sysprocesses などのシステム テーブルを通じて公開されていますが、内部構造から関連する情報を抽出するには、SQL Server プロセス メモリの物理ダンプを生成しなければならない場合があります。この場合、大きな問題が 2 つあります。第 1 に、ダンプのサイズや作成に要する時間を考えると、顧客が常に物理ダンプを提供できるとは限りません。第 2 に、分析目的で Microsoft Corporation にファイルを転送しなければならないため、問題の診断に時間がかかることがあります。
そのため、このホワイト ペーパーでは、DMV を紹介することを二次的目標とします。DMV では、ほとんどの場合、物理ダンプの生成と分析を行う必要がなくなるため、診断プロセスを短縮化できます。このホワイト ペーパーでは、可能な限り、SQL Server 2000 と SQL Server 2005 で同一の問題のトラブルシューティング方法を相互比較します。DMV には、重要なシステム情報を取得するための簡単で使いやすいリレーショナル インターフェイスがあります。この情報を監視目的で使用して、管理者に問題の可能性について注意を促すことができます。または情報を定期的にポーリングして収集し、詳細分析に役立てることもできます。
方法
SQL Server の速度が低下する理由は多数あります。次の 3 つの主な兆候から、問題の診断を開始します。
リソースのボトルネック : このホワイト ペーパーでは、CPU、メモリ、I/O のボトルネックについて説明します。ネットワークの問題については検討しません。各リソースのボトルネックについて、問題の特定方法を説明し、考えられる原因を繰り返し処理します。たとえば、メモリのボトルネックではページングが過剰に発生し、それがパフォーマンスの低下につながることがあります。
Tempdb のボトルネック: 各 SQL Server のインスタンスに tempdb は 1 つしかないため、これがパフォーマンスとディスク容量のボトルネックになることがあります。不適切に動作するアプリケーションは、過剰な DDL/DML 操作と領域の両方の面で tempdb を過負荷にすることがあります。これが原因で、サーバー上で実行されている無関係なアプリケーションの速度が低下したり失敗することがあります。
実行速度の遅いユーザー クエリ : 既存のクエリのパフォーマンスが低下したり、新しいクエリが予想より時間がかかったりすることがあります。これには多くの理由が考えられます。たとえば、
統計情報を変更すると、既存のクエリのクエリ プランが役に立たなくなることがあります。
インデックスが見つからないと、テーブルのスキャンが強制的に行われるため、クエリの速度が低下することがあります。
リソースの使用率が通常でも、ブロックが原因でアプリケーションが遅くなることがあります。
ブロックが過剰になるのは、アプリケーションやスキーマの設計が正しくなかったり、トランザクションに選択した分離レベルが不適切だったりすることが原因です。
このような兆候の原因は、必ずしもそれぞれが独立しているとは限りません。クエリ プランの選択を間違えると、システム リソースに負荷がかかり、全体の作業負荷が遅くなることがあります。そのため、大きなテーブルで役立つインデックスが見つからなかったり、クエリ オプティマイザでそのインデックスが使用されない場合、これによってクエリが遅くなるだけでなく、不要なデータ ページを読み込まなければならないために多大な負荷が I/O サブシステムにかかます。また、これらのページをキャッシュに保存しなければならないためにメモリ (バッファ プール) にも負荷がかかります。同様に、実行頻度の高いクエリの再コンパイルが多すぎると、CPU にも負荷がかかります。
リソースのボトルネック
このホワイト ペーパーの次のセクションでは、CPU、メモリ、I/O サブシステム リソースとこれらがどのようにボトルネックになり得るかについて説明します (ネットワークの問題は、このホワイト ペーパーの説明範囲外です)。各リソースのボトルネックについて、問題の特定方法を説明し、考えられる原因を繰り返し処理します。たとえば、メモリのボトルネックではページングが過剰に発生し、それがパフォーマンスの低下につながることがあります。
リソースのボトルネックがあるかどうかを判断する前に、通常の状況ではリソースがどのように使用されているかを把握しておく必要があります。このホワイト ペーパーで説明する方法で、(パフォーマンス問題が生じていないときの) リソースの使用状況に関する基準の情報を収集することができます。
リソースが容量の限界に近づいており、SQL Server では現在の構成でその作業負荷をサポートできないということが問題かもしれません。この問題に対処するために、処理能力やメモリを増強したり、I/O またはネットワーク チャネルの帯域幅を増やす必要があるかもしれません。しかし、そのような対策を講じる前に、リソースのボトルネックに関する一般的な原因を理解しておくと役立ちます。再構成などのように、リソースを追加しなくてもよい解決方法もあります。
リソースのボトルネックを解決するためのツール
次のツールを 1 つ以上使用して、特定のリソースのボトルネックを解決します。
システム モニタ (PerfMon): このツールは、Windows の一部として提供されています。詳細は、システム モニタのドキュメントを参照してください。
SQL Server Profiler: SQL Server 2005 プログラム グループのパフォーマンス ツール グループの SQL Server プロファイラ を参照してください。
DBCC コマンド : 詳細は、SQL Server Books Online と「付録 A」を参照してください。
DMV: 詳細は、SQL Server Books Online を参照してください。
CPU のボトルネック
サーバー上に負荷が追加されていないのに、予期せぬ状況で CPU のボトルネックが突然発生する主な原因には、最適化されていないクエリ プラン、不適切な構成、設計要素があります。ハードウェア リソースの不足は原因ではありません。慌ててプロセッサの追加や高速化を試みる前に、まず CPU 帯域幅の最大消費原因を特定し、これを調整できないかどうかを確認する必要があります。
通常、サーバーが CPU バインドでないかどうかを判断するには、システム モニタが最良の手段です。Processor:% Processor Time カウンタが高いかどうかを確認してください。CPU ごとのプロセッサ時間が 80% を超えている場合は、ボトルネックと見なされます。また、sys.dm_os_schedulers ビューを使用して SQL Server スケジューラを監視し、実行可能なタスクの数がゼロ以外であることを確認することもできます。ゼロ以外の値は、タスクが時間要素が実行されるまで待機しなければならないということを意味します。このカウンタの値が大きいのも CPU ボトルネックの兆候です。次のクエリを使用してすべてのスケジューラを一覧表示し、実行可能なタスクの数を見ることができます。
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255
次のクエリを使用すると、現在キャッシュされているバッチまたはプロシージャのうち、CPU を多く使用しているものはどれかを詳しく表示できます。このクエリは、同じ plan__handle を持つ (つまり同じバッチまたはプロシージャの一部である) 全ステートメントによって消費された CPU を集計します。所定の plan_handle が複数のステートメントを持つ場合、詳しく掘り下げて、全体の CPU 使用率に最も大きな影響を与えている特定のクエリを見つけなければならない場合があります。
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
このセクションの残りの部分では、問題を検出および解決するための効果的な方法だけでなく、SQL Server で発生する可能性がある、CPU を多く使用する一般的な操作についても説明します。
過度のコンパイルと再コンパイル
バッチまたはリモート プロシージャ コール (RPC) を SQL Server に送信すると、サーバーはクエリを実行する前に、クエリ プランの有効性と正確さをチェックします。これらのチェックのいずれかが失敗した場合、バッチを再度コンパイルして別のクエリ プランを作成しなければならない場合があります。このようなコンパイルは、"再コンパイル" と呼ばれます。このような再コンパイルは通常、正確さを徹底するために必要であり、基礎となるデータが変更されたために、さらに適切なクエリ プランができるはずだとサーバーが判断した場合に、しばしば実行されます。コンパイルは CPU を多く使用するため、過度の再コンパイルを行うと、システムに CPU バインドのパフォーマンス問題が生じることになります。
SQL Server 2000 で SQL Server がストアド プロシージャを再コンパイルすると、再コンパイルをトリガしたステートメントだけでなく、ストアド プロシージャ全体が再コンパイルされます。SQL Server 2005 では、ストアド プロシージャのステートメント レベルでの再コンパイルが導入されました。SQL Server 2005 がストアド プロシージャを再コンパイルすると、プロシージャ全体ではなく、再コンパイルの原因となったステートメントだけがコンパイルされます。これにより、CPU 帯域幅の使用が減り、COMPILE ロックなどのロック リソースの競合も少なくなります。再コンパイルが行われる理由はさまざまですが、次のような理由が挙げられます。
スキーマ変更
統計変更
コンパイルの延期
SET オプションの変更
一時テーブルの変更
RECOMPILE クエリ ヒントまたは OPTION (RECOMPILE) を使用したストアド プロシージャの作成
検出
システム モニタ (PerfMon) または SQL トレース (SQL Server Profiler) を使用して、過度のコンパイルおよび再コンパイルを検出することができます。
システム モニタ (Perfmon)
SQL Statistics オブジェクトを使用すると、コンパイルおよび SQL Server のインスタンスに送信された要求の種類を監視するカウンタが提供されます。クエリのコンパイルおよび再コンパイルの数を受け取ったバッチの数と一緒に監視して、コンパイルが CPU 使用率を高める原因になっているかどうかを見つけることができます。SQL 再コンパイル / 秒とバッチ要求 / 秒の比率は、ユーザーがアドホック クエリを送信しているのでない限り、低いのが理想です。
主に注意すべきデータ カウンタは、次のとおりです。
SQL Server: "SQL Statistics:バッチ要求/秒"
SQL Server: "SQL Statistics:SQL コンパイル/秒
SQL Server: "SQL Statistics:SQL 再コンパイル/秒
詳細は、SQL Server Books Online の「SQL Statistics Object」を参照してください。
"SQL トレース "
PerfMon カウンタから、再コンパイルの数が多いことがわかった場合、再コンパイルが SQL Server の CPU 消費が高い原因になっている可能性があります。その場合、プロファイラのトレースを調べて、再コンパイルされているストアド プロシージャを見つける必要があります。SQL Server Profiler のトレースを調べると、再コンパイルの理由と一緒にその情報がわかります。この情報を取得するために、次のイベントを使用することができます。
SP:Recompile / SQL:StmtRecompile.SP:Recompile イベント クラスと SQL:StmtRecompile イベント クラスは、再コンパイルされたストアド プロシージャとステートメントを示します。ストアド プロシージャをコンパイルすると、ストアド プロシージャに対して 1 つのイベントが、またコンパイルされる各ステートメントに対して 1 つのイベントが生成されます。しかし、ストアド プロシージャを再コンパイルすると、再コンパイルの原因となったステートメントだけが再コンパイルされます (SQL Server 2000 のようにストアド プロシージャ全体が再コンパイルされることはありません)。SP:Recompile イベント クラスで特に重要なデータ列を以下に一覧表示します。EventSubClass データ列は、再コンパイルの理由を判断する場合に特に重要です。SP:Recompile はプロシージャや再コンパイルされたトリガに対して 1 回トリガされ、再コンパイルされる可能性が高いアド ホック バッチに対しては発生しません。SQL Server 2005 では、あらゆる種類のバッチ、アド ホック、ストアド プロシージャ、またはトリガが再コンパイルされるたびにこのイベント クラスが発生するので、SQL:StmtRecompiles を監視した方が実用的です。
これらのイベントで注目する主なデータ列は次のとおりです。
EventClass
EventSubClass
ObjectID (このステートメントを含むストアド プロシージャを表します)
SPID
StartTime
SqlHandle
TextData
詳細は、SQL Server Books Online の「SQL:StmtRecompile イベント クラス」を参照してください。
トレース ファイルが保存されている場合は、次のクエリを使用すると、トレースでキャプチャされた再コンパイル イベントをすべて表示できます。
select ™
spid,
StartTime,
Textdata,
EventSubclass,
ObjectID,
DatabaseID,
SQLHandle
from
fn_trace_gettable ( 'e:\recompiletrace.trc' , 1)
where
EventClass in(37,75,166)
EventClass 37 = Sp:Recompile, 75 = CursorRecompile, 166=SQL:StmtRecompile
さらにこのクエリ結果を SqlHandle 列とObjectID 列ごとに、あるいは別の列ごとにグループ化して、再コンパイルの大部分が 1 つのストアド プロシージャに起因するものか、その他別の理由 (SET オプションの変更など) に起因するものかを調べることもできます。
Showplan XML For Query Compile.Showplan XML For Query Compile イベント クラスは、Microsoft SQL Server が SQL ステートメントをコンパイルまたは再コンパイルしたときに発生します。このイベントには、コンパイルまたは再コンパイルされているステートメントについての情報が含まれています。この情報には、該当するプロシージャのクエリ プランとオブジェクト ID が含まれています。このイベントをキャプチャすると、各コンパイルまたは再コンパイルからキャプチャを行うために、多大なパフォーマンス オーバーヘッドがかかります。システム モニタの "SQL コンパイル / 秒のカウンタ値が大きい場合は、このイベントを監視する必要があります。この情報を使用すると、頻繁に再コンパイルされているステートメントを見つけることができます。この情報を使用して、これらのステートメントのパラメータを変更します。変更すると、再コンパイルの回数が減ります。
DMVsys.dm_exec_query_optimizer_info DMV を使用すると、SQL Server が最適化に要する時間を把握できます。この DMV のスナップショットを 2 つ撮ると、一定の期間中に最適化に費やされている時間を把握することができます。
select *
from sys.dm_exec_query_optimizer_info
counter occurrence value
---------------- -------------------- ---------------------
optimizations 81 1.0
elapsed time 81 6.4547820702944486E-2
特に最適化に要した経過時間を見てください。最適化中の経過時間は、通常、最適化に使用される CPU 時間に近いので (最適化プロセスが非常に CPU バインドであるため)、コンパイル時間が CPU 使用率を高めるのにどの程度起因しているかを測定できます。
この情報をキャプチャするのに便利なもう 1 つの DMV は sys.dm_exec_query_stats です。
調べるべきデータ列は次のとおりです。:
Sql_handle
作業者の合計時間
プラン生成番号
ステートメント開始オフセット
詳細は、SQL Server Books Online の sys.dm_exec_query_stats のトピックを参照してください。
特に、
plan_generation_num
は、クエリが再コンパイルされた回数を示します。次のサンプル クエリを使用すると、再コンパイルされた上位 25 のストアド プロシージャがわかります。
select *
from sys.dm_exec_query_optimizer_info
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
その他の詳細は、Microsoft TechNet の「Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005」 (英語) (https://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx) を参照してください。
解決策
過剰なコンパイル/再コンパイルが検出されたら、次のオプションを検討してください。
SET オプションを変更したために再コンパイルが発生した場合は、SQL Server Profiler を使用して、変更された SET オプションを判断してください。ストアド プロシージャ内で SET オプションを変更することは避けてください。接続レベルで設定する方が適切です。接続の有効期間を通じて SET オプションが変更されていないことを確認してください。
一時テーブルの再コンパイルのしきい値は、通常のテーブルよりも低くなっています。一時テーブルでの再コンパイルが統計変更によるものである場合は、一時テーブルをテーブル変数に変更することができます。テーブル変数の基数を変更しても、再コンパイルの原因とはなりません。この方法の欠点は、テーブル変数で統計が作成または維持されないため、クエリ オプティマイザでテーブル変数の基数が追跡されないことです。このため、最適化されていないクエリ プランが生じることがあります。さまざまなオプションを試して、最も適したものを選ぶとよいでしょう。
他のオプションとして、KEEP PLAN クエリ ヒントを使用する方法があります。これを使用すると、一時テーブルのしきい値が、永続的なテーブルの値と同じに設定されます。EventSubclass 列は、一時テーブルで操作の「統計変更」があったことを示します。
統計変更があったため (たとえば、データ統計を変更したためにプランが最適でなくなった場合など) の再コンパイルを避けるには、KEEPFIXED PLAN クエリ ヒントを指定します。このオプションが有効になっていると、統計ではなく正確さに関連した理由 (たとえば基になっているテーブル構造が変更されてプランが適用されなくなった場合) の場合にのみ再コンパイルが行われます。一例を挙げると、ステートメントが参照したテーブルのスキーマが変更された場合や、テーブルが sp_recompile ストアド プロシージャでマークされた場合に再コンパイルが発生します。
インデックスの統計やテーブルまたはインデックス ビューで定義された統計の自動更新をオフにすると、そのオブジェクトの統計変更が原因となっている再コンパイルが行われません。しかし、この方法で「自動統計」機能をオフにすることはお勧めできません。これは、クエリ オプティマイザがオブジェクトのデータ変更に敏感でなくなるため、最適ではないクエリ プランができることがあるからです。この方法は、他の方法をすべて使い果たした後の最後の手段としてのみ使用してください。
再コンパイルやオブジェクト間での不明瞭さを避けるためにも、バッチには修飾されたオブジェクト名 (dbo.Table1 など) を付けてください。
延期されたコンパイルが原因の再コンパイルを避けるためには、DML と DDL をインターリーブしたり、IF ステートメントのような条件構造から DDL を作成したりしないでください。
データベース エンジン チューニング アドバイザ (DTA) を実行して、インデックスを変更するとコンパイル時間とクエリの実行時間が改善されるかどうかを確認します。
ストアド プロシージャが WITH COMPILE オプションを使用して作成されているか、あるいは RECOMPILE クエリ ヒントが使用されているかどうかをチェックします。プロシージャが WITH RECOMPILE オプションを使用して作成されている場合は、SQL Server 2005 で RECOMPILE ヒントのステートメント レベルを活用して、そのプロシージャ内の特定のステートメントを再コンパイルする必要があるかどうかを調べられる場合があります。これを使用すると、実行されるたびにプロシージャ全体を再コンパイルする必要がなくなり、同時に個別のステートメントをコンパイルすることができます。RECOMPILE ヒントについての詳細は、SQL Server Books Online を参照してください。
効率の悪いクエリ プラン
クエリの実行プランを生成する場合、SQL Server オプティマイザはそのクエリへの応答時間が最も速いプランを選択しようとします。しかし、応答時間が最も速いということは、必ずしも使用される I/O の量が減るということでも、CPU の使用率が最も少ないということでもありません。これはさまざまなリソースのバランスによって決定されます。
オペレータの中には、他よりも CPU を多く使用するものがあります。Hash オペレータと Sort オペレータは、それぞれの入力データ全体をスキャンする性質があります。そのようなスキャンのときに先読み (前取り出し) を使用すると、オペレータでページが必要になる前に、常にページがバッファ キャッシュで使用できるようになります。そのため、物理 I/O を待つ時間を短縮化または省略できます。このような種類の操作が物理 I/O の制限を受けなくなると、CPU の消費が多くなるという形で表れる傾向があります。これに対して、入れ子になったループ結合にはインデックス検索が多く存在するため、インデックス検索がページがバッファ キャッシュに収まらないほどテーブルの多くの部分に渡るものであれば、すぐに I/O バインドになりがちです。
オプティマイザがさまざまな代替クエリ プランのコストを評価する際に使用する最も重要な入力は各オペレータの基数予想ですが、これはプラン表示から表示することができます (EstimateRows 属性と EstimateExecutions 属性)。基数予想が正確でないと、最適化に使用される主な入力に欠点があるため、多くの場合最終プランも正確でなくなります。
SQL Server オプティマイザが統計をどのように使用するかについて詳しく説明した優れたホワイト ペーパーは、「Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 (英語)」(https://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx) を参照してください。このホワイト ペーパーは、オプティマイザによる統計の使用方法、最新の統計を維持するためのベスト プラクティス、また正確な基数予想を妨げ、その結果、効率の悪いクエリ プランになってしまう一般的なクエリ設計の問題について説明しています。
検出
効率の悪いクエリ プランは、通常、比較することによって検出されます。効率の悪いクエリ プランが原因で、CPU 消費が増えることがあります。
sys.dm_exec_query_stats に対してクエリを行うと、累積的な CPU の使用が最も多いクエリを効率的に判断することができます。
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
または、CPU の使用が多い可能性がある ‘%Hash Match%’ や ‘%Sort%’ など、さまざまなオペレータのフィルタを使用して sys.dm_exec_cached_plans に対するクエリを行い、疑わしいクエリを探すことができます。
解決策
効率の悪いクエリ プランが検出された場合は、次のオプションを検討してください。
データベース エンジン チューニング アドバイザを使用してクエリを調整し、インデックスに関する推奨事項がないかどうかを調べます。
基数予想が正しくない問題をチェックします。
適用可能な最も厳しい WHERE 句を使用するようにクエリが作成されていますか? 制限されていないクエリは、リソースを多く使用する性質があります。
クエリと関係のあるテーブルで UPDATE STATISTICS を実行し、問題が持続するかどうかを調べます。
クエリで、オプティマイザが正確に基数を予想できないような構造が使用されていますか? 問題を回避できるようにクエリを変更できないかどうかを検討してください。
スキーマやクエリを変更することが不可能な場合、SQL Server 2005 には、条件テキストに一致するクエリに追加するクエリ ヒントを指定できる新しいプラン ガイド機能があります。これは、ストアド プロシージャ内だけでなく、アド ホック クエリでも行うことができます。OPTION (OPTIMIZE FOR) などのヒントを使用すると、オプティマイザのすべてのプランの可能性を残しながら、基数予想の効果を高めることができます。OPTION (FORCE ORDER) や OPTION (USE PLAN) などのその他のヒントを使用すると、クエリ プランをさまざまなレベルで制御することができます。
クエリ内の並列処理
クエリの実行プランを生成する場合、SQL Server オプティマイザはそのクエリへの応答時間が最も速いプランを選択しようとします。クエリのコストが、並列処理のコストしきい値オプションで指定されている値を超えて、並列処理が無効になっていない場合、オプティマイザは並列で実行できるプランを生成しようとします。並列クエリ プランは、複数のスレッドを使用してクエリを処理します。各スレッドは使用可能な CPU 全体に配分され、各プロセッサの CPU 時間を同時に使用します。並列処理の最大限度は、[並列処理の最大限度] オプションを使用するか、クエリ レベルごとに OPTION (MAXDOP) ヒントを使用して、サーバー全体に制限することができます。
実行で使用する並列処理の実際の程度 (DOP) の決定は、いくつのスレッドが並列で所定の操作を行うかを測定するものですが、実行時間まで延期されます。SQL Server 2005 は、クエリを実行する前に、使用率の低いスケジューラの数を決定し、残りのスケジューラをすべて使用するクエリの DOP を選択します。DOP が選択されると、クエリは選択された並列程度で完了まで実行されます。並列クエリは、対応する連続した実行プランと比べると、よく似た、多少 CPU 時間の多いプランを使用しますが、経過時間は短くなります。物理 I/O の待機などその他のボトルネックがなければ、並列プランは通常、すべてのプロセッサ全体で CPU を 100% 使用します。
並列プランにつながる 1 つの主要な要素 (システムのアイドル状態) は、クエリが実行を開始すると変更されることがあります。しかし、これはクエリが実行を開始した後で変更されます。たとえば、クエリがアイドル時間中に発生すると、サーバーは並列プランを使用して実行することを選択し、DOP を 4 として、4 つのプロセッサでスレッドを起動することがあります。これらのスレッドが実行を開始した後で、既存の接続から多くの CPU を必要とする別のクエリが送信されることがあります。その時点で、異なるスレッドすべてが使用可能な CPU の短いタイム スライスを共有するため、クエリ期間が長くなります。
並列プランで実行することは、本質的に悪いことではなく、そのクエリに最も速い応答が得られるはずです。しかし、特定のクエリの応答時間は、全体のスループットとシステム上の残りのクエリの応答を比較して検討すべきです。並列クエリは通常バッチ処理や意思決定支援作業に最適であり、トランザクション処理環境では望ましくないことがあります。
検出
クエリ内の並列処理に関する問題は、次の方法で検出できます。
システム モニタ (Perfmon)
詳細は、「SQL Server:SQL Statictics – バッチ要求 / 秒」カウンタおよび SQL Server Books Online の「SQL Statistics オブジェクト」を参照してください。
並列プランを検討する前に、クエリには並列構成設定のコストしきい値 (既定値は 5) を超える予想コストが必要なため、サーバーが処理する秒あたりのバッチ数が増えるほど、並列プランでバッチが実行される可能性が低くなります。多くの並列クエリを実行しているサーバーの毎秒のバッチ要求数は少ないのが普通です (100 未満など)。
DMV
次のクエリを使用して、実行中のサーバーから、特定のセッションでアクティブな要求が並列処理で実行されているかどうかを決定することができます。
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0
この情報を使用すると、sys.dm_exec_cached_plan を使用してプランを取得しながら、sys.dm_exec_sql_text を使用してクエリのテキストを簡単に取得することができます。
また、並列処理で実行できるプランを検索することもできます。これはキャッシュされているプランを検索し、リレーショナル オペレータがゼロ以外の値の Parallel 属性を持っているかどうかを調べることによって検索できます。これらのプランは並列処理で実行されているわけではありませんが、システムがあまり使用されていない場合に並列処理することができます。
--
-- Find query plans that may run in parallel
--
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="https://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
通常、クエリの時間は、ロックや物理 I/O などのリソースを待つのに時間が費やされるため、CPU 時間よりも長くかかります。クエリで経過時間よりも多くの CPU 時間を使用する唯一のシナリオは、複数のスレッドで同時に CPU を使用するなどの並列プランでクエリが実行される場合です。すべての並列クエリでこの動作 (CPU 時間が実行時間よりも長い) が見られるわけではありません。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time
SQL Trace
Look for the following signs of parallel queries,
which could be either statements or batches that have
CPU time greater than the duration.
select
EventClass,
TextData
from
::fn_trace_gettable('c:\temp\high_cpu_trace.trc',
default)
where
EventClass in (10, 12) -- RPC:Completed,
SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in
milliseconds, Duration in microseconds Or can be
Showplans (un-encoded) that have Parallelism operators]
in them
select
EventClass,
TextData
from
::fn_trace_gettable('c:\temp\high_cpu_trace.trc',
default)
where
TextData LIKE '%Parallelism%'
解決策
並列プランで実行されるクエリはすべて、オプティマイザが並列処理のコストしきい値を超えるだろうと考えるクエリです。このしきい値の既定値は 5 (参照マシンでの実行時間が約 5 秒) です。前述の方法で特定したクエリは、詳しい調整の候補です。
データベース エンジン チューニング アドバイザを使用して、インデックスの変更、インデックス ビューの変更、パーティション分割変更などによってクエリのコストを下げることができないかを調べます。
基数予想がクエリのコストを見積もる際の主な要素となるため、実際の基数と予想基数との間に大きな違いがないかをチェックしてください。大きな違いが見つかった場合 :
統計の自動作成データベース オプションが無効になっている場合は、プラン表示出力の警告列に MISSING STATS エントリがないことを確認してください。
基数予想がオフになっているテーブルで UPDATE STATISTICS の実行を試みてください。
複数のステートメント テーブル値を持つ関数や CLR 関数、テーブル変数、Transact-SQL 変数との比較など、オプティマイザが正確に予想できないクエリ構造がクエリで使用されていないことを確認します (パラメータとの比較は使用できます)。
クエリを別の Transact-SQL ステートメントや式を使用してもっと効率的な方法で作成できないかどうかを評価します。
不適切なカーソル使用
SQL Server 2005 の前の SQL Server バージョンでは、1 つの接続ごとに 1 つのアクティブな共通接続だけがサポートされていました。実行されているクエリやクライアントに送信する結果が保留になっているクエリは、アクティブと見なされます。状況によっては、クライアント アプリケーションで結果を読み通し、結果セットから読み取った行に基づいて別のクエリを SQL Server に送信しなければならない場合があります。これは、他の結果が保留になっている既定の結果セットでは行うことができません。一般的な解決策として、サーバー側のカーソルを使用するように接続プロパティを変更することができます。
サーバー側のカーソルを使用すると、データベース クライアント ソフトウェア (OLE DB プロバイダまたは ODBC ドライバ) は、sp_cursoropen、sp_cursorfetch などのように、特別に拡張されたストアド プロシージャの内側にクライアント要求を透過的にカプセル化します。これは (TSQL カーソルではなく) "API カーソル" と呼ばれます。ユーザーがクエリを実行すると、クエリ テキストが sp_cursoropen を通じてサーバーに送信されます。結果のセットから読み取った要求は、sp_cursorfetch として、特定の行数だけを返すようにサーバーに指示します。取得する行数を制御することで、ODBC ドライバまたは OLE DB プロバイダで行をキャッシュすることが可能になります。こうすれば、送信されたすべての行を読み取るまでサーバーがクライアントを待つ状況を回避できます。そのため、サーバーはその接続で新しい要求を受け取る準備ができます。
カーソルを開き、一度に 1 行 (または少数の行) を取得するアプリケーションは、特にワイド エリア ネットワーク (WAN) では、ネットワーク レイテンシのために簡単にボトルネックになりがちです。多数の異なるユーザーが接続している高速ネットワークでは、多くのカーソル要求を処理するのに必要なオーバーヘッドが大きくなります。結果セットの正しい場所にカーソルを再配置する関連オーバーヘッド、要求ごとの処理オーバーヘッド、また類似した処理などが必要になるため、同じ 100 行を一度に 1 行ずつ返す 100 の個別の要求を処理するよりも、100 行を返す 1 つの要求を処理した方がサーバーにとって効率的です。
検出
次の方法を使用して、不適切なカーソル使用をトラブルシューティングできます。
システム モニタ (Perfmon)
SQL Server:Cursor Manager By Type – カーソル要求 / 秒カウンタを調べると、このパフォーマンス カウンタを見てシステムで使用されているカーソルの数について全体の感じをつかむことができます。フェッチ サイズが小さいために CPU 使用率が高くなっているシステムは、毎秒数百のカーソル要求があるのが普通です。フェッチ バッファ サイズがわかる特定のカウンタはありません。
DMV
次のクエリを使用して、1 行のフェッチ バッファ サイズを使用している (TSQL カーソルではなく) API カーソルとの接続を決定することができます。100 行などの大きいフェッチ バッファを使用した方がずっと効率的です。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API
cursor (TSQL cursors always have fetch buffer of 1)
SQL トレース
sp_cursorfetch ステートメントに RPC:Completed イベント クラス検索が含まれるトレースを使用します。4 番目のパラメータの値が、フェッチから返される行数になります。返すように要求された行の最大数は、対応する RPC:Starting イベント クラスで入力パラメータとして指定されます。
解決策
処理を達成するのにカーソルが最も適した方法か、より効率よいセットベースの操作が可能かを決定します。
SQL Server 2005 に接続するときは、複数のアクティブな結果 (MARS) を有効にすることを検討してください。
カーソルに大きなフェッチ バッファ サイズを指定する方法を決定するには、特定の API に該当するドキュメントを参照してください。
ODBC - SQL_ATTR_ROW_ARRAY_SIZE
OLE DB – IRowset::GetNextRows または IRowsetLocate::GetRowsAt
メモリのボトルネック
このセクションでは特にメモリ不足の条件とその診断方法、またさまざまなメモリ エラーや考えられる理由、トラブルシューティングの方法についても説明します。
背景情報
さまざまなメモリ リソースを総称して "メモリ" と呼ぶことがよくあります。メモリ リソースにはいろいろな種類があるため、どのメモリ リソースについて言及しているかを理解し、区別することが大切です。
仮想アドレス領域と物理メモリ
Microsoft Windows® では、各プロセスが独自の仮想アドレス領域 (VAS) を持っています。プロセスに使用できるすべての仮想アドレスのセットによって、VAS のサイズが構成されます。VAS のサイズは、アーキテクチャ (32 ビットまたは 64 ビット) とオペレーティング システムによって異なります。トラブルシューティングの状況では、仮想アドレス領域が消費メモリ リソースであり、物理メモリを使用できても 64 ビットのプラットフォームでそこからアプリケーションを実行できることを理解しておくことが大切です。
仮想アドレス領域についての詳細は、SQL Server Books Online の「Process Address Space」および MSDN の記事「Virtual Address Space」 (英語) を参照してください。
Address Windowing Extensions (AWE) と SQL Server
Address Windowing Extensions (AWE) は、32 ビット アプリケーションが、32 ビットのアドレス特有の制限を超えて物理メモリを操作できるようにした API です。AWE メカニズムは、64 ビットのプラットフォームでは技術的に必要ありません。ただし、存在することは事実です。AWE メカニズムを通じて割り当てられるメモリ ページは、64 ビットのプラットフォームでは "ロックされたページ" と呼ばれます。
32 ビットと 64 ビットの両方のプラットフォームで、AWE メカニズムを通じて割り当てられたメモリはページ アウトできません。これはアプリケーションにとっては好都合かもしれません (これは 64 ビットのプラットフォームで AWE メカニズムを使用する理由の 1 つです)。これにより、システムおよび他のアプリケーションで使用可能な RAM の量が影響を受け、悪い影響を与えることがあります。このため、AWE を使用するには、SQL SERVER を実行するアカウントでメモリ内のページのロック特権が有効になっている必要があります。
トラブルシューティングの観点から見て重要な点は、SQL Server のバッファ プールは AWE でマップしたメモリを使用しますが、AWE を通じて割り当てられたメモリを最大限に活用できるのはデータベース (ハッシュされた) ページだけだということです。AWE メカニズムを通じて割り当てられたメモリは、タスク マネージャまたは Process: Private Bytes パフォーマンス カウンタではレポートされません。この情報を取得するには、SQL Server 固有のカウンタまたは動的管理ビューを使用する必要があります。
AWE でマップされたメモリについての詳細は、SQL Server Books Online の「Managing memory for large databases」と「Memory Architecture」のトピックと MSDN の「Large Memory Support」(英語) を参照してください。
次の表に、SQL Server 2005 のさまざまな構成における最大メモリ サポート オプションを示します (SQL Server または Windows の特定のエディションでは、サポートされているメモリの制限がさらに制約を受ける場合があります)。
表 1
構成 |
VAS |
最大物理メモリ |
AWE/ロックしたページのサポート |
|---|---|---|---|
32 ビットの OS でネイティブ 32 ビット 3GB の起動パラメータを使用1 |
2 GB 3 GB |
64 GB 16 GB |
有効 有効 |
x64 OS で 32 ビット (WOW) |
4 GB |
64 GB |
有効 |
IA64 OS で 32 ビット (WOW) |
2 GB |
2 GB |
無効 |
x64 OS でネイティブの 64 ビット |
8 TB |
1 TB |
有効 |
IA64 OS でネイティブの 64 ビット |
7 TB |
1 TB |
有効 |
| 1 | 起動パラメータについての詳細は、SQL Server Books Online の「Using AWE」のトピックを参照してください。 |
メモリ負荷
メモリ負荷とは、使用できるメモリの量が限られている状況を示します。メモリ負荷で SQL Server が実行される状況を特定すると、メモリ関連の問題をトラブルシューティングする際に役立ちます。SQL Server の応答は、存在するメモリ負荷の種類によって異なります。次の表に、メモリ負荷の種類と一般的な根本原因を示します。いずれの場合もタイムアウトを経験するか明らかなメモリ不足メッセージが表示されるはずです。
表 2
負荷 |
外部 |
内部 |
|---|---|---|
物理 |
物理メモリ (RAM) が不足しています。これが原因で、システムでは現在実行中の処理のワーキング セットがトリミングされ、全体的な処理速度が低下する場合があります。 SQL Server はこの状態を検出し、構成に応じて、バッファ プールのコミットしたターゲットを削減し、内部キャッシュの削除を開始する場合があります。 |
SQL Server は内部でメモリ消費が多いことを検出し、内部コンポーネント間のメモリ再配分の原因となります。 内部メモリの負荷は、次に示す項目の結果として生じる場合があります。
|
仮想 |
システム ページ ファイルの容量が不足しています。これが原因で、システムでは現在割り当てられているメモリをページ アウトできず、メモリの割り当てが失敗する場合があります。この状態により、システム全体の応答が非常に遅くなったり、中断することになる場合もあります。 |
断片化 (多くの VAS が利用可能であるが小さなブロックになっている) や消費 (直接割り当て、SQL Server VAS にロードされたDLL、多数のスレッド) のため VAS が不足しています。 SQL Server はこの状態を検出すると、VAS の予約された領域を開放し、バッファ プールのコミットされたターゲットを削減し、キャッシュの圧縮を開始する場合があります。 |
Windows には、物理メモリが高速であるか、不足しているかどうかを通知するメカニズムがあります2SQL Server ではメモリ管理に関する決定を行う際に、このメカニズムを使用します。
表 3 に、各ケースにおける一般的なトラブルシューティング手順を示します。
表 3
メモリの負荷 |
外部 |
内部 |
|---|---|---|
物理 |
|
|
仮想 |
|
|
ツール
トラブルシューティングに使用できるツールおよび情報ソースを次に示します。
メモリに関連した DMV
DBCC MEMORYSTATUS command
パフォーマンス モニタや SQL Server 固有のオブジェクトの DMV などのパフォーマンス カウンタ
タスク マネージャ
アプリケーション ログ、システム ログなどのイベント ビューア
メモリの負荷を検出する
メモリの負荷自体は問題を示しません。メモリの負荷は、サーバーで後にメモリ エラーが発生するのに必要な条件ですが十分な条件ではありません。メモリの負荷状態下での作業は、サーバーの通常の操作状態である可能性があります。しかし、メモリの負荷の兆候はサーバーが容量の限界に近づいていること、およびメモリ不足エラーの可能性が存在することを示している場合があります。通常の運用サーバーの場合、この情報は後でメモリ不足状態の理由を決定するための基準として使用できます。
外部の物理メモリの負荷
[タスク マネージャ] を開き、[パフォーマンス] 表示で、[物理メモリ] セクション、[利用可能] の値をチェックします。利用可能なメモリ容量が低い場合は、外部メモリの負荷が存在する場合があります。正確な値は多くの要素により異なりますが、値が 50 ～ 100 MB を下回る場合は外部メモリの負荷の可能性を調べ始めます。利用可能な容量が 10 MB を下回るときは、外部メモリの負荷が明確に存在します。
同等の情報は、システム モニタの Memory: Available Bytes カウンタを使用して取得することもできます。
外部メモリの負荷が存在し、メモリに関連したエラーが表示される場合は、システムの物理メモリの主要なコンシューマを特定する必要があります。これを行うには、Process: Working Set パフォーマンス カウンタまたはタスク マネージャの [プロセス] タブの [メモリ使用] 列を表示し、最も使用率が高いコンシューマを特定します。
システムでの物理メモリの使用量の合計は、次のカウンタを合計することで大まかに説明できます。
各プロセスの Process オブジェクトおよび Working Set カウンタ
Memory オブジェクト
システム ワーキング セットの Cache Bytes カウンタ
非ページ プールのサイズの Pool Nonpaged Bytes カウンタ
Available Bytes (タスク マネージャの [利用可能] 値と同等)
外部の負荷がない場合、Process: Private Bytes カウンタまたはタスク マネージャの[仮想メモリ サイズ] がワーキング セット (Process: Working Set またはタスク マネージャの [メモリ使用量]) のサイズに近づく必要があります。つまり、ページ アウトされるメモリはありません。
タスク マネージャの [メモリ使用量] 列および対応するパフォーマンス カウンタは、AWE 経由で割り当てられたメモリをカウントしないことに注意してください。そのため、AWE が有効の場合、情報が不十分です。この場合は、SQL Server 内のメモリ分布を調べ、全体の稼働状態を把握する必要があります。
次に示す sys.dm_os_memory_clerks DMV を使用して、SQL Server が AWE メカニズム経由でどのぐらいのメモリを割り当てたかを調べることができます。
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
SQL Server では、このメカニズムは現在、AWE が有効な場合にのみ、バッファ プール クラーク (type = "MEMORYCLERK_SQLBUFFERPOOL") によってのみ使用されます。
主要な物理メモリ コンシューマを特定および削除 (可能な場合) し、メモリを増やすことで、外部メモリの負荷を軽減します。これにより通常、メモリに関連する問題は解決するはずです。
外部の仮想メモリの負荷
現在のメモリ割り当てに対応するのに十分なページ ファイルの容量があることを判別する必要があります。これを確認するには、タスク マネージャを開き、[パフォーマンス] 表示で [コミット チャージ] セクションをチェックします。[合計] が [制限値] に近づいている場合は、ページ ファイル容量が不足している可能性があります。[制限値] は、ページ ファイル容量を拡張せずにコミットできるメモリの最大容量を示します。タスク マネージャの [コミット チャージ] の [合計] は、実際の使用量ではなく、ページ ファイルに使用できる可能性を示していることに注意してください。物理メモリの負荷の下では、ページ ファイルの実際の使用量は増加します。
同等の情報は、Memory: Commit Limit、Paging File: %Usage、および Paging File: %Usage Peak カウンタから取得することもできます。
プロセスごとにページ アウトされたメモリ容量を大まかに推測するには、Process Private Bytes カウンタから Process: Working Set の値を減算します。
Paging File: %Usage Peak (またはピーク コミット チャージ) が高い場合、ページ ファイルの増加を示すイベントの System イベント ログまたは "仮想メモリの不足" の通知をチェックします。ページ ファイルのサイズを増やす必要がある場合があります。High Paging File: %Usage はコミットを超える物理メモリを示し、外部の物理メモリの負荷 (大きなコンシューマ、搭載された RAM の十分な容量) とともに考慮する必要があります。
内部の物理メモリの負荷
内部メモリの負荷は SQL Server 自体によって設定されるため、論理的なステップでは、バッファ配布に異常がないことをチェックすることによって、SQL Server 内のメモリ配布を調べます。通常、バッファ プールは、SQL Server によってコミットされたメモリの大部分を占めています。バッファ プールに属するメモリの容量を判断するには、DBCC MEMORYSTATUS 出力を確認します。Buffer Counts セクションで、Target 値を確認します。サーバーが通常の負荷に達した後の DBCC MEMORYSTATUS 出力の一部を次に示します。
Buffer Counts Buffers
------------------------------ --------------------
Committed 201120
Target 201120
Hashed 166517
Reserved Potential 143388
Stolen Potential 173556
External Reservation 0
Min Free 256
Visible 201120
Available Paging File 460640
Target は 8 KB ページの数として SQL Server によって計算され、ページングの原因にならずにコミットできます。Target は定期的に、Windows からメモリ不足/増加の通知に応答して再計算されます。通常の負荷があるサーバー上のターゲット ページの数の減少は、外部の物理メモリの負荷への対応を示している場合があります。
SQL Server が (Process: Private Bytes またはタスク マネージャの [メモリ使用量] 列で決定される) 多くのメモリを使用した場合、Target カウント数がメモリ容量の大部分に達しているかどうかを確認します。AWE が有効の場合、AWE 割り当てメモリを sys.dm_os_memory_clerks または DBCC MEMORYSTATUS 出力から説明する必要があります。
前述の例 (AWE が有効) では、Target * 8 KB = 1.53 GB です。一方で、サーバーの Process: Private Bytes は約 1.62 GB です。つまり、バッファ プール ターゲットが SQL Server によって使用されるメモリの 94% を占めています。サーバーが負荷されていない場合、Target は、Process: Private Bytes パフォーマンス カウンタで報告される通常の容量を超える可能性があることに注意してください。
Target は低いが、サーバーの Process: Private Bytes またはタスク マネージャのメモリ使用量が多い場合は、バッファ プール以外からメモリを使用するコンポーネントによる内部メモリの負荷に直面している場合があります。COM オブジェクト、リンクされたサーバー、拡張ストアド プロシージャ、SQLCLRなどの SQL Server プロセスにロードされたコンポーネントがバッファ プール以外のメモリ消費の原因となります。これらのコンポーネントにより消費されるメモリを、特にこれらが SQL Server メモリ インターフェイスを使用しない場合、追跡するのに簡単な方法はありません。
SQL Server のメモリ管理メカニズムを認識しているコンポーネントは、少ないメモリ割り当てのバッファ プールを使用します。メモリ割り当てが 8 KB より大きい場合、これらのコンポーネントは複数ページ アロケータ インターフェイスを介してバッファ プール以外のメモリを使用します。
複数ページ アロケータを介して消費されるメモリの容量をチェックする簡単な方法を次に示します。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
-- amount of mem allocated though multipage
allocator interface select sum(multi_pages_kb)
from sys.dm_os_memory_clerks
複数ページ アロケータによって割り当てられたメモリの詳細な配布を次のように取得できます。
select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
type
------------------------------------------ ---------
MEMORYCLERK_SQLSTORENG 56
MEMORYCLERK_SQLOPTIMIZER 48
MEMORYCLERK_SQLGENERAL 2176
MEMORYCLERK_SQLBUFFERPOOL 536
MEMORYCLERK_SOSNODE 16288
CACHESTORE_STACKFRAMES 16
MEMORYCLERK_SQLSERVICEBROKER 192
MEMORYCLERK_SNI 32
膨大な容量 (100 ~ 200 MB 以上) のメモリが複数ページ アロケータにより割り当てられている場合は、さらなる調査を必要とします。
複数ページ アロケータにより割り当てられた膨大な容量のメモリが見つかった場合は、サーバー構成を確認し、前述のクエリまたは次のクエリを使用して、メモリの大部分を消費しているコンポーネントを特定します。
Target は低いが割合において高い場合は、メモリの大部分が SQL Server によって消費されていることを示します。前のサブセクション (外部の物理メモリの負荷) の手順に従って、外部メモリの負荷のソースを調べるか、サーバーのメモリ構成パラメータをチェックします。
最大サーバー メモリや最小サーバー メモリのオプション セットがある場合は、これらの値とターゲットの値を比較する必要があります。最大サーバー メモリのオプションでは、バッファ プールにより消費されるメモリの最大容量を制限しますが、サーバー全体としてはより多くのメモリを消費できます。最小サーバー メモリのオプションでは、設定値を下回るバッファ プール メモリを解放しないようにサーバーに指示します。Target が最小サーバー メモリの設定値を下回り、サーバーが負荷の下にあるときは、サーバーは外部メモリの負荷の下で稼働し、この設定で指定された容量を一度も取得できなかったことを示す場合があります。また、前述のとおり、内部コンポーネントからの負荷を示す場合もあります。Target カウントは、最大サーバー メモリ オプションの設定値を上回ることはできません。
まず、奪われたページ カウントを DBCC MEMORYSTATUS 出力からチェックします。
Buffer Distribution Buffers
------------------------------ -----------
Stolen 32871
Free 17845
Cached 1513
Database (clean) 148864
Database (dirty) 259
I/O 0
Latched 0
ターゲット (前述の出力のフラグメントを参照) と比べて奪われたページの割合が高い (75 ~ 80% を上回る) 場合は、内部メモリの負荷があることを示します。
サーバー コンポーネントによるメモリ割り当ての詳細については、sys.dm_os_memory_clerks DMV を使用することによって評価できます。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
-- amount of memory consumed by components
outside the Buffer pool
-- note that we exclude single_pages_kb as
they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as
[Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'
-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'
コンポーネントごとの詳細情報は次のように取得できます (これには、バッファ プールから、およびバッファ プール以外から割り当てられたメモリが含まれます)。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool
committed memory as it represents the largest
consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab
前述のクエリでは、バッファ プールの処理が異なり、単一ページ アロケータを介して他のコンポーネントにメモリを提供することに注意してください。(単一ページ アロケータ経由の) バッファ プール ページを最も消費する上位 10 個のコンシューマを特定するには、次のクエリを使用します。
-- top 10 consumers of memory from BPool
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc
通常は、内部コンポーネントによるメモリ消費を制御できませんが、メモリの大部分を消費するコンポーネントを特定することで、問題の調査対象を絞り込むことができます。
システム モニタ (Perfmon)
メモリの負荷の兆候に関して、次のカウンタをチェックすることもできます (詳細については、SQL Server Books Online を参照してください)。
SQL Server: Buffer Manager オブジェクト
バッファ キャッシュのヒット率が低い。
Page life expectancy が低い。
チェックポイントの Pages/sec カウンタの値が大きい。
Lazy writes/sec カウンタの値が大きい。
メモリ不足および I/O オーバーヘッドは通常、ボトルネックに関連しています。この記事の「I/O ボトルネック」を参照してください。
キャッシュおよびメモリの負荷
外部および内部メモリの負荷を調べるもう 1 つの方法として、メモリ キャッシュの動作を調べることができます。
SQL Server 2000 と比較して SQL Server 2005 の内部実装が異なる点の 1 つに同型のキャッシュ フレームワークがあります。最近までほとんど使われていないエントリをキャッシュから削除するため、フレームワークはクロック アルゴリズムを実装しています。現在は、内部クロック ハンドと外部クロック ハンドの 2 つのクロック ハンドを使用しています。
内部クロック ハンドは、他のキャッシュと比較して、キャッシュのサイズを制御し、キャッシュが限界容量に近づいていることをフレームワークが予測すると、動作し始めます。
外部クロック ハンドは SQL Server 全体がメモリ負荷に陥ると、動作し始めます。外部クロック ハンドの動作は、外部および内部メモリのどちらの負荷によっても開始されます。内部クロック ハンドおよび外部クロック ハンドの動作がそれぞれ内部メモリの負荷および外部メモリの負荷に関連すると混同しないでください。
クロック ハンドの動作についての情報は、次のコードで示すように sys.dm_os_memory_cache_clock_hands DMV によって提供されます。各キャッシュ エントリでは、内部および外部クロック ハンドに対して別々の行があります。rounds_count および removed_all_rounds_count の増加が見つかった場合、サーバーは内部/外部メモリ負荷が低い状態になっています。
select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0
次に示すように、sys.dm_os_cache_counters DMV と合わせることで、サイズなどのキャッシュの詳細情報を取得できます。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc
USERSTORE エントリで、使用中のページの数はレポートされないため、NULL となることに注意してください。
リング バッファ
sys.dm_os_ring_buffers リング バッファ DMV からメモリ診断の多くの情報を取得できます。各リング バッファは、特定の種類の通知において最新の数のレコードを維持します。特定のリング バッファの詳細情報を次に示します。
RING_BUFFER_RESOURCE_MONITOR
リソース モニタの通知からの情報を使用して、メモリの状態の変化を識別できます。内部的に、SQL Server には異なるメモリの負荷を監視するフレームワークがあります。メモリの状態が変化すると、リソース モニタのタスクによって通知が生成されます。この通知はコンポーネントによって内部的に使用され、メモリの状態に応じてメモリ使用状況が調整されます。また、次のコードに示すとおり、sys.dm_os_ring_buffers DMV を介してユーザーに提供されます。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
レコードは、次のようになります。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
RESOURCE_MEMPHYSICAL_LOW
2
0
1646380
432388
0
0
26592
17128
17624
50
3833132
3240228
5732340
5057100
2097024
336760
0
このレコードから、サーバーは物理メモリ不足の通知を受け取ったことが推定できます。メモリの容量はキロバイト単位で確認することもできます。SQL Server の XML 機能を使用して、この情報をクエリできます。例を次のコードで示します。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc
メモリ不足の通知を受け取ると、バッファ プールはそのターゲットを再計算します。ターゲット カウントは、最大サーバー メモリおよび最小サーバー メモリのオプションで指定された制限範囲を超えないように注意します。バッファ プールの新しくコミットしたターゲットの値が、現在コミットされているバッファより低い場合は、外部の物理メモリの負荷が除去されるまでバッファ プールを圧縮します。AWE が有効な状態で実行した場合、SQL Server 2000 は 物理メモリの負荷に反応できないことに注意してください。
RING_BUFFER_OOM
このリング バッファには、次のコードの例で示すように、サーバーのメモリ不足状態を示すレコードが含まれます。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'
レコードは、次のようになります。
FAIL_VIRTUAL_COMMIT
4096
このレコードは、どの操作 (コミット、予約、またはページ割り当て) が失敗したか、および要求されたメモリの容量を示しています。
RING_BUFFER_MEMORY_BROKER および内部メモリの負荷
内部メモリの負荷が検出されると、バッファ プールをメモリ割り当てのソースとして使用するコンポーネントのメモリ不足通知がオンになります。メモリ不足通知をオンにすると、ページを使用するキャッシュや他のコンポーネントからページを解放できます。
内部メモリの負荷は、最大サーバー メモリを調整したり、バッファ プールから奪われたページの割合が 80% を超えたりすると、発生する可能性もあります。
内部メモリ負荷の通知 ("Shrink") はメモリ ブローカ リング バッファをクエリして観察できます。コードの例を次に示します。
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)')
as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc
RING_BUFFER_BUFFER_POOL
このリング バッファには、バッファ プールのメモリ不足状態などのバッファ プールの重大なエラーを示すレコードが含まれます。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'
レコードは、次のようになります。
84344
84350
20
20345
64001
64001
このレコードは、エラー (FAIL_OOM、FAIL_MAP、FAIL_RESERVE_ADJUST、FAIL_LAZYWRITER_NO_BUFFERS) およびエラー時のバッファ プールのステータスを示します。
内部の仮想メモリの負荷
sys.dm_os_virtual_address_dump DMV を使用して、VAS 使用量を追跡できます。VAS 要約は、次に示すビューを使用してクエリで表すことができます。
注 : 次の表に示すコード スニペットの一部は、読みやすさのために複数の行に分けて表示されています。これらは単一行で入力する必要があります。
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions
of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation
base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with
zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size
次に示すクエリを使用して、VAS 状態を評価できます。
-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0
-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
利用可能な最大の領域が 4 MB を下回る場合は、VAS の負荷が発生している可能性があります。SQL Server 2005 は VAS の負荷を監視し、それに対応します。SQL Server 2000 は VAS の負荷をアクティブに監視しませんが、仮想メモリの不足エラーが発生すると、キャッシュを削除して対応します。
メモリ エラー時の一般的なトラブルシューティング手順
メモリ エラーをトラブルシューティングするのに役立つ一般的な手順の概要を次に示します。
サーバーが外部メモリの負荷の下で動作しているかどうかを確認します。外部の負荷が存在する場合は、まずそれを解決し、その後も問題/エラーがあるかどうかを確認します。
SQL Server: Buffer Manager、SQL Server: Memory Manager のパフォーマンス モニタのカウンタを収集し始めます。
メモリ構成パラメータ (sp_configure) の min memory per query、min/max server memory、awe enabled、および lock pages in memory 特権を確認します。通常とは異なる設定に注意し、必要に応じて修正します。SQL Server 2005 の増加したメモリ要件を明らかにします。
サーバーに間接的に影響を及ぼす場合がある、規定値以外の sp_configure パラメータがあるかどうかを確認します。
内部メモリの負荷をチェックします。
メモリ エラーのメッセージが表示されたときの DBCC MEMORYSTATUS 出力および変化する状態を観察します。
ワークロード (同時処理セッションの数、現在実行中のクエリなど) を確認します。
メモリ エラー
701 - システム メモリが不足しているため、このクエリを実行できません。
原因
これは、サーバーの一般的なメモリ不足エラーです。メモリ割り当てに失敗したことを示しており、現在のワークロードのメモリ限界に達したなど、さまざまな理由から発生する可能性があります。SQL Server 2005 の増加したメモリ要件、および max server memory オプションなどの特定の構成設定の状態では、SQL Server 2000 よりこのエラーが表示される可能性が高くなります。通常、トランザクション失敗の原因はこのエラーではありません。
トラブルシューティング
このエラーが (同じ状態で) 一貫し、反復しているか、(異なる状態で) ランダムに表示されるかどうかに関係なく、このエラーが表示される間は、サーバーのメモリ配分を調査する必要があります。このエラーが存在するときは、診断クエリが失敗する可能性があります。外部的な評価から調査を始めます。「メモリ エラー時の一般的なトラブルシューティング手順」で概説する手順に従います。
可能な解決策には、外部メモリの負荷の除去およびmax server memory 設定の増加、DBCC FREESYSTEMCACHE、DBCC FREESESSIONCACHE、またはDBCC FREEPROCCACHE コマンドのいずれかを使用するキャッシュの解放などが含まれます。問題が再び発生する場合は、ワークロードを減らします。
802 - バッファ プールで使用できるメモリが不足しています。
原因
このエラーはメモリ不足状態を必ずしも示していません。バッファ プールのメモリが他の何かによって使用されていることを示す場合があります。SQL Server 2005 では、このエラーが発生するのは比較的まれです。
トラブルシューティング
一般的なトラブルシューティング手順および 701 エラーで概説した推奨事項を使用します。
8628 - クエリの最適化を待機中にタイムアウトが発生しました。クエリを再実行してください 。
原因
このエラーは、クエリのコンパイル処理を完了するのに必要なメモリの容量が取得できなかったためにこの処理が失敗したことを示しています。解析、代数処理、最適化を含むコンパイル処理を行うにつれて、メモリ要件が増加する場合があります。そのため、クエリはメモリ リソースを他のクエリと競合することになります。クエリが、リソースの待機中にあらかじめ定義されたタイムアウトの値 (クエリのメモリ消費が増加すると大きくなる) を超えると、このエラーが返されます。このエラーが最も起こり得る理由は、サーバー上に存在する膨大な数のクエリのコンパイル処理です。
トラブルシューティング
一般的なトラブルシューティング手順に従い、全般的にサーバー メモリの消費が影響を受けているかどうかを確認します。
ワークロードをチェックし、別のコンポーネントによって消費されるメモリの容量を確認します (この記事の「内部の物理メモリの負荷」を参照してください)。
DBCC MEMORYSTATUS 出力で各ゲートウェイで待機中の数をチェックします (この情報は、膨大なメモリ容量を消費する、実行中の他のクエリがあるかどうかを示します)。
Small Gateway Value ------------------------------ -------------------- Configured Units 8 Available Units 8 Acquires 0 Waiters 0 Threshold Factor 250000 Threshold 250000 (6 row(s) affected) Medium Gateway Value ------------------------------ -------------------- Configured Units 2 Available Units 2 Acquires 0 Waiters 0 Threshold Factor 12 (5 row(s) affected) Big Gateway Value ------------------------------ -------------------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8可能であればワークロードを減らします。
8645 - クエリ実行のためのメモリ リソースを待機中にタイムアウトが発生しました。クエリを再実行してください。
原因
このエラーは、メモリを多く消費するクエリが多数同時にサーバーで実行されていることを示します。並べ替え (ORDER BY) および結合を使用するクエリは実行中に多くのメモリを消費する場合があります。高度な並列処理が有効である場合、または固定されていないインデックスとともにパーティション テーブルでクエリが動作する場合は、クエリのメモリ要件が大幅に増加します。クエリが事前定義されたタイムアウト値 (デフォルトでタイムアウトはクエリの推定コストの 25 倍、または設定する場合は sp_configure の 'query wait' の値) 以内に必要なメモリ リソースを取得できないと、このエラーを受け取ります。通常、エラーを受け取るクエリは、メモリを消費しているクエリではありません。
トラブルシューティング
一般的な手順に従い、サーバーのメモリ状況を評価します。
問題のあるクエリを特定します。パーティション テーブルで動作する多数のクエリがあるかどうか確認し、これらが固定されていないインデックスを使用するか、結合や並べ替えなどのクエリが多数あるかどうかをチェックします。
sp_configure パラメータ degree of parallelism および min memory per query をチェックします。並列処理の程度を減らし、min memory per query が大きな値に設定されていないことを確認します。大きな値に設定されている場合、少ないクエリでも一定の容量のメモリを取得します。
クエリが RESOURCE_SEMAPHORE を待機中であるかどうかを確認するには、この記事の後にある「Blocking 」(英語) を参照してください。
8651 - 最小クエリ メモリが使用できないので、要求された操作を実行できませんでした。 'min memory per query' サーバー構成オプションの設定値を減らしてください。
原因
原因の一部分は 8645 エラーと同様です。サーバーの全般的なメモリ不足の状態を示している場合もあります。このエラーは、min memory per query オプションの設定値が大きすぎると発生する場合もあります。
トラブルシューテ ィング
一般的なメモリ エラーのトラブルシューティング手順に従います。
sp_configure min memory per query オプション設定を確認します。
I/O ボトルネック
SQL Server パフォーマンスは、I/O サブシステムに大きく依存しています。データベースが物理メモリに収まらなければ、SQL Server はバッファ プールとの間でデータベース ページを継続的に送受信します。これにより、大幅な I/O トラフィックが生じます。同様に、ログ レコードは、トランザクションがコミットしたと宣言される前に、ディスクにフラッシュされる必要があります。最後に、SQL Server は、中間結果の保存、並べ替え、行バージョンの保持などのさまざまな目的で tempdb を使用します。そのため、最適な I/O サブシステムは SQL Server のパフォーマンスには不可欠です。
トランザクションがロールバックされる必要がある場合を除いて、ログ ファイルは順次アクセスされ、一方、tempdb を含むデータ ファイルはランダムにアクセスされます。そのため、一般的な規則として、パフォーマンスの向上のためにはデータ ファイルではなくログ ファイルを別個の物理ディスクに配置します。この記事では、I/O デバイスの設定方法ではなく、I/O ボトルネックが存在するかどうかを識別する方法の説明に重点を置きます。I/O ボトルネックを識別したら、I/O サブシステムを再設定する必要がある場合があります。
I/O サブシステムの処理速度が低下している場合は、応答時間の遅れ、タイムアウトによるタスクの中断などのパフォーマンスの問題が発生する場合があります。
次に示すパフォーマンス カウンタを使用して、I/O ボトルネックを識別できます。コレクションの間隔が不定期の場合、これらの AVG 値に誤差がある (低い値に) 傾向があります。たとえば、60 秒のスナップショットで I/O スパイクの性質を示すのは困難です。また、ボトルネックと決定するには 1 つのカウンタに頼るのではなく、複数のカウンタを検索し、発見の正当性をそれぞれチェックします。
PhysicalDisk Object: Avg. Disk Queue Length は、サンプリング期間中に選択した物理ディスクのキューに置かれた物理的な読み取りおよび書き込み要求の平均の数を表します。I/O システムがオーバー ロードされた場合は、より多くの読み取り/書き込み操作が待機中になります。ディスク キューの長さが SQL Server の使用レベルのピーク時に 2 の値を頻繁に超える場合は、I/O ボトルネックが存在する可能性があります。
Avg. Disk Sec/Read は、ディスクからのデータの読み取りの平均時間 (秒単位) です。次のように分けられます。
Less than 10 ms - very good Between 10 - 20 ms - okay Between 20 - 50 ms - slow, needs attention Greater than 50 ms – Serious I/O bottleneckAvg. Disk Sec/Write は、ディスクへのデータの書き込み時間の平均時間 (秒単位) です。前述のガイドラインを参照してください。
Physical Disk: %Disk Time は、読み取りまたは書き込み要求を処理するため選択したディスク ドライブがビジー状態である経過時間の割合です。一般的なガイドラインでは、この値が 50% を超える場合は、I/O ボトルネックであることを表します。
Avg. Disk Reads/Sec は、ディスク上の読み取り操作の比率です。この数値がディスク容量の 85% 未満であることを確認する必要があります。容量の 85% を超えると、ディスクへのアクセス時間が急激に遅くなります。
Avg. Disk Writes/Sec はディスク上の書き込み操作の比率です。この数値がディスク容量の 85% 未満であることを確認します。容量の 85% を超えると、ディスクへのアクセス時間が急激に遅くなります。
カウンタを上回る値を使用している場合は、次の数式を使用して、RAID 設定の値を調整する必要があります。
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
たとえば、2 台の物理ディスクがある RAID-1 システムで、カウンタの値は次のようになります。
Disk Reads/sec 80
Disk Writes/sec 70
Avg. Disk Queue Length 5
このケースでは、(80 + (2 * 70))/2 = 110 I/Os per disk および disk queue length = 5/2 = 2.5 であり、I/O ボトルネックの境界であることを示します。
ラッチの待機を確認することで、I/O ボトルネックを識別することもできます。これらのラッチの待機は、読み取りまたは書き込みのためにページにアクセスし、バッファプールのページが使用できないときに、物理 I/O が待機することを説明しています。バッファ プールでページが見つからないと、非同期 I/O が要求され、I/O のステータスがチェックされます。I/O が完了している場合は、ワーカーは正常に続行します。そうでない場合は、要求の種類に応じて、PAGEIOLATCH_EX または PAGEIOLATCH_SH で待機します。I/O ラッチ待機の統計を検出するには、次の DMV クエリを使用します。
Select wait_type,
waiting_tasks_count,
wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type
wait_type waiting_tasks_count wait_time_ms signal_wait_time_ms
-----------------------------------------------------------------------
PAGEIOLATCH_DT 0 0 0
PAGEIOLATCH_EX 1230 791 11
PAGEIOLATCH_KP 0 0 0
PAGEIOLATCH_NL 0 0 0
PAGEIOLATCH_SH 13756 7241 180
PAGEIOLATCH_UP 80 66 0
ここで目的のラッチ待機は、下線が引かれている部分です。I/O が完了すると、ワーカーは実行可能なキューに置かれます。ワーカーが実際スケジュールされるときまでの I/O 完了の間隔は、 signal_wait_time_ms 列の下に示されています。waiting_task_counts および wait_time_ms が正常の値より大幅に逸脱しているかどうかによって、I/O の問題を識別できます。この場合、パフォーマンス カウンタの基準と SQL Server が円滑に実行しているときのキー DMV クエリの出力を取得することが重要です。これらの wait_types は、I/O サブシステムでボトルネックが発生しているかどうかを示すことができますが、問題が発生している物理ディスクで目に見える形では示せません。
次に示す DMV クエリを使用して、現在保留中の I/O 要求を検索できます。このクエリを定期的に実行して、I/O の正常な状態をチェックし、I/O ボトルネックが含まれる物理ディスクを分離することができます。
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
サンプル出力は次のようになります。対象のデータベースでこのとき 3 つの保留中 I/O があることを表しています。database_id および file_id を使用して、ファイルがマップされた物理ディスクを検索できます。io_pending_ms_ticks は、保留中のキューで個々の I/O が待機している時間の合計を表します。
Database_id File_Id io_stall io_pending_ms_ticks scheduler_address
----------------------------------------------------------------------
6 1 10804 78 0x0227A040
6 1 10804 78 0x0227A040
6 2 101451 31 0x02720040
解決策
I/O ボトルネックを識別した場合は、対処するために、次の 1 つまたは複数の操作を行います。
SQL Server のメモリ構成をチェックします。SQL Server が不十分なメモリで構成されている場合は、より多くの I/O オーバーヘッドが発生します。メモリの負荷を識別するには、次のカウンタを確認します。
Buffer Cache hit ratio
Page Life Expectancy
Checkpoint pages/sec
Lazywrites/sec
メモリの負荷の詳細については、「Memory Bottlenecks」(英語) を参照してください。
I/O 帯域幅を増やします。
現在のディスク アレイの物理ドライブを増やします。または、現在のディスクを高速なドライブと交換します。これにより、読み取りおよび書き込みのアクセス時間が高速になりますが、I/O コントローラがサポートできるより多くのドライブをアレイに追加しないでください。
より高速なまたは追加の I/O コントローラを追加します。現在のコントローラに (可能な場合) より多くのキャッシュを追加することを検討します。
実行プランを検証し、どのプランがより多くの I/O 消費を引き起こしているかどうかを確認します。優れたプラン (インデックスなど) は I/O を最小限にすることができます。インデックスが欠落している場合は、データベース エンジン チューニング アドバイザを実行して、欠落したインデックスを検索できます。
次に示す DMV クエリを使用して、I/O が最も生じるバッチ/要求を検索できます。物理的な書き込みをアカウンティングしていないことがわかります。データベースの動作状況を検討する場合は問題ありません。要求内の DML/DDL ステートメントはデータ ページをディスクに直接書き込みません。その代わり、ディスクへのページの物理的な書き込みは、ステートメントによってトランザクションをコミットすることでのみトリガされます。チェックポイントまたは SQL Server レイジー ライタのどちらかによって、物理的な書き込みがまれに実行されます。次のような DMV クエリを使用して、I/O が最も生成される上位 5 つの要求を検索できます。論理的な読み取りをより少なく実行するように、これらのクエリをチューニングすることで、バッファ プールへの負荷を解放できます。これによって、(物理 I/O を実行する代わりに) 他の要求を繰り返し実行することでバッファ プールの必要なデータを検出できます。したがって、システム全体のパフォーマンスが向上します。
select top 5 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Descもちろん、データの異なるビューを取得するようにこのクエリを変更できます。たとえば、最も I/O を生成する上位 5 つの要求を単一の要求で生成する場合は、次のように要求します。
(total_logical_reads + total_logical_writes)/execution_count
または、物理 I/O などで要求することもできますが、論理的な読み取り/書き込みの数は、クエリによって選択されたプランが最適であるかどうかを決定するのに役立ちます。たとえば、インデックスを使用する代わりにテーブル スキャンを行うとします。ネストされたループ結合を使用するクエリなど、クエリの中には多くの論理カウンタを持つものがあります。しかし、これらは同じページに再度アクセスするので、キャッシュに適しています。
例 : 2 つのSQL クエリで構成される次の 2 つのバッチを見てみましょう。ここで各テーブルは 1000 行あり、rowsize > 8000 (1 ページに 1 行) です。
バッチ - 1
select c1, c5 from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 order by c2 Batch-2 select * from t1この例の場合、DMV クエリを実行する前に、次のコマンドを実行して、バッファ プールとプロシージャ キャッシュを削除します。
checkpoint dbcc freeproccache dbcc dropcleanbuffersDMV クエリの出力は次のとおりです。2 つのバッチを表している 2 行に注目します。
Avg_logical_reads Avg_logical_writes Avg_phys_reads Execution_count stmt_start_offset ----------------------------------------------------------------------- --------------- 2794 1 385 1 0 1005 0 0 1 146 sql_handle plan_handle ----------------------------------------------------------------------- ----- 0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB x0600050099EC8520A8619803000000000000000000000000 0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB x0600050099EC8520A86198030000000000000000000000002 番目のバッチでは、論理読み取りのみが発生し、物理 I/O がありません。これは最初のクエリ (十分なメモリがあると想定) によって必要なデータがすでにキャッシュされたためです。
次のクエリを実行して、クエリのテキストを取得できます。
select text from sys.dm_exec_sql_text( 0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB) Here is the output. select c1, c5 from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 order by c2次のクエリを実行して個々のステートメントの文字列を検索することもできます。
select substring(text, (<statement_start_offset>/2), (<statement_end_offset> -<statement_start_offset>)/2) from sys.dm_exec_sql_text (0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB)SQL Server がこの種類のデータを Unicode で保存することを補正するため、
statement_start_offestおよびstatement_end_offsetの値は 2 で除算する必要があります。statement_end_offset-1 という値は、ステートメントがバッチの最後まで進むことを示していますが、substring() 関数で -1 は有効な値として受け入れられません。(<statement_end_offset> -<statement_start_offset>)/2として -1 を使用する代わりに、値 64000 を入力して、ステートメントがすべてのケースで扱われていることを確認する必要があります。この方法で、長期実行しているまたはリソースを消費しているステートメントは、大きな容量が保存されているプロシージャまたはバッチからフィルタできます。同様に、次のクエリを実行して、クエリ プランを検索して、多数の I/O が不適切なプランの選択の結果であるかどうかを識別できます。
select * from sys.dm_exec_query_plan (0x0600050099EC8520A8619803000000000000000000000000)
Tempdb
Tempdb は、内部オブジェクトとユーザーオブジェクト、および SQL Server 動作中に作成される一時テーブル、オブジェクト、ストアド プロシージャをグローバルに保存します。
各 SQL Server インスタンスに 1 つの tempdb があり、パフォーマンスおよびディスク容量のボトルネックになる可能性があります。また、使用可能な空き容量および DDL/DML 操作の超過のため、tempdb がオーバーロードになる可能性があります。これにより、サーバー上で実行している無関係なアプリケーションの処理速度が低下したり、実行が失敗したりする可能性があります。
tempdb に関する一般的な問題の一部を次に示します。
tempdb の記憶域スペースの不足
tempdb の I/O ボトルネックが原因のクエリの実行速度の低下。これについては、「I/O ボトルネック」で説明しています。
過剰な DDL 操作によるシステム テーブルのボトルネック
割り当ての競合
tempdb に関する問題を診断する前に、まず、tempdb の領域がどのように使用されるかについて見ていきます。次のような 4 つの主要なカテゴリにグループ化できます。
ユーザー オブジェクト |
ユーザー セッションによって明示的に作成され、システム カタログで追跡されます。このオブジェクトには、次のようなものがあります。
|
内部オブジェクト |
クエリを処理するために SQL Server によって作成および破壊されるステートメント スコープ化オブジェクトです。内部オブジェクトは、システム カタログで追跡されません。このオブジェクトには、次のようなものがあります。
作業テーブルが破棄されると、最適化手法として IAM ページとエクステントが 1 つずつ保存され、新しい作業テーブルで使用できるようになります。 一時 LOB ストレージとカーソル作業テーブルの 2 つは例外で、前者はスコープ化バッチ、後者はスコープ化セッションです。 |
バージョン ストア |
行バージョンを格納するために使用されます。行のバージョン管理は、MARS、オンライン インデックス、トリガ、およびスナップショット ベースの分離レベルで使用されています。これは SQL Server 2005 の新機能です。 |
空き領域 |
tempdb で使用可能なディスク領域を表します。 |
tempdb によって使用される合計領域は、ユーザー オブジェクト、内部オブジェクト、バージョン ストア、および空き領域を足し合わせたものになります。
この空き領域は、tempdb におけるパフォーマンス カウンタの空き領域と同じです。
tempdb 領域の監視
問題が発生してから解決に取り組むより、問題を未然に防ぐ方が懸命です。次のパフォーマンス カウンタを使用すると、tempdb によって使用されている領域の容量を監視できます。
- tempdb の空き領域 (KB)。このカウンタでは、tempdb の空き領域が KB 単位で追跡されます。管理者は、このカウンタを使用して、tempdb の空き領域が足りなくなってきているかどうかを判別できます。
しかし、それよりさらに興味深く建設的な質問は、前述のさまざまなカテゴリによって tempdb のディスク領域がどのように使用されているかということです。
次のクエリを実行すると、ユーザー オブジェクトと内部オブジェクトが使用している tempdb 領域の容量が返されます。現時点では、tempdb に関する情報しか返されません。
Select
SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2
次に出力の一例を示します (領域の単位は KB)。
user_objets_kb internal_objects_kb version_store_kb freespace_kb
---------------- -------------------- ------------------ ------------
8736 128 64 448
これらの計算値は混合エクステントのページ数に加算されません。混合エクステントのページ数は、ユーザー オブジェクトと内部オブジェクトに割り当てることができます。
領域に関する問題のトラブルシューティング
ユーザー オブジェクト、内部オブジェクト、およびバージョン ストレージはどれも、tempdb の領域に関する問題を引き起こすことがあります。このセクションでは、次の各カテゴリをトラブルシューティングする方法について説明します。
ユーザー オブジェクト
ユーザー オブジェクトは特定のセッションによって所有されないので、ユーザー オブジェクトを作成したアプリケーションの仕様を理解し、tempdb の容量要件を適宜調整する必要があります。個々のユーザー オブジェクトによって使用される領域を調べるには、exec sp_spaceused @objname='<user-object>' を実行します。たとえば、次のスクリプトを実行すると、すべての tempdb オブジェクトを列挙できます。
DECLARE userobj_cursor CURSOR FOR
select
sys.schemas.name + '.' + sys.objects.name
from sys.objects, sys.schemas
where object_id > 100 and
type_desc = 'USER_TABLE'and
sys.objects.schema_id = sys.schemas.schema_id
go
open userobj_cursor
go
declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0)
begin
exec sp_spaceused @objname = @name
fetch userobj_cursor into @name
end
close userobj_cursor
バージョン ストア
SQL Server 2005 には、新規の機能と既存の機能を実装するための行バージョン管理用フレームワークが用意されています。現在、この行バージョン管理フレームワークは、次の機能で使用されています。各機能の詳細については、SQL Server Books Online を参照してください。
トリガ
MARS
オンライン インデックス
行バージョン管理ベースの分離レベル : データベース レベルでオプションを設定する必要があります。
行バージョンはセッション間で共有されます。行バージョンの作成者は、行バージョンをいつ再生できるかを制御できません。行バージョンのクリーンアップを妨げている実行時間が最長のトランザクションを見つけて、場合によっては強制終了することが必要となります。
次のクエリを実行すると、バージョン ストアのバージョンに依存する、実行時間が 1 番長いトランザクションと 2 番目に長いトランザクションが返されます。
select top 2
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC
次に出力の一例を示します。この例では、XSN 3 のトランザクション (トランザクション ID 8609) が 6523 秒アクティブであることが示されています。
transaction_id transaction_sequence_num elapsed_time_seconds
-------------------- ------------------------ --------------------
8609 3 6523
20156 25 783
2 番目のトランザクションがアクティブになっている期間は比較的短いので、最初のトランザクションを強制終了することによって、多大な量のバージョン ストアを解放できる可能性があります。しかし、このトランザクションを強制終了することによって解放できるバージョン領域のサイズを予測することはできません。多大な領域を解放するには、強制終了するトランザクションをいくつか追加することが必要となる場合があります。
この問題を軽減するには、バージョン ストアに合わせて tempdb のサイズを正しく設定するか、可能な場合は、スナップショット分離に基づく長時間実行のトランザクションまたは READ COMMITTED スナップショットに基づく長時間実行のトランザクションを排除します。次の計算式を使用すると、必要なバージョン ストアの推定容量を概算できます (係数 2 は、実行時間が最長である 2 つのトランザクションが重複するという最悪のシナリオを想定して必要となります)。
[Size of version store] = 2 * [version store data generated per minute] *
[longest running time (minutes) of the transaction]
行バージョン管理ベースの分離レベルが有効になっているデータベースの場合、トランザクションごとに毎分生成されるバージョン ストア データは、毎分生成されるログとほぼ同じになります。ただし、これには例外がいくつかあります。まず、更新用のログに記録されるのは相違点だけです。また、新しく挿入されたデータ行はバージョン管理されませんが、それが一括ログ処理であるかどうかによって記録される場合があり、復旧モードは完全復旧に設定されていません。
パフォーマンス カウンタであるバージョン生成レートとバージョン クリーンアップ レートを使用すると、計算処理を微調整することもできます。バージョン クリーンアップ レートが 0 の場合は、長時間実行トランザクションによってバージョン ストアのクリーンアップが妨げられていることを意味します。
SQL Server 2005 では、tempdb 領域エラーの出力を生成する前に、バージョン ストアを強制的に圧縮することによって、最後の解決策が試みられます。この圧縮処理では、行バージョンをまだ生成していない長時間実行トランザクションが対象としてマークされ、これらのトランザクションによって使用されるバージョン領域が解放されます。このような対象トランザクションが見つかるたびに、メッセージ 3967 がエラー ログに生成されます。対象としてマークされたトランザクションは、バージョン ストアの行バージョンを読み取ったり、新規の行バージョンを作成したりできなくなります。対象トランザクションが行バージョンの読み取りを試みると、メッセージ 3966 が生成され、トランザクションはロールバックされます。バージョン ストアの圧縮が正しく行われると、tempdb の使用可能な領域が増えます。圧縮が正しく行われなかった場合、tempdb は領域不足になります。
内部オブジェクト
前出の表で説明した例外を除き、内部オブジェクトはステートメントごとに作成および破壊されます。膨大な容量の tempdb 領域が割り当てられていることがわかった場合は、その領域がどのセッションまたはタスクによって消費されているかを調べ、場合によっては修正動作を試みる必要があります。
SQL Server 2005 には、セッションとタスクにそれぞれ割り当てられた tempdb 領域を追跡するために、sys.dm_db_session_space_usage と sys.dm_db_task_space_usage という追加の DMV が 2 つ用意されています。タスクはセッションのコンテキストで実行されますが、タスクによって使用される領域は、タスクが完了した後でしかセッションに加算されません。
次のクエリを使用すると、内部オブジェクトを割り当てている上位セッションを見つけることができます。このクエリには、セッションで完了したタスクしか含められません。
select
session_id,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count DESC
次のクエリを使用すると、現在アクティブになっているタスクも含め、内部オブジェクトを割り当てている上位ユーザー セッションを見つけることができます。
SELECT
t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
deallocated
from sys.dm_db_session_space_usage as t1,
(select session_id,
sum(internal_objects_alloc_page_count)
as task_alloc,
sum (internal_objects_dealloc_page_count) as
task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
order by allocated DESC
次に出力の一例を示します。
session_id allocated deallocated
---------- -------------------- --------------------
52 5120 5136
51 16 0
内部オブジェクトの割り当てを多数生成しているタスクを分離したら、それに対応する Transact-SQL ステートメントとクエリ プランを特定し、さらに詳しく分析できます。
select
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t2.plan_handle
from (Select session_id,
request_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1,
sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and
(t1.request_id = t2.request_id)
order by t1.task_alloc DESC
次に出力の一例を示します。
session_id request_id task_alloc task_dealloc
---------------------------------------------------------
52 0 1024 1024
sql_handle statement_start_offset
-----------------------------------------------------------------------
0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356
statement_end_offset plan_handle
---------------------------------
-1 0x06000500D490961BA8C19503000000000000000000000000
sql_handle と plan_handle を次のように使用すると、SQL ステートメントとクエリ プランを取得できます。
select text from sys.dm_exec_sql_text(@sql_handle)
select * from sys.dm_exec_query_plan(@plan_handle)
クエリ プランは、アクセスする必要が生じたときにキャッシュに必ず含まれているとは限りません。クエリ プランの可用性を保証するには、プラン キャッシュを頻繁にポーリングし、可能な場合はテーブルに結果を保存して、後で照会できるようにしておく必要があります。
SQL Server が再起動されると、tempdb のサイズは初期設定値に戻り、要件に基づいて大きくなります。これによって、tempdb が断片化するだけでなく、データベースの自動拡張時に新規エクステントの割り当てがブロックされるなどのオーバーヘッドが発生して、tempdb のサイズが大きくなる可能性もあります。このような状態が発生すると、ワークロードのパフォーマンスに影響が及びかねません。tempdb は適切なサイズに事前に割り当てておくことをお勧めします。
過剰な DDL と割り当て処理
tempdb に競合原因が 2 つあると、次の状況が発生する場合があります。
多数の一時テーブルとテーブル変数を作成および破棄すると、メタデータで競合が発生する可能性があります。SQL Server 2005 の場合、ローカル一時テーブルとテーブル変数は、メタデータの競合を最小化するためにキャッシュされます。ただし、次の条件が満たされていることが必要です。満たされていない場合、テーブルはキャッシュされません。
名前付き制約がテーブルに含まれていない。
テーブル内の作成ステートメント (CREATE INDEX、CREATE STATISTICS など) の後に DDL が含まれていない。
通常、大部分の一時/作業テーブルはヒープです。したがって、挿入、削除、または破棄の各処理を行うと、ページ空き領域 (PFS) ページに深刻な競合が生じる可能性があります。これらのテーブルの大部分が 64 KB より小さく、割り当てや割り当て解除に混合エクステントを使用している場合は、共有グローバル割り当てマップ (SGAM) ページで深刻な競合が発生する可能性があります。SQL Server 2005 では、ローカル一時テーブルが割り当て時の競合を最小化できるように、データ ページと IAM ページが 1 つずつキャッシュされます。このキャッシュ処理は、SQL Server 2000 以降の作業テーブルで既に行われています。
SGAM ページと PFS ページはデータ ファイル内で固定間隔で発生するため、リソース記述を簡単に見つけることができます。たとえば、2:1:1 は tempdb における最初の PFS ページ (データベース ID = 2、ファイル ID = 1、ページ ID = 1) を表し、2:1:3 は最初の SGAM ページを表します。SGAM ページは 511232 ページ間隔で発生し、PFS ページは 8088 ページ間隔で発生します。この情報に基づいて、その他のすべての PFS ページと SGAM ページを tempdb の全ファイルで見つけることができます。これらのページでラッチの取得待ちをしているタスクがあると、そのことが sys.dm_os_waiting_tasks に示されます。ラッチ待機は一時的なため、このテーブルを頻繁に (10 秒に一度程度) 照会して、これらのデータを後で分析できるように収集する必要があります。たとえば、次のクエリを使用すると、tempdb ページで待機しているすべてのタスクを分析データベースの waiting_tasks テーブルに読み込むことができます。
-- get the current timestamp
declare @now datetime
select @now = getdate()
-- insert data into a table for later analysis
insert into analysis..waiting_tasks
select
session_id,
wait_duration_ms,
resource_description,
@now
from sys.dm_os_waiting_tasks
where wait_type like ‘PAGE%LATCH_%’ and
resource_description like ‘2:%’
tempdb ページでラッチの取得待ちをしているタスクが表示されたら、その原因が PFS ページまたは SGAM ページにあるかどうかを調べるために分析できます。これらのページに原因がある場合は、tempdb に割り当て時の競合があることを意味します。tempdb の他のページに競合が認められ、システム テーブルに属しているページがあることを特定できる場合は、過剰な DDL 処理を原因とした競合があることを意味します。
次のパフォーマンス カウンタを監視すると、一時オブジェクトの割り当て/割り当て解除活動に異常な増加があるかどうかを調べることもできます。
SQL Server:Access Methods\Workfiles Created /Sec
SQL Server:Access Methods\Worktables Created /Sec
SQL Server:Access Methods\Mixed Page Allocations /Sec
SQL Server:General Statistics\Temp Tables Created /Sec
SQL Server:General Statistics\Temp Tables for destruction
分解能
tempdb における競合の原因が過剰な DDL 処理にある場合は、アプリケーションを調べて、DDL 処理を最小化できるかどうかを確認する必要があります。次の方法を試してください。
ストアド プロシージャによってスコープ化された一時テーブルを使用している場合は、これらのテーブルのストアド プロシージャ外部への移動を検討します。移動できない場合は、ストアド プロシージャを実行するたびに、一時テーブルが作成/破棄されます。
クエリ プランを表示し、一部のプランによって多数の一時オブジェクト、スプール、並べ替え、または作業テーブルが作成されているかどうかを調べます。一部の一時オブジェクトを排除しなければならない場合があります。たとえば、ORDER BY によって使用される列でインデックスを作成すると、並べ替えが排除されることがあります。
競合の原因が SGAM ページと PFS ページでの競合にある場合は、次の方法で問題を軽減できます。
tempdb の各データ ファイルの数を同じ数だけ増やして、すべてのディスクとファイルにワークロードを分散します。ファイル数を CPU と同じ数にすることをお勧めします (プロセッサの関係を考慮に入れる)。
TF-1118 を使用して、混合エクステントの割り当てを排除します。
実行が遅いクエリ
クエリの実行が遅くなったり、クエリが長時間実行されたりすると、過剰なリソースが消費され、クエリがブロックされる原因になりかねません。
過剰に消費されるのは CPU リソースだけに限らず、I/O ストレージ帯域幅やメモリ帯域幅が含まれる場合もあります。SQL Server のクエリは、適切な WHERE 句を使って結果セットを制限することによって、テーブル全体がスキャンされるのを防ぐように設計されています。しかし、対象となるクエリをサポートする適切なインデックスがない場合、クエリは予期しない動作を示すことがあります。また、WHERE 句は、ユーザーの入力に基づいてアプリケーションによって動的に構築されることもあります。このため、考えられるあらゆる制約を既存のインデックスによって処理できるとは限りません。Transact-SQL ステートメントによる過剰な CPU、I/O、およびメモリの消費については、このホワイト ペーパーの前出の項で説明してあります。
インデックスの不在の問題に加え、存在するが使用されていないインデックスもあります。インデックスはすべて維持する必要があるため、このことがクエリのパフォーマンスに影響を及ぼすことはありませんが、DML クエリに支障をきたします。
クエリをブロックしているシステム リソースと論理ロックが待機状態になるため、クエリの実行が遅くなる可能性もあります。ブロックの原因としては、不適切なアプリケーション設計、不適切なクエリ プラン、有効なインデックスの欠如、ワークロードに適した設計になっていない SQL Server インスタンスなどが挙げられます。
この項では、クエリの実行が遅くなる際の 2 つの原因、すなわちブロックとインデックスの問題について説明します。
ブロック
ブロックとは主に、論理ロックに対する待機 (リソースの X ロックの取得待ちなど) または下位の同期プリミティブ (ラッチなど) に起因する待機を指します。
既にロックされているリソース上の非互換ロックを取得するための要求が発行されると、論理ロックの待機が発生します。このことは、特定の Transact-SQL ステートメントが実行されているトランザクション分離レベルに基づいてデータの一貫性を確保するうえで必要となりますが、エンド ユーザーにとっては SQL Server の実行が遅くなったように感じられます。クエリがブロックされると、システム リソースがまったく消費されなくなるため、処理に時間がかかるもののリソースの消費量は低いことに気付きます。同時実行制御とブロックの詳細については、SQL Server Books Online を参照してください。
システムがワークロードを処理するように設定されていない場合は、下位の同期プリミティブで待機が発生する可能性があります。
ブロック/待機の一般的なシナリオは次のとおりです。
ブロックの原因の特定
長時間続くブロックの特定
オブジェクトごとのブロック
ページ ラッチの問題
SQL Server の待機を使用したブロックが全般的なパフォーマンスに与える影響
要求を処理するためにシステム リソース (またはロック) を使用できない場合、SQL Server のセッションは待機状態になります。すなわち、未処理要求がリソースのキューに入れられます。DMV からは、リソースで待機状態になっているセッションに関する情報を取得できます。
SQL Server 2005 では、詳細で一貫性のある待機情報が提供され、SQL Server 2000 の 76 種類を上回る約 125 種類の待機が報告されます。これらの情報を提供する DMV としては、SQL Server の全般的な待機や累積的な待機を報告する sys.dm_os_wait_statistics や、セッションごとに待機を分類して報告するセッション固有型の sys.dm_os_waiting_tasks があります。次の DMV では、あるリソースで待ち状態になっているタスクの待機キューに関する詳細情報が提供されます。この DMV は、システム内のすべての待機キューを同時に表したものです。たとえば、ブロックされたセッション 56 に関する詳細を取得するには、次のクエリを実行します。
select * from sys.dm_os_waiting_tasks where session_id=56
waiting_task_address session_id exec_context_id wait_duration_ms
wait_type
resource_address blocking_task_address blocking_session_id
blocking_exec_context_id resource_description
-------------------- ---------- --------------- -------------------- -----
------------------------------------------------------- ------------------
--------------------- ------------------- ------------------------ -------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
---------------------------
0x022A8898 56 0 1103500
LCK_M_S 0x03696820
0x022A8D48 53 NULL
ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00 mode=X
associatedObjectId=72057594038321152
この結果からは、セッション 56 がセッション 53 によってブロックされており、セッション 56 がロックを 1103500 ミリ秒待っていることがわかります。
ロックを許可されたセッションまたはロックを待っているセッションを特定するには、sys.dm_tran_locksDMV ** *を使用します。*各行は、ロック マネージャに対する現在アクティブな要求を表します。これらの要求は既に許可されているか、既に許可された別の要求によってブロックされているため許可待ちになっています。通常のロックの場合、要求が許可されたということは、ロックがリソース上で要求者に付与されていることを意味します。待機要求があるということは、要求がまだ許可されていないことを意味します。たとえば、次のクエリを実行すると、セッション 53 によって X モードに設定されているリソース 1:143:3 上でセッション 56 がブロックされていることがわかります。
select
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
from sys.dm_tran_locks
Here is the sample output
spid rt rdb objname rd rm
rs
--------------------------------------------------------------------------
---
56 DATABASE 9
S GRANT
53 DATABASE 9 S GRANT
56 PAGE 9 t_lock 1:143 IS GRANT
53 PAGE 9 t_lock 1:143 IX GRANT
53 PAGE 9 t_lock 1:153 IX GRANT
56 OBJECT 9 t_lock IS GRANT
53 OBJECT 9 t_lock IX GRANT
53 KEY 9 t_lock (a400c34cb X GRANT
53 RID 9 t_lock 1:143:3 X GRANT
56 RID 9 t_lock 1:143:3 S WAIT
ストアド プロシージャ sp_block を使用すると、前述の 2 つの DMV を結合できます。図 1 のブロック レポートには、ブロックされているセッションとそれをブロックしているセッションが示されています。ソース コードは付録 B に記載されています。必要に応じてストアド プロシージャを変更して、選択リストの属性を追加/削除できます。省略可能な @spid パラメータを指定すると、ロック要求とこの特定の SPID をブロックしているセッションに関する詳細を取得できます。
.gif "Cc966540.tsfprb01(ja-jp,TechNet.10).gif")
図 1: sp_block レポート
.gif){kind=link}
SQL Server 2000 では、次のステートメントを使用することによって、どの SPID がブロックされているかを調べることができます。
select * from master..sysprocesses where blocked <> 0.
関連付けられたロックを調べるには、ストアド プロシージャ sp_lock を使用します。
長時間続くブロックの特定
前述のように、ブロックは SQL Server でよく発生します。このことは、トランザクションの一貫性を保持するために論理ロックが必要であることを明示しています。ただし、ロックの待ち時間がしきい値を上回ると、応答が遅くなることがあります。長時間実行されているブロックを特定するには、BlockedProcessThreshold 構成パラメータを使用して、ユーザーによる構成が可能なサーバー規模ブロックしきい値を設定します。このしきい値によって期間が秒単位で定義されます。いずれかのブロックがこのしきい値を超えると、SQL トレースによってキャプチャできるイベントが開始されます。
たとえば、200 秒という blocked process threshold は、SQL Server Management Studio で次のように設定できます。
Sp_configure ‘blocked process threshold’, 200 を実行します。
RECONFIGURE WITH OVERRIDE ステートメントを使用します。
blocked process threshold が設定されたら、次にトレース イベントをキャプチャする必要があります。ユーザー設定のしきい値を上回ったブロック イベントのトレース イベントは、SQL トレースまたは SQL プロファイラでキャプチャできます。
SQL トレースを使用する場合は、sp_trace_setevent と event_id=137 を使用します。
SQL Server プロファイラを使用する場合は、Blocked Process Report イベント クラス (Errors and Warnings オブジェクトの配下) を選択します。図 2 を参照してください。
.gif "Cc966540.tsfprb02(ja-jp,TechNet.10).gif")
図 2: 長時間実行されるブロックとデッドロックのトレース
注 : これはライトウェイト トレースであるため、(1) ブロックがしきい値を超えたとき、または (2) デッドロックが発生したときにのみイベントがキャプチャされます。ロックが 200 秒間隔でブロックされるたびに、トレース イベントが開始されます。つまり、1 つのロックが 600 秒ブロックされると、トレース イベントが 3 つ生成されます。図 3 を参照してください。
.gif "Cc966540.tsfprb03(ja-jp,TechNet.10).gif")
図 3: ブロックの報告 > ブロックのしきい値
.gif){kind=link}
.gif){kind=link}
トレース イベントには、ブロックした側とブロックされた側の両方の SQL ステートメント全体が含められます。この場合、“Update Customers” ステートメントが “Select from Customers” ステートメントをブロックしています。
一方、長時間実行されているブロック シナリオの有無を SQL Server 2000 でチェックするには、ユーザー定義のコードを使って Sysprocesses をポーリングし、結果を事後処理する必要があります。サポート技術情報の記事 271509 には、ブロックの監視に使用できるサンプル スクリプトが収録されてます。
sys.dm_db_index_operational_stats を使用したオブジェクトごとのブロック
SQL Server 2005 の新しい DMV Sys.dm_db_index_operational_stats を使用すると、ブロックをはじめ、インデックスの使用に関する総合的な統計を取得できます。ブロックに関しては、ロック統計に関する詳細な説明がテーブル、インデックス、およびパーティションごとに提供されます。この例としては、特定のインデックスまたはテーブルに関するアクセス履歴、ロック数 (row_lock_count)、ブロック数 (row_lock_wait_count)、待機数 (row_lock_wait_in_ms) などが挙げられます。
この DMV を使用して取得できる情報のタイプは次のとおりです。
設定されたロックの数 (行、ページなど)。
ブロックまたは待機の数 (行、ページなど)。
ブロックまたは待機の期間 (行、ページなど)。
ページ ラッチ待機の数。
page_latch_wait の期間 : ここでは、昇順によるキーの挿入など、特定ページの競合が発生します。このような場合は、ホット スポットが最後のページになるため、同一の最終ページに対する複数のライタによって排他的なページ ラッチの取得が同時に試みられます。このラッチは Pagelatch タイプの待機として示されます。
page_io_latch_wait の期間 : バッファ プールにないページがユーザーによって要求されると、I/O ラッチが発生します。実行が遅い I/O サブシステムや過剰使用の I/O サブシステムでは、実際は I/O 上の問題である、PageIOlatch タイプの待機数が多くなることがあります。これらの問題は、キャッシュのフラッシュやインデックスの不在によってさらに悪化することがあります。
ページ ラッチ待機の期間。
ブロック関連の情報だけでなく、インデックスへのアクセス用に保持される情報もあります。
アクセスのタイプ (範囲、シングルトン ルックアップなど)。
リーフ レベルでの挿入数、更新数、削除数。
リーフ レベルより上の挿入数、更新数、削除数。リーフより上にある活動はインデックス管理です。各リーフ ページの最初の行には、その上のレベルのエントリが含まれています。新しいページがそのリーフに割り当てられた場合、その上のレベルには、新規リーフ ページの最初の行に対応する新しいエントリが挿入されます。
リーフ レベルで結合されたページは、行が削除されたために割り当て解除される空白ページを表します。
インデックスの管理。リーフ レベルより上で結合されたページは、リーフ レベルの行が削除されたために割り当て解除される空白ページとなるため、中間レベルのページは空白のままとなります。各リーフ ページの最初の行には、その上のレベルのエントリが含まれています。リーフ レベルで十分な数の行が削除された場合、リーフ ページの最初の行のエントリを当初含んでいた中間レベルのインデックス ページは空白になり、結合がリーフの上で発生します。
これらの情報は、インスタンスの開始時から累積され、インスタンスの再開後は保持されません。また、情報をリセットすることはできません。この DMV によって返されるデータは、ヒープまたはインデックスを表すメタデータ キャッシュ オブジェクトを使用できる限り存続します。ヒープまたはインデックスを表すメタデータがメタデータ キャッシュに入れられるたびに、各列の値はゼロに設定されます。キャッシュ オブジェクトがメタデータ キャッシュから削除されない限り、統計は累積されます。ただし、このテーブルを定期的にポーリングして、これらの情報をテーブルに収集し、後で照会することもできます。
付録 B には、インデックスのオペレーション データを収集するために使用できる一連のストアド プロシージャが定義されています。次に、必要な期間のデータを分析できます。付録 B のストアド プロシージャを使用する手順は、次のとおりです。
init_index_operational_stats を使用して、indexstats テーブルを初期化します。
insert_indexstats を使用してベースラインをキャプチャします。
ワークロードを実行します。
insert_indexstats を使用して、インデックス統計の最終的なスナップショットをキャプチャします。
収集したインデックス統計を分析するには、ストアド プロシージャ get_indexstats を実行して、インデックスあたりのロック (インデックスとパーティションの場合は Row_lock_count)、ブロック、および待機の平均数を生成します。ブロック率や平均待機数が高い場合は、インデックスまたはクエリの構成が不適切である可能性があります。
前述のストアド プロシージャを使用すると、次の例のような情報を取得できます。
すべてのデータベースの上位 5 のインデックスを取得し、インデックスの用途の説明ごとに並べ替えます。
exec get_indexstats @dbid=-1, @top='top 5', @columns='index, usage', @order='index usage'ロック プロモーションが試行された、上位 5 (すべての列) のインデックス ロック プロモーションを取得します。
exec get_indexstats @dbid=-1, @top='top 5', @order='index lock promotions', @threshold='[index lock promotion attempts] > 0'avg row lock waits>2ms を使用して上位 5 のシングルトン ルックアップを取得し、wait、scan、singleton が含まれている列を返します。
exec get_indexstats @dbid=5, @top='top 5', @columns='wait,scan,singleton', @order='singleton lookups', @threshold='[avg row lock wait ms] > 2'すべてのデータベースの上位 10 のインデックスと "avg, wait" が含まれている列を取得し、wait ms で並べ替えます。ここで、row lock waits は 1 未満です。
exec get_indexstats @dbid=-1, @top='top 10 ', @columns='wait,row', @order='row lock wait ms', @threshold='[row lock waits] > 1'上位 5 のインデックス統計を取得し、avg row lock waits desc で並べ替えます。
exec get_indexstats @dbid=-1, @top='top 5', @order='avg row lock wait ms'上位 5 のインデックス統計を取得し、avg page latch lock waits desc で並べ替えます。
exec get_indexstats @dbid=-1, @top='top 5', @order='avg page latch wait ms'上位 5% のインデックス統計を取得し、avg pageio latch waits で並べ替えます。
exec get_indexstats @dbid=-1, @top='top 3 percent', @order='avg pageio latch wait ms', @threshold='[pageio latch waits] > 0'db=5 で上位 10 のすべてのインデックス統計を取得し、block% で並べ替えます。ここで、block% は 0.1 より大きいです。
exec get_indexstats @dbid=-1, @top='top 10', @order='block %', @threshold='[block %] > 0.1'
図 4 のサンプル ブロック分析レポートを参照してください。
.gif "Cc966540.tsfprb04(ja-jp,TechNet.10).gif")
図 4: ブロック分析レポート
.gif){kind=link}
SQL Server 2000 では、オブジェクトまたはインデックスの使用率に関する統計は生成されません。
SQL の待機を使用したブロックが全般的なパフォーマンスに与える影響
SQL Server 2000 では、76 種類の待機が報告されます。SQL Server 2005 では、アプリケーションのパフォーマンスを追跡するための待機が 100 種類以上追加されています。ユーザーによる接続が待機状態になると、SQL Server によって待機時間が累計されます。たとえば、I/O、ロック、メモリなどのリソースを要求したアプリケーションは、リソースが使用可能になるまで待機する場合があります。この待機情報は要約され、特定のワークロードに関するパフォーマンス プロファイルを取得できるように、すべての接続に基づいて分類されます。つまり、SQL の待機の種類を使用すると、アプリケーション ワークロードまたはユーザーの観点から、ユーザー (またはスレッド) の待機を特定したり、分類したりできるようになります。
次のクエリを実行すると、SQL Server における上位 10 の待機が列挙されます。これらの待機統計情報は累積されますが、DBCC SQLPERF ([sys.dm_os_wait_stats], clear) を使用してリセットすることもできます。
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
次に出力を示します。ここでは、次の点に注意する必要があります。
遅延書き込みを実行するバックグラウンド スレッドで発生する待機など、一部の待機は正常です。
一部のセッションは、SH ロックを取得するために長時間待機状態になりました。
"シグナル待機時間" とは、ワーカーがリソースへのアクセスを許可されてから、CPU でスケジュール設定されるまでの時間を指します。シグナル待機時間が長い場合は、CPU の競合率が高い可能性があります。
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms ------------------ -------------------- -------------------- ------------- ------- ------- LAZYWRITER_SLEEP 415088 415048437 1812 156 SQLTRACE_BUFFER_FLUSH 103762 415044000 4000 0 LCK_M_S 6 25016812 23240921 0 WRITELOG 7413 86843 187 406 LOGMGR_RESERVE_APPEND 82 82000 1000 0 SLEEP_BPOOL_FLUSH 4948 28687 31 15 LCK_M_X 1 20000 20000 0 PAGEIOLATCH_SH 871 11718 140 15 PAGEIOLATCH_UP 755 9484 187 0 IO_COMPLETION 636 7031 203 0
待機状態を分析するには、これらのデータを定期的にポーリングしておいて、後で分析する必要があります。付録 B には、次の 2 つのサンプル ストアド プロシージャが記載されています。
Track_waitstats。指定した数のサンプルとサンプル間の間隔に関するデータを収集します。このサンプルを呼び出すと、次のようになります。
exec dbo.track_waitstats @num_samples=6 ,@delay_interval=30 ,@delay_type='s' ,@truncate_history='y' ,@clear_waitstats='y'Get_waitstats。前の手順で収集されたデータを分析します。このサンプルを呼び出すと、次のようになります。
exec [dbo].[get_waitstats_2005]SPID が実行されています。次に、現在使用不可になっているリソースが必要となります。リソースを使用できないので、SPID は時間 T0 でリソース待機リストに移動します。
リソースが使用可能であることがシグナルで示され、SPID は時間 T1 で実行可能キューに移動します。
CPU が実行可能キューを到着順に処理する間、SPID は T2 まで実行状態のまま待機します。
これらのストアド プロシージャを使用すると、リソースとシグナルの各待機統計を分析し、分析結果に基づいてリソースの競合を特定することができます。
図 5 にサンプル レポートを示します。
.gif "Cc966540.tsfprb05(ja-jp,TechNet.10).gif")
図 5: 待機統計の分析レポート
.gif){kind=link}
図 5 に示すサンプルの待機統計分析レポートからは、ブロック (LCK_M_S) とメモリ割り当て (RESOURCE_SEMAPHORE) に起因するパフォーマンス上の問題があることがわかります。具体的には、全待機のうち 55% が共有ロックに関するものであり、43% がメモリ要求を原因としています。オブジェクトごとにブロックを分析すると、競合の主な箇所を特定できます。
インデックスの使用の監視
クエリのパフォーマンスには、DML クエリ、クエリの削除、データの挿入と変更も影響を及ぼします。特定のテーブルで定義されているインデックスの数が多いほど、データの変更に必要なリソースも多くなります。トランザクションにロックが設定され、変更処理が長くなると、同時実行制御に支障をきたす可能性があります。したがって、特定の期間にどのインデックスがアプリケーションによって使用されるかを把握しておくことが重要となります。そうすることにより、決して使用されることのないインデックスという負荷がデータベース スキーマで多く発生しているかどうかを特定できます。
SQL Server 2005 には、sys.dm_db_index_usage_stats という新しい動的管理ビューが用意されており、どのインデックスが使用されているかだけでなく、インデックスがユーザー クエリによって使用されているのか、システム処理でのみ使用されているのかも示されます。クエリが実行されるたびに、そのクエリの実行に使用されたクエリ プランに基づいて、このビューの列数が増加します。SQL Server が実行されている間、データの収集は続けられます。この DMV のデータはメモリにのみ保存され、永続しません。そのため、SQL Server のインスタンスがシャットダウンされると、データも失われます。このテーブルを定期的にポーリングして、後で分析できるようにデータを保存しておくことはできます。
インデックスの処理は、ユーザー タイプとシステム タイプに分類されます。ユーザー タイプとは、SELECT と INSERT/DELETE/UPDATE による処理を指します。システム タイプの処理は、DBCC ステートメント、DDL コマンド、統計更新などのコマンドです。各カテゴリのステートメントには、それぞれ次の列が使用されます。
インデックスに対するシーク処理 (user_seeks または system_seeks)
インデックスに対するルックアップ処理 (user_lookups または system_lookups)
インデックスに対するスキャン処理 (user_scans または system_scans)
インデックスに対する更新処理 (user_updates または system_updates)
インデックスに対するこれらのアクセスが行われるたびに、最終アクセス日時を示すタイムスタンプも付けられます。
インデックス自体は、database_id、object_id、および index_id を示す 3 つの列によって識別されます。ここで、index_id=0 はヒープ テーブル、index_id=1 はクラスタ化インデックス、そして index_id>1 は非クラスタ化インデックスを表します。
データベースに対するアプリケーションの実行時間が経過するにつれ、sys.dm_db_index_usage_stats でアクセスされるインデックスのリストは長くなります。
SQL Server 2005 におけるシーク、スキャン、およびルックアップのルールと定義は、次のようになっています。
SEEK: B ツリー構造がデータにアクセスするために使用された回数を示します。ここでは、1 データ行を取得する目的でインデックスの各レベルの数ページだけを読み取るために B ツリー構造が使用されたか、基本テーブルの何ギガバイトものデータまたは数百万行を読み取るためにインデックス ページの半分が読み取られたかどうかは問題となりません。そのため、このカテゴリには、各インデックスに対する大部分の処理が累計されます。
SCAN: インデックスの B ツリーを使用せずにテーブルのデータ レイヤが取得に使用された回数を示します。この処理は、インデックスが定義されていないテーブルの場合に発生します。インデックスが定義されているテーブルの場合は、テーブルで定義されたインデックスをステートメントに対して実行されるクエリに使用できないときに発生します。
LOOKUP: テーブルで定義されているクラスタ化インデックスが、同じテーブルの非クラスタ化インデックスによって "seeking" に指定されなかったデータを検索するために使用されたことを示します。これは、SQL Server 2000 で "ブックマーク ルックアップ" と呼ばれたシナリオです。ブックマーク ルックアップとは、非クラスタ化インデックスがテーブルのアクセスに使用され、この非クラスタ化インデックスがクエリの選択リストの列と where 句で定義された列を処理しないシナリオを表します。また、使用された非クラスタ化インデックスに基づいて列
user_seeksの値が増加され、クラスタ化インデックスのエントリに基づいて列user_lookupsの値が増加されます。テーブルに複数の非クラスタ化インデックスが定義されている場合、このカウント値は非常に高くなることがあります。テーブルのクラスタ化インデックスに対するuser_seeksの数が多い場合は、user_lookupsの数だけでなく、特定の非クラスタ化インデックスのuser_seeksの数も多くなります。このような場合は、カウント値が大きい非クラスタ化インデックスをクラスタ化インデックスにすると効果的です。
次の DMV クエリを使用すると、すべてのデータベースのオブジェクトについて、インデックスの使用に関する有益な情報を取得できます。
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
あるテーブルに関するクエリの結果を次に示します。
object_id index_id user_seeks user_scans user_lookups
------------ ------------- -------------- -------------- -----------
------
521690298 1 0 251
123
521690298 2 123 0
0
この場合は、いずれのインデックスも使用せずに、テーブルのデータ レイヤに直接アクセスするクエリが 251 回実行されています。最初の非クラスタ化インデックスを使用してテーブルにアクセスするクエリは、123 回実行されています。クラスタ化インデックスに対するルックアップは 123 個あるので、これには、クエリの選択リストと WHERE 句で指定された列のいずれも含まれません。
最も注目に値するのは、"ユーザー タイプ ステートメント" のカテゴリです。"システム カテゴリ" にインデックスの使用に関するデータが示される場合は、インデックスが存在した結果と見なすことができます。インデックスが存在しなかった場合、このカテゴリの統計は更新される必要がなく、一貫性をチェックする必要もありません。したがって、分析にあたっては、アドホック ステートメントまたはユーザー アプリケーションによる使用を示す 4 つの列に注目する必要があります。
SQL Server を前回起動してからまだ使用されていない特定のテーブルのインデックスに関する情報を取得するには、オブジェクトを所有するデータベースのコンテキストで次のクエリを実行します。
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
次のステートメントを実行すると、まだ使用されていないすべてのインデックスを取得できます。
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc
この場合、テーブル名とインデックス名はテーブル名に基づいて並べ替えられます。
この動的管理ビューの真の目的は、インデックスの使用を長期間観察することです。このビューのスナップショットまたはクエリ結果のスナップショットを取得して、毎日保存し、時間の経過に伴う変化を比較すると効果的な場合があります。何か月間も使用されなかったインデックスや、四半期単位または年単位の報告期間中に一度も使用されなかったインデックスを特定できる場合は、それらのインデックスをデータベースから最終的に削除できます。
まとめ
詳細情報 :
https://www.microsoft.com/technet/prodtechnol/sql/default.mspx (英語)
付録 A: DBCC MEMORYSTATUS の説明
DBCC MEMORYSTATUS コマンドを主な使用法として取得できる情報もありますが、これらの情報の一部は動的管理ビュー (DMV) を使用して取得することもできます。
SQL Server 2000 の DBCC MEMORYSTATUS コマンドについては、次のページで説明しています。
https://support.microsoft.com/?id=271624
SQL Server 2005 の DBCC MEMORYSTATUS コマンドについては、次のページで説明しています。
https://support.microsoft.com/?id=907877
付録 B: ブロック スクリプト
付録 B には、このホワイト ペーパーで使用したストアド プロシージャのソース リストが記載されています。これらのストアド プロシージャはそのまま使用することも、ニーズに合わせてカスタマイズすることもできます。
sp_block
create proc dbo.sp_block (@spid bigint=NULL)
as
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- https://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- This proc reports blocks
-- 1. optional parameter @spid
--
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)
オペレーション インデックス統計の分析
次の一連のストアド プロシージャを使用すると、インデックスの使用を分析できます。
get_indexstats
create proc dbo.get_indexstats
(@dbid smallint=-1
,@top varchar(100)=NULL
,@columns varchar(500)=NULL
,@order varchar(100)='lock waits'
,@threshold varchar(500)=NULL)
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- https://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- This proc analyzes index statistics including accesses, overhead,
-- locks, blocks, and waits
--
-- Instructions: Order of execution is as follows:
-- (1) truncate indexstats with init_indexstats
-- (2) take initial index snapshot using insert_indexstats
-- (3) Run workload
-- (4) take final index snapshot using insert_indexstats
-- (5) analyze with get_indexstats
-- @dbid limits analysis to a database
-- @top allows you to specify TOP n
-- @columns is used to specify what columns from
-- sys.dm_db_index_operational_stats will be included in
-- the report
-- For example, @columns='scans,lookups,waits' will include
-- columns
-- containing these keywords
-- @order used to order results
-- @threshold used to add a threshold,
-- example: @threshold='[block %] > 5' only include if
-- blocking is over 5%
--
------ definition of some computed columns returned
-- [blk %] = percentage of locks that cause blocks e.g. blk% = 100 * lock
-- waits / locks
-- [index usage] = range_scan_count + singleton_lookup_count +
-- leaf_insert_count
-- [nonleaf index overhead]=nonleaf_insert_count + nonleaf_delete_count +
-- nonleaf_update_count
-- [avg row lock wait ms]=row_lock_wait_in_ms/row_lock_wait_count
-- [avg page lock wait ms]=page_lock_wait_in_ms/page_lock_wait_count
-- [avg page latch wait ms]=page_latch_wait_in_ms/page_latch_wait_count
-- [avg pageio latch wait
-- ms]=page_io_latch_wait_in_ms/page_io_latch_wait_count
--------------------------------------------------------------------------
-------------------------
--- Case 1 - only one snapshot of sys.dm_db_operational_index_stats was
-- stored in
--- indexstats. This is an error - return errormsg to user
--- Case 2 - beginning snapshot taken, however some objects were not
-- referenced
--- at the time of the beginning snapshot. Thus, they will
-- not be in the initial
--- snapshot of sys.dm_db_operational_index_stats, use 0 for
-- starting values.
--- Print INFO msg for informational purposes.
--- Case 3 - beginning and ending snapshots, beginning values for all
-- objects and indexes
--- this should be the normal case, especially if SQL Server
-- is up a long time
--------------------------------------------------------------------------
-------------------------
set nocount on
declare @orderby varchar(100), @where_dbid_is varchar(100), @temp
varchar(500), @threshold_temptab varchar(500)
declare @cmd varchar(max),@col_stmt varchar(500),@addcol varchar(500)
declare @begintime datetime, @endtime datetime, @duration datetime,
@mincount int, @maxcount int
select @begintime = min(now), @endtime = max(now) from indexstats
if @begintime = @endtime
begin
print 'Error: indexstats contains only 1 snapshot of
sys.dm_db_index_operational_stats'
print 'Order of execution is as follows: '
print ' (1) truncate indexstats with init_indexstats'
print ' (2) take initial index snapshot using insert_indexstats'
print ' (3) Run workload'
print ' (4) take final index snapshot using insert_indexstats'
print ' (5) analyze with get_indexstats'
return -99
end
select @mincount = count(*) from indexstats where now = @begintime
select @maxcount = count(*) from indexstats where now = @endtime
if @mincount < @maxcount
begin
print 'InfoMsg1: sys.dm_db_index_operational_stats only contains
entries for objects referenced since last SQL re-cycle'
print 'InfoMsg2: Any newly referenced objects and indexes captured
in the ending snapshot will use 0 as a beginning value'
end
select @top = case
when @top is NULL then ''
else lower(@top)
end,
@where_dbid_is = case (@dbid)
when -1 then ''
else ' and i1.database_id = ' + cast(@dbid as varchar(10))
end,
--- thresholding requires a temp table
@threshold_temptab = case
when @threshold is NULL then ''
else ' select * from #t where ' + @threshold
end
--- thresholding requires temp table, add 'into #t' to select statement
select @temp = case (@threshold_temptab)
when '' then ''
else ' into #t '
end
select @orderby=case(@order)
when 'leaf inserts' then 'order by [' + @order + ']'
when 'leaf deletes' then 'order by [' + @order + ']'
when 'leaf updates' then 'order by [' + @order + ']'
when 'nonleaf inserts' then 'order by [' + @order + ']'
when 'nonleaf deletes' then 'order by [' + @order + ']'
when 'nonleaf updates' then 'order by [' + @order + ']'
when 'nonleaf index overhead' then 'order by [' + @order + ']'
when 'leaf allocations' then 'order by [' + @order + ']'
when 'nonleaf allocations' then 'order by [' + @order + ']'
when 'allocations' then 'order by [' + @order + ']'
when 'leaf page merges' then 'order by [' + @order + ']'
when 'nonleaf page merges' then 'order by [' + @order + ']'
when 'range scans' then 'order by [' + @order + ']'
when 'singleton lookups' then 'order by [' + @order + ']'
when 'index usage' then 'order by [' + @order + ']'
when 'row locks' then 'order by [' + @order + ']'
when 'row lock waits' then 'order by [' + @order + ']'
when 'block %' then 'order by [' + @order + ']'
when 'row lock wait ms' then 'order by [' + @order + ']'
when 'avg row lock wait ms' then 'order by [' + @order + ']'
when 'page locks' then 'order by [' + @order + ']'
when 'page lock waits' then 'order by [' + @order + ']'
when 'page lock wait ms' then 'order by [' + @order + ']'
when 'avg page lock wait ms' then 'order by [' + @order + ']'
when 'index lock promotion attempts' then 'order by [' + @order + ']'
when 'index lock promotions' then 'order by [' + @order + ']'
when 'page latch waits' then 'order by [' + @order + ']'
when 'page latch wait ms' then 'order by [' + @order + ']'
when 'pageio latch waits' then 'order by [' + @order + ']'
when 'pageio latch wait ms' then 'order by [' + @order + ']'
else ''
end
if @orderby <> '' select @orderby = @orderby + ' desc'
select
'start time'=@begintime,
'end time'=@endtime,
'duration (hh:mm:ss:ms)'=convert(varchar(50),
@endtime-@begintime,14),
'Report'=case (@dbid)
when -1 then 'all databases'
else db_name(@dbid)
end +
case
when @top = '' then ''
when @top is NULL then ''
when @top = 'none' then ''
else ', ' + @top
end +
case
when @columns = '' then ''
when @columns is NULL then ''
when @columns = 'none' then ''
else ', include only columns containing ' + @columns
end +
case(@orderby)
when '' then ''
when NULL then ''
when 'none' then ''
else ', ' + @orderby
end +
case
when @threshold = '' then ''
when @threshold is NULL then ''
when @threshold = 'none' then ''
else ', threshold on ' + @threshold
end
select @cmd = ' select i2.database_id, i2.object_id, i2.index_id,
i2.partition_number '
select @cmd = @cmd +' , begintime=case min(i1.now) when max(i2.now) then
NULL else min(i1.now) end '
select @cmd = @cmd +' , endtime=max(i2.now) '
select @cmd = @cmd +' into #i '
select @cmd = @cmd +' from indexstats i2 '
select @cmd = @cmd +' full outer join '
select @cmd = @cmd +' indexstats i1 '
select @cmd = @cmd +' on i1.database_id = i2.database_id '
select @cmd = @cmd +' and i1.object_id = i2.object_id '
select @cmd = @cmd +' and i1.index_id = i2.index_id '
select @cmd = @cmd +' and i1.partition_number = i2.partition_number '
select @cmd = @cmd +' where i1.now >= ''' +
convert(varchar(100),@begintime, 109) + ''''
select @cmd = @cmd +' and i2.now = ''' + convert(varchar(100),@endtime,
109) + ''''
select @cmd = @cmd + ' ' + @where_dbid_is + ' '
select @cmd = @cmd + ' group by i2.database_id, i2.object_id, i2.index_id,
i2.partition_number '
select @cmd = @cmd + ' select ' + @top + ' i.database_id,
db_name=db_name(i.database_id),
object=isnull(object_name(i.object_id),i.object_id), indid=i.index_id,
part_no=i.partition_number '
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf inserts]=i2.leaf_insert_count -
isnull(i1.leaf_insert_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,@col_stmt=' ,
[leaf deletes]=i2.leaf_delete_count –
isnull(i1.leaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf updates]=i2.leaf_update_count –
isnull(i1.leaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf inserts]=i2.nonleaf_insert_count –
isnull(i1.nonleaf_insert_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf deletes]=i2.nonleaf_delete_count –
isnull(i1.nonleaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf updates]=i2.nonleaf_update_count –
isnull(i1.nonleaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf index overhead]=(i2.nonleaf_insert_count –
isnull(i1.nonleaf_insert_count,0)) + (i2.nonleaf_delete_count –
isnull(i1.nonleaf_delete_count,0)) + (i2.nonleaf_update_count –
isnull(i1.nonleaf_update_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf allocations]=i2.leaf_allocation_count –
isnull(i1.leaf_allocation_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf allocations]=i2.nonleaf_allocation_count –
isnull(i1.nonleaf_allocation_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[allocations]=(i2.leaf_allocation_count –
isnull(i1.leaf_allocation_count,0)) + (i2.nonleaf_allocation_count –
isnull(i1.nonleaf_allocation_count,0))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf page merges]=i2.leaf_page_merge_count –
isnull(i1.leaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf page merges]=i2.nonleaf_page_merge_count –
isnull(i1.nonleaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[range scans]=i2.range_scan_count –
isnull(i1.range_scan_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing= @columns,
@col_stmt=' ,[singleton lookups]=i2.singleton_lookup_count –
isnull(i1.singleton_lookup_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index usage]=(i2.range_scan_count –
isnull(i1.range_scan_count,0)) + (i2.singleton_lookup_count –
isnull(i1.singleton_lookup_count,0)) + (i2.leaf_insert_count –
isnull(i1.leaf_insert_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row locks]=i2.row_lock_count –
isnull(i1.row_lock_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row lock waits]=i2.row_lock_wait_count –
isnull(i1.row_lock_wait_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[block %]=cast (100.0 * (i2.row_lock_wait_count –
isnull(i1.row_lock_wait_count,0)) / (1 + i2.row_lock_count –
isnull(i1.row_lock_count,0)) as numeric(5,2))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row lock wait ms]=i2.row_lock_wait_in_ms –
isnull(i1.row_lock_wait_in_ms,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg row lock wait ms]=cast ((1.0*(i2.row_lock_wait_in_ms
- isnull(i1.row_lock_wait_in_ms,0)))/(1 + i2.row_lock_wait_count -
isnull(i1.row_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page locks]=i2.page_lock_count –
isnull(i1.page_lock_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page lock waits]=i2.page_lock_wait_count –
isnull(i1.page_lock_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page lock wait ms]=i2.page_lock_wait_in_ms –
isnull(i1.page_lock_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg page lock wait ms]=cast
((1.0*(i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)))/(1 +
i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)) as
numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index lock promotion
attempts]=i2.index_lock_promotion_attempt_count –
isnull(i1.index_lock_promotion_attempt_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index lock promotions]=i2.index_lock_promotion_count –
isnull(i1.index_lock_promotion_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page latch waits]=i2.page_latch_wait_count –
isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page latch wait ms]=i2.page_latch_wait_in_ms –
isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg page latch wait ms]=cast
((1.0*(i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)))/(1
+ i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)) as
numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[pageio latch waits]=i2.page_io_latch_wait_count –
isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[pageio latch wait ms]=i2.page_io_latch_wait_in_ms –
isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg pageio latch wait ms]=cast
((1.0*(i2.page_io_latch_wait_in_ms –
isnull(i1.page_io_latch_wait_in_ms,0)))/(1 + i2.page_io_latch_wait_count –
isnull(i1.page_io_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
select @cmd = @cmd + @temp
select @cmd = @cmd + ' from #i i '
select @cmd = @cmd + ' left join indexstats i1 on i.begintime = i1.now and
i.database_id = i1.database_id and i.object_id = i1.object_id and
i.index_id = i1.index_id and i.partition_number = i1.partition_number '
select @cmd = @cmd + ' left join indexstats i2 on i.endtime = i2.now and
i.database_id = i2.database_id and i.object_id = i2.object_id and
i.index_id = i2.index_id and i.partition_number = i2.partition_number '
select @cmd = @cmd + ' ' + @orderby + ' '
select @cmd = @cmd + @threshold_temptab
exec ( @cmd )
go
insert_indexstats
create proc insert_indexstats (@dbid smallint=NULL,
@objid int=NULL,
@indid int=NULL,
@partitionid int=NULL)
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
confers no rights.
-- Use of included script samples are subject to the terms specified at
https://www.microsoft.com/info/cpyright.htm
-- This stored procedure stores a snapshot of
sys.dm_db_index_operational_stats into the table indexstas
-- for later analysis by the stored procedure get_indexstats. Please note
that the indexstats table has an additional
-- column to store the timestamp when the snapshot is taken
--
-- T. Davidson
-- snapshot sys.dm_db_index_operational_stats
--
declare @now datetime
select @now = getdate()
insert into indexstats
(database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms,
now)
select database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms
,@now
from sys.dm_db_index_operational_stats(@dbid,@objid,@indid,@partitionid)
go
init_index_operational_stats
CREATE proc dbo.init_index_operational_stats
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- https://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
--
-- create indexstats table if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 from dbo.sysobjects where
id=object_id(N'[dbo].[indexstats]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
create table dbo.indexstats (
database_id smallint NOT NULL
,object_id int NOT NULL
,index_id int NOT NULL
,partition_number int NOT NULL
,leaf_insert_count bigint NOT NULL
,leaf_delete_count bigint NOT NULL
,leaf_update_count bigint NOT NULL
,leaf_ghost_count bigint NOT NULL
,nonleaf_insert_count bigint NOT NULL
,nonleaf_delete_count bigint NOT NULL
,nonleaf_update_count bigint NOT NULL
,leaf_allocation_count bigint NOT NULL
,nonleaf_allocation_count bigint NOT NULL
,leaf_page_merge_count bigint NOT NULL
,nonleaf_page_merge_count bigint NOT NULL
,range_scan_count bigint NOT NULL
,singleton_lookup_count bigint NOT NULL
,forwarded_fetch_count bigint NOT NULL
,lob_fetch_in_pages bigint NOT NULL
,lob_fetch_in_bytes bigint NOT NULL
,lob_orphan_create_count bigint NOT NULL
,lob_orphan_insert_count bigint NOT NULL
,row_overflow_fetch_in_pages bigint NOT NULL
,row_overflow_fetch_in_bytes bigint NOT NULL
,column_value_push_off_row_count bigint NOT NULL
,column_value_pull_in_row_count bigint NOT NULL
,row_lock_count bigint NOT NULL
,row_lock_wait_count bigint NOT NULL
,row_lock_wait_in_ms bigint NOT NULL
,page_lock_count bigint NOT NULL
,page_lock_wait_count bigint NOT NULL
,page_lock_wait_in_ms bigint NOT NULL
,index_lock_promotion_attempt_count bigint NOT NULL
,index_lock_promotion_count bigint NOT NULL
,page_latch_wait_count bigint NOT NULL
,page_latch_wait_in_ms bigint NOT NULL
,page_io_latch_wait_count bigint NOT NULL
,page_io_latch_wait_in_ms bigint NOT NULL
,now datetime default getdate())
else truncate table dbo.indexstats
go
add_column
create proc dbo.add_column (
@add_stmt varchar(500) output,
@find varchar(100)=NULL,
@cols_containing varchar(500)=NULL,
@col_stmt varchar(max))
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- https://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find.
-- If @col_stmt contains @find, include this statement.
-- set @add_stmt = @col_stmt
--
declare @length int, @strindex int, @EOS bit
if @cols_containing is NULL
begin
select @add_stmt=@col_stmt
return
end
select @add_stmt = '', @EOS = 0
while @add_stmt is not null and @EOS = 0
@dbid=-1,
select @strindex = charindex(',',@cols_containing)
if @strindex = 0
select @find = @cols_containing, @EOS = 1
else
begin
select @find = substring(@cols_containing,1,@strindex-1)
select @cols_containing =
substring(@cols_containing,
@strindex+1,
datalength(@cols_containing) - @strindex)
end
select @add_stmt=case
--when @cols_containing is NULL then NULL
when charindex(@find,@col_stmt) > 0 then NULL
else ''
end
end
--- NULL indicates this statement is to be passed back through out parm
@add_stmt
if @add_stmt is NULL select @add_stmt=@col_stmt
go
待機状態
次の一連のストアド プロシージャを使用すると、SQL Server におけるブロックを分析できます。
track_waitstats_2005
CREATE proc [dbo].[track_waitstats_2005] (
@num_samples int=10,
@delay_interval int=1,
@delay_type nvarchar(10)='minutes',
@truncate_history nvarchar(1)='N',
@clear_waitstats nvarchar(1)='Y')
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- https://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @num_samples is the number of times to capture waitstats, default is 10
-- times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
-- Revision: 4/19/05
--- (1) added object owner qualifier
--- (2) optional parameters to truncate history and clear waitstats
set nocount on
if not exists (select 1
from sys.objects
where object_id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
create table [dbo].[waitstats]
([wait_type] nvarchar(60) not null,
[waiting_tasks_count] bigint not null,
[wait_time_ms] bigint not null,
[max_wait_time_ms] bigint not null,
[signal_wait_time_ms] bigint not null,
now datetime not null default getdate())
If lower(@truncate_history) not in (N'y',N'n')
begin
raiserror ('valid @truncate_history values are ''y'' or
''n''',16,1) with nowait
end
If lower(@clear_waitstats) not in (N'y',N'n')
begin
raiserror ('valid @clear_waitstats values are ''y'' or
''n''',16,1) with nowait
end
If lower(@truncate_history) = N'y'
truncate table dbo.waitstats
If lower (@clear_waitstats) = N'y'
-- clear out waitstats
dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs
declare @i int,
@delay varchar(8),
@dt varchar(3),
@now datetime,
@totalwait numeric(20,1),
@endtime datetime,
@begintime datetime,
@hr int,
@min int,
@sec int
select @i = 1
select @dt = case lower(@delay_type)
when N'minutes' then 'm'
when N'minute' then 'm'
when N'min' then 'm'
when N'mi' then 'm'
when N'n' then 'm'
when N'm' then 'm'
when N'seconds' then 's'
when N'second' then 's'
when N'sec' then 's'
when N'ss' then 's'
when N's' then 's'
else @delay_type
end
if @dt not in ('s','m')
begin
raiserror ('delay type must be either ''seconds'' or
''minutes''',16,1) with nowait
return
end
if @dt = 's'
begin
select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60)
as int), @hr = cast((@min / 60) as int)
end
if @dt = 'm'
begin
select @sec = 0, @min = @delay_interval % 60, @hr =
cast((@delay_interval / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
+ right('0'+convert(varchar(2),@min),2) + ':' +
+ right('0'+convert(varchar(2),@sec),2)
if @hr > 23 or @min > 59 or @sec > 59
begin
select 'delay interval and type: ' + convert
(varchar(10),@delay_interval) + ',' + @delay_type + ' converts to ' +
@delay
raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait
return
end
while (@i <= @num_samples)
begin
select @now = getdate()
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
@now
from sys.dm_os_wait_stats
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
'Total',
sum([waiting_tasks_count]),
sum([wait_time_ms]),
0,
sum([signal_wait_time_ms]),
@now
from [dbo].[waitstats]
where now = @now
select @i = @i + 1
waitfor delay @delay
end
--- create waitstats report
execute dbo.get_waitstats_2005
go
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'
get_waitstats_2005
CREATE proc [dbo].[get_waitstats_2005] (
@report_format varchar(20)='all',
@report_order varchar(20)='resource')
as
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- https://www.microsoft.com/info/cpyright.htm
--
-- this proc will create waitstats report listing wait types by
-- percentage.
-- (1) total wait time is the sum of resource & signal waits,
-- @report_format='all' reports resource & signal
-- (2) Basics of execution model (simplified)
-- a. spid is running then needs unavailable resource, moves to
-- resource wait list at time T0
-- b. a signal indicates resource available, spid moves to
-- runnable queue at time T1
-- c. spid awaits running status until T2 as cpu works its way
-- through runnable queue in order of arrival
-- (3) resource wait time is the actual time waiting for the
-- resource to be available, T1-T0
-- (4) signal wait time is the time it takes from the point the
-- resource is available (T1)
-- to the point in which the process is running again at T2.
-- Thus, signal waits are T2-T1
-- (5) Key questions: Are Resource and Signal time significant?
-- a. Highest waits indicate the bottleneck you need to solve
-- for scalability
-- b. Generally if you have LOW% SIGNAL WAITS, the CPU is
-- handling the workload e.g. spids spend move through
-- runnable queue quickly
-- c. HIGH % SIGNAL WAITS indicates CPU can't keep up,
-- significant time for spids to move up the runnable queue
-- to reach running status
-- (6) This proc can be run when track_waitstats is executing
--
-- Revision 4/19/2005
-- (1) add computation for CPU Resource Waits = Sum(signal waits /
-- total waits)
-- (2) add @report_order parm to allow sorting by resource, signal
-- or total waits
--
set nocount on
declare @now datetime,
@totalwait numeric(20,1),
@totalsignalwait numeric(20,1),
@totalresourcewait numeric(20,1),
@endtime datetime,@begintime datetime,
@hr int,
@min int,
@sec int
if not exists (select 1
from sysobjects
where id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
raiserror('Error [dbo].[waitstats] table does not exist',
16, 1) with nowait
return
end
if lower(@report_format) not in ('all','detail','simple')
begin
raiserror ('@report_format must be either ''all'',
''detail'', or ''simple''',16,1) with nowait
return
end
if lower(@report_order) not in ('resource','signal','total')
begin
raiserror ('@report_order must be either ''resource'',
''signal'', or ''total''',16,1) with nowait
return
end
if lower(@report_format) = 'simple' and lower(@report_order) <> 'total'
begin
raiserror ('@report_format is simple so order defaults to
''total''',
16,1) with nowait
select @report_order = 'total'
end
select
@now=max(now),
@begintime=min(now),
@endtime=max(now)
from [dbo].[waitstats]
where [wait_type] = 'Total'
--- subtract waitfor, sleep, and resource_queue from Total
select @totalwait = sum([wait_time_ms]) + 1, @totalsignalwait =
sum([signal_wait_time_ms]) + 1
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total' ,'WAITFOR',
'***total***') and
now = @now
select @totalresourcewait = 1 + @totalwait - @totalsignalwait
-- insert adjusted totals, rank by percentage descending
delete waitstats
where [wait_type] = '***total***' and
now = @now
insert into waitstats
select
'***total***',
0,@totalwait,
0,
@totalsignalwait,
@now
select 'start time'=@begintime,'end time'=@endtime,
'duration (hh:mm:ss:ms)'=convert(varchar(50),@endtime-
@begintime,14),
'report format'=@report_format, 'report order'=@report_order
if lower(@report_format) in ('all','detail')
begin
----- format=detail, column order is resource, signal, total. order by
resource desc
if lower(@report_order) = 'resource'
select [wait_type],[waiting_tasks_count],
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as
numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as
numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'res_wt_%' desc
----- format=detail, column order signal, resource, total. order by signal
desc
if lower(@report_order) = 'signal'
select [wait_type],
[waiting_tasks_count],
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait
as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as
numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as
numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'sig_wt_%' desc
----- format=detail, column order total, resource, signal. order by total
desc
if lower(@report_order) = 'total'
select
[wait_type],
[waiting_tasks_count],
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as
numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'wt_%' desc
end
else
---- simple format, total waits only
select
[wait_type],
[wait_time_ms],
percentage=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by percentage desc
---- compute cpu resource waits
select
'total waits'=[wait_time_ms],
'total signal=CPU waits'=[signal_wait_time_ms],
'CPU resource waits % = signal waits / total waits'=
cast (100*[signal_wait_time_ms]/[wait_time_ms] as
numeric(20,1)),
now
from [dbo].[waitstats]
where [wait_type] = '***total***'
order by now
go
declare @now datetime
select @now = getdate()
select getdate()
| 1 | 。詳細については、MSDN の 「QueryMemoryResourceNotification」(英語) を参照してください。 |