クエリ プランの移行

データベースを SQL Server 2008 にアップグレードすると、ほとんどの場合、クエリのパフォーマンスが向上します。ただし、慎重にパフォーマンス チューニングされたミッションクリティカルなクエリがある場合は、アップグレードする前に、各クエリのプラン ガイドを作成してこれらのクエリのクエリ プランを保存しておくことをお勧めします。アップグレード後に、効率性の劣るクエリ プランが 1 つ以上のクエリに対してクエリ オプティマイザで選択される場合は、プラン ガイドを有効にし、強制的にアップグレード前のプランが使用されるように設定できます。

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

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

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

3. データベースを SQL Server 2008 にアップグレードします。

   このクエリ プランは、アップグレードしたデータベースのプラン ガイドに保存され、アップグレード後に効率性の劣るプランが選択された場合のフォールバックとして機能します。

   アップグレード後にプラン ガイドを有効にしないようお勧めします。統計の更新により、新しいリリースに含まれる優れたプランや有益な再コンパイルを利用できなくなる可能性があるためです。

4. アップグレード後に効率性の劣るプランが選択される場合は、すべてのプラン ガイドまたはその一部を有効にして新しいプランをオーバーライドします。

例

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

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

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

• SET SHOWPLAN_XML

• SET STATISTICS XML

• sys.dm_exec_query_plan 動的管理関数の query_plan 列に対するクエリ

• SQL Server ProfilerShowplan XML、Showplan XML Statistics Profile、および Showplan XML For Query Compile の各イベント クラス

クエリ プランの生成および分析の詳細については、「クエリの分析」を参照してください。

次の例では、動的管理ビューに対してクエリを実行することにより、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 プラン自体に含まれている引用符 (') には引用符をもう 1 つ付けてエスケープします。たとえば、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)

概念

USE PLAN クエリ ヒントの使用

プラン強制シナリオと例