Indexed Views and Statistics

インデックス付きビューは SQL Server のどのエディションでも作成できますが、それらを最大限に活用したい場合は注意すべき動作が多数あります。

自動統計は NOEXPAND ヒントを必要とします

SQL Server はクエリー最適化の際に基数の推定とコストベースの意思決定を助けるために、統計を自動的に作成することができます。 この機能は、ベース テーブルと同様にインデックス付きのビューでも機能しますが、ビューがクエリ内で明示的に命名され、NOEXPAND ヒントが指定された場合のみです。 (ビューの各インデックスには常に統計オブジェクトが関連付けられており、ここで話しているのはインデックスに関連付けられない統計情報の自動生成と保守です。)

SQL Server の非 Enterprise エディションでの作業に慣れている場合、この動作にこれまで気付かなかったかもしれません。 SQL Server の下位エディションでは、インデックス付きビューにアクセスするクエリ プランを作成するために NOEXPAND ヒントが必要です。 NOEXPAND が指定されると、通常のテーブルの場合とまったく同じように、インデックス付きビューの自動統計が作成されます。

Example – Standard Edition with NOEXPAND

SQL Server 2012 Standard Edition と Adventure Works サンプル データベースを使って、まず、2つの販売テーブルを結合して、顧客と商品ごとの合計注文数量を計算するビューを作成します。

CREATE VIEW dbo.CustomerOrdersWITH SCHEMABINDING ASSELECT SOH.CustomerID, SOD.ProductID, OrderQty = SUM(SOD.OrderQty), NumRows = COUNT_BIG(*)FROM Sales.SalesOrderDetail AS SODJOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SOD.SalesOrderIDGROUP BY SOH.CustomerID, SOD.ProductID;

このビューで統計をサポートするには、一意のクラスタ化インデックスを追加して実体化する必要があります。 顧客と製品IDの組み合わせは、(定義上)ビュー内で一意であることが保証されているので、これをキーとして使用します。 この2つの列はインデックスでどちらでも指定できますが、商品でフィルタリングするクエリが多くなると想定して、商品IDを先頭の列にします。

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.CustomerOrders (ProductID, CustomerID);

ここで、特定の製品範囲について顧客ごとの注文総量を示すクエリーを作成するように依頼されました。 インデックス付きビューを使用した実行計画が、結合を避け、すでに部分的に集約されているデータを操作するため、効果的な戦略であることを期待しています。 SQL Server Standard Edition を使用しているため、ビューを明示的に指定し、NOEXPAND ヒントを使用してインデックス付きビューにアクセスするクエリプランを作成しなければなりません。

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS CO WITH (NOEXPAND)WHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerID;

作成した実行計画は、インデックス付きビューで検索を行い目的の製品の行を探し、続いて集計を行って顧客あたりの合計量を計算することを示しています。

SQL Sentry Plan Explorer のプラン ツリー ビューでは、カーディナリティ推定がインデックス付きビューの検索では正確に行われ、集約の結果では非常に良好であることがわかります。 この統計は、たとえば集約戦略を選択する際に、顧客 ID の予想される数と分布が重要である可能性があるため、作成されます。 Management Studio のオブジェクト エクスプローラを使用して、新しい統計値を確認できます。

統計オブジェクトをダブルクリックすると、それが(ベース テーブルではなく)ビューの顧客 ID 列から構築されたことが確認できます。

Indexed Views can improve Cardinality Estimation

まだ Standard Edition を使用しているので、インデックス付きビューを削除して再作成し(これによりビュー統計も削除)、今度は NOEXPAND ヒントをコメントアウトして再びクエリーを実行します。

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS CO --WITH (NOEXPAND)WHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerID;

NOEXPAND なしで Standard Edition を使用した場合の予想どおり、結果のクエリ プランはビューではなくベース テーブルに対して直接操作されました。

上記の計画におけるルート演算子上の警告の三角形は、現在の目的には重要でない Sales Order Detail テーブルの潜在的に有用なインデックスを警告しています。 このコンパイルでは、インデックスされたビューの統計情報は作成されません。

この問い合わせのプランツリー表示では、カーディナリティ推定は2つのテーブルスキャンと結合では正しいですが、他の計画演算子ではかなり悪くなっていることが示されています。

インデックス付きビューを NOEXPAND ヒントとともに使用すると、テスト クエリではより正確な推定値が得られました。

一般に、統計情報の精度は、それが通過し、問い合わせプラン演算子によって修正されると、非常に速く劣化します。 単純な結合はこの点ではあまり悪くありませんが、集約の結果に関する情報は経験則に基づく推測に過ぎないことがよくあります。

