データベース アプリケーションと 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

    注:

    プレースホルダー EventSessionName と FilePath> を、作成するプレースホルダー<に置き換<えます。>

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

    ALTER EVENT SESSION <EventSessionName> ON SERVER
STATE = START

  4. クエリを実行して問題を再現します。

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

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

    • オブジェクト エクスプローラーで、[管理>拡張イベント>セッション]>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の実行時間のみを測定します。

  • SET STATISTICS TIME を使用してクエリを実行します。

    SET STATISTICS TIME ON
<YourQuery>
SET STATISTICS TIME OFF

  • 手順 1 の XEvent を使用して、クエリの期間または経過時間 (イベント クラスSQL:StmtCompleted、、SQL:BatchCompletedまたは RPC:Completed) を調べます。

場合によっては、異なるネットワークまたはアプリケーション自体で実行されている 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. イベント login と 、具体的には オプション列と existing_connectionオプション列をチェックして、セット オプションを options_text 比較します。