SharePoint と SQL ブロッキングの話 Part1
<関連記事> SharePoint と SQL ブロッキングの話 Part1 |
皆さんこんにちは
SharePoint サポートチームの荒川です。
先日 Community Open Day というコミュニティ主催のイベントで登壇させていたき、SharePoit のパフォーマンス問題についてお話しさせていただきました。
内容は、以前にこちらのブログで公開した セキュリティクロール のお話と、最近になってサポートへのお問い合わせが増えてきた SQL ブロッキングのお話しだったのですが、SQL ブロッキングについては、最近、この問題に関連するサポートへのお問い合わせが増えてきましたので、こちらのブログでも内容をまとめておきたいと思います。
このブログの内容を簡単に確認されたい方は、Community Open Day で登壇した際の私のセッション動画 (USTREAM) をご参照ください。セッションでは概要部分を図解とデモを交えてお話ししていますので視覚的にもよりわかりやすいと思います。
USTREAM:過去事例から学ぶ SharePoint パフォーマンス問題とその対策 |
■現象
突然サイトにアクセスしても応答が返されず、砂時計表示のままページが閲覧できません。
複数のユーザーで同じ現象が発生している状況です。
■特徴
現象発生中に Web サーバーで IIS リセットをすると回避します。
また、数分から数十分程度で自然復旧することもあります。
■原因
一度に大量のレコードが更新された際に SQL Server のロックエスカレーションが発生し、コンテンツデータベース内のユーザーデータテーブルが長時間ロックされ、その間の後続の処理がブロックされることによりこの問題が発生します。
■解説
SQL Server にはメモリを節約するための機能としてロック エスカレーションという機能があります。 SQL Server の既定の設定では、1 つの Transact-SQL ステートメントで 5000 個以上のロックを取得した場合、メモリを節約するために、ロックをより粒度が大きい少数のロックにエスカレーションします。例えば、一度に数万件のレコードを更新するような処理をした際に、行ロックがテーブルロックにエスカレーションされることがあります。
SharePoint ではユーザーデータをコンテンツ データベースの 1 つのテーブルで一元管理していますので、データ量が多い時に、大量のレコードの更新を発生させるような処理を行うと、SQL Server の仕組みとしてロックエスカレーションが発生することがあります。これが発生すると、ユーザーデータが格納されたテーブルがロックされてしまいますので、同じテーブルを参照する後続の処理がロックの解放まで待たされる状況 (ブロッキング) が発生します。当然、ユーザーデータを参照するサイトの閲覧処理もこの影響を受けますので、結果としてサイトの閲覧ができなくなる (砂時計で待たされる) という状況が発生します。
この問題は必ず発生するものではありませんが、データ量が多い環境では比較的発生しやすいようです。ユーザーが長時間サイトを閲覧できなくなることから、かなりインパクトの大きな問題と言えるでしょう。
■対処方法
ブロッキングがロックエスカレーションによって発生しているのであれば、ロック エスカレーションの発生条件を緩和するための SQL Server チューニングが有効です。これにはトレース フラグ 1224 を使用します。SQL Server でロックエスカレーションが発生する条件は、ロックの数と、ロックに伴うメモリの使用量です。トレースフラグ 1224 を設定することで、このうちの「数」に関する条件を取り払うことができます。従って、大量の行ロックが発生した場合でもロックエスカレーションされなくなりますので、使用メモリは増加するものの、ブロッキングによりサイトの閲覧ができなくなるといった致命的な問題は回避することができます。
<手順> 1) [Microsoft SQL Server 2005] -> [構成ツール] -> [SQL Server 構成マネージャ] を起動します。 2) SQL Server のサービス -> SQL Server (MSSQLSERVER) を右クリックし、プロパティを開きます。 3) 詳細設定タブ -> 起動時のパラメータの先頭に、次の文字列を追加します。
-T1224; （セミコロンで区切ることで複数のパラメータを追加できます。）
4) OK を押して完了します。 5) SQL Server サービスを再起動します。 |
注意(2012/12/18 更新)： トレースフラグ T1224 の設定は SQL Server のインスタンス全体で有効になることに注意する必要があります。たとえば、検索データベースとコンテンツ データベースが同一インスタンスで構成されている場合、本設定の影響により、クロールに伴う検索データベースの大量の更新がロック エスカレーションされず、結果として SQL Server のメモリを大量に消費してしまう可能性があります。そうなると、メモリ負荷により SQL Server でキャッシュ済みのクエリプランがメモリ上から削除され、結果的にクエリのコンパイル待ちによるブロッキングやパフォーマンス劣化が発生する可能性があります。たとえば、T1224 の設定により一時的に問題が解消されるものの、クロールのタイミングで今度はシステム全体のパフォーマンス劣化が生じるといった別の問題が起きるといったケースが想定されます。
トレースフラグ T1224 の設定を行う場合、事前に十分な検証を行っていっただき、可能であれば別途専用の SQL Server インスタンスを用意して、そこに問題のコンテンツデータベースのみを移してから実装していただくことをお勧めします。
実は、最近になってこの現象に関するサポートへのお問い合わせが増えてきているように感じます。恐らく SharePoint を長期運用されているユーザーが増えてきたことで、システムに格納されるデータ量が増えてきたことがその原因ではないかと考察しています。
SharePoint では、キャパシティプランニングやパフォーマンス検証の資料において、リストに大量のアイテムを格納しないといったガイドラインがありますが、これらのガイドを守らなくとも、通常の動作には支障が無いケースもあります。ただし、一度に大量のデータを削除したり、更新したるするようなオペレーションが合わさることで突発的に問題が発生するケースもありますので、この記事の例のように、状況に応じた対策を行う必要が出てくることもあるでしょう。
勿論、事前の検証は必要ですが、サイトが閲覧できなくなるという、最悪の事態を防ぐために、データ量の増加が見込まれる環境においては予めトレースフラグ 1224 の設定を検討することをお勧めします。
<参考資料> ロックのエスカレーション (データベース エンジン) https://msdn.microsoft.com/ja-jp/library/ms184286(v=sql.105).aspx トレース フラグ (Transact-SQL) https://msdn.microsoft.com/ja-jp/library/ms188396(v=sql.90).aspx |
次回は、Community Open Day の限られた時間内に収められなかった、技術的により深い内容について掘り下げてお話ししたいと思います。