SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

互換性レベル 130 以降をサポートしている環境のダウンレベルの互換性レベルと統計情報の更新について

leave a comment

SQL Server の統計情報の更新タイミングというと「データ量の 20% が更新された場合」に自動更新の対象となるというのが一般的に知られている内容ではないでしょうか。

これについては 統計 に記載されています。

SQL Server 2014 (12.x) まで、SQL Server は変更された行の割合に基づくしきい値を使用します。 これには、テーブル内の行数は考慮されません。 しきい値は次のようになります。

  • 統計情報が評価された時点でテーブルのカーディナリティが 500 以下の場合、500 回変更されるたびに更新されます。
  • 統計情報が評価された時点でテーブルのカーディナリティが 500 よりも大きい場合、500 プラス 20% の数の変更があるたびに更新されます。

SQL Server 2016 以降で追加された互換性レベル「130」に設定した場合は、統計情報の自動更新の閾値が変更されました。

SQL Server 2016 (13.x) 以降で、データベースの互換性レベルが 130 未満の場合、SQL Server では、テーブル内の行数に基づいて調整された、より小さな値の動的な統計情報更新しきい値を使用します。 これは、1,000 と現在のテーブルのカーディナリティの積の平方根として計算されます。 たとえば、テーブルに 200 万行含まれている場合、計算は sqrt (1000 * 2000000) = 44721.359 となります。 この変更により、大規模なテーブルの統計がより頻繁に更新されます。 ただし、データベースの互換性レベルが 130 未満の場合、SQL Server 2014 (12.x) のしきい値が適用されます

これにより、大規模なデータが格納されているテーブルについては、今までより少ない更新で統計情報の自動更新対象となるようになりました。

今回テストで使用するテーブルでは、互換性レベルによって、統計情報の自動更新の閾値に次のような違いが出ることになります。

SELECT 
	FORMAT(COUNT(*), '#,###0') AS row_count,
	FORMAT(CAST((COUNT(*) + 500) * 0.2 AS int), '#,###0') AS oldThreshold,
	FORMAT(CAST(SQRT(1000.0 * COUNT(*)) AS int), '#,###0') AS newThreshold
FROM LINEITEM2

image

今回は「3,749,974」(370 万件) 程度のデータが入ったテーブルを対象としています。

従来までの統計の閾値では、「75 万件」程度の変更が行われないと、自動更新の対象となりませんでしたが、新しい閾値では「6 万件」程度の変更が発生すると自動更新が行われることになります。

互換性レベル 130 以降を指定した場合は、新しい更新の閾値が反映されます。

それでは、互換性レベル 130 より小さいバージョンや、SQL Server 2014 以前の互換性レベル 130 を指定できないようなバージョンではどうなるでしょうか。

これらのバージョンでは、デフォルトの設定では従来型の 20% の閾値が適用されます。

しかし、「トレースフラグ 2371」を設定することで、データ量に応じた、新しい閾値の変更を適用することができます。

 

それでは、互換性レベル 130 を選択できる SQL Server 2016 以降のバージョンで、互換性レベル 120 以下を使用した場合はどうなるでしょうか?

2019/4/14 時点のトレースフラグの説明としては次のように記載されています。

自動更新の統計の固定しきい値を、自動更新の統計の動的しきい値に変更します。 詳しくは、こちらの Microsoft サポート技術情報をご覧ください。

注:SQL Server 2016 (13.x) 以降の、データベース互換性レベル 130 より下では、この動作はエンジンによって制御されるようになり、トレース フラグ 2371 に効力はありません。

スコープ: グローバルのみ

日本語の内容が機械翻訳なことが起因していると思うのですが、上記の記述ですと、130 より下の互換性レベルではトレースフラグ 2371 が効かないというように読めてしまいますが、この理解だと誤りで、130 よりした (120 以下) の互換性レベルを使用している場合、トレースフラグ 2371 で統計情報の閾値を変更することはできます。

動作を実際に確認してみたいと思います。

最初に「互換性レベル 130」で検証をしてみます。

UPDATE TOP (12) PERCENT LINEITEM2 SET L_COMMENT = NEWID()
GO
SELECT STATS_DATE(object_id,stats_id), * FROM sys.stats WHERE name = '_WA_Sys_00000010_65F62111'
GO
SELECT TOP 1 * FROM LINEITEM2 WHERE L_COMMENT = '87B1AB64-6252-468E-A653-1760392D1B8D'
GO
SELECT STATS_DATE(object_id,stats_id), * FROM sys.stats WHERE name = '_WA_Sys_00000010_65F62111'
SELECT STATS_DATE(s.object_id,s.stats_id), sp.modification_counter,* 
FROM sys.stats AS s 
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp 
WHERE name = '_WA_Sys_00000010_65F62111' 

 

今回はデータの 12 % を更新しています。

その後、更新した項目を使用したクエリを実行し、このタイミングで統計情報の自動更新が動作するようにしています。

健作をしたタイミングで次の画像のように「Auto Stats」イベントが発生していることが確認できます。

つまり、「統計情報の自動更新」が発生したことになります。

image

統計情報の自動更新が行われましたので、変更カウンタ (modification_counter) についてもリセットされていることが確認できますね。

image

それでは「互換性レベルを 120」にしてみるとどうでしょうか。

先ほどとは異なり、「Auto Stats」イベントは発生しませんでした。

image

統計情報の自動更新が行われていませんので、変更カウンタについてもカウントアップされた状態となっています。

image

もう一度同じクエリを実行するとトータルとして「24 %」データが更新されたことになりますので、統計情報の自動更新が発生します。

このことから「SQL Server 2016 以降で、互換性レベル 120 以下を使用した場合、統計情報の更新の閾値は従来型の 20% が使用される」ということが言えます。

それでは「トレースフラグ 2371」を追加するとどうなるでしょうか。

トレースフラグ 2371 を追加すると、SQL Server 2016 以降で、互換性レベル 120 以下を使用していたとしても、新しいデータ量に応じた統計情報の更新を行うモデルに変更することができます。

トレースフラグを設定した状態であれば、「Auto Stats」イベントが発生し、変更カウンタについてもクリアされていることが確認できます。

image

新規インストールであれば、互換性レベルは最新の状態で利用することになると思いますが、アップグレードや移行を行った環境では、互換性レベルを変更せずに運用を行うケースも多いのではないでしょうか。

そのような場合は、トレースフラグを使用することで、統計情報の閾値を変更することができますので、方法を知っておくとよいかと。

新しい統計情報の閾値のモデルを使用すると、統計情報が更新される回数が増えるかと思います。

統計情報の更新は、同期的に実行するとコンパイルロックの競合に発生することもありますので、AUTO_UPDATE_STATISTICS_ASYNC のような統計情報の非同期更新の利用も検討しておくと良いのではないでしょうか。

Written by masayuki.ozawa

4月 14th, 2019 at 8:52 pm

Leave a Reply

*