SE の雑記

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

SharePoint 2010 の Health Analyzer とデータベースのメンテナンスについて

leave a comment

Facebook に SharePoint サーバーの突発的高負荷は統計情報の再作成で回復する、こともある という書き込みがあり、どうなっているのか興味を持ったので調べてみました。
こちらのブログの記事は、SharePoint 2007 の内容になりますが、私の用意できた環境は 2010 だったので 2010 の場合の内容となります。

■Health Analyzer とデータベースのメンテナンス


SharePoint 2007 の場合はタイマージョブだったようなのですが、SharePoint 2010 の場合、データベースのメンテナンスは Health Analyzer で実行されています。

この中でデータベースのメンテナンス系のジョブとして、以下の 2 種類があります。

  • SharePoint で使用されているデータベースのインデックスは断片化されています。
  • SharePoint で使用されているデータベースには、古いインデックス統計情報があります。

SharePoint ではこれらのジョブを使用してデータベースのメンテナンスを行っているようです。
日単位で実行として設定されているので日次実行のようですね。

冒頭に紹介した記事の中でも書かれていますが、SharePoint のコンテンツデータベースは以下の設定になっています。

設定 SharePoint の設定 デフォルト
統計情報の自動更新 無効 有効
統計情報の自動作成 無効 有効
統計情報の非同期的自動更新 無効 無効
復旧モデル 完全 完全

 

SharePoint のコンテンツデータベースでは時計情報は自動更新/自動作成がされません。
そのため、統計情報が不足していても自動で作成が行われず、統計情報が古くなっていても Health Analyzer で該当のジョブが実行されない限り統計情報が更新されず、実データとの乖離が発生した状態となります。
SharePoint のテーブルの統計を見ると自動作成により作成された統計がなく、結構きれいな設定状態となっています。

それではそれぞれの内容を見ていきたいと思います。

SharePoint で使用されているデータベースのインデックスは断片化されています。

このジョブでは [dbo].[proc_DefragmentIndices] というストアドプロシージャが実行されます。
このストアドプロシージャではインデックスの再構築 (REBUILD) が実行されています。

処理の概略としては、

  • オンラインインデックス再構築ができるエディションではオンラインインデックス再構築を実行
    # SQL Server の Enterprise Edition を使用している場合
  • [sys.dm_db_index_physical_stats] を [LIMITED] で検索し、行数が 10,000 件を超えており、断片化の状態が 30% を超えているインデックスに対してインデックスの再構築を実行
  • 再構築の際に [FILLFACTOR] は [80] を設定
  • インデックスの再構築時に [STATISTICS_NORECOMPUTE=ON] を指定して、統計情報の自動更新は有効化させない

ということを行っています。

すべてのインデックスを再構築するのではなく、行数が 10,000 件 / 断片化の情報が 30% を超えているものを対象にインデックスの再構築を実施しています。また、オンラインで再構築ができるエディションに関してはオンライン再構築を行うようです。

統計情報の自動更新に関しては再構築後も無効にしていますが、これは自動更新の設定を無効にしているだけです。
インデックスの再構築を実行した場合、統計情報も更新されますがこの更新は無効になりません。
そのため、Health Analyzer でインデックスの再構築が実行された場合、統計情報も最新の状態に更新されます。

SharePoint で使用されているデータベースには、古いインデックス統計情報があります。

このジョブでは [dbo].[proc_UpdateStatistics] というストアドプロシージャが実行されます。
このストアドプロシージャで統計情報が更新されます。

こちらの処理の概略としては、

  • 自動作成により作成された統計情報を削除
  • 前回統計情報作成以降のインデックスの (変更行数×100) / (行数 +1) > 1 の場合、統計情報を再作成
  • サンプリングは既定のサンプリングを使用
  • [dbo].[AllDocs] AllDocs_Level の統計情報を再作成

ということを行っています。

統計情報を更新する対象は絞り込んでいるようですね。

これらのジョブを使用して SharePoint のデータベースのメンテナンスを実行しているようです。

ここで一つ気になるのが復旧モデルです。
デフォルトだと完全復旧モデルが設定されています。
インデックス再構築の際に復旧モデルの変更は行っていませんので、インデックスの再構築は最小ログ記録ではなく完全にログに記録されます。
# インデックス操作の復旧モデルの選択

また、SQL Server はデフォルトではクエリ実行の並列度は無制限になっており、使用できる CPU をすべて使うことができます。
SQL Server のエディション (Enterprise Edition) によっては 並列インデックス操作の構成 を使用することができますので場合によってはインデックス再構築で高い CPU の値を示すことがあるかもしれません。
# SharePoint で MAXDOP を 1 にするというドキュメントがありますが、通常利用のクエリのほかにこの辺の影響も考慮されていると思います。

実際にこれらのジョブが CPU を使用しているかは以下のようなクエリで調査できるかと思います。

SELECT
session_id
, start_time
, command
, blocking_session_id
, wait_type
, wait_time
, last_wait_type
, cpu_time
, total_elapsed_time
FROM
sys.dm_exec_requests
CROSS APPLY
sys
.dm_exec_sql_text(plan_handle)
WHERE
text LIKE ‘%[dbo].[proc_DefragmentIndices]%’
OR
text LIKE ‘%[dbo].[proc_UpdateStatistics]%’

image

cpu_time で CPU の使用状況を判断できます。
CPU の使用率が高い状態でこのクエリの WHERE を抜いた形で結果を取得してみるとどのクエリが影響していそうか調べられるかもしれないですね。

ちなみに、統計情報の自動更新を有効にしていて自動更新がジョブと重なる、手動でインデックスの再構築をしていた場合にジョブの実行と重なるといった場合にはロックの競合で後から実行された処理がブロッキングで待たされることになると思います。

統計情報を更新して現象が解決する場合、適切ではない実行プランが生成されてテーブルのフルスキャンが並列実行で実行されているという可能性もあるかもしれないですね。
# MAXDOP が制限されていない場合ですが。

Share

Written by Masayuki.Ozawa

9月 28th, 2012 at 11:34 pm

Posted in SharePoint,SQL Server

Tagged with ,

Leave a Reply