[HOWTO] アドホック クエリのパフォーマンスのトラブルシューティングを行う方法

文書翻訳 文書翻訳
文書番号: 243588 - 対象製品
この記事は、以前は次の ID で公開されていました: JP243588
すべて展開する | すべて折りたたむ

目次

概要

この資料では、多数の同時アドホック クエリによるパフォーマンスの低下の問題のトラブルシューティングを行う方法について説明します。問題の発生源が正確に特定できていない場合は、次に進む前に、以下の「サポート技術情報」 (Microsoft Knowledge Base) を参照してください。
224587 [HOWTO] SQL Server に関するアプリケーション パフォーマンスのトラブルシューティング

この資料では、既に上記の資料を使用して問題の発生部分をある程度絞り込み、また、特定のカウンタ、イベント、およびデータ列を詳細に示す Windows NT パフォーマンス モニタのログおよび SQL プロファイラのトレース情報をキャプチャしているという前提で説明します。

パフォーマンスの問題の特性

パフォーマンスの問題には、以下の特性があります。
  • 通常短時間で終了するような短いアドホック クエリを多数のユーザーが同時に実行すると、システム全体のパフォーマンスが低下する。
  • CPU の使用率が非常に高い、または 100% になっている。
  • パフォーマンスが低下しているときに、関連するブロッキングが存在しない。

    ブロッキングについては、sp_who システム ストアド プロシージャの出力の blk 列ですぐにチェックできます。多数のシステム プロセス ID (SPID) に対して blk 列がゼロ以外になっている場合は、ブロッキングが発生しています。 ブロッキングの問題の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    224453 [INF] SQL Server 7.0 または SQL Server 2000 のブロッキング問題と解決策
  • サーバーのメモリに負担がかかり、以下のようなエラーが報告されることがある。
    エラー : 701、レベル : 17、状態 : 1
    このクエリを実行するには、システム メモリが不足しています。
    または
    メッセージ 8645、レベル 17、状態 1、プロシージャ、行 1
    クエリ実行のためのメモリ リソースを待機中にタイムアウトが発生しました。クエリを再実行してください。

クエリのコンパイルの機能強化

SQL Server 7.0 では、システム アーキテクチャ、特にクエリ オプティマイザが強化されています。SQL Server の以前のバージョンと比較すると、アプリケーションのシステム リソースの使用方法に違いがある場合があります。具体的には、SQL Server 7.0 では、CPU 使用量およびメモリの使用量がいずれも増加することがあります。これに比べて、以前のバージョンでは、一般的に、大量のディスク I/O がボトルネックとなっていました。これらの変更は、以下の 2 つの要因で記録、追跡できます。
  • ハッシュ結合とマージ結合
  • クエリのコンパイル時間
以前のバージョンの SQL Server は、完全にネストしたループの繰り返しに依存して結合を実行していました。ネストしたループ結合は、その特性上、ディスク I/O を使用します。このネストしたループに加えて、SQL Server 7.0 からは、ハッシュ結合およびマージ結合が使用できるようになりました。これらの結合は、ネストしたループ結合よりも、メモリ内処理が多くなります。したがって、これらの結合手法が使用される場合は、CPU およびメモリの使用が増える結果になります。ハッシュ結合およびマージ結合の詳細については、SQL Server 7.0 Books Online の「ハッシュ結合について」および「マージ結合について」を参照してください。

クエリのコンパイル時間に影響が出るのは、以前のバージョンと比べて、新しいハッシュ結合およびマージ結合のテクニック、強化された検索アルゴリズム、列統計など、クエリ オプティマイザで利用できるオプションや情報が増えたためです。これらの追加情報により、クエリ オプティマイザで、クエリ データの取得に最も効率的なプランを選択できるようになります。しかし、その一方で、これらの新しいテクニックおよび情報の分析や検討に処理時間がかかります。これによって CPU の使用が増加した結果、クエリのコンパイル時間が前のバージョンより長くなる場合があります。

