移行後の検証および最適化ガイド

  • [アーティクル]

適用対象:SQL Server

SQL Server の移行後手順は、データの精度と完全性の調整、およびワークロードのパフォーマンスの問題の発見に、非常に重要です。

パフォーマンスの一般的なシナリオ

次に示すのは、SQL Server プラットフォームへの移行後に発生する一般的なパフォーマンスのシナリオと、その解決方法です。 SQL Server から SQL Server への移行 (古いバージョンから新しいバージョン) に固有のシナリオや、外部のプラットフォーム (Oracle、DB2、MySQL、Sybase など) から SQL Server への移行に固有のシナリオが含まれています。

CE バージョンの変更によるクエリ パフォーマンス低下

適用対象: SQL Server から SQL Server への移行。

古いバージョンの SQL Server から SQL Server 2014 (12.x) 以降に移行する場合、およびデータベース互換性レベルを使用可能な最新のものにアップグレードする場合、ワークロードのパフォーマンスが低下するリスクにさらされる可能性があります。

これは、SQL Server 2014 (12.x) 以降、すべてのクエリ オプティマイザーの変更が最新のデータベース互換性レベルと連携しているため、プランの変更は、アップグレードの時点ではなく、ユーザーが COMPATIBILITY_LEVEL のデータベース オプションを最新のものに変更した時点で発生するためです。 この機能とクエリ ストアの組み合わせによって、アップグレード プロセス中のクエリのパフォーマンスを高いレベルで制御できます。

SQL Server 2014 (12.x) で導入されたクエリ オプティマイザーの変更の詳細については、「SQL Server 2014 のカーディナリティ推定機能によるクエリ プランの最適化」を参照してください。

解決手順

データベース互換性レベルをソース バージョンに変更して、次の図に示すように推奨されるアップグレードのワークフローに従います。

Diagram showing the recommended upgrade workflow.

このトピックの詳細については、「SQL Server 2016 へのアップグレード中にパフォーマンスの安定性を維持する」を参照してください。

パラメーター スニッフィングに対する感度

適用対象: 外部プラットフォーム (Oracle、DB2、MySQL、Sybase など) から SQL Server への移行。

注意

SQL Server から SQL Server への移行の際、ソース SQL Server にこの問題が存在する場合は、そのまま新しいバージョンの SQL Server に移行しても、このシナリオに対処することはできません。

SQL Server は、最初のコンパイルで入力パラメーターのスニッフィングを使って、その入力データの分布に最適化された、パラメーター化された再利用可能なプランを生成することで、ストアド プロシージャのクエリ プランをコンパイルします。 ストアド プロシージャではない場合でも、単純なプランを生成するほとんどのステートメントがパラメーター化されます。 プランが最初にキャッシュされた後、それ以降の実行は前にキャッシュされたプランにマップします。 その最初のコンパイルで通常のワークロードに対する最も一般的なパラメーターのセットが使われないことがある場合、問題が発生する可能性があります。 異なるパラメーターに対して実行プランが同じでは非効率的になります。 このトピックの詳細については、「パラメーター スニッフィング」を参照してください。

解決手順

  1. RECOMPILE ヒントを使います。 プランは、各パラメーター値に適応されるたびに計算されます。
  2. (OPTIMIZE FOR(<input parameter> = <value>)) オプションを使うように、ストアド プロシージャを書き直します。 関連するワークロードのほとんどに適した値を決定し、パラメーター化された値に対して効率的になる 1 つのプランを作成して保守します。
  3. プロシージャ内でローカル変数を使うように、ストアド プロシージャを書き直します。 これで、オプティマイザーは予測に密度ベクトルを使うようになり、パラメーター値に関係なく同じプランになります。
  4. (OPTIMIZE FOR UNKNOWN) オプションを使うように、ストアド プロシージャを書き直します。 ローカル変数の手法を使う場合と同じ効果があります。
  5. DISABLE_PARAMETER_SNIFFING ヒントを使うようにクエリを書き直します。 OPTION(RECOMPILE)、WITH RECOMPILE、または OPTIMIZE FOR <value> が使われていない場合はパラメーター スニッフィングを完全に無効にすることで、ローカル変数の手法を使う場合と同じ効果があります。

ヒント

これが問題かどうかをすばやく識別するには、Management Studio のプラン分析機能を利用します。 詳細については、こちらをご覧ください。

欠落したインデックス

適用対象: 外部プラットフォーム (Oracle、DB2、MySQL、Sybase など) および SQL Server から SQL Server への移行。

正しくないインデックスまたは不足しているインデックスにより、余分な I/O が発生し、結果としてメモリや CPU が浪費されます。 原因は、ワークロード プロファイルが変更されて、異なる述語が使われるようになり、既存のインデックス設計が無効になったためである可能性があります。 不適切なインデックス戦略またはワークロード プロファイルの変更の証拠としては、次のようなものがあります。

  • 重複したインデックス、冗長なインデックス、ほとんど使われていないインデックス、およびまったく使われていないインデックスを探します。
  • 更新では使われていないインデックスに特に注意します。

