SQLServerで使用する実行プランを固定化する

はじめに

SQLServerで思ってたんと違う実行プランになって、クエリの実行速度が激遅になることがある。
そういうときには実行プランの固定化が役に立つ。

クエリ内でヒント句を使って使用するインデックスを指定する方法もあるが、プログラムの修正がすぐに出来ないけど血止めしたい・・・とかあると思う。
今回はsp_create_plan_guide を使用してプランガイドを作成することによって実行プランを固定化する。

概要

今回使用する環境

AWS RDS
SQLServer 2017 standard

主な手順

問題となっているクエリをTraceから探す
上記で見つけたクエリのいい感じの実行プランを探す
実行プランXMLを編集する
プランガイドを作成する
プランガイドが使用されているかを確認する

データベース作成(事前準備)

create_database.sql

CREATE DATABASE [SAKON_TEST_DB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SAKON_TEST_DB', FILENAME = N'D:\RDSDBDATA\DATA\SAKON_TEST_DB.mdf' , SIZE = 5120KB , FILEGROWTH = 10%)
 LOG ON 
( NAME = N'SAKON_TEST_DB_log', FILENAME = N'D:\RDSDBDATA\DATA\SAKON_TEST_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE [SAKON_TEST_DB]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [SAKON_TEST_DB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

テーブル作成(事前準備)

create_table.sql

use SAKON_TEST_DB

CREATE TABLE human (
	id int NOT NULL PRIMARY KEY,
	name varchar(50) NULL,
	job_id int NOT NULL,
	foreign key (job_id) references job (id) ,
);

CREATE TABLE job (
	id int NOT NULL PRIMARY KEY,
	jobname varchar(50) NULL,
);

データ作成(事前準備)

エクセルか何かで適当にデータを作って突っ込む

使用するクエリ(事前準備)

実際はプログラムから発行されるので、sq_executesqlにて実行する

select_from_program.sql

exec sp_executesql N'select * from human left join job on human.job_id=job.id where @human_id = human.id',N'@human_id int',@human_id=1

実行されているSQLを見つける

ここは、実際に運用環境だとTraceとかの出力を仕込んでると思うので、対象となるSQLのtext_dataを見つける必要がある。
今回は、SQL Server Profilerにてクエリを見つける。

固定化を行いたい実行プランを取得する

上記で取得したSQLを使用して、実行プランを取得する。
このとき、開発環境とかで実行速度が早い、いい感じの実行プランを取得する。(正直、ガチャ的な要素があるけど・・・)

SSMSでは以下のような感じになる。

以下のように 実際の実行プランを含める にチェックをつけた状態で実施。

実行プラン タブが表示される

適当に空欄で右クリックして、 実行プランのXMLの表示 にてXMLを表示する

実行プランのXMLを編集する

ヘッダ行を削除する

具体的には以下の行を削除する

<?xml version="1.0" encoding="utf-16"?>

スペースを削除する

分かりにくいけど、←の半角スペース2つを空欄で置換する

こんな感じ

シングルクォーテーションを置換する

'を ' に置換する

改行を置換する

\r\nを空欄で置換する
※このとき、正規表現を使用するようにする

最終的な状態

以下のような状態になる

plan.xml

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.481" Build="14.0.3049.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">  <BatchSequence>    <Batch>      <Statements>        <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.00657038" StatementText="select * from human left join job on human.job_id=job.id where @human_id = human.id" StatementType="SELECT" QueryHash="0x00EFF4BC74CC5E1B" QueryPlanHash="0x02EBBD814D0A351F" RetrievedFromCache="true" SecurityPolicyApplied="false">          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="168">            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="789248" EstimatedPagesCached="98656" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="7493096" />            <OptimizerStatsUsage>              <StatisticsInfo Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Statistics="[_WA_Sys_00000003_3C69FB99]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-07-28T00:02:41.38" />              <StatisticsInfo Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Statistics="[PK__job__3213E83F784FE040]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-07-28T00:02:41.38" />            </OptimizerStatsUsage>            <TraceFlags IsCompileTime="true">              <TraceFlag Value="3226" Scope="Global" />            </TraceFlags>            <TraceFlags IsCompileTime="false">              <TraceFlag Value="3226" Scope="Global" />            </TraceFlags>            <QueryTimeStats CpuTime="1" ElapsedTime="1" />            <RelOp AvgRowSize="75" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">              <OutputList>                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />              </OutputList>              <RunTimeInformation>                <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />              </RunTimeInformation>              <NestedLoops Optimized="false">                <OuterReferences>                  <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                </OuterReferences>                <RelOp AvgRowSize="44" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2">                  <OutputList>                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                  </OutputList>                  <RunTimeInformation>                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />                  </RunTimeInformation>                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">                    <DefinedValues>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                      </DefinedValue>                    </DefinedValues>                    <Object Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Index="[PK__human__3213E83FF2D34269]" IndexKind="Clustered" Storage="RowStore" />                    <SeekPredicates>                      <SeekPredicateNew>                        <SeekKeys>                          <Prefix ScanType="EQ">                            <RangeColumns>                              <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                            </RangeColumns>                            <RangeExpressions>                              <ScalarOperator ScalarString="[@human_id]">                                <Identifier>                                  <ColumnReference Column="@human_id" />                                </Identifier>                              </ScalarOperator>                            </RangeExpressions>                          </Prefix>                        </SeekKeys>                      </SeekPredicateNew>                    </SeekPredicates>                  </IndexScan>                </RelOp>                <RelOp AvgRowSize="40" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2">                  <OutputList>                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />                  </OutputList>                  <RunTimeInformation>                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />                  </RunTimeInformation>                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">                    <DefinedValues>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />                      </DefinedValue>                    </DefinedValues>                    <Object Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Index="[PK__job__3213E83F784FE040]" IndexKind="Clustered" Storage="RowStore" />                    <SeekPredicates>                      <SeekPredicateNew>                        <SeekKeys>                          <Prefix ScanType="EQ">                            <RangeColumns>                              <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                            </RangeColumns>                            <RangeExpressions>                              <ScalarOperator ScalarString="[SAKON_TEST_DB].[dbo].[human].[job_id]">                                <Identifier>                                  <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                                </Identifier>                              </ScalarOperator>                            </RangeExpressions>                          </Prefix>                        </SeekKeys>                      </SeekPredicateNew>                    </SeekPredicates>                  </IndexScan>                </RelOp>              </NestedLoops>            </RelOp>            <ParameterList>              <ColumnReference Column="@human_id" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />            </ParameterList>          </QueryPlan>        </StmtSimple>      </Statements>    </Batch>  </BatchSequence></ShowPlanXML>

実行プラン固定化クエリテンプレ

プランガイドを作成するクエリのテンプレ。

template.sql

sp_create_plan_guide 
@name = N'',  -- プランガイドの名前
@stmt = N'', -- クエリ内容
@type = N'SQL',
@module_or_batch = NULL, 
@params = N'', -- パラメータ

@hints = N'OPTION (USE PLAN N'''')'; -- 実行プランのXML

--EXEC sp_control_plan_guide N'ENABLE', N''; -- プランガイドを指定して有効化

-- EXEC sp_control_plan_guide N'DROP', N'';    -- プランガイドを指定して削除

-- select * from sys.plan_guides -- 設定したプランガイドを確認

実行プラン固定化クエリを作成する

プランガイドを作成する。実際にやることは以下

@nameパラメータ：プランガイドの名前をつける
@stmtパラメータ：実行しているクエリを入力(Traceから取得したクエリを使用する)これはスペース一つでも間違えたらダメだからちゃんとコピペする。もちろん、アドホッククエリなどは毎回クエリが変わるから使えない
@paramsパラメータ：クエリで使用しているパラメータ定義を入力(Traceから取得したクエリを使用する)
@hintsパラメータ：上記で修正した実行プランを入れる。

create_plan_guide.sql

sp_create_plan_guide 
@name = N'sakon_test_plan_guide', 
@stmt = N'select * from human left join job on human.job_id=job.id where @human_id = human.id',
@type = N'SQL',
@module_or_batch = NULL, 
@params = N'@human_id int',

@hints = N'OPTION (USE PLAN N''<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.481" Build="14.0.3049.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">  <BatchSequence>    <Batch>      <Statements>        <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.00657038" StatementText="select * from human left join job on human.job_id=job.id where @human_id = human.id" StatementType="SELECT" QueryHash="0x00EFF4BC74CC5E1B" QueryPlanHash="0x02EBBD814D0A351F" RetrievedFromCache="true" SecurityPolicyApplied="false">          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="168">            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="789248" EstimatedPagesCached="98656" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="7493096" />            <OptimizerStatsUsage>              <StatisticsInfo Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Statistics="[_WA_Sys_00000003_3C69FB99]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-07-28T00:02:41.38" />              <StatisticsInfo Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Statistics="[PK__job__3213E83F784FE040]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-07-28T00:02:41.38" />            </OptimizerStatsUsage>            <TraceFlags IsCompileTime="true">              <TraceFlag Value="3226" Scope="Global" />            </TraceFlags>            <TraceFlags IsCompileTime="false">              <TraceFlag Value="3226" Scope="Global" />            </TraceFlags>            <QueryTimeStats CpuTime="1" ElapsedTime="1" />            <RelOp AvgRowSize="75" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">              <OutputList>                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />              </OutputList>              <RunTimeInformation>                <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />              </RunTimeInformation>              <NestedLoops Optimized="false">                <OuterReferences>                  <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                </OuterReferences>                <RelOp AvgRowSize="44" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2">                  <OutputList>                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                  </OutputList>                  <RunTimeInformation>                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />                  </RunTimeInformation>                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">                    <DefinedValues>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                      </DefinedValue>                    </DefinedValues>                    <Object Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Index="[PK__human__3213E83FF2D34269]" IndexKind="Clustered" Storage="RowStore" />                    <SeekPredicates>                      <SeekPredicateNew>                        <SeekKeys>                          <Prefix ScanType="EQ">                            <RangeColumns>                              <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                            </RangeColumns>                            <RangeExpressions>                              <ScalarOperator ScalarString="[@human_id]">                                <Identifier>                                  <ColumnReference Column="@human_id" />                                </Identifier>                              </ScalarOperator>                            </RangeExpressions>                          </Prefix>                        </SeekKeys>                      </SeekPredicateNew>                    </SeekPredicates>                  </IndexScan>                </RelOp>                <RelOp AvgRowSize="40" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2">                  <OutputList>                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />                  </OutputList>                  <RunTimeInformation>                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />                  </RunTimeInformation>                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">                    <DefinedValues>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />                      </DefinedValue>                    </DefinedValues>                    <Object Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Index="[PK__job__3213E83F784FE040]" IndexKind="Clustered" Storage="RowStore" />                    <SeekPredicates>                      <SeekPredicateNew>                        <SeekKeys>                          <Prefix ScanType="EQ">                            <RangeColumns>                              <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                            </RangeColumns>                            <RangeExpressions>                              <ScalarOperator ScalarString="[SAKON_TEST_DB].[dbo].[human].[job_id]">                                <Identifier>                                  <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                                </Identifier>                              </ScalarOperator>                            </RangeExpressions>                          </Prefix>                        </SeekKeys>                      </SeekPredicateNew>                    </SeekPredicates>                  </IndexScan>                </RelOp>              </NestedLoops>            </RelOp>            <ParameterList>              <ColumnReference Column="@human_id" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />            </ParameterList>          </QueryPlan>        </StmtSimple>      </Statements>    </Batch>  </BatchSequence></ShowPlanXML>'')';

作成したプランガイドを確認する

SSMSで確認する

クエリで確認する

confirm_plan_guide.sql

select * from sys.plan_guides

-- 実行結果
plan_guide_id	name	create_date	modify_date	is_disabled	query_text	scope_type	scope_type_desc	scope_object_id	scope_batch	parameters	hints
65537	sakon_test_plan_guide	2019-07-28 01:15:42.707	2019-07-28 01:15:42.707	0	select * from human left join job on human.job_id=job.id where @human_id = human.id	2	SQL	NULL	select * from human left join job on human.job_id=job.id where @human_id = human.id	@human_id int	OPTION (USE PLAN N'<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.481" Build="14.0.3049.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">  <BatchSequence>    <Batch>      <Statements>        <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.00657038" StatementText="select * from human left join job on human.job_id=job.id where @human_id = human.id" StatementType="SELECT" QueryHash="0x00EFF4BC74CC5E1B" QueryPlanHash="0x02EBBD814D0A351F" RetrievedFromCache="true" SecurityPolicyApplied="false">          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="168">            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="789248" EstimatedPagesCached="98656" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="7493096" />            <OptimizerStatsUsage>              <StatisticsInfo Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Statistics="[_WA_Sys_00000003_3C69FB99]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-07-28T00:02:41.38" />              <StatisticsInfo Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Statistics="[PK__job__3213E83F784FE040]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-07-28T00:02:41.38" />            </OptimizerStatsUsage>            <TraceFlags IsCompileTime="true">              <TraceFlag Value="3226" Scope="Global" />            </TraceFlags>            <TraceFlags IsCompileTime="false">              <TraceFlag Value="3226" Scope="Global" />            </TraceFlags>            <QueryTimeStats CpuTime="1" ElapsedTime="1" />            <RelOp AvgRowSize="75" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">              <OutputList>                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />              </OutputList>              <RunTimeInformation>                <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />              </RunTimeInformation>              <NestedLoops Optimized="false">                <OuterReferences>                  <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                </OuterReferences>                <RelOp AvgRowSize="44" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2">                  <OutputList>                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                  </OutputList>                  <RunTimeInformation>                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />                  </RunTimeInformation>                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">                    <DefinedValues>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="name" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                      </DefinedValue>                    </DefinedValues>                    <Object Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Index="[PK__human__3213E83FF2D34269]" IndexKind="Clustered" Storage="RowStore" />                    <SeekPredicates>                      <SeekPredicateNew>                        <SeekKeys>                          <Prefix ScanType="EQ">                            <RangeColumns>                              <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="id" />                            </RangeColumns>                            <RangeExpressions>                              <ScalarOperator ScalarString="[@human_id]">                                <Identifier>                                  <ColumnReference Column="@human_id" />                                </Identifier>                              </ScalarOperator>                            </RangeExpressions>                          </Prefix>                        </SeekKeys>                      </SeekPredicateNew>                    </SeekPredicates>                  </IndexScan>                </RelOp>                <RelOp AvgRowSize="40" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2">                  <OutputList>                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                    <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />                  </OutputList>                  <RunTimeInformation>                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />                  </RunTimeInformation>                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">                    <DefinedValues>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="jobname" />                      </DefinedValue>                    </DefinedValues>                    <Object Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Index="[PK__job__3213E83F784FE040]" IndexKind="Clustered" Storage="RowStore" />                    <SeekPredicates>                      <SeekPredicateNew>                        <SeekKeys>                          <Prefix ScanType="EQ">                            <RangeColumns>                              <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[job]" Column="id" />                            </RangeColumns>                            <RangeExpressions>                              <ScalarOperator ScalarString="[SAKON_TEST_DB].[dbo].[human].[job_id]">                                <Identifier>                                  <ColumnReference Database="[SAKON_TEST_DB]" Schema="[dbo]" Table="[human]" Column="job_id" />                                </Identifier>                              </ScalarOperator>                            </RangeExpressions>                          </Prefix>                        </SeekKeys>                      </SeekPredicateNew>                    </SeekPredicates>                  </IndexScan>                </RelOp>              </NestedLoops>            </RelOp>            <ParameterList>              <ColumnReference Column="@human_id" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />            </ParameterList>          </QueryPlan>        </StmtSimple>      </Statements>    </Batch>  </BatchSequence></ShowPlanXML>')

実際に作成したプランガイドが使用されているかを確認する

再度クエリを実行して、実行プランを確認する。
そして、先程と同じようにXMLを表示する。

ちゃんと出来てたら、以下のようにPlanGuideNameに上記で作成したプランガイドが表示されてたらOK