2 つのサーバー間のパフォーマンスの大きな違いを示すクエリのトラブルシューティング

[アーティクル]
06/20/2023

適用対象: SQL Server

この記事では、あるサーバーでクエリの実行速度が別のサーバーよりも遅くなるパフォーマンスの問題のトラブルシューティング手順について説明します。

現象

SQL Serverがインストールされている 2 つのサーバーがあるとします。 SQL Serverインスタンスの 1 つに、もう一方のSQL Server インスタンス内のデータベースのコピーが含まれています。両方のサーバー上のデータベースに対してクエリを実行すると、一方のサーバーでもう一方のサーバーよりもクエリの実行速度が低下します。

次の手順は、この問題のトラブルシューティングに役立ちます。

手順 1: 複数のクエリで一般的な問題であるかどうかを判断する

次の 2 つの方法のいずれかを使用して、2 つのサーバー上の 2 つ以上のクエリのパフォーマンスを比較します。

両方のサーバーでクエリを手動でテストします。
1. 次のようなクエリに優先順位を付けてテストするために、いくつかのクエリを選択します。
  - 一方のサーバーでは、もう一方のサーバーよりも大幅に高速です。
  - ユーザー/アプリケーションにとって重要です。
  - 問題をオンデマンドで再現するために頻繁に実行または設計されています。
  - データをキャプチャするのに十分な時間 (たとえば、5 ミリ秒のクエリではなく、10 秒のクエリを選択するなど)。
2. 2 つのサーバーでクエリを実行します。
3. クエリごとに 2 つのサーバーの経過時間 (期間) を比較します。
SQL Nexus を使用してパフォーマンスデータを分析します。
1. 2 つのサーバー上のクエリの PSSDiag/SQLdiag または SQL LogScout データを収集します。
2. 収集したデータファイルを SQL Nexus でインポートし、2 つのサーバーからのクエリを比較します。詳細については、「 2 つのログコレクション間のパフォーマンスの比較 (低速と高速など)」を参照してください。

シナリオ 1: 2 つのサーバーで実行されるクエリは 1 つだけです

実行するクエリが 1 つだけ異なる場合、問題は環境ではなく個々のクエリに固有である可能性が高くなります。この場合は、「手順 2: データを収集する」に進み、パフォーマンスの問題の種類を特定します。

シナリオ 2: 2 つのサーバーで複数のクエリの実行が異なる

1 つのサーバーで複数のクエリの実行速度が他方よりも遅い場合、最も可能性の高い原因は、サーバーまたはデータ環境の違いです。 [ 環境の違いの診断] に移動し、2 つのサーバー間の比較が有効かどうかを確認します。

手順 2: データを収集し、パフォーマンスの問題の種類を特定する

経過時間、CPU 時間、論理読み取りを収集する

両方のサーバーでクエリの経過時間と CPU 時間を収集するには、状況に最適な次のいずれかの方法を使用します。

現在実行中のステートメントの場合は、sys.dm_exec_requests内のtotal_elapsed_time列とcpu_time列をチェックします。次のクエリを実行してデータを取得します。

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

クエリの過去の実行については、sys.dm_exec_query_statsの列last_elapsed_timeとlast_worker_timeをチェックします。次のクエリを実行してデータを取得します。

SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
     qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
     qs.total_logical_writes / qs.execution_count AS avg_writes,
     (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

注:

負の値を示す場合 avg_wait_time は、並列クエリです。

SQL Server Management Studio (SSMS) または Azure Data Studio でオンデマンドでクエリを実行できる場合は、SET STATISTICS TIMEON と SET STATISTICS IOON を使用してクエリを実行します。

SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

次に、[ メッセージ] から、CPU 時間、経過時間、論理読み取りが次のように表示されます。

  Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

クエリプランを収集できる場合は、[実行プラン] プロパティからデータをチェックします。
1. [実際の実行プランを含める] をオンにしてクエリを実行します。
2. [実行プラン] から左端の演算子を選択します。
3. [プロパティ] で、[QueryTimeStats] プロパティを展開します。
4. ElapsedTime と CpuTime を確認します。

クエリの経過時間と CPU 時間を比較して、両方のサーバーの問題の種類を判断します。

型 1: CPU バインド (ランナー)

CPU 時間が経過時間に近い、等しい、またはそれより長い場合は、CPU バインドクエリとして扱うことができます。たとえば、経過時間が 3000 ミリ秒 (ms) で、CPU 時間が 2900 ミリ秒の場合、経過時間の大部分が CPU に費やされます。その後、CPU バインドクエリと言うことができます。

実行 (CPU バインド) クエリの例:

経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
3200	3000	300000
1080	1000	20

論理読み取り ( キャッシュ内のデータ/インデックスページの読み取り) は、SQL Serverにおける CPU 使用率の最も高い要因です。 CPU の使用は、while ループ (T-SQL や XProcs や SQL CRL オブジェクトなどの他のコード) の他のソースから取得される場合があります。表の 2 番目の例は、CPU の大部分が読み取りからのものではありませんが、このようなシナリオを示しています。

注:

CPU 時間が期間を超える場合は、並列クエリが実行されていることを示します。複数のスレッドが同時に CPU を使用しています。詳細については、「並列クエリ - ランナーまたはウェイター」を参照してください。

タイプ 2: ボトルネックを待機している (ウェイター)

経過時間が CPU 時間を大幅に超える場合、クエリはボトルネックを待機しています。経過時間には、CPU でクエリを実行する時間 (CPU 時間) と、リソースが解放されるのを待つ時間 (待機時間) が含まれます。たとえば、経過時間が 2000 ミリ秒で CPU 時間が 300 ミリ秒の場合、待機時間は 1700 ミリ秒 (2000 - 300 = 1700) です。詳細については、「待機の種類」を参照してください。

待機クエリの例:

経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
2000	300	28000
10080	700	80000

並列クエリ - ランナーまたはウェイター

並列クエリでは、全体的な期間よりも多くの CPU 時間が使用される場合があります。並列処理の目的は、複数のスレッドがクエリの一部を同時に実行できるようにすることです。 1 秒のクロック時間で、クエリでは 8 つの並列スレッドを実行することで 8 秒の CPU 時間を使用できます。そのため、経過時間と CPU 時間差に基づいて CPU バインドまたは待機クエリを決定することが困難になります。ただし、一般的な規則として、上記の 2 つのセクションに記載されている原則に従ってください。概要は次のとおりです。

経過時間が CPU 時間よりもはるかに長い場合は、ウェイターと見なします。
CPU 時間が経過時間よりもはるかに長い場合は、ランナーと見なします。

並列クエリの例:

経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
1200	8100	850000
3080	12300	1500000

手順 3: 両方のサーバーのデータを比較し、シナリオを把握し、問題のトラブルシューティングを行う

Server1 と Server2 という名前の 2 つのマシンがあるとします。また、Server1 では Server2 よりもクエリの実行速度が遅くなります。両方のサーバーの時間を比較し、次のセクションのシナリオに最も一致するシナリオのアクションに従います。

シナリオ 1: Server1 のクエリでは CPU 時間が多く、論理読み取りは Server1 の方が Server2 よりも高い

Server1 の CPU 時間が Server2 よりもはるかに長く、経過時間が両方のサーバーの CPU 時間と密接に一致する場合、大きな待機やボトルネックはありません。 Server1 での CPU 時間の増加は、論理読み取りの増加が原因である可能性が最も高くなります。論理読み取りの大幅な変更は、通常、クエリプランの違いを示します。例:

サーバー	経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
Server1	3100	3000	300000
Server2	1100	1000	90200

アクション: 実行プランと環境を確認する

両方のサーバーでクエリの実行プランを比較します。これを行うには、次の 2 つの方法のいずれかを使用します。
- 実行プランを視覚的に比較します。詳細については、「実際の実行プランを表示する」を参照してください。
- プラン比較機能を使用して、実行プランSQL Server Management Studio保存し、比較します。
環境を比較します。環境が異なると、クエリプランの違いや CPU 使用率の直接的な違いが生じる可能性があります。環境には、サーバーバージョン、データベースまたはサーバー構成設定、トレースフラグ、CPU 数またはクロック速度、仮想マシンと物理マシンが含まれます。詳細については、「クエリプランの相違点を診断する」を参照してください。

シナリオ 2: クエリは Server1 のウェイターですが、Server2 では待機者ではありません

両方のサーバーのクエリの CPU 時間が似ているものの、Server1 の経過時間が Server2 よりもずっと長い場合、Server1 のクエリはボトルネックの待機にはるかに長い時間を費やします。例:

サーバー	経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
Server1	4500	1000	90200
Server2	1100	1000	90200

Server1 の待機時間: 4500 - 1000 = 3500 ミリ秒
Server2 の待機時間: 1100 - 1000 = 100 ミリ秒

アクション: Server1 で待機の種類を確認する

Server1 のボトルネックを特定して排除します。待機の例としては、ブロック (ロック待機)、ラッチ待機、ディスク I/O 待機、ネットワーク待機、メモリ待機などがあります。一般的なボトルネックの問題をトラブルシューティングするには、「待機またはボトルネックの診断」に進みます。

シナリオ 3: 両方のサーバーのクエリは待機者ですが、待機の種類または時間は異なります

例:

サーバー	経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
Server1	8000	1000	90200
Server2	3000	1000	90200

Server1 の待機時間: 8000 - 1000 = 7000 ミリ秒
Server2 の待機時間: 3000 - 1000 = 2000 ミリ秒

この場合、CPU 時間は両方のサーバーで似ています。これは、クエリプランが同じ可能性があることを示します。ボトルネックを待機しない場合、クエリは両方のサーバーで均等に実行されます。そのため、期間の違いは、さまざまな待機時間から生じます。たとえば、クエリは Server1 のロックを 7000 ミリ秒待機し、Server2 の I/O で 2000 ミリ秒待機します。

アクション: 両方のサーバーで待機の種類を確認する

各サーバーで各ボトルネック待機に個別に対処し、両方のサーバーでの実行を高速化します。この問題のトラブルシューティングは、両方のサーバーのボトルネックを排除し、パフォーマンスを同等にする必要があるため、手間がかかります。一般的なボトルネックの問題をトラブルシューティングするには、「待機またはボトルネックの診断」に進みます。

シナリオ 4: Server1 のクエリでは、Server2 よりも多くの CPU 時間が使用されますが、論理読み取りは近い

例:

サーバー	経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
Server1	3000	3000	90200
Server2	1000	1000	90200

データが次の条件に一致する場合:

Server1 の CPU 時間は、Server2 よりもはるかに長くなります。
経過時間は、各サーバーの CPU 時間と密接に一致します。これは待機がないことを示します。
論理読み取り (通常は CPU 時間の最も高いドライバー) は、両方のサーバーで似ています。

その後、追加の CPU 時間は、他の CPU バインドアクティビティから取得されます。このシナリオは、すべてのシナリオで最もまれです。

原因: トレース、UDF、CLR 統合

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

XEvents/SQL Server トレース。特に、テキスト列 (データベース名、ログイン名、クエリテキストなど) に対するフィルター処理を使用します。トレースが 1 つのサーバーで有効になっているが、他方では有効になっていない場合は、これが違いの原因である可能性があります。
CPU バインド操作を実行するユーザー定義関数 (UDF) またはその他の T-SQL コード。これは通常、データサイズ、CPU クロック速度、電源プランなど、Server1 と Server2 で他の条件が異なる場合に発生します。
SQL SERVER CLR 統合または拡張ストアドプロシージャ (XP) は、CPU を駆動する可能性がありますが、論理読み取りを実行しません。 DLL の違いにより、CPU 時間が異なる場合があります。
CPU バインドSQL Server機能の違い (文字列操作コードなど)。

アクション: トレースとクエリを確認する

両方のサーバーでトレースを確認し、次の点を確認します。
1. Server1 で有効になっているトレースがあるが、Server2 では有効になっていない場合。
2. トレースが有効になっている場合は、トレースを無効にして、Server1 でクエリを再実行します。
3. 今回クエリの実行速度が速い場合は、トレースを有効に戻しますが、テキストフィルターがある場合は削除します。
クエリで文字列操作を行う UDF を使用するか、リスト内のデータ列に対して広範な処理を SELECT 行うかどうかを確認します。
クエリにループ、関数再帰、または入れ子が含まれているかどうかを確認します。

環境の違いを診断する

次の質問を確認し、2 つのサーバー間の比較が有効かどうかを判断します。

2 つのSQL Server インスタンスのバージョンまたはビルドは同じですか?

そうでない場合は、違いの原因となるいくつかの修正が発生する可能性があります。次のクエリを実行して、両方のサーバーのバージョン情報を取得します。
```
SELECT @@VERSION
```
両方のサーバーで物理メモリの量は似ていますか?

一方のサーバーに 64 GB のメモリがあり、もう一方のサーバーに 256 GB のメモリがある場合、大きな違いになります。データ/インデックスページとクエリプランをキャッシュするために使用できるメモリが増えるので、ハードウェアリソースの可用性に基づいてクエリを異なる方法で最適化できます。
CPU 関連のハードウェア構成は両方のサーバーで似ていますか? 例:
- CPU の数は、マシンによって異なります (1 台のコンピューターでは 24 個の CPU、もう一方のコンピューターでは 96 個の CPU)。
- 電源プラン - バランスの取れた高パフォーマンス。
- 仮想マシン (VM) と物理 (ベアメタル) マシン。
- Hyper-V と VMware の構成の違い。
- クロック速度の違い (クロック速度の低下とクロック速度の向上)。たとえば、2 GHz と 3.5 GHz では違いが生じます。サーバーでクロック速度を取得するには、次の PowerShell コマンドを実行します。
```
Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
```
サーバーの CPU 速度をテストするには、次の 2 つの方法のいずれかを使用します。同等の結果が得られない場合、問題はSQL Server外です。これは、電源プランの違い、CPU の数の減少、VM ソフトウェアの問題、またはクロック速度の違いである可能性があります。
- 両方のサーバーで次の PowerShell スクリプトを実行し、出力を比較します。
```
$bf = [System.DateTime]::Now
for ($i = 0; $i -le 20000000; $i++) {}
$af = [System.DateTime]::Now
Write-Host ($af - $bf).Milliseconds " milliseconds"
Write-Host ($af - $bf).Seconds " Seconds"
```
- 両方のサーバーで次の Transact-SQL コードを実行し、出力を比較します。
```
SET NOCOUNT ON 
DECLARE @spins INT = 0
DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT

WHILE (@spins < 20000000)
BEGIN
   SET @spins = @spins +1
END

SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate())

SELECT @spins Spins, @time_millisecond Time_ms,  @spins / @time_millisecond Spins_Per_ms
```

待機またはボトルネックを診断する

ボトルネックを待機しているクエリを最適化するには、待機の時間とボトルネックの場所 (待機の種類) を特定します。待機の種類が確認されたら、待機時間を短縮するか、待機を完全に排除します。

おおよその待機時間を計算するには、クエリの経過時間から CPU 時間 (ワーカー時間) を減算します。通常、CPU 時間は実際の実行時間であり、クエリの有効期間の残りの部分は待機しています。

おおよその待機時間を計算する方法の例:

経過時間 (ms)	CPU 時間 (ミリ秒)	待機時間 (ミリ秒)
3200	3000	200
7080	1000	6080

ボトルネックの特定または待機

待機時間の長いクエリの履歴 (たとえば、経過時間全体の 20% が待機時間) を特定するには、 >次のクエリを実行します。このクエリでは、SQL Serverの開始以降、キャッシュされたクエリプランのパフォーマンス統計が使用されます。

SELECT t.text,
         qs.total_elapsed_time / qs.execution_count
         AS avg_elapsed_time,
         qs.total_worker_time / qs.execution_count
         AS avg_cpu_time,
         (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
         AS avg_wait_time,
         qs.total_logical_reads / qs.execution_count
         AS avg_logical_reads,
         qs.total_logical_writes / qs.execution_count
         AS avg_writes,
         qs.total_elapsed_time
         AS cumulative_elapsed_time
FROM sys.dm_exec_query_stats qs
         CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
         > 0.2
ORDER BY qs.total_elapsed_time / qs.execution_count DESC

待機時間が 500 ミリ秒を超える現在実行中のクエリを特定するには、次のクエリを実行します。

SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
FROM sys.dm_exec_requests r 
   JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
WHERE wait_time > 500
AND is_user_process = 1

クエリプランを収集できる場合は、SSMS の実行プランプロパティから WaitStats をチェックします。
1. [実際の実行プランを含める] をオンにしてクエリを実行します。
2. [ 実行プラン ] タブで、左端の演算子を右クリックします
3. [ プロパティ] を選択し、[ WaitStats ] プロパティを選択します。
4. WaitTimeMs と WaitType を確認します。
PSSDiag/SQLdiag または SQL LogScout LightPerf/GeneralPerf シナリオに精通している場合は、どちらかを使用してパフォーマンス統計を収集し、SQL Server インスタンスで待機中のクエリを特定することを検討してください。収集されたデータファイルをインポートし、 SQL Nexus を使用してパフォーマンスデータを分析できます。

待機の排除または削減に役立つ参照

待機の種類ごとに原因と解決策が異なります。すべての待機の種類を解決する一般的な方法はありません。一般的な待機の種類に関する問題のトラブルシューティングと解決に関する記事を次に示します。

多くの待機の種類とそれらが示す内容の説明については、「待機の種類」の表を参照してください。

クエリプランの違いを診断する

クエリプランの違いの一般的な原因を次に示します。

データサイズまたはデータ値の違い

両方のサーバーで同じデータベースが使用されていますか。同じデータベースバックアップを使用していますか? データは、あるサーバーで他のサーバーと比較して変更されていますか? データの違いにより、クエリプランが異なる場合があります。たとえば、テーブル T1 (1000 行) とテーブル T2 (2,000,000 行) の結合は、テーブル T1 (100 行) とテーブル T2 の結合 (2,000,000 行) とは異なります。操作の JOIN 種類と速度は大きく異なる場合があります。
統計の違い

一方のデータベースで統計が更新され、もう一方のデータベースでは更新されていませんか? 別のサンプルレート (たとえば、30% と 100% のフルスキャン) で統計が更新されていますか? 両方の側の統計を同じサンプルレートで更新してください。
データベース互換性レベルの違い

データベースの互換性レベルが 2 つのサーバー間で異なるかどうかを確認します。データベース互換性レベルを取得するには、次のクエリを実行します。
```
SELECT name, compatibility_level
FROM sys.databases
WHERE name = '<YourDatabase>'
```
サーバーのバージョン/ビルドの違い

SQL Serverのバージョンまたはビルドは、2 つのサーバー間で異なりますか? たとえば、1 つのサーバー SQL Serverバージョン 2014 と、もう 1 つのSQL Server バージョン 2016 ですか。製品の変更により、クエリプランの選択方法が変更される可能性があります。同じバージョンとSQL Serverのビルドを比較してください。
```
SELECT ServerProperty('ProductVersion')
```
カーディナリティ推定 (CE) バージョンの違い

レガシカーディナリティ推定機能がデータベースレベルでアクティブ化されているかどうかを確認します。 CE の詳細については、「カーディナリティ推定 (SQL Server)」を参照してください。
```
SELECT name, value, is_value_default
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'
```
オプティマイザー修正プログラムの有効化/無効化

クエリオプティマイザー修正プログラムが 1 つのサーバーで有効になっているが、もう一方のサーバーで無効になっている場合は、異なるクエリプランを生成できます。詳細については、「クエリオプティマイザー修正プログラムトレースフラグ 4199 サービスモデルSQL Server」を参照してください。

クエリオプティマイザー修正プログラムの状態を取得するには、次のクエリを実行します。
```
-- Check at server level for TF 4199
DBCC TRACESTATUS (-1)
-- Check at database level
USE <YourDatabase>
SELECT name, value, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'QUERY_OPTIMIZER_HOTFIXES'
```
トレースフラグの違い

一部のトレースフラグは、クエリプランの選択に影響します。もう一方のサーバーで有効になっていないトレースフラグが有効になっているかどうかを確認します。両方のサーバーで次のクエリを実行し、結果を比較します。
```
-- Check at server level for trace flags
DBCC TRACESTATUS (-1)
```
ハードウェアの違い (CPU 数、メモリサイズ)

ハードウェア情報を取得するには、次のクエリを実行します。
```
SELECT cpu_count, physical_memory_kb/1024/1024 PhysicalMemory_GB 
FROM sys.dm_os_sys_info
```

クエリオプティマイザーに従ったハードウェアの違い

クエリプランのを OptimizerHardwareDependentProperties 確認し、ハードウェアの違いが異なるプランで重要と見なされるかどうかを確認します。

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
  txt.text,
  t.OptHardw.value('@EstimatedAvailableMemoryGrant', 'INT') AS EstimatedAvailableMemoryGrant , 
  t.OptHardw.value('@EstimatedPagesCached', 'INT') AS EstimatedPagesCached, 
  t.OptHardw.value('@EstimatedAvailableDegreeOfParallelism', 'INT') AS EstimatedAvailDegreeOfParallelism,
  t.OptHardw.value('@MaxCompileMemory', 'INT') AS MaxCompileMemory
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//OptimizerHardwareDependentProperties') AS t(OptHardw)
CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) txt
WHERE text Like '%<Part of Your Query>%'

オプティマイザーのタイムアウト

オプティマイザーのタイムアウトの問題はありますか? クエリオプティマイザーは、実行されるクエリが複雑すぎる場合、プランオプションの評価を停止できます。停止すると、その時点で使用可能なコストが最も低いプランが選択されます。これにより、あるサーバーと別のサーバーで任意のプランの選択が行われる可能性があります。
SET オプション

SET ARITHABORT など、一部の SET オプションはプランに影響します。詳細については、「 SET オプション」を参照してください。
クエリヒントの違い

1 つのクエリではクエリヒントが使用され、もう 1 つのクエリは使用されませんか? クエリテキストを手動で確認して、クエリヒントの存在を確立します。
パラメーターに依存するプラン (パラメータースニッフィングの問題)

まったく同じパラメーター値を使用してクエリをテストしていますか? そうでない場合は、そこから開始できます。プランは、別のパラメーター値に基づいて 1 つのサーバーで以前にコンパイルされましたか? RECOMPILE クエリヒントを使用して 2 つのクエリをテストし、プランの再利用が行われないようにします。詳細については、「パラメーターに依存する問題を調査して解決する」を参照してください。

さまざまなデータベースオプション/スコープ構成設定

両方のサーバーで同じデータベースオプションまたはスコープ構成設定が使用されていますか? 一部のデータベースオプションは、プランの選択に影響を与える可能性があります。たとえば、データベースの互換性、レガシ CE と既定の CE、パラメータースニッフィングなどです。 1 つのサーバーから次のクエリを実行して、2 つのサーバーで使用されるデータベースオプションを比較します。

-- On Server1 add a linked server to Server2 
EXEC master.dbo.sp_addlinkedserver @server = N'Server2', @srvproduct=N'SQL Server'

-- Run a join between the two servers to compare settings side by side
SELECT 
   s1.name AS srv1_config_name, 
   s2.name AS srv2_config_name,
   s1.value_in_use AS srv1_value_in_use, 
   s2.value_in_use AS srv2_value_in_use, 
   Variance = CASE WHEN ISNULL(s1.value_in_use, '##') != ISNULL(s2.value_in_use,'##') THEN 'Different' ELSE '' END
FROM sys.configurations s1 
FULL OUTER JOIN [server2].master.sys.configurations s2 ON s1.name = s2.name


SELECT 
   s1.name AS srv1_config_name,
   s2.name AS srv2_config_name,
   s1.value srv1_value_in_use,
   s2.value srv2_value_in_use,
   s1.is_value_default,
   s2.is_value_default,
   Variance = CASE WHEN ISNULL(s1.value, '##') != ISNULL(s2.value, '##') THEN 'Different' ELSE '' END
FROM sys.database_scoped_configurations s1
FULL OUTER JOIN [server2].master.sys.database_scoped_configurations s2 ON s1.name = s2.name

プランガイド

プランガイドは、あるサーバー上のクエリに使用されますが、もう一方のサーバーでは使用されませんか? 次のクエリを実行して、違いを確立します。
```
SELECT * FROM sys.plan_guides
```

2 つのサーバー間のパフォーマンスの大きな違いを示すクエリのトラブルシューティング

現象

手順 1: 複数のクエリで一般的な問題であるかどうかを判断する

シナリオ 1: 2 つのサーバーで実行されるクエリは 1 つだけです

シナリオ 2: 2 つのサーバーで複数のクエリの実行が異なる

手順 2: データを収集し、パフォーマンスの問題の種類を特定する

経過時間、CPU 時間、論理読み取りを収集する

型 1: CPU バインド (ランナー)

タイプ 2: ボトルネックを待機している (ウェイター)

並列クエリ - ランナーまたはウェイター

手順 3: 両方のサーバーのデータを比較し、シナリオを把握し、問題のトラブルシューティングを行う

シナリオ 1: Server1 のクエリでは CPU 時間が多く、論理読み取りは Server1 の方が Server2 よりも高い

アクション: 実行プランと環境を確認する

シナリオ 2: クエリは Server1 のウェイターですが、Server2 では待機者ではありません

アクション: Server1 で待機の種類を確認する

シナリオ 3: 両方のサーバーのクエリは待機者ですが、待機の種類または時間は異なります

アクション: 両方のサーバーで待機の種類を確認する

シナリオ 4: Server1 のクエリでは、Server2 よりも多くの CPU 時間が使用されますが、論理読み取りは近い

原因: トレース、UDF、CLR 統合

アクション: トレースとクエリを確認する

環境の違いを診断する

待機またはボトルネックを診断する

ボトルネックの特定または待機

待機の排除または削減に役立つ参照

クエリプランの違いを診断する

フィードバック

フィードバック

その他のリソース

2 つのサーバー間のパフォーマンスの大きな違いを示すクエリのトラブルシューティング

現象

手順 1: 複数のクエリで一般的な問題であるかどうかを判断する

シナリオ 1: 2 つのサーバーで実行されるクエリは 1 つだけです

シナリオ 2: 2 つのサーバーで複数のクエリの実行が異なる

手順 2: データを収集し、パフォーマンスの問題の種類を特定する

経過時間、CPU 時間、論理読み取りを収集する

型 1: CPU バインド (ランナー)

タイプ 2: ボトルネックを待機している (ウェイター)

並列クエリ - ランナーまたはウェイター

手順 3: 両方のサーバーのデータを比較し、シナリオを把握し、問題のトラブルシューティングを行う

シナリオ 1: Server1 のクエリでは CPU 時間が多く、論理読み取りは Server1 の方が Server2 よりも高い

アクション: 実行プランと環境を確認する

シナリオ 2: クエリは Server1 のウェイターですが、Server2 では待機者ではありません

アクション: Server1 で待機の種類を確認する

シナリオ 3: 両方のサーバーのクエリは待機者ですが、待機の種類または時間は異なります

アクション: 両方のサーバーで待機の種類を確認する

シナリオ 4: Server1 のクエリでは、Server2 よりも多くの CPU 時間が使用されますが、論理読み取りは近い

原因: トレース、UDF、CLR 統合

アクション: トレースとクエリを確認する

環境の違いを診断する

待機またはボトルネックを診断する

ボトルネックの特定または待機

待機の排除または削減に役立つ参照

クエリ プランの違いを診断する

フィードバック

フィードバック

その他のリソース

クエリプランの違いを診断する