大多数のクエリでは、このコンパイル時間の増加は、実行時間の減少で相殺されます。したがって、全体的な影響としてみると、クエリの実行は以前のバージョンよりも高速になっています。ただし、この原則には、例外が 1 つあります。もともと短時間で実行できるようなサイズの小さい簡単な OLTP タイプのクエリがこれにあたります。この場合、クエリ プランの生成に、クエリの実行と同じかそれ以上の処理を必要とする可能性があります。その結果、以前のバージョンで実行した場合よりもわずかに時間がかかります。ただし、その差は、ほぼミリ秒単位になるのが普通なので、特定のクエリを個別に実行した場合にその影響が認識されることはありません。しかし、多数のアドホック クエリが多くのユーザーから同時に実行された場合は、システム全体の CPU 使用率が以前のバージョンの SQL Server の場合よりも高くなることがあります。

パラメータ化クエリの開発

SQL Server 7.0 では、この影響を緩和するために、アドホック クエリのキャッシングや自動パラメータ化などの新しいテクニックが採用されています。ただし、自動パラメータ化の対象となるクエリには制限があります。アプリケーションを開発する際は以下の方法を利用して、開発するクエリ プランがパラメータ化され、より効果的に再利用できるようにしてください。
  • パラメータ マーカー : OLE-DB API と ODBC API のどちらの場合も、クエリ送信時にパラメータに疑問符を付けて指定できます。これは、どのアプリケーションであってもたいへん便利です。特に、クエリ生成モジュールを含む中階層アプリケーションでは、ストアド プロシージャが使用できないので、この機能が役に立ちます。パラメータ マーカー付きで実行されるクエリのために生成されるクエリ プランは、これ以降、同じクエリを実行する任意のクライアントで再使用できます。異なるパラメータ値が指定されている場合も使用可能です。詳細については、SQL Server 7.0 Books Online の「パラメータ マーカー」を参照してください。
  • sp_executesql : sp_executesql ストアド プロシージャは、アプリケーションでパラメータ マーカーが使用されるときに OLE DB プロバイダまたは ODBC ドライバによって呼び出されます。また、このシステム プロシージャは、アプリケーションから直接呼び出されたり、他のストアド プロシージャ内からアドホック クエリを明示的にパラメータ化するために呼び出されたりすることもあります。これは、動的 SQL ステートメントを実行するために EXECUTE ステートメントを使用するアプリケーションやバッチ ファイルで役立ちます。これは、EXECUTE ステートメントでは、sp_executesql とは異なり、パラメータ化が許可されていないので、クエリ プランの再使用の可能性が限られているためです。詳細については、SQL Server 7.0 Books Online の「sp_executesql (T-SQL)」および「sp_executesql の使用」を参照してください。
  • ストアド プロシージャ : ストアド プロシージャには、パラメータ化クエリや実行プランの再使用など、多くの利点があります。詳細については、SQL Server 7.0 Books Online の「ストアド プロシージャ」および「ストアド プロシージャのプ ログラミング」を参照してください。

パフォーマンス問題の特定と解決

システムのパフォーマンス分析には、Windows NT パフォーマンス モニタおよび SQL プロファイラのデータが役立ちます。これらのデータをキャプチャする方法の詳細については、以下の「サポート技術情報」 (Microsoft Knowledge Base) を参照してください
224587 [HOWTO] SQL Server に関するアプリケーション パフォーマンスのトラブルシューティング

パフォーマンス モニタ データの表示

パフォーマンス モニタ ログを使用して、ボトルネックになっているシステム リソースを特定します。これにより、システムの全体像をつかむことができます。また、SQL プロファイラのデータを見るときにどこを重点的に調べればよいかがわかります。パフォーマンスに問題がなかった時点からパフォーマンス モニタ データを表示し、パフォーマンスが低下するに従って、カウンタがどのように変化したかを調べます。どのカウンタが最初に悪化したかを特定できれば、後に挙げる問題点の中で、どれが現在の状況に最も関係があるのかがわかります。後述する問題に関係するカウンタは、以下のとおりです。
  • オブジェクト : Process
    カウンタ : % Processor Time
    インスタンス : sqlservr
  • オブジェクト : Processor
    カウンタ : % Processor Time
    インスタンス : 各 Processor インスタンスを調べる
  • オブジェクト : SQL Server:Buffer Manager
    カウンタ : Free Pages
  • オブジェクト : SQL Server:Buffer Manager
    カウンタ : Stolen pages
  • オブジェクト : SQL Server:Memory Manager
    カウンタ : Memory Grants Pending
  • オブジェクト : SQL Server:SQL Statistics
    カウンタ : SQL Compilations/sec
