次の方法で共有

クエリ プランの移行

ほとんどの場合、データベースを最新バージョンの SQL Server にアップグレードすると、クエリのパフォーマンスが向上します。 ただし、パフォーマンスのために慎重に調整されたミッション クリティカルなクエリがある場合は、各クエリのプラン ガイドを作成してアップグレードする前に、これらのクエリのクエリ プランを保持することをお勧めします。 アップグレード後に、クエリ オプティマイザーが 1 つ以上のクエリに対して効率の低いプランを選択した場合は、プラン ガイドを有効にして、クエリ オプティマイザーにアップグレード前プランの使用を強制できます。

アップグレードする前にプラン ガイドを作成するには、次の手順に従います。

  1. sp_create_plan_guide ストアド プロシージャを使用し、USE PLAN クエリ ヒントでクエリ プランを指定して、各ミッション クリティカルなクエリの現在のプランを記録します。

  2. プラン ガイドがクエリに適用されていることを確認します。

  3. データベースを新しいバージョンの SQL Server にアップグレードします。

    プランは、プラン ガイドのアップグレードされたデータベースに保持され、アップグレード後のプランの回帰の場合のフォールバックとして機能します。

    アップグレード後にプラン ガイドを有効にしないことをお勧めします。これは、新しいリリースでのより優れたプランや、更新された統計による有益な再コンパイルの機会を逃す可能性があるためです。

  4. アップグレード後に効率の低いプランを選択した場合は、プラン ガイドのすべてまたはサブセットをアクティブにして、新しいプランをオーバーライドします。

例

次の例は、プラン ガイドを作成して、クエリのアップグレード前プランを記録する方法を示しています。

手順 1: プランを収集する

プラン ガイドに記録されるクエリ プランは、XML 形式である必要があります。 XML 形式のクエリ プランは、次の方法で生成できます。

次の例では、動的管理ビューにクエリを実行して、ステートメント SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; のクエリ プランを収集します。

USE AdventureWorks;  
GO  
SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';  
GO

手順 2: プラン ガイドを作成してプランを強制する

プラン ガイドの XML 形式のクエリ プラン (前述のメソッドのいずれかによって取得) を使用して、クエリ プランをコピーし、sp_create_plan_guideの OPTION 句で指定された USE PLAN クエリ ヒント内の文字列リテラルとして貼り付けます。

XML プラン自体内で、プラン ガイドを作成する前に、2 番目の引用符でプランに表示される引用符 (') をエスケープします。 たとえば、 WHERE A.varchar = 'This is a string' を含むプランは、コードを WHERE A.varchar = ''This is a string''に変更してエスケープする必要があります。

次の例では、手順 1 で収集したクエリ プランのプラン ガイドを作成し、クエリの XML プラン表示を @hints パラメーターに挿入します。 簡潔にするために、この例には部分的なプラン表示出力のみが含まれています。

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@type = N'SQL',  
@module_or_batch = NULL,  
@params = NULL,  
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan''''   
    Version=''''0.5'''' Build=''''9.00.1116''''>  
    <BatchSequence><Batch><Statements><StmtSimple>  
    ...  
    </StmtSimple></Statements></Batch>  
    </BatchSequence></ShowPlanXML>'')';  
GO

手順 3: プラン ガイドがクエリに適用されていることを確認する

クエリをもう一度実行し、生成されたクエリ プランを調べます。 プランがプラン ガイドで指定したものと一致していることがわかります。

こちらもご覧ください

sp_create_plan_guide (Transact-SQL)
クエリ ヒント (Transact-SQL)
プラン ガイド