クエリの実行プランを強制する。
いま、クエリのパフォチュをしている。
その中でちょっとした問題にぶつかった。
本番サーバー(高スペック)で実行すると劇的に遅く(5分)、
ハードウェアスペックのみが異なる開発環境では、予期している速度(5秒)という現象だ。
詳しく調べていると、本番サーバーでのクエリ実行プランが開発環境とは、まったく異なることに気づく。
実行プランは、SQLServerのオプティマイザ機能が、インデックスや統計情報、CPUやメモリ状態など
あらゆる点を考慮して自動的に作成するが、今回はオプティマイザがちょっとおばかだったらしい。
この対応としては、「Option(HASH JOIN)」というクエリヒントをつけることで、
ある程度、こちらの想定する実行プランに強制することで対処した。
ただ、非常に危ない機能なので、利用を躊躇していたけど、
クエリの内容を検討して、確実にHash Joinであるほうが早いと踏んだため利用することにした。
この検討の中で、実行プランを強制する方法として、
より強烈な方法に行き当たったので試してみることにした。
それはXML保存した実行プランを、再利用するというもの。
実行プランの保存と再利用
SQL Server Management Studioで、クエリを選択して「CTRL+L」で実行プランをビジュアル表示できる。
この実行プラン上で、右クリック⇒「実行プランに名前をつけて保存」で、拡張子「sqlplan」のファイルが保存できる。
このファイルは、XMLで書かれた実行プランのDumpであり、
保存した実行プランは、USE PLANヒントに指定してやることで、再利用することができる。
実行プランを再利用することで、環境が変わろうがなんだろうが、指定したプラン通りに実行される。
ただし、他のクエリヒント(HASH JOINやLOOP JOIN)などと同様に、
オプティマイザが働かなくなるため、よほどの状況でないと使わないTipsだろう。
select * from tbl option(USE PLAN N'<?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.1406.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="4" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0032864" StatementText="select * from tbl" StatementType="SELECT"> <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /> <QueryPlan CachedPlanSize="9"> <RelOp AvgRowSize="20" EstimateCPU="0.0001614" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032864"> <OutputList> <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[tbl]" Column="idcol" /> <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[tbl]" Column="col1" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[tbl]" Column="idcol" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[tbl]" Column="col1" /> </DefinedValue> </DefinedValues> <Object Database="[MyDB]" Schema="[dbo]" Table="[tbl]" Index="[PK__tbl__7E6CC920]" /> </IndexScan> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>');
■(参考)実行プランを XML 形式で保存する方法
http://msdn.microsoft.com/ja-jp/library/ms190646.aspx
■USE PLAN クエリ ヒントの使用
http://msdn.microsoft.com/ja-jp/library/ms186954.aspx