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
