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

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

## はじめに

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

クエリ内で[ヒント句](https://docs.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017)を使って使用するインデックスを指定する方法もあるが、プログラムの修正がすぐに出来ないけど血止めしたい・・・とかあると思う。
今回は`sp_create_plan_guide `を使用してプランガイドを作成することによって実行プランを固定化する。

## 概要

### 今回使用する環境

* AWS RDS
* SQLServer 2017 standard

### 主な手順

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

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

```sql: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

```

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

```sql: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にて実行する

```sql: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では以下のような感じになる。

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

<img width="671" alt="スクリーンショット 2019-07-28 0.40.45.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/0f4ea03d-924e-ab83-2d4f-d162829a5a29.png">

**実行プラン** タブが表示される
<img width="1022" alt="スクリーンショット 2019-07-28 0.43.28.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/b5a04796-713f-aeb8-63db-5d6a05b4fcf1.png">

適当に空欄で右クリックして、 **実行プランのXMLの表示** にてXMLを表示する
<img width="882" alt="スクリーンショット 2019-07-28 0.44.30.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/439b9245-e06d-3eaf-95a0-12ec3ebda20b.png">

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

### ヘッダ行を削除する

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

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

### スペースを削除する

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

こんな感じ
<img width="1154" alt="スクリーンショット 2019-07-28 0.50.19.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/ce4eadf9-d182-d61b-020c-6360f2e42b88.png">

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

`'`を `&apos;` に置換する
<img width="1154" alt="スクリーンショット 2019-07-28 0.53.13.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/8621131f-7396-a62f-390d-0389c5f04337.png">

### 改行を置換する

`\r\n`を空欄で置換する
※このとき、正規表現を使用するようにする
![スクリーンショット 2019-07-28 0.54.50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/5e90aa1e-cf9c-e26c-13c8-6d1781305313.png)

### 最終的な状態

以下のような状態になる

<img width="1154" alt="スクリーンショット 2019-07-28 0.57.31.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/1ddd8137-3be9-ac15-9fc6-b781c702ad12.png">


```xml: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>
```

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

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

```sql: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 -- 設定したプランガイドを確認
```

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

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

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

```sql: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で確認する

<img width="279" alt="スクリーンショット 2019-07-28 1.18.24.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/df998f02-8004-77a1-2861-74e8438feeed.png">

### クエリで確認する

```sql: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

<img width="954" alt="jumphost01 2019-07-28 01-25-41.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/33101/3d74ecfa-0269-4e23-70f4-3ebdbc7b28ee.png">