クエリ発行時にクエリコンパイルに時間がかかりすぎて、クエリがタイムアウトしたりするのが怖いので、プランガイドを使用して、少しでもクエリコンパイル時間を短くしてみる

　クエリコンパイル恐るべし....ryuchan.hatenablog.com

　ということで、クエリコンパイル時間を短縮するため、プランガイドを使用します。手順は下記の通りです。

• プランガイドを設定したいクエリを実行します。

SELECT * FROM Production.WorkOrder WHERE ProductID = 725;

• クエリプランのハンドルおよびオフセット値を取得します。下記のクエリを SQL Server Management Studio で実行します。

SELECT
    [クエリプラン] = query_plan,
    [SQL文] = st.text,
    [プランハンドル] = plan_handle,
    [オフセット] = qs.statement_start_offset
FROM
    sys.dm_exec_query_stats qs 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset,qs.statement_end_offset) qp
WHERE
    st.text LIKE N'%WorkOrder%'

• sp_create_plan_guide_from_handle に該当のプランハンドルおよびオフセット値を指定し実行します。

EXECUTE sp_create_plan_guide_from_handle 
        @name =  N'GuideTest',
        @plan_handle = 0x060007005FC50F0070FAEF4F0100000001000000000000000000000000000000000000000000000000000000,
        @statement_start_offset = 26;

• 下記の通り、プランガイドが登録されました。
  

※作成したプランガイドは下記のクエリで参照可能です。

SELECT * FROM sys.plan_guides

※プランガイドは、sp_create_plan_guide でも作成可能です。個人的には sp_create_plan_guide_from_handle 好きですが....

EXEC sp_create_plan_guide @name = N'[GuideTest]', 
                          @stmt = N'SELECT * FROM [Production].[WorkOrder] WHERE [ProductID]=@1', 
                          @type = N'SQL', 
                          @module_or_batch = N'SELECT * FROM [Production].[WorkOrder] WHERE [ProductID]=@1', 
                          @params = N'@1 smallint', 
                          @hints = N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.2000.8"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [Production].[WorkOrder] WHERE [ProductID]=@1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00455681" StatementEstRows="305.004" StatementOptmLevel="TRIVIAL" QueryHash="0x8AC3C4AE8267BFC5" QueryPlanHash="0x11B40690225AD27F" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="200"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="206624" EstimatedPagesCached="51656" EstimatedAvailableDegreeOfParallelism="2"/><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="305.004" EstimateIO="0" EstimateCPU="3.05004e-005" AvgRowSize="60" EstimatedTotalSubtreeCost="0.00455681" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="WorkOrderID"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ProductID"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="OrderQty"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ScrappedQty"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StartDate"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="EndDate"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="DueDate"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ScrapReasonID"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ModifiedDate"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StockedQty" ComputedColumn="1"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StockedQty" ComputedColumn="1"/><ScalarOperator ScalarString="[AdventureWorks2014].[Production].[WorkOrder].[StockedQty]"><Identifier><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StockedQty" ComputedColumn="1"/></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="305.004" EstimateIO="0.00403381" EstimateCPU="0.000492505" AvgRowSize="60" EstimatedTotalSubtreeCost="0.00452631" TableCardinality="72591" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="WorkOrderID"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ProductID"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="OrderQty"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StockedQty" ComputedColumn="1"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ScrappedQty"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StartDate"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="EndDate"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="DueDate"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ScrapReasonID"/><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ModifiedDate"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="WorkOrderID"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ProductID"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="OrderQty"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StockedQty" ComputedColumn="1"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ScrappedQty"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="StartDate"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="EndDate"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="DueDate"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ScrapReasonID"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ModifiedDate"/></DefinedValue></DefinedValues><Object Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Index="[IX_WorkOrder_ProductID]" IndexKind="NonClustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[AdventureWorks2014]" Schema="[Production]" Table="[WorkOrder]" Column="ProductID"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@1],0)"><Identifier><ColumnReference Column="ConstExpr1002"><ScalarOperator><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"/></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>'

　コンパイル時間がどの程度かかるのか計測してみましょう。まずプランガイドを使用した状態で、下記のコマンド実行し、プランキャッシュをクリアします。

DBCC FREEPROCCACHE

　下記のクエリを SQL Server Management Studio で実行します。

SET STATISTICS TIME ON
SELECT * FROM Production.WorkOrder WHERE ProductID = 725;

　次にプランガイドを無効にした状態で、実行時間を計測します。まずプランガイドを右クリック→無効化を選択します。

　下記のコマンド実行し、プランキャッシュをクリアします。

DBCC FREEPROCCACHE

　下記のクエリを SQL Server Management Studio で実行します。

SET STATISTICS TIME ON
SELECT * FROM Production.WorkOrder WHERE ProductID = 725;

　単純なクエリなので、大差はありませんが、プランガイドを使用したほうが 2msec 程度高速でした。複雑なクエリだと効果を発揮しそうです。ただ、プランガイドを使いこなすには、ある程度の知識を要求されますね。
※プランガイドについては、下記のサイトも参考にしてください。
SQL Server がプラン ガイドをクエリに照合するプロセス

※世の中、AIとか、機械学習が熱いですね。うーん、波に乗れていないわー。

ＡＩの衝撃　人工知能は人類の敵か (講談社現代新書)

知りたい分かりたい人の　体験する機械学習

実践 機械学習システム