NOEXPANDのないビューは劣った計画を生成するかもしれません

上に示した問い合わせ計画(スタンダード版、NOEXPANDなし)は、問い合わせ最適化機能がビューを拡張するのではなく、基本テーブルに対して問い合わせを自分自身で書いた場合よりも実際には最適度が低くなっています。

SELECT SOH.CustomerID, SUM(OrderQty)FROM Sales.SalesOrderHeader AS SOHJOIN Sales.SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderIDWHERE SOD.ProductID BETWEEN 711 AND 718GROUP BY SOH.CustomerID;

この問い合わせは以下の実行計画を生成します。 ビュー拡張が使用されたとき、クエリオプティマイザは残念ながら冗長な集約操作を削除することができず、より効率の悪い実行プランとなりました。 新しいクエリの最終的なカーディナリティ推定値も、インデックス付きビューがNOEXPANDなしで参照されたときよりもわずかに良好です。

それでも、最良の推定値は、インデックス付きビューをNOEXPANDで参照したときに生成されたもの(便宜上以下に繰り返す)です。

Enterprise Edition とビュー マッチング

エンタープライズ版インスタンスでは、クエリが明示的にビューに言及していない場合でも、クエリオプティマイザがインデックス付きビューを使用できることがあります。 オプティマイザは、クエリツリーの一部をインデックス付きビューに一致させることができる場合、そのビューを使用するかどうかのコストの推定に基づいて、使用するかどうかを選択することができます。 ビューマッチングのロジックは非常に賢いのですが、実際にはかなり簡単にヒットする限界があります。 2055>

The EXPAND VIEWS クエリ ヒント

可能性のうち稀なものから始めると、クエリがインデックス付きビューを参照する場合がありますが、代わりにベース テーブルにアクセスすることでより良い計画が得られるでしょう。

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS COWHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerIDOPTION (EXPAND VIEWS);

エンタープライズ版では、この問い合わせは NOEXPAND が省略されたときにスタンダード版で見たのと同じ計画を生成します (重複した集約操作を含む)。

余談ですが、EXPAND VIEWS ヒントは名前が悪いと思います。 SQL Server は、NOEXPAND ヒントが指定されていない限り、常にクエリ内のビュー定義を展開します。 EXPAND VIEWS ヒントは、展開されたツリーの一部をインデックス付きのビューにマッチングさせるオプティマイザーのルールを無効にします。 どちらのヒントも指定しない場合、SQL Server はまずビューをその基本テーブル定義に展開し、その後、インデックス付きビューへのマッチングを検討します。 EXPAND VIEWS ヒントのより良い名前は DISABLE INDEXED VIEW MATCHING であったかもしれません。

ヒント EXPAND VIEWS は、ベース テーブルに対するクエリがインデックス付きビューにマッチングされるのを防ぐために最もよく使用されます。

SELECT SOH.CustomerID, SUM(OrderQty)FROM Sales.SalesOrderHeader AS SOHJOIN Sales.SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderIDWHERE SOD.ProductID BETWEEN 711 AND 718GROUP BY SOH.CustomerIDOPTION (EXPAND VIEWS);

クエリー ヒントは Standard Edition と同じベース テーブルのみのクエリを使用していたときの実行プランと推定値に帰結しました。

Enterprise View Matching and Statistics

エンタープライズ版でも、インデックス以外のビュー統計は、NOEXPAND ヒントを使用する場合にのみ作成されます。 このことを明確にするために、Enterprise 版のみのビュー マッチング機能では、ビュー統計が作成または更新されることはありません。 この直感的でない動作は、驚くべき副作用があるため、少し調べてみる価値があります。

ここで、Enterprise Edition インスタンスのビューに対して、ヒントなしで基本的なクエリを実行します。

SELECT CO.CustomerID, SUM(CO.OrderQty)FROM dbo.CustomerOrders AS COWHERE CO.ProductID BETWEEN 711 AND 718GROUP BY CO.CustomerID;

新しいことは View Clustered Index Seek 上の警告三角形です。 ツールチップに詳細が表示されます。

ヒント NOEXPAND を使用しなかったので、インデックス付きビューの顧客 ID 列の統計は自動的には作成されませんでした。 Customer ID の統計は、この単純化された例では実際にはそれほど重要ではありませんが、常にそうであるとは限りません。

Curious Cardinality Estimates

