次の方法で共有


データベース アプリケーションと SSMS でのクエリ パフォーマンスの違いに関するトラブルシューティング

データベース アプリケーションでクエリを実行すると、SQL Server Management Studio (SSMS)、Azure Data Studio、SQLCMD などのアプリケーションで同じクエリよりも実行速度が低下します。

この問題は、以下のことが原因で発生する可能性があります。

  • クエリでは、さまざまなパラメーターまたは変数が使用されます。

  • クエリは、異なるネットワーク経由でサーバーに送信されるか、アプリケーションがデータを処理する方法に違いがあります。

  • データベース アプリケーションと SSMS の SET オプションは異なります。

この問題のトラブルシューティングを行うには、これらの手順に従います。

手順 1: 同じパラメーターまたは変数を使用してクエリが送信されていることを確認する

これらのクエリを比較し、すべての方法で同じであることを確認するには、次の手順に従います。

  1. SSMS を開き、使用しているデータベース エンジンに接続します。

  2. 次のコマンドを実行して、拡張イベント セッションを 作成します

    CREATE EVENT SESSION <EventSessionName> ON SERVER
    ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
    ADD EVENT sqlserver.login(SET collect_options_text=(1)
        ACTION(sqlserver.client_app_name)),
    ADD EVENT sqlserver.rpc_completed,
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION(sqlserver.client_app_name)),
    ADD EVENT sqlserver.sql_batch_completed(
        ACTION(sqlserver.client_app_name)),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION(sqlserver.client_app_name))
    ADD TARGET package0.event_file(SET filename=N'<FilePath>')
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 
    SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO
    

    Note

    プレースホルダー <EventSessionName> と <FilePath>を、作成したいもので置き換えます。

  3. 次のコマンドを実行して、セッション EventSessionNameを開始します。

    ALTER EVENT SESSION <EventSessionName> ON SERVER
    STATE = START
    
  4. クエリを実行して問題を再現します。

  5. 収集されたデータを分析するには、次のいずれかの方法を使用します。

    • Windows エクスプローラーを開き、ターゲット .xel ファイルを見つけてダブルクリックします。 ファイルは、表示および分析に使用できる別の SSMS ウィンドウで開かれます。

    • オブジェクト エクスプローラーで、Management>Extended Events>Sessions>EventSessionName を展開し、package0.event_fileを右クリックして、[ターゲット データの表示]... を選択します。

    • .xel ファイルの場所を検索し、関数sys.fn_xe_file_target_read_fileを使用してこのファイルを読み取ります。

  6. 次のイベントを確認して、 Field ステートメントを比較します。

    • sp_statement_completed
    • sql_batch_completed
    • sql_statement_completed
    • rpc_completed

同じクエリの詳細については、次の例を参照してください。

  • ストアド プロシージャまたは関数のパラメーター値が異なる場合、クエリ時間が異なる場合があります。

    • SpUserProc @p1 = 100

    • SpUserProc @p1 = 270

  • 次のクエリは異なります。 最初のクエリではカーディナリティ推定にヒストグラムの平均密度を使用し、2 番目のクエリではカーディナリティ推定にヒストグラム ステップを使用します。

    • declare @variable1 = 123
      select * from table where c1 = @variable1
      
    • select * from table where c1 = 123
      

上記と同じ理由で、ストアド プロシージャの実行と同等のアドホック クエリの実行 (ローカル変数を使用) の実行を比較すると、異なる場合があります。 同じステートメントを比較する必要があります。

手順 2: サーバーでの実行時間を測定する

クエリ期間を正確に比較するために、ネットワーク待機時間またはアプリケーション固有のデータ処理時間を除外できます。 SQL Server での実行時間のみを測定するには、次のいずれかの方法を使用します。

場合によっては、クエリ間の時間差は、1 つのアプリケーションが別のネットワークまたはアプリケーション自体で実行されていることが原因である可能性があります。 サーバーでの実行を比較すると、サーバーでのクエリの実行にかかった時間が比較されます。

手順 3: 各接続の SET オプションを確認する

クエリ プランに影響する SET オプション があります。つまり、クエリ プランの選択を変更できます。 そのため、データベース アプリケーションで SSMS とは異なるセット オプションを使用する場合、各セット オプションで異なるクエリ プランを取得できます。 たとえば、ARITHABORT、NUMERIC_ROUNDABORT、ROWCOUNT、FORCEPLAN、ANSI_NULLSなどです。 SSMS アプリケーションと .NET アプリケーションの間で見られる最も一般的な違いは、 SET ARITHABORT オプションです。 既定では、このオプションは SSMS では ON に設定されますが、ほとんどのデータベース アプリケーションでは OFF に設定されています。 アプリケーションのニーズに基づいて、SSMS とアプリケーションの両方で ARITHABORT を同じ設定に設定し、2 つの間で有効な比較を行います。

警告

SQL Server Management Studio の既定の ARITHABORT 設定は ON です。 ARITHABORT が OFF に設定されているクライアント アプリケーションは異なるクエリ プランを受け取り、パフォーマンスに問題のあるクエリのトラブルシューティングが困難になる場合があります。 つまり、同じクエリが Management Studio で高速に実行される可能性がありますが、アプリケーションでは低速です。 トラブルシューティングを行うとき、Management Studio を使用するクエリは常に、クライアントの ARITHABORT 設定と一致します。

プランに影響を与えるすべてのオプションの一覧については、「 設定オプションを参照してください。

SSMS とアプリケーションの両方の SET オプションが有効な比較を実行できるように同じであることを確認するには、次の手順に従います。

  1. 収集したデータは、 手順 1 で使用します。

  2. イベントの loginexisting_connection、具体的には options_text 列とオプション列を確認して、セット オプションを比較します。