次の方法で共有

SQL Server で SQL ステートメントの大規模なバッチの実行が完了しない

この記事は、複数の結果セットを返す SQL ステートメントの大規模なバッチを実行するときに発生する問題を解決するのに役立ちます。

元の製品バージョン: SQL Server
元の KB 番号: 827575

現象

複数の結果セットを返す SQL ステートメントの大規模なバッチを実行すると、バッチ内のすべてのステートメントが実行される前に、Microsoft SQL Server によってバッチの処理が停止されることがあります。 この動作の影響は、バッチ ステートメントが実行する操作によって異なります。 たとえば、バッチが最初にトランザクションを開始し、最後にトランザクションをコミットした場合、コミットが発生しない可能性があります。 この動作により、ロックが予想よりも長く保持されます。 これにより、接続が閉じられたときにトランザクションがロールバックされる可能性もあります。 バッチがトランザクションを開始しない場合、問題の症状として、一部のステートメントが実行されない可能性があります。

この問題の影響を次に示します。 効果はさまざまであり、バッチに含まれるものに正確に依存します。

  • データベース クエリ ステートメントのバッチがアプリケーションから実行されることを検討してください。 データベース クエリ ステートメントのバッチが先頭に BEGIN TRANSACTION で構成され、最後に COMMIT TRANSACTION 場合、コントロールがアプリケーションに返されてもコミット操作が発生しない可能性があります。 これは、保持されている可能性のあるロックが保留中のトランザクションを引き起こす可能性があり、気付かれていない可能性があるため、問題です。

    このシナリオでは、トランザクションはバッチでコミットされないため、保留中のままであり、SQL Server からの切断時にロールバックされます。

  • アプリケーション プログラム インターフェイス (API) を使用してトランザクションを開始およびコミットすると、次の動作が表示されることがあります。

    • API を使用してトランザクションを開始する通知をサーバーに送信した後、バッチを実行すると、SQL はバッチの一部のみを処理し、アプリケーションにコントロールを返します。
    • この手順の後、API を使用してトランザクションをコミットすると、処理されたバッチの一部のみがコミットされます。 エラーは発生しません。

    たとえば、ODBC では、 SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) を呼び出してトランザクションを開始し、 SQLEndTran(SQL_COMMIT) を使用してトランザクションをコミットします。

原因

バッチの結果を処理すると、SQL Server は接続出力バッファーにバッチからの結果を入力します。 これらの結果は、クライアント アプリケーションによって処理される必要があります。 複数の結果セット (結果を生成する複数のステートメント) で大規模なバッチを実行している場合、SQL Server は内部制限に達するまでその出力バッファーを満たし、クライアント アプリケーションがそれらの結果の使用を開始するまで続行できません。 クライアントが結果セットの使用を開始すると、出力バッファーに使用可能なメモリが追加されたため、SQL Server はバッチの実行を再開します。 この動作は仕様によるものです。

多くの場合、名前付きパイプ プロトコルまたは共有メモリ (LPC) プロトコルを使用して SQL Server に接続すると、この問題が発生します。 これは、SQL Server がさまざまなプロトコルで使用できる内部バッファー サイズが原因です。

回避策

この問題を回避するには、次の手順に従います。

  1. クライアント アプリケーションがすべての出力結果セットを使用していることを確認します。 すべての出力結果セットがクライアントによって使用されるとすぐに、SQL Server はバッチの実行を完了します。

    • Open Database Connectivity (ODBC) を使用して SQL Server に接続する場合は、メソッドから結果セットがなくなったと報告されるまで、 SQLMoreResults メソッドを呼び出すことができます。
    • OLE DB を使用して SQL Server に接続している場合は、 DB_S_NORESULTが返されるまで、IMultipleResults::GetResult メソッドを繰り返し呼び出すことができます。

  2. ステートメント SET NOCOUNT ON をバッチの先頭に追加します。 バッチがストアド プロシージャ内で実行される場合は、ストアド プロシージャ定義の先頭にステートメントを追加します。 これにより、メインの結果セットの後に、処理された行数を示す追加の結果セットが SQL Server から返されなくなります。 そのため、サーバーの出力バッファーに出力されるデータを減らすことができます。 ただし、これは問題が発生しないことを保証するものではありません。 サーバーから返されるデータが、結果セットの 1 つのバッチに収まるほど小さい可能性が高まるだけです。