次に興味深いことは、カーディナリティ推定値が Standard Edition の例を含め、これまで遭遇したどのケースよりも悪いように見えるということです。 この推定値は、ビュー クラスタ化インデックスに関連する統計情報からの Product ID ヒストグラム情報に基づいていると予想されます。 このヒストグラムの該当箇所は以下の通りです。

DBCC SHOW_STATISTICS ('dbo.CustomerOrders', 'cuq') WITH HISTOGRAM;

統計が作成されてからテーブルが変更されていないことを考えると、この統計は、以下のようになります。 推定値は、プロダクトID値が711から718の間のRANGE_ROWSとEQ_ROWSの単純合計になると思われます(推定値には、711の項目に対して表示されている28個のRANGE_ROWSは含まれていません。) 表示されたEQ_ROWSの合計は7,301です。 これは、ビューによって実際に返された行の数とまったく同じです。

その答えは、ビュー マッチングが現在機能している方法にあります。 このクエリでは NOEXPAND ヒントを指定していないため、最初のカーディナリティの推定はビューで展開されたクエリツリーに基づいて行われます。

赤い網掛けの部分は、ビューマッチング活動によって置き換えられるツリーの部分を表します。 この部分からの出力cardinalityは11,267である。 11,220と推定される網掛けされていない部分は、ビューマッチングの影響を受けていない。

ビュー マッチングは単に赤い網掛け部分をインデックス付きビューの論理的に等価なシークに置き換えただけです。 一般に、ある統計情報のセットから計算された推定値が他のものより優れていると期待する理由はほとんどないのです。 しかし、それを一般化することや、基礎となるデータが変更されると統計情報のさまざまなソースがどれだけ速く古くなるかを正しく説明することは難しいかもしれません。

Even More Curious Cardinality Estimates

Enterprise Edition では、基本テーブルに対するクエリを記述し、自動ビュー マッチングに依存する場合、さらに興味深い状況が生じます。

SELECT SOH.CustomerID, SUM(OrderQty)FROM Sales.SalesOrderHeader AS SOHJOIN Sales.SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderIDWHERE SOD.ProductID BETWEEN 711 AND 718GROUP BY SOH.CustomerID;

Missing statistics 警告は以前と同様で、同じ説明がついています。 より興味深い特徴は、View Clustered Index Seek によって生成される行の数の見積もりが低くなり(7,149)、集約から返される行の数の見積もりが高くなったことです(8,226)。

ポイントを強調すると、このクエリープランは 7,149 行のソースが集約されて 8,226 行を生成できる!

一部の説明は以前のものと変わりません。 ビュー マッチングによって置き換えられる赤い領域を示す EXPAND VIEWS クエリ プランを以下に示します。

これで最終的な推定値 8,226 はどこから来るのかがわかりますが、推定値 7,149 行はどうでしょうか。 前に見たロジックに従うと、ビューは 11,267 行の推定値を表示する必要があるように見えます。 そうです、本当に。 インデックス付きビューには、合計で 79,433 行が含まれています。 商品ID BETWEEN述語の魔法の推測率は9%で、0.09 * 79433 = 7148.97行になります。 SSMS のクエリ プランは、この計算が丸める前でも正確に正しいことを示しています。

この状況では、SQL Server 最適化機能は、置き換えられたサブツリーからの結合後の基数推定値よりもインデックス付きのビュー基数に基づいて推測することを優先したようです。 不思議です。

概要

ヒント NOEXPAND を使用すると、インデックス付きビューが最終的なクエリ プランで使用され、クエリ最適化機能がインデックス以外の統計を自動的に作成、維持、および使用できるようになることが保証されます。 また、NOEXPAND を使用すると、最初のカーディナリティ推定値がベース テーブルからではなく、インデックス付きビュー情報に基づいて行われるようになります。

NOEXPAND が指定されていない場合、ビュー参照は常に、クエリのコンパイル開始前 (したがって最初のカーディナリティ推定の前) にベース テーブル定義に置き換えられます。 Enterprise SKU のみで、インデックス付きビューは最適化プロセスの後半でクエリ ツリーに置換されます。

クエリ ヒント EXPAND VIEWS は、Enterprise Edition のオプティマイザによるインデックス付きビューのマッチングを防止します。 これは、クエリがもともとインデックス付きビューを参照していたかどうかに関係なく適用されます。

インデックス付きビューで見つからないと示された統計は、手動で作成できますが、オプティマイザーは通常、NOEXPAND ヒントを使用しないクエリではそれらを使用しません。 NOEXPAND が指定されている場合、クエリはより正確なビュー統計から恩恵を受ける可能性が高くなります。

コメントを残す

メールアドレスが公開されることはありません。