解決手順

  1. 存在しないインデックス参照にはグラフィカル実行プランを利用します。
  2. データベース エンジン チューニング アドバイザーによって生成されたインデックスの提案。
  3. 不足インデックス DMV または SQL Server パフォーマンス ダッシュ ボードを利用します。
  4. 欠落、重複、冗長、低使用頻度、完全不使用のインデックスに関するインサイト、およびインデックス参照がデータベースの既存のプロシージャにヒント/ハードコーディングされているかどうかに関するインサイトを提供する既存の DMV を使用できる既存のスクリプトを活用します。

ヒント

既存のスクリプトの例としては、Index Creation や Index Information などがあります。

述語を使ってデータをフィルターできない

適用対象: 外部プラットフォーム (Oracle、DB2、MySQL、Sybase など) および SQL Server から SQL Server への移行。

注意

SQL Server から SQL Server への移行の際、ソース SQL Server にこの問題が存在する場合は、そのまま新しいバージョンの SQL Server に移行しても、このシナリオに対処することはできません。

SQL Server クエリ オプティマイザーは、コンパイル時に認識されている情報のみを考慮することができます。 ワークロードが実行時にのみ認識できる述語に依存する場合は、不適切なプランの選択が増える可能性があります。 高品質のプランでは、述語は SARGable (Search Argumentable: 検索引数化可能 ) である必要があります。

SARGable ではない述語の例を次に示します。

  • VARCHAR から NVARCHAR、INT から VARCHAR のような暗黙的なデータ変換。 実際の実行プランで実行時の CONVERT_IMPLICIT 警告を探します。 型を変換すると、精度が失われるをこともあります。
  • WHERE UnitPrice + 1 < 3.975 などの複雑な不定式。WHERE UnitPrice < 320 * 200 * 32 は違います。
  • WHERE ABS(ProductID) = 771 や WHERE UPPER(LastName) = 'Smith' などの関数を使う式
  • WHERE LastName LIKE '%Smith' のような先頭にワイルドカード文字がある文字列。WHERE LastName LIKE 'Smith%' は違います。

解決手順

  1. 常に目的のターゲットデータ型として変数/パラメーターを宣言します。
  • これには、データベースに格納されるユーザー定義のコード構造 (ストアド プロシージャ、ユーザー定義関数、ビューなど) と、基になるテーブルで使われるデータ型についての情報を保持するシステム テーブル (sys.columns など) の比較が含まれる場合があります。
  1. 前のポイントまですべてのコードをスキャンできない場合は、同じ目的で、変数/パラメーターの宣言と一致するように、テーブルのデータ型を変更します。
  2. 次の構造の有用性を熟考します。
  • 述語として使われている関数
  • ワイルドカード検索
  • 列データに基づく複雑な式 – インデックスを作成できる永続計算列を代わりに作成する必要性を評価します。

注意

上記のすべてをプログラムで実行することができます。

テーブル値関数の使用 (複数ステートメントとインライン)

適用対象: 外部プラットフォーム (Oracle、DB2、MySQL、Sybase など) および SQL Server から SQL Server への移行。

注意

SQL Server から SQL Server への移行の際、ソース SQL Server にこの問題が存在する場合は、そのまま新しいバージョンの SQL Server に移行しても、このシナリオに対処することはできません。

テーブル値関数は、ビューの代わりになるテーブル データ型を返します。 ビューは 1 つの SELECT ステートメントに制限されますが、ユーザー定義関数はビューより多くのロジックを許される追加ステートメントを含むことができます。

重要

MSTVF (複数ステートメントのテーブル値関数) の出力テーブルはコンパイル時に作成されないので、SQL Server クエリ オプティマイザーは実際の統計ではなくヒューリスティックに依存して、行の推定を決定します。 基底テーブルにインデックスを追加しても、役には立ちません。 MSTVF の場合、 SQL Server では MSTVF によって返されるものと予想される行数に固定推定値 1 を使います (固定推定値が 100 行の SQL Server 2014 (12.x) 以降)。

解決手順

  1. 複数ステートメントの TVF が 1 ステートメントのみである場合は、インライン TVF に変換します。

    CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
RETURNS @tblAddress TABLE
([Address] VARCHAR(60) NOT NULL)
AS
BEGIN
  INSERT INTO @tblAddress ([Address])
  SELECT TOP 1 [AddressLine1]
  FROM [Person].[Address]
  WHERE  AddressID = @ID
  ORDER BY [ModifiedDate] DESC
RETURN
END

    インライン形式の例を次に示します。

    CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int)
RETURNS TABLE
AS
RETURN (
  SELECT TOP 1 [AddressLine1] AS [Address]
  FROM [Person].[Address]
  WHERE  AddressID = @ID
  ORDER BY [ModifiedDate] DESC
)

  2. さらに複雑な場合は、メモリ最適化テーブルまたは一時テーブルに格納される中間結果を使うことを検討します。

追加の参考資料

クエリ ストアを使用する際の推奨事項
メモリ最適化テーブル
ユーザー定義関数
テーブル変数と行の推定 - パート 1
テーブル変数と行の推定 - パート 2
実行プランのキャッシュと再利用