クライアント アプリケーションでは、実行しているバッチのサイズに関係なく、SQL Server からのすべての結果セットを常に使用することをお勧めします。 このデータを処理せず、結果セットバッチのエラーの前に正常な結果セットが返された場合、クライアントはサーバー エラーを検出しない可能性があります。 クライアント アプリケーションは、正しい実行を保証するために、結果セット全体を処理する必要があります。

問題を再現する手順

  1. SQL Server Management Studio (SSMS) を使用して SQL Server に接続し、サンプル pubs データベースを作成します。

  2. 次のように、データベース クエリ ステートメントの比較的大きなバッチを使用して、 pubs に SQL ストアド プロシージャを作成します。

    CREATE PROC bigBatch AS
BEGIN TRANSACTION
UPDATE authors SET au_fname = 'newname1' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname2' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname3' WHERE au_id='172-32-1176'
-- Add more UPDATE statements here ... 
UPDATE authors SET au_fname = 'newname1000' WHERE au_id='172-32-1176'
COMMIT TRANSACTION

  3. オブジェクト エクスプローラーで、Management>Extended Events を選択します。

  4. Sessionsを右クリックし、新しいセッション ウィザード選択。

  5. TSQL_SPs セッション テンプレートを使用して、新しいイベント セッションを作成します。

  6. セッションを開始し、ライブ データを監視します。 詳細については、「 Quickstart: SQL Server の拡張イベント」を参照してください。

  7. ODBC または OLE DB を使用して SQL Server に接続し、 bigBatch実行してから、イベント セッションのライブ データを分析します。

ODBC に接続する

ODBC を使用して SQL Server に接続するには、次の手順に従います。

  1. SQL Server に接続する pubs データベースを使用して、データ ソース名 (DSN) を作成して構成します。
  2. Data Access (MDAC) SDK のインストールで使用できる ODBC Test ツール サンプルを開きます。
  3. [ Conn ] メニューの [ Full Connect を選択します。
  4. Full Connect ダイアログ ボックスで、手順 1 で作成した DSN を選択します。
  5. SQL Server への接続が成功したことを確認します。
  6. Stmt メニューで、SQLExecDirect を選択します。
  7. [ StatementText ボックスに「 {call bigBatch}」と入力し、 OK を選択します。

XEvent ライブ データでは、ストアド プロシージャの処理が完了していないことがわかります。 ただし、ODBC テスト ツールは、実行が成功したことを示します。 すべての結果セットをフェッチし、サーバーでバッチを終了するには、Results メニューの [データをすべて取得] を選択します。

OLE DB を使用して接続する

OLE DB を使用して SQL Server に接続するには、次の手順に従います。

  1. MDAC SDK で使用できる OLE DB RowsetViewer ツール サンプルを開きます。
  2. Full Connect オプションを使用して、SQL Server pubs データベースに接続します。
  3. Command メニューで、ICommand をポイントし、Execute を選択します。
  4. [ Cmd テキスト ボックスに、「 {call bigBatch}」と入力します。
  5. REFIIDの一覧でIID_IMultipleResultsを選択し、Propertiesを選択します。
  6. ICommandProperties::SetProperties::SetProperties ダイアログ ボックスで、DBPROP_IMultipleResultsを選択し、値をVARIANT_TRUEに変更し、OK を選択します。
  7. [OK] を選択します。

XEvent ライブ データでは、ストアド プロシージャの処理が完了していないことがわかります。 ただし、RowsetViewer ツールは、操作が成功したことを示します。 すべての結果セットを取得するには、左側のウィンドウで MultipleResults オブジェクトを右クリックし、 IMultipleResultsをポイントして、 GetResult を選択します。 すべての結果セットが使用されるまで繰り返します。

関連情報

  • Last updated on