クエリの実行プランを強制する。 - レベルエンター山本大のブログ

いま、クエリのパフォチュをしている。

その中でちょっとした問題にぶつかった。

本番サーバー（高スペック）で実行すると劇的に遅く（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