CPU の利用状況、SQL Compilations/sec、および Free Pages のカウンタ値が高く、Memory Grants Pending および Stolen Page Count カウンタ値が低い場合は、CPU がボトルネックであることを示しています。クエリ プラン生成のコストを避けるために、効果的なパラメータ化やクエリ プランの再使用を行う方法に重点を置いてください (後述する「イベント クラスによる SQL プロファイラ トレースのグループ化」を参照してください)。Free Pages および SQL Compilations/sec のカウンタ値が低く、Stolen Page Count および Memory Grants Pending のカウンタ値が高い場合は、SQL Server のメモリ不足です。ループ結合に変更できるハッシュ結合を使用しているクエリを探すことに重点を置いていください (後述する「実行時間による SQL プロファイラ トレースのグループ化」を参照してください)。これらのカウンタの詳細については、『SQL Server 7.0 Books Online』でカウンタ名を検索してください。

SQL プロファイラ データの表示

SQL プロファイラ データを効率的に表示することは、パフォーマンスの問題を解決するうえで、非常に価値があります。また、キャプチャしたデータをすべて見る必要はありません。関係のある箇所だけをチェックするようにしてください。SQL プロファイラには、キャプチャしたデータを効果的に表示する機能があります。プロパティ シート ([ファイル] メニューの [プロパティ] をクリック) を使用すると、データ列やイベントの削除、データ列のグループ化 (並べ替え)、フィルタの適用を行うことによって、表示するデータを制限できます。また、検索対象は、トレース情報全部にすることも、特定の値を持つ特定の列だけにすることもできます ([編集] メニューの [検索] をクリックします)。また、SQL プロファイラ データを SQL Server テーブルに保存し ([ファイル] メニューの [名前を付けて保存] をポイントし [トレース テーブル] をクリック)、それに対して SQL クエリも実行できます。

: フィルタは、既に保存したトレース ファイルにだけ実行してください。アクティブなトレースに以下の手順を実行すると、トレースが開始されてからキャプチャされたデータが失われる危険があります。まず、アクティブなトレースをファイルまたはテーブルに保存 ([ファイル] メニューの [名前を付けて保存] をクリック) してから、再度開き ([ファイル] メニューの [開く] をクリック)、処理を行ってください。保存済みのトレース ファイルに対してフィルタを適用した場合、除外されたデータは単に表示されないだけで、完全に削除されることはありません。必要に応じてイベントやデータ列を追加または削除して、検索の絞り込みを行うことができます。

また、最も大きな成果が得られる部分に作業の重点を置くことも大切です。以下に挙げた要因はすべて、アプリケーションのパフォーマンス向上に役立ちますが、その程度はさまざまです。以下の項目をすべてチェックして、どの程度成果が得られるかを判断してから、変更の組み込みを始めてください。
  • クエリが実行される頻度
  • そのクエリで改善可能な範囲
たとえば、あるクエリの実行時間を 1.5 秒から 1.2 秒に短縮したとしても、それが 1 日のうち数回しか実行されないクエリであれば、あまり意味がありません。しかし、多数の同時ユーザーが非常に頻繁に実行するクエリであれば、パフォーマンスは劇的に向上する可能性があります。逆に言うと、1 つのクエリが 6 分から 3 秒になったとしても、それがめったに使用されなければ、全体のパフォーマンスとしては目に見える結果は現れません。変更の組み込みを行う前に、SQL プロファイラでグループ化およびフィルタを使用し、またアプリケーションについての知識を活用して、特定のクエリまたはプロシージャにおける効果を見積もってください。最初は、効果が一番大きい変更に集中し、それ以降、パフォーマンスが十分なレベルに達するまで、他のクエリおよびプロシージャで同じ作業を繰り返してください。

