私はとある企業の情シスをしていますが、月次で実行しているとあるデータ集計処理が極端に遅くなりました。
普段であれば4～5秒で程度で終わる処理が1分以上掛かってタイムアウトするようになり、その処理のタイムアウト時間を2分に変更したところ、なんとかタイムアウトせずに処理が完了するようになりました。
集計処理内容は前月以前と変わっておらず、対象となるデータも極端に増えているわけではない。
すぐに「統計情報」に起因した事象の可能性を疑い、適切に対処を行ったことでDBのパフォーマンスは改善しましたが、このような現象における対処法などは誰かの参考になるかもと考えたため、簡単に記事でまとめておきます。
DBのパフォーマンスの異変ですぐに「統計情報」を疑えるようになるかは経験がものを言います！
よければ参考にしてくださいませ。
「統計情報」とは
「統計情報（statistics）」は、データベース内のテーブルやインデックスに関するデータの分布に関する情報を格納するシステムです。
統計情報は、クエリ最適化の際に重要な役割を果たします。
SQL Serverに限らず、一般的なRDBMSでは、アプリケーションなどから渡されてきたSQLコマンドを解析して最適化を行い、そのRDBMSで実行できるようにコンパイルを行い、指示されたコマンドを実行します。
この一連の処理を「オプティマイザ」と呼ばれる機能が担っており、RDBMSにおいて、この「オプティマイザ」は非常に重要です。
オプティマイザがSQLコマンドの最適化を行うために使用する情報が「統計情報」です。
統計情報では、各テーブル毎に行数や値の分布状態など様々な情報を保持しており、オプティマイザはその情報を参考にしながら、各テーブル間の結合方式に何を採用するか、インデックスをSeekするかScanするかなどの様々な検討を行い実行プランを決定します。
よって、この統計情報が正確ではない場合、オプティマイザは適切に実行プランの最適化を行うことができなくなります。
統計情報は、例えば既存の列に対して新しくインデックスを付与した場合などの特定のタイミングで更新されます。
また、RDBMSの既定設定として自動で更新されるようになっている場合も多いですが、更新タイミングや更新処理が実行される条件はRDBMSごとにまちまちです。
よって、この統計情報はデータベース管理者が適切なタイミングで更新し、データベースのパフォーマンスを維持するために適切に管理する必要があります。
統計情報がおかしくなるケース
一般的なRDBにおいては、管理対象のテーブルの用途は大きく分けて二種類あります。
一つは「マスタ系テーブル」です。
「商品マスタ」や「社員マスタ」などのように、頻繁に追加や更新がされるものではなく、主に参照で使われるテーブル群です。
もう一つは「トランザクション系テーブル」です。
「売上データ」や「発注データ」などのように、頻繁に追加や更新が発生するテーブル群です。
マスタ系テーブルでは頻繁に追加や更新がされるものではないことが大半であり、過去に生成した統計情報と現在のテーブルの状態が大きく乖離することは少ないのですが、トランザクション系テーブルの場合は、前回統計情報を生成した時期から時間が経つほど、当時のデータ状態との乖離は広がっていきます。
このように統計情報と実際のテーブルの状態において乖離が広がった場合は、オプティマイザは適切なクエリの実行プランを作成することができなくなり、パフォーマンスの低下を招きます。
時間の経過とともに統計情報が実際のテーブルの状態と乖離する以外にも、様々な利用で統計情報が適切ではなくなる場合があります。
統計情報がおかしくなる例は以下です。
- 過去データの移行などにより大量の行の追加や減少が発生した場合
- システム改修などによりデータの値の分布傾向が大きく変化した場合
- 行の追加や削除、値の更新が頻繁に発生しインデックスが断片化した場合
- RDBMSの何らかの異常によって統計情報が破損した場合
統計情報を生成した時点の行数から大幅に追加や削除が発生した場合は、当然統計情報で管理しているテーブルの行数を大きく乖離することになるため、最適な実行プランを選択することができなくなります。
業務アプリケーションのシステム改修などにより、対象テーブルの値の分布傾向が大きく変化する場合もあるかと思いますが、そのような場合も統計情報で管理していた値の分布情報と乖離が発生して、最適な実行プランを選択できなくなります。
行の追加や削除、値の更新が発生すると、対象のテーブルのインデックス領域のデータは断片化していきます。
断片化したインデックスに対して再構築などの適切なメンテナンスを怠っている場合、インデックスを使用したクエリのパフォーマンスは断片化する前に比べて低下していきます。
それにより、過去においては問題がなかった統計情報も断片化したインデックスの状態が加味されておらず、実際のテーブルの状態と乖離が発生して、最適な実行プランを選択できなくなります。
これらのように、通常は明確に原因があって統計情報が実態と乖離していくのですが、なかには、何の前触れもなく突然統計情報がおかしくなり、特定のテーブルのパフォーマンスが急激に劣化することもあります。
実際には統計情報がおかしくなる何らかの原因はあるんだと思いますが、原因を調べてもわからないままという事も多々あります。
DBのパフォーマンス低下で「統計情報」を疑うべき事象
当項では、データベースのパフォーマンスが急激に低下して「統計情報」を疑うべく事象を簡単にまとめていきます。
特定の画面や特定のクエリが急に遅くなった場合
統計情報は各テーブルごと、且つ列単位で保持します。
この統計情報がおかしくなった場合は、その対象のテーブル内の特定の列を条件にしたクエリのパフォーマンスが低下します。
よって、統計情報に起因したデータベースのパフォーマンス低下は、データベース全体ではなく、特定のテーブルや特定の列を参照したり条件とするクエリで発生します。
その為、特別なシステム改修も無く、データの極端な増加などもない状況で以下のような現象であれば、統計情報を疑います。
- ある日突然「業務システムの特定の機能(画面)」だけ急激に遅くなった。
- 数日前から特定のバッチ処理の処理時間が普段の倍以上掛かるようになった。
これらのポイントは「極端な性能劣化が局所的に発生」している状態です。
この場合は統計情報が一番怪しいでしょう。
データベースでロック待ちやデッドロックが頻発するようになった場合
これまでは問題がなかったのに、最近になって急にデータベースでロック待ちが発生するようになったり、これまで殆ど発生しなかったデッドロックが発生するようになった場合なども、統計情報を確認した方がよいです。
RDBMSのロック制御の仕様によって「ロック待ち」の発生し易さに違いがあり、例えばSQL Serverの場合は既定で共有ロックを取得しにいくため、Oracleなどと比較するとロック待ちが発生し易いのですが、システム改修などの変更をしていないにも関わらずそれが極端に増えたり、デッドロックが増えたという場合は、データベース自体の性能低下が疑われます。
クエリ単体のレスポンスが下がることにより、本来は一瞬で終了していたロック時の専有時間が延び、その積み重ねでロック待ちが極端に増える、デッドロックが発生するようになるケースは十分考えられます。
その性能低下がデータベース全体なのか、原因を突き詰めると特定のテーブルのパフォーマンス低下に起因しているかを見極めたうえで、特定のテーブルに起因しているなら、原因はインデックスの断片化か統計情報のどちらかが怪しくなります。
尚、インデックスの断片化による性能の低下は、レコードのINSERTやDELETE、UPDATEの発生の都度、少しずつ悪化していくものであり、ある日突然性能が低下した場合は、やはり統計情報がおかしくなった可能性が高いです。
この事例でのポイントも、表面的にはロックに起因した性能低下ですが、そのロックを発生させている原因は「極端な性能劣化が局所的に発生」している状態にあります。
尚、当ブログでは、過去にRDBにおける「ロック」について記事で解説しています。
興味があれば一度ご一読ください。
統計情報がおかしい場合の対処方法(SQL Serverの場合)
当項では、統計情報がおかしいと判断した場合にどのように対処するべきかを紹介します。
ストアドプロシージャ「sp_updatestats」の実行
SQL Serverでは、組み込みストアドプロシージャとして「sp_updatestats」が用意されており、このストアドを実行することで、対象のデータベースのすべてのテーブルの統計情報を更新します。
詳しい仕様は以下のリンク先を確認してください。
ストアドプロシージャはEXECUTEコマンドで実行できます。
EXEC と略すことも可能です。
例えばSSMS(SQL Server Management Studio)のクエリ作成画面で以下のように指定して実行します。
EXEC sp_updatestats
実行すると、実行結果をメッセージに出力してくれます。
尚、上記のリンク先にも記載されていますが、このストアドプロシージャを実行するには、実行するユーザーのロールがデータベースの所有者かsysadminのメンバーである必要があります。
よって、実行権限を持つユーザーでログインしてください。
「sp_updatestats」をタスクスケジューラーから実行する場合
前述した「sp_updatestats」を実行する場合は、一般的には夜間など極力オンラインのユーザーに影響が少ないタイミングで行いたいものです。
その場合は、何らかの方法でスケジューリングし、深夜などの時間に自動実行させることになります。
スケジューリングして自動実行させる方法も色々ありますが、当記事では、「タスクスケジューラー」＋「bat」＋「sqlcmd」を組み合わせた実装方法を紹介します。
まず、「sqlcmd」はSQL Serverをインストールすると併せて利用可能になるSQL Serverのコマンド実行用ユーティリティです。
複数のオプションを組み合わせてコマンドラインでSQLの実行などの操作が行えます。
詳しい解説については以下のリンク先をご確認ください。
尚、当ブログでは、過去にタスクスケジューラの使い方についても記事にしておりますため、良ければこちらもご一読ください。
細かい解説を抜きにして、まず、batファイルを作成して、そのなかでsqlcmdを使用してsp_updatestatsを実行するスクリプトの作成例だと以下です。
@echo off
sqlcmd -U [ユーザー名] -P [パスワード] -S [ホスト名] -d [データベース名] -Q "EXEC sp_updatestats;" -o [ログ出力先ファイルパス]
exit
-o オプションで実行結果をテキストファイルに出力しています。
ログ出力先ファイルパスは、フルパスでファイル名まで指定してください。
また、複数のデータベースを対象に実施したい場合は、以下のように記述します。
@echo off
sqlcmd -U [ユーザー名] -P [パスワード] -S [ホスト名] -d [データベース名1] -Q "EXEC sp_updatestats;" -o [ログ出力先ファイルパス1]
sqlcmd -U [ユーザー名] -P [パスワード] -S [ホスト名] -d [データベース名2] -Q "EXEC sp_updatestats;" -o [ログ出力先ファイルパス2]
exit
また、以下のようにbatファイルに記述してあげれば、ログの出力内容の先頭と末尾に開始時間と終了時間を残せるのでおススメです。
@echo off
rem yyyymmdd形式の日付を生成します。
set yyyyMMdd=%date:/=%
echo %time% 処理を開始します。>> %yyyyMMdd%_exec.log
rem カレントディレクトリに移動します。
cd /d %~dp0
sqlcmd -U [ユーザー名] -P [パスワード] -S [ホスト名] -d [データベース名1];" >> %yyyyMMdd%_exec.log
echo %time% 処理を終了します。>> %yyyyMMdd%_exec.log
exit
テーブル名指定などで実行する場合は「UPDATE STATISTICS」
前述した「sp_updatestats」は、対象のデータベース内のすべてのテーブルの統計情報を更新します。
また、既定のサンプリングが使用されます。
統計情報の更新対象を全テーブルではなく特定のテーブルや特定の列(インデックス)に限定したい場合や、サンプリングではなく対象テーブルや対象列の全行を参照して統計情報を更新したい場合は、「UPDATE STATISTICS」句を使用します。
「UPDATE STATISTICS」の詳しい解説は以下のリンク先をご確認ください。
また、前項で紹介したように、この構文をsqlcmdで実行するように記述したbatファイルを作成して、タスクスケジューラーにセットしておくことで、任意のタイミングで自動実行させることも可能です。
テーブル名指定して実行する場合
統計情報を更新するテーブル名を指定して実行する場合は以下のように記述します。
UPDATE STATISTICS [スキーマ.テーブル名]
既定のスキーマのdboを使用しており、テーブル名が「table01」なら以下です。
UPDATE STATISTICS dbo.table01
列名(インデクス名)指定して実行する場合
統計情報を更新する列まで指定する場合は以下のように記述します。
UPDATE STATISTICS [スキーマ.テーブル名] [インデックス名]
インデックス名が「IX_01」なら以下です。
UPDATE STATISTICS dbo.table01 IX_01
全行を解析して実行する場合
既定では、テーブルの全行ではなく一部のデータをサンプリングして統計情報を作成しますが、それでは正確な統計情報が生成されず、パフォーマンスが改善しない場合もあります。
その場合は明示的に全行を指定して更新を実施します。
その場合は「FULLSCAN」オプションを使用します。
UPDATE STATISTICS [スキーマ.テーブル名] WITH FULLSCAN
インデックス名まで指定して全行を対象に統計情報を更新するなら以下です。
UPDATE STATISTICS dbo.table01 IX_01 WITH FULLSCAN
その他の統計情報更新における参考情報
統計情報を扱うにあたり、これまで記載していた内容以外にも、知っておいてほしい内容を簡単に紹介しておきます。
- 統計情報を更新することで、メモリ内でキャッシュされたコンパイル済みクエリは消える
- 統計情報を更新しても、ストアドプロシージャ自体の再コンパイルはされない。
RDBMSでは、実行されたSQLは最適化されて実行可能な状態にコンパイルされると説明しましたが、このコンパイル処理はRDBMSでも重い処理にあたり、その負荷の軽減や高速化のために、コンパイルされた状態をキャッシュとしてメモリ内に保持します。
その後、類似したクエリが発行されたら、そのキャッシュにあるコンパイル済みクエリを実行します。
統計情報を更新することで、これらのキャッシュにあるコンパイル済みクエリも消えることになるため、一時的な性能の低下や、負荷の増加が発生する可能性があります。
また、ストアドプロシージャもコンパイルされた状態でRDBMSのキャッシュに保持されますが、統計情報を更新するだけはストアドプロシージャの再コンパイルはされません。
再コンパイルがされないことで、古い実行プランを持ち続ける可能性があるため、もし統計情報を更新しても特定のストアドプロシージャのパフォーマンスが改善されないといった場合は、明示的に「sp_recompile」で再コンパイルが必要な場合もあります。
詳しくは以下のリンク先を確認してください。
SQL Serverにおける統計情報の仕様
当記事では、統計情報について簡単な内容で紹介していますが、SQL Serverにおける統計情報に関する詳しい解説を確認されたい場合は、Microsoft Learnの以下のリンク先をご一読ください。
サンプリング方式や統計情報の内容に関する仕様や統計情報に関わるオプションなどが記載されています。
内容は難解ですが、SQL Serverのパフォーマンスで困ったときは真剣に読んでもらうとヒントに繋がるかも知れません。
最後に
今回の記事では、主にSQL Serverを利用している環境を対象に、統計情報の異常によるデータベースのパフォーマンス低下の事例や、判別方法、実際の統計情報を更新するSQLコマンドなどを紹介しました。
尚、統計情報の役割やクエリパフォーマンスにおける重要性はSQL Server以外のRDBMSにとっても同様であり、当記事で記載しているSQLコマンド以外の内容については、別のRDBMSに置き換えて読んでいただいても構いません。
長年データベースを運用していると、統計情報に起因したトラブルや障害も度々経験し、現象からすぐに原因が思い当たるようになりますが、経験が浅いSEやシステム管理者の場合、すぐに原因に辿り着けずハマる場合も多いです。
当記事がそんなSEやシステム管理者のお役に立てば幸いです。
今回も長々と読んでいただきましてありがとうございます。
それでは皆さまごきげんよう！