システム移行では、移行期間中はデータベースの統計情報に注目しておらず、移行後に「特定の処理が遅延するようになった」といったパフォーマンスのトラブルが発生して、初めて統計情報に向き合う事例が散見されます。その原因は、旧システムで最適化されていた統計情報が新しい環境ではリセットされてしまい、一から新たに構築する必要がある点にあります。
本稿では、SAPユーザー様に見えづらかった統計情報の仕組みを理解する目的として、リアルテックジャパンがSAPシステム移行時に統計情報に対してどのように対処しているかを解説したいと思います。
SQLServer編とOracle編の2回に分けてご紹介する予定であり、本稿はSQLServer編です。
統計情報とは？
各データベースには、個々のSQL文から具体的なデータの取得方法(実行計画)を選択する独自の機能(オプティマイザ)が搭載されています。オプティマイザには、実行計画の選択基準として大きく2つのアプローチがあり、コストベース・オプティマイザ(CBO)とルールベース・オプティマイザ(RBO)です。
コストベース・オプティマイザ(CBO)は、データベースのオブジェクトに関する情報(表やインデックス・データの量や偏りなど)とシステムに関する情報(CPUやI/Oなど)を定期的に収集し(オプティマイザ統計と呼ぶ)、その情報に基づいて最も効率的と思われるアクセスパスをSQL文の実行計画として選択します。そのため、オプティマイザ統計のメンテナンスは必要不可欠です。
ルールベース・オプティマイザ(RBO)は、アクセスパスに対する固定のルールから実行計画を選択する方法です。アクセスパスに対して予めランクを定め、SQL文のWhere条件やインデックスの有無などからランクが最も上位のものを選択します。コストベースとは異なり、データの量や偏りが変動しても、同じSQL文では毎回同じ実行計画を選択します。そのため、データベースの使用状況によってチューニングが必要不可欠です。
現在は、刻一刻と変化するデータベースの使用状況にフィットした実行計画が自動的に選択できるようになるコストベース・オプティマイザ(CBO)が主流となっています。CBOを利用しながら、一部の処理ではクエリヒントを使用してRBO的な実行計画を選択するパターンもあります。最近ではインメモリデータベースがSAPでサポートされていますが、インメモリ用に最適化された統計の新しい仕組みが各データベースで提供されており、統計情報(あるいはそれに代わる新しい仕組み)が重要であることに変わりはありません。
今回説明する統計情報とは、ディスクベースのコストベース・オプティマイザ(CBO)で利用されるオプティマイザ統計を対象とします。
SAPシステム移行と統計情報の関係
SAPシステムの移行には、大きく２種類の方法があります。
１つはデータベースのデータファイルやバックアップを新しいシステムにコピーまたはリストアする方法です。SAPでは一般的にデータベース固有の方法と呼ばれ、同機種間システムコピーで利用されます。新旧のシステムでOSとデータベースが同一であることが必須です。データベースのオブジェクト(テーブルやインデックスなど)を新しい環境で再利用することができ、統計情報も引き継がれます。
もう1つは旧データベースからデータをエクスポートし、新しいデータベースにデータをインポートする方法です。SAPではデータベース非依存の方法またはR3loadベースと呼ばれ、同機種と異機種間システムコピーで利用されます。新旧のシステムでOSやデータベースが異なる場合やユニコード変換では必須ですが、データベースの再構築による目的(断片化の解消や圧縮化など)のために、同機種間システムコピーでも多く利用されています。この方法では、データベースのオブジェクトを新規に作成する必要があり、統計情報も移行されないため、同様に新規に構築する必要があります。
SAPシステム移行と同時にデータベースのバージョンをアップグレードする場合も注意が必要です。データベースのバージョンが上がるとオプティマイザの動作に変更があるからです。一般的に、新しい環境・新しいデータベースではハードウェアやオプティマイザの更改によって、SQLの性能が一般的によくなると思われます。ですが、新しいオプティマイザが意図しない実行計画を選択して、一時的にパフォーマンスが低下することもあります。そのため、SAPシステム移行では、新しいデータベースに搭載されるオプティマイザの機能や仕組みを押さえ、必要に応じて統計情報を再設計することが重要となります。
次項では、リアルテックジャパンがSAPシステムの移行において、統計情報に対して具体的にどのように対処しているかをご説明します。特に断わりのない限り、移行方法は同機種間のデータベース非依存の方法(R3loadベース)、SAPはERP(NW7.0以上)、SQLServerは2008R2以上を前提とします。なお、対処方法は顧客要件や環境によって異なるため、SAP環境下の標準的な事例としてご認識ください。
[RELATED_POSTS]
SQLServerの統計情報とその対処方法
SQLServerの統計情報は、自動機能を最大限に活用することがコンセプトとなります。Oracleとは異なり、統計情報に関する多くのパラメータや機能によるマニュアル調整はSQLServerでは不要であり、非常にシンプルです。言いかえると、良くも悪くもSQLServerにお任せで、SAPレベルで統計を制御したり管理することはほとんどどありません。
テーブルとインデックス
SAPのSQLServerは、主キーを索引列にした1つのクラスター化インデックス(プライマリインデックス)と、主キー以外を索引列にした複数の非クラスター化インデックス(セカンダリインデックス)を保持します。テーブルの実データは、クラスター化インデックス内に一体化しており、主キーの順序で物理的に並べられた状態で格納されています。
統計情報
SQLServerの統計情報は、カタログビューを介して取得することができ、主にテーブルに作成されたインデックス単位で管理されています。また後述する「統計の自動作成」がONの場合や手動で明示的に作成した場合は列単位でも存在します。
統計に関係する移行作業は概ね以下の流れです。- 旧SQLServerからデータをエクスポート
- 新SQLServerにデータをインポート
- 統計オプションの設定
- 統計更新
- 統計設定のチェック
<ステップ①：旧SQLServerからデータをエクスポート>
データのエクスポートは、特定の条件下でない限りは、特に統計情報を意識することはありません。エクスポートは「ソートあり(主キーの順で並び替え)」または「ソートなし(順次読み込み)」のどちらかで行うことができ、テーブル単位で制御することが可能です。SQLServerのテーブルはクラスター化インデックス構造であるため、どちらの方法でも、クラスター化インデックスをスキャンしてデータを読み込みファイルに書き込みます。テーブルを分割してエクスポートする場合は、クラスター化インデックスをシークしてデータを読み込み複数のファイルに分割して書き込みます。
<ステップ②：新SQLServerにデータをインポート>
インポートステップは次の順で処理されます。- テーブルの作成
- プライマリインデックスの作成
- データのインポート
- セカンダリインデックスの作成 (※存在する場合)
データのインポートでは、SQLServerの バルク・コピー インターフェイスを使用したデータの一括ロードが行われ、エクスポートデータの並び状況によって、内部的にソートが発生します。インデックスの作成では、自動的にインデックスに対する統計も作成されます。このとき取得される統計の精度はフルスキャン(サンプリング100%)です。プライマリインデックス作成時はテーブルが空のため、プライマリの統計は作成されません。一方、セカンダリインデックスではデータが存在するため、フルスキャンの統計が作成されます。つまりデータのインポートが完了した時点で、統計情報は不完全な状態です。
<ステップ③：統計オプションの設定>
ここでは、以下の設定を行います。
- 統計の自動作成をON
- 統計の自動更新(同期)をON
- 統計の自動更新(非同期)をON
統計の自動作成
統計の自動作成とは、SQL文のWhere条件または Join条件において、その列に統計(ヒストグラム)がない場合に、列単位の統計を自動的に作成します。これにより、オプティマイザは実行計画を選択するために必要な情報を確実に得ることができるようになります。自動的に作成される統計は、名前が_WA_で始まります。
統計の自動更新(同期/非同期)
統計の自動更新とは、統計が古いと判断されたときに、オプティマイザが統計を自動的に更新します。同期更新は、クエリの実行前に統計更新が行われ、 非同期更新は、非同期(通常はクエリの実行後)に統計更新が行われます。同期更新を適用すれば、常に最新の統計が使用されますが、 非同期更新では、統計が古い場合でも既存の統計から実行計画を選択することになります。非同期更新は同期更新のオプションとなり、非同期更新を適用するには同期更新の設定もONにしておく必要があります。つまり、上記設定は非同期更新を適用しています。非同期更新の場合、統計が最新になるまで待機せずにクエリを実行できることから、ERPのようなOLTPシステムでは、非同期更新を適用することが推奨されます。BWでは、一括更新によってデータの分布が大きく変わる特性を考慮し、確実に最新の統計を使用したクエリが実行されるよう、同期更新を適用することが推奨されます。
自動更新の条件
上記に「統計が古いと判断されたときに」とありますが、以下の条件を満たしたときに、統計の自動更新が行われます。
- テーブル行数が 500 以下で、前回の更新から 500 回以上変更された場合
- テーブル行数が 501 以上で、前回の更新から「500 +テーブル行数の20% 」以上変更された場合 ※2014以前
- テーブル行数が 501 以上で、前回の更新から「①(500 +テーブル行数の20%) と②(1000×テーブル行数の平方根)のうち小さい値」以上変更された場合 ※2016以降
※変更数はsys.dm_db_stats_properties カタログ ビューのmodification_counterから追跡されます
上記の条件は、SQLServer のバージョンによって計算方法が異なり、上位バージョンでは、特に大規模なテーブルにおいて統計の自動更新がより発生しやすくなるように変更されています。たとえば、10万行のテーブルの更新条件は以下となるため、SQLServer 2016以降は統計がより頻繁に更新されます。
2014では、500 + (100,000×0.2) = 20,500
2016では、MIN((500 + 100,000×0.2), SQRT(1000×100,000)) = MIN(20,500, 10,000) = 10,000
<ステップ④：統計更新>
ステップ②で説明したとおり、インポート後はプライマリインデックスに対する統計が欠落した状態です。この状態では、オプティマイザが誤った実行計画を選択する可能性があるため、sp_updatestatsを実行して、手動で全テーブルに対して統計更新を行います。サンプリングを指定しない場合、既定の精度(SQLServerによって決められるデフォルトのサンプリング)で統計が取得されます。sp_updatestatsは、自動統計更新の条件を使用するため、インポート直後は大半変更がないのでセカンダリインデックスは更新の対象外となります。そのため、プライマリインデックスは新規に統計が作成され、セカンダリインデックスのほとんどの統計は100%の精度を維持した状態となります。
<ステップ⑤：統計設定のチェック>
SQLServerの設定が推奨どおり適切な状態であるかをチェックするために、トランザクションDBACOCKPITまたはDB02のSQL Server Setup Checkを実行します。このチェック項目の中で、テーブルVBHDR、VBDATA、およびVBMODの自動統計更新のステータスを確認し、ON(有効)であればOFF(無効)に変更します。前述のとおり、SQLServerでは統計の自動更新をONにしておきますが、この3テーブルについては自動更新されないようにOFFにしておく必要があります。この対応は、SAPインストール時にスクリプトsap_z_set_parametersで自動的に実行されており通常は不要ですが、リセットされている場合もあるため注意が必要です。OFFにするには、レポートMSSPROCSから手動でsap_z_set_parametersを実行するか、直接sp_autostatsを実行することで可能です。
以上が基本のステップとなります。
旧システムで統計の精度を高める対策を個別に実施しており、新システムにおいても対応が必要である場合は、さらに追加のステップを実施していくことになります。たとえば、特定のテーブルの既定のサンプリングではデータの分布を表すのに不十分として、100%のサンプリングで更新している場合などです。また、移行と同時にSAP/SQLServerのアップグレードを行っている場合は、同じ機能でもクエリやオプティマイザの動作に変更が発生している可能性があるため、注視していく必要があります。
まとめ
統計情報はシステムの全体的なパフォーマンスにとって重要な要素であり、適切なメンテナンスと注意が必要です。また各環境はさまざまで且つ固有のものであり統計の保守に関するニーズも異なります。SAPシステムの移行やアップグレードでパフォーマンス問題が発生しないか心配されている方も、すでにSAPのパフォーマンス問題に直面している方も、何か疑問点がございましたらリアルテックジャパンにお気軽にご相談ください。
