SE の雑記

SQL Server 2012 以降で統計情報関連の機能で機能更新が行われ、実行プランに影響を与えるケースが見られてきましたので、どのような更新が行われてきたか軽くまとめてみたいと思います。

■統計情報の自動更新タイミング (SQL Server 2008 R2 SP1 以降)

---

こちらについては SQL Server 2008 R2 SP1 以降で追加されたものですが、SQL Server 2012 では標準で利用することができます。

厳密な動作としては異なりますが、統計情報の自動更新が有効になっていると、データの母数の 20% 程度が変更されると統計情報の更新が行われます。
SQL Server の統計保守機能 (Autostats)
統計情報の自動更新が ON の時には統計情報を手動で更新する必要はない？

SQL Server 2008 R2 SP1 以降では、トレースフラグ 2371 が追加され、データの母数の 20% ではなく、データの母数に応じて更新のタイミングを変更するような動作に変更をすることができます。

SQL Server 2008 R2 SP1 で追加された統計情報の自動更新に関してのトレースフラグ
New SQL 2008 R2 SP1 trace flag adjusts autostats threshold
Changes to automatic update statistics in SQL Server ? traceflag 2371
SQL Server 2012/2008 R2 新機能: 列変更カウンタ (colmodctr)

SQL Server では統計情報の非同期更新 (AUTO_UPDATE_STATISTICS_ASYNC) を使用することで、統計情報の更新中のクエリ実行への影響を抑えることができるので、これとの併用を検討すると同時実行性の低下を抑えることが可能になるかと思います。
# 一時的に古い統計情報を使用したプランの生成が行われてしまうためトレードオフではありますが。
ALTER DATABASE の SET オプション (Transact-SQL)
クエリのパフォーマンスを向上させるための統計の使用
[データベースのプロパティ] ([オプション] ページ)

■Inaccurate Cardinality Estimate Detection Framework (SQL Server 2012)

これについては、以下が参考になります。

あまり知られていないSQL Server 2012の機能強化点 (後編)
C13 SQL Server2012知られざるTips集 by 平山理
How the query execution engine calculates the threshold it uses to detect inaccurate cardinality estimation and fire the inaccurate_cardinality_estimate extended event?
Thinking about the inaccurate_cardinality_estimate Event
String length and SARGability

対象データの見積もり件数と実際の処理件数の差が閾値を超えた場合に発生 (検知) するイベントとなります。
拡張イベントとしては、[inaccurate_cardinality_estimate] が追加されています。
この拡張イベントは [Debug] のチャネルに含まれており、デフォルトでは [Debug] のチャネルはフィルタに含まれていないため、拡張イベントのフィルタに追加する必要があります。

冒頭で紹介した情報に記載されていますが、見積もり行数が 100,000 より大きい場合は、乖離が以下の公式で算出した閾値より大きい場合にイベントが発生します。

100,000 * (5-1.2) + 1.2 *[見積もり行数]

この拡張イベントでは実際の行数 (actual_rows) と見積もり行数 (estimated_rows) を確認できますので、どの程度乖離が起こり発生したのかを確認することもできます。

SQL Server 2012 以降では、型変換によりデータの選択に影響が発生し、SeekPlan になった際のイベントが、[plan_affectinv_convert] として追加されているようです。

<xsd:attribute name="ConvertIssue" use="required">
	<xsd:simpleType>
		<xsd:restriction base="xsd:string">
			<xsd:enumeration value="Cardinality Estimate" />
			<xsd:enumeration value="Seek Plan" />
			<!--  to be extended here   -->
		</xsd:restriction>
	</xsd:simpleType>
</xsd:attribute>

■新しい基数推定 (SQL Server 2014)

SQL Server 2014 では新しい基数推定によりクエリが実行されるようになりました。

基数推定 (SQL Server)

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

新しい基数推定の利用によりデータの分布の判断が異なるようになり、CardinalityEstimationModelVersion が 70 (SQL Server 2012 までの基数推定) か、120 (SQL Server 2014 の基数推定) のどちらが使われているかを実行プランから判断できるようになっています。

新しい基数推定については SQL Server 2014 の互換性レベル (120) を使用することで使用されるようになります。

これにより見積もり行数の算出が変更され、実行プランが変わってくることがあります。

Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level に記載されていますが、クエリレベルのトレースフラグ (QUERYTRACEON) を使用することで

• 互換性レベル 120 を使用している際に、CardinalityEstimationModelVersion を 70 で実行
  
  トレースフラグ 9481
• 互換性レベル 110 を使用している際に、CardinalityEstimationModelVersion を 120 で実行
  
  トレースフラグ 231

とういような制御をすることができます。

どの、CardinalityEstimationModelVersion で実行されているかは実行プランから確認することが可能です。

以下は互換性レベルが 120 に設定されているデータベースで 3 パターンのクエリを実行したものになります。

SELECT * FROM CETest WHERE Col2 = N'2999/1/1'
SELECT * FROM CETest WHERE Col2 = N'2999/1/1'
OPTION (QUERYTRACEON 9481)
SELECT * FROM CETest WHERE Col2 = N'2999/1/1'
OPTION (QUERYTRACEON 2312)

互換性レベルを 120 のデータベースで実行したクエリでは、上から

• CardinalityEstimationModelVersion : 120
• CardinalityEstimationModelVersion : 70
• CardinalityEstimationModelVersion : 120

で実行されます。

互換性レベルを110 のデータベースで実行したクエリでは、上から

• CardinalityEstimationModelVersion : 70
• CardinalityEstimationModelVersion : 70
• CardinalityEstimationModelVersion : 120

として実行されます。

DBCC TRACEON を使用したインスタンスレベルでの、グローバルトレースフラグによる制御も可能なようですので、クエリレベルのトレースフラグではなく、インスタンスとしての動作も制御可能かと。

DBCC TRACEON(9481, -1)
DBCC TRACEON(2312, -1)

拡張イベントとして [query_optimizer_force_both_cardinality_estimation_behaviors] が追加されており、両方のトレースフラグを同時に設定してしまい、トレースフラグによる基数設定の制御が無効になっている場合などの判断は拡張イベントからできます。

■統計情報の増分更新 (SQL Server 2014)

SQL Server 2014 では統計情報の増分更新 (INCREMENTAL STATS) の機能が追加されました。

統計

増分統計

SQL Server 2014 Incremental Statistics

以下のケースでは増分更新がサポートされていませんが、統計情報更新のコストを減らす方法としては有効なのかと。

# まだ検証ができていないのですが。

• ベース テーブルにパーティションで固定されていないインデックスを使用して作成された統計。

• AlwaysOn の読み取り可能なセカンダリ データベースに対して作成された統計。

• 読み取り専用のデータベースに対して作成された統計。

• フィルター選択されたインデックスに対して作成された統計。

• ビューに対して作成された統計。

• 内部テーブルに対して作成された統計。

• 空間インデックスまたは XML インデックスを使用して作成された統計。