適切なイベントおよびデータ列を含むトレースを作成する方法の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
224587 [HOWTO] SQL Server に関するアプリケーション パフォーマンスのトラブルシューティング
SQL プロファイラのトレースをファイルまたはテーブルに保存したら、それを SQL プロファイラで再度開き、内容の検討を始めます。SQL プロファイラのトレースをグループ化するには、以下の手順を実行します。
  • duration (実行時間) による SQL プロファイラ トレースのグループ化を行うには、次の手順を実行します。
    1. [ファイル] メニューの [プロパティ] をクリックします。
    2. [データ列] タブで [上へ] ボタンを使用して、[duration] (実行時間) を [グループ] 見出しに移動します。[下へ] ボタンを使用して、それ以外の列をすべて [グループ] 見出しから削除します。
    3. [イベント] タブで、[TSQL SQL:StmtCompleted] および [TSQL RPC:Completed] 以外のすべてのイベントを削除します。これで、実行されるクエリだけに集中できます。
    4. [OK] をクリックします。
    duration (実行時間) でグループ化することによって、どの SQL ステートメント、バッチまたはプロシージャが実行に最も時間がかかっているかが容易にわかるようになります。問題が発生した時点だけに注目するのではなく、パフォーマンスが良かったときの基準を作成することも非常に重要です。開始時刻にフィルタを適用して、パフォーマンスが良かったときのトレースを 1 つのセクションに、また、パフォーマンスに問題があったときを別のセクションに分けることができます。パフォーマンスが良かったときに実行時間が最長だったクエリを探します。これが問題の原因になる可能性が高いからです。システム全体のパフォーマンスが低下している間は、問題がないクエリでも、システム リソース待ちの分だけ実行に時間がかかります。

    実行に時間がかかるクエリのうち、実行頻度が最も高いクエリの実行プランを調べます。ハッシュ結合が使用されている場合は、LOOP JOIN クエリ ヒントを使用して、ネストしたループ結合を強制的に使用させることを検討します。ハッシュ結合よりもループ結合を使用したクエリの実行時間の方が短いか、同じか、またはわずかに長い場合で、コンピュータのメモリまたは CPU の使用率が高くなっているときは、ループ結合を使うことをお勧めします。リソースのボトルネック (CPU とメモリの両方または一方) への負荷を軽減することによって、全体のシステム パフォーマンスを向上できるからです。LOOP JOIN ヒントの詳細については、SQL Server 7.0 Books Online の「SELECT (T-SQL)」を参照してください。

    特定のクエリの実行時間が異常と思われるほど長い場合は、以下の「サポート技術情報」 (Microsoft Knowledge Base) を参照してください。 実行に時間がかかるクエリの関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    243589 [HOWTO] SQL Server 7.0 以降で実行に時間がかかるクエリのトラブルシューティング
  • イベント クラスによる SQL プロファイラ トレースのグループ化を行うには、次の手順を実行します。
    1. [ファイル] メニューの [プロパティ] をクリックします。
    2. [データ列] タブで [上へ] ボタンを使用して、[Event Class] (イベント クラス) および [TextData] を [グループ] 見出しに移動し、[Event Class] を一番上にします。[下へ] ボタンを使用して、この 2 つ以外の列をすべて [グループ] 見出しから削除します。
    3. [イベント] タブで、すべてのイベントが含まれていることを確認します。
    4. [OK] をクリックします。

イベントの種類

