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

適用対象: 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 のカーディナリティ推定機能によるクエリ プランの最適化」を参照してください。

CE の詳細については、「カーディナリティ推定 (SQL Server)」を参照してください。

解決手順

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

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

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

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

Note

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 のプラン分析機能を使用します。 詳細については、「SSMS の新機能: クエリ パフォーマンスのトラブルシューティングが簡単になりました」を参照してください。

欠落したインデックス

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

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

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

解決手順

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

ヒント

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

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

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

Note

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 (Transact-SQL) など) の比較が含まれる場合があります。

2. 前のポイントまですべてのコードをスキャンできない場合は、同じ目的で、変数/パラメーターの宣言と一致するように、テーブルのデータ型を変更します。

3. 次の構造の有用性を熟考します。

   • 述語として使われている関数
   • ワイルドカード検索
   • 列データに基づく複雑な式 – インデックスを作成できる永続計算列を代わりに作成する必要性を評価します。

Note

これらのすべての手順をプログラムで実行できます。

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

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

Note

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. MSTVF が単一ステートメントのみの場合は、インライン テーブル値関数に変換します。

   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
• 実行プランのキャッシュと再利用