都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

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

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

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

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

    SELECT * FROM Production.WorkOrder WHERE ProductID = 725;
    

    f:id:koogucc11:20150420003623p:plain

  • クエリプランのハンドルおよびオフセット値を取得します。下記のクエリを 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%'
    

    f:id:koogucc11:20150420012506p:plain

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

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

    f:id:koogucc11:20150420012726p:plain

  • 下記の通り、プランガイドが登録されました。
    f:id:koogucc11:20150420013010p:plain

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

SELECT * FROM sys.plan_guides

f:id:koogucc11:20150501222045p:plain

※プランガイドは、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;

f:id:koogucc11:20150501221221p:plain

 次にプランガイドを無効にした状態で、実行時間を計測します。まずプランガイドを右クリック→無効化を選択します。
f:id:koogucc11:20150501220746p:plain
 下記のコマンド実行し、プランキャッシュをクリアします。

DBCC FREEPROCCACHE

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

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

f:id:koogucc11:20150501221233p:plain

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

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

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

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

実践 機械学習システム

実践 機械学習システム