イベント クラスによるグループ化によって、SQL Server が実行されているコンピュータで発生しているイベントとその頻度を表示できます。この列で以下のイベントを検索します。
  • その他: Prepare SQL および Exec Prepared SQL、カーソル: Cursorprepare

    Prepare SQL イベントは、デフォルトのカーソル オプション (前方のみ、読み取りのみ、行サイズ = 1) を指定した SQLPrepare/SQLExecute (ODBC の場合) または ICommandText::Prepare/ICommandText::Execute (OLE DB の場合) を使用して SQL ステートメントがデフォルトの結果セット (クライアント側カーソル) で使用されるように準備されたことを示します。Cursorprepare イベントは、上記のカーソル オプションのいずれかをデフォルト値以外に設定した SQLPrepare/SQLExecute (ODBC の場合) または ICommandText::Prepare/ICommandText::Execute (OLE DB の場合) を使用してサーバー側のカーソルが SQL ステートメントで準備されたことを示します。Exec Prepared SQL イベントは、準備された既存のステートメントのうち、上述したタイプのいずれかが実行されたことを示します。これらのイベントが頻繁に発生している場合は、アプリケーションで結果セットを開くときに準備/実行モデルが使用されています。この場合は、準備/実行モデルを正しく使用していることを確認する必要があります。

    アプリケーションでは、SQL ステートメントを 1 回だけ準備し、それを何回も実行するのが理想的です。これによって、ステートメント実行のたびにオプティマイザが新しいプランをコンパイルするコストを削減できるからです。つまり、準備されたステートメントを実行するたびに、クエリのコンパイル コストを削減できます。ただし、1 回しか実行しないクエリに対しては準備/実行モデルを使用しないことを、マイクロソフトは推奨します。SQL ステートメントを準備して実行するには、準備に 1 回、実行に 1 回、そして準備解除に 1 回と、ネットワークを 3 往復する必要があります。また、サーバー側でカーソルを準備する場合には、準備に 1 回、実行 (オープン) に 1 回、それからのフェッチに 1 回以上、カーソルを閉じるのに 1 回、そして準備解除に 1 回と、少なくとも 5 往復が必要です。単にクエリを実行するだけなら、1 往復で済みます。

    アプリケーションで準備/実行モデルがどの程度効果的に使用されているかを調べるには、この 2 種類のイベント (準備と実行) が発生する回数を比較します。Exec Prepared SQL イベントの回数が、Prepare SQL + CursorPrepare イベントの合計よりも、かなり (少なくとも、3 〜 5 倍程度が望ましい) 大きくなっている必要があります。これは、準備されたステートメントがその作成のために増加したオーバーヘッドを補って余りあるほど頻繁に再使用されていることを示します。Prepare SQL + CursorPrepare イベントの数が、Exec Prepared SQL イベントの数とほぼ同じである場合は、アプリケーションで準備/実行モデルが有効に使用されていない可能性があります。1 回しか実行しないステートメントは準備しないでください。そして、いったん準備したステートメントは、できるだけ何回も再使用するように努めてください。1 つのステートメントだけでなく、アプリケーションのステートメント全体にわたり、準備/実行モデルを有効に使用するように、アプリケーションを変更してください。

    アプリケーションは、準備/実行モデルを効率的に使用するように特に注意して記述する必要があります。準備されたステートメントのハンドルの有効期間は、ODBC の場合は HSTMT オープン、OLE DB の場合は ICommandText オブジェクトをどれだけ長く保持するかによって制御されます。一般的に不用意に記述してしまう方法では、HSTMT を取得し、SQL ステートメントを準備し、準備されたステートメントを実行し、HSTMT を解放します。ここで準備されたプランへのハンドルが失われます。このような実行方法では、準備/実行モデルを使用するメリットはありません。それどころか、実際には、前述したネットワークへの往復にかかるオーバーヘッドの分だけ、パフォーマンスが低下することがあります。アプリケーションには、準備されたステートメント ハンドルを含む HSTMT またはオブジェクトをキャッシュし、再使用のためにそれにアクセスするための何らかの手段が必要となります。これは、ドライバやプロバイダによって自動的に実行されることはありません。この情報の実装、維持、および使用はアプリケーション側で行う必要があります。アプリケーションでこれを実行できない場合は、準備や実行ではなく、パラメータ マーカーの使用を考慮してください。
  • パラメータ マーカーの使用

    パラメータ マーカーを使用すると、アプリケーションは、異なる入出力値で同じ Transact-SQL ステートメントを複数回実行する場合に最適化できます。クエリは、初めて実行されたときにパラメータ化クエリとして準備されます。次に、SQL Server がそのクエリのパラメータ化プランを生成し、キャッシュします。これ以降は、同じクエリ (パラメータは同じでも異なっていてもかまいません) が呼び出されても、SQL Server で新しいクエリ プランを生成する必要はありません。現在のパラメータで置き換えを行って既存のプランを再使用できます。

    SQLExecDirect (ODBC の場合) または ICommandText::Execute (OLE DB の場合) の呼び出しでパラメータ マーカーを使用すると、ドライバまたはプロバイダが自動的に SQL ステートメントをパッケージ化して、それを sp_executesql 呼び出しとして実行します。アプリケーションでステートメントの準備と実行を個別に行う必要はありません。SQL Server は、sp_executesql への呼び出しを受け取ると、プロシージャ キャッシュ内に一致する既存プランがあるかどうかを自動的にチェックし、利用できるプランがあればそれを再使用します。プランがない場合は、新しいプランを生成します。

    アプリケーションがパラメータ マーカーを使用中であるかどうかを確認するには、SQL プロファイラのトレース情報の TextData 列で「sp_executesql」を検索します。ただし、sp_executesql は直接呼び出される場合もあるので、見つかったすべてのインスタンスでパラメータ マーカーが使用されているとは限りません。

    準備/実行モデルの詳細については、SQL Server 7.0 Books Online の「実行プランのキャッシュと再利用」を参照してください。また、パラメータ マーカーの詳細については、SQL Server 7.0 Books Online の「パラメータ マーカー」を参照してください。
  • SP:Completed

    EXECUTE コマンドで実行された動的 SQL ステートメントは、SP:Completed イベントとして「Dynamic SQL」というテキストと共に表示されます。SP:Completed イベントを展開して、「Dynamic SQL」というテキストが含まれているものがあるかどうかを検索します。この種のイベントが非常に多い場合は、EXECUTE ステートメントの代わりに sp_executesql を使用すると、アプリケーションのパフォーマンスが向上する可能性があります。sp_executesql を使用すると、同じクエリが異なるパラメータで再実行される場合でも、SQL Server で実行プランを再使用できます。EXECUTE ステートメントの場合、プランがパラメータ化されないので、クエリがまったく同じパラメータで再実行される場合を除いて、プランは再使用されません。

    EXECUTE ステートメントで動的 SQL を使用しているクエリまたはプロシージャを調べるには、それらのイベントの Connection ID (接続 ID) 列と Start Time (開始時刻) 列の値をメモします。次に、トレース情報をグループ解除します ([Event Class] と [TextData] を [グループ] 見出しから削除します)。これでトレース情報の表示が発生順に戻ります。([フィルタ] タブで) 特定の接続 ID についてトレースにフィルタを適用し、見やすくなるように SP:Starting および SP:Complete イベント以外のイベント クラスをすべて削除します。さらに、([編集] メニューの [検索] をクリックして) メモしておいた開始時刻を検索します。これで、動的 SQL イベントが開始された時点が正確に表示されます。ストアド プロシージャ内で発生している場合は、イベントがそのプロシージャの SP:Starting と SP:Completed イベント間に表示されます。それ以外は、アドホック クエリとして実行されています。この場合は、他のデータ列 (ApplicationName、NTUserName など) を使用して、コマンドの実行元を特定できます。SQL:BatchCompleted、SQL:RPCCompleted などのイベント クラスを再度追加して、コマンドのテキスト、およびそれが実行されたコンテキストを特定することもできます。

    EXECUTE ステートメントが使用されている場所を特定したら、それを sp_executesql 呼び出しと置き換えることを考えます。たとえば、EXECUTE コマンドが動的 SQL と共に使用されている次のような例を考えます。プロシージャは、入力パラメータとして table (テーブル名)、idName (列名)、idValue (列の値) を取り、idName で指定された列の値が idValue で指定された値に等しい行を、指定されたテーブルにクエリする SELECT ステートメントを実行します。EXECUTE ステートメントを使用すると、このプロシージャは以下のようになります。
    drop proc dynamicUsingEXECUTE
    		  go create proc dynamicUsingEXECUTE @table sysname, @idName varchar(10),
    		  @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string
    		  with parameter. -- Notice the use of escape quotes. select @query = 'select *
    		  from ' + @table + ' where ' + @idName + ' = ''' + @idValue + '''' exec (@query)
    		  go
    クエリは自動的にパラメータ化されないことを前提として、pubs サンプル データベースの titles テーブルに対して、@idValue パラメータに異なる値を指定してこのプロシージャを 2 回実行した場合、SQL Server は、実行のたびに別のクエリ プランを生成する必要があります。
    exec dynamicUsingEXECUTE
    		  'titles', 'title_id', 'MC2222' go exec dynamicUsingEXECUTE 'titles',
    		  'title_id', 'BU7832'
    : この例では、クエリが単純なので、SQL Server はこのクエリを自動的にパラメータ化し、実際に実行プランを再使用できます。しかし、これが SQL Server による自動パラメータ化ができないような複雑なクエリだとすると、@idValue パラメータが変更された場合は、2 回目の実行でプランの再使用はできません。ここで単純なクエリを使用したのは、例が複雑にならないようにするためです。

    EXECUTE ステートメントの代わりに、sp_executesql を使用してこのプロシージャを書き換えることができます。パラメータの置換をサポートすることによって、sp_executesql の効率が向上します。これは、SQL Server によって再使用される可能性が高い実行プランが生成されるからです。
    drop proc dynamicUsingSP_EXECUTESQL go create proc
    		  dynamicUsingSP_EXECUTESQL @table sysname, @idName varchar(10), @idValue
    		  varchar(10) as declare @query nvarchar(4000) -- Build query string with
    		  parameter select @query = 'select * from ' + @table + ' where ' + @idName + ' =
    		  @idValue' -- Now execute with parameter exec sp_executesql @query, N'@idValue
    		  varchar(10)', @idValue go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'MC2222' go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'BU7832'
    この例では、初めて sp_executesql ステートメントが実行されるときに、SQL Server は、テーブル名 (titles)、列名 (title_id)、列の値 (MC2222) をパラメータとしてクエリを行う SELECT に関するパラメータ化プランを生成します。2 回目の実行からは、このプランが新しいパラメータ値で再使用されます。sp_executesql の詳細については、SQL Server 7.0 Books Online の「sp_executesql (T-SQL)」および「sp_executesql の使用」を参照してください。
  • SP:RECOMPILES

    このイベントは、ストアド プロシージャが実行中に再コンパイルされたことを示します。再コンパイル イベントの回数が多いことは、SQL Server がクエリの実行よりもコンパイルにそのリソースを消費していることを意味します。 関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    243586 ストアド プロシージャの再コンパイルのトラブルシューティング
これまでに挙げたイベントがいずれも表示されていない場合は、アプリケーションは、SQL Server に対してアドホック クエリだけを実行しています。SQL Server で特定のクエリの自動パラメータ化が可能と判断されたか、まったく同じパラメータが繰り返し使用されている場合以外は、実行されるクエリごとに、新しい実行プランの生成が必要です。パフォーマンス モニタでは、SQL Compilations/sec に表示される数字が大きくなります。前述したように、これは、多数の同時ユーザーが CPU を集中的に使用している可能性があります。この状態を緩和するには、実行頻度の高いクエリを探して、そのストアド プロシージャを作成するか、パラメータ マーカーまたは sp_executesql を使用します。

プロパティ

文書番号: 243588 - 最終更新日: 2005年12月22日 - リビジョン: 4.3
この資料は以下の製品について記述したものです。
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
キーワード:?
kbhowtomaster kbhowto kbinfo KB243588
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com