SharePoint 2010 SQL Sever の統計の自動作成の無効について
こんにちは、SharePoint サポートの渡邉です。
今回は SharePoint 2010 が使用するデータベースの統計の自動作成 (AUTO_CREATE_STATISTICS) に関する内容です。
データベースの統計の自動作成を、一部の例外を除いて無効にすることを推奨している理由や自動作成を有効にした場合の影響について解説します。
- 目次
2. なぜ統計の自動作成 (AUTO_CREATE_STATISTICS) を無効化するのか
1. はじめに
SharePoint 2010 製品が使用するデータベースでは、統計の自動作成 (AUTO_CREATE_STATISTICS) を有効にしないでください。
この内容は TechNet の以下の部分でも記載しています。詳細について、次の項目で説明していきます。
タイトル : SQL Server のオプションを設定する
アドレス : https://technet.microsoft.com/en-us/library/cc298801(v=office.14).aspx#Section6_3
機械翻訳 : https://technet.microsoft.com/ja-jp/library/cc298801(v=office.14).aspx#Section6_3
-- 抜粋 --
Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server. SharePoint Server configures the required settings upon provisioning and upgrade. . Auto-create statistics can significantly change the execution plan of a query from one instance of SQL Server to another instance of SQL Server. Therefore, to provide consistent support for all customers, SharePoint Server provides coded hints for queries as needed to provide the best performance across all scenarios.
-- 抜粋 –
2. なぜ統計の自動作成 (AUTO_CREATE_STATISTICS) を無効化するのか
SharePoint 2010 製品のすべてのデータベースは、既定で 統計の自動作成 (AUTO_CREATE_STATISTICS) が無効になっています。
SharePoint 2010 製品では、SQL Server に対してクエリを実行する場合、必要なクエリヒントを使用しています。
そのため、環境や運用状況に依存せず、最適な実行プランでクエリが実行されます。
TechNet の記載の通り(リンク先 : SQL Server 2005 を使用した統計情報の作成)、
SharePoint 2010 製品がインストールされた環境で 統計の自動作成 (AUTO_CREATE_STATISTICS) を有効にした場合、
SharePoint 製品が予め作成している統計以外の列統計が作成されます。
列統計が作成された環境でクエリを実行した場合、クエリヒントや SharePoint 製品が予め作成した統計情報以外の要素が実行プランに影響するため、
SharePoint 2010 製品が想定する実行プランでクエリが実行されない可能性があります。
なお、列統計は、統計の自動作成 (AUTO_CREATE_STATISTICS) の有効にした以降のクエリ実行時に自動で作成されます。
作成された列統計はオブジェクトモデルを使用して削除することは出来ますが、
SharePoint 製品ではデータベースに対して直接操作することは推奨していません。
手動で削除をした場合、SharePoint 製品が予期しない動作をする可能性があります。
そのため、通常は統計の自動作成 (AUTO_CREATE_STATISTICS) は無効のまま運用していただくことをお勧めします。
3. 例外的な対応として
ほとんどの環境では、統計の自動作成 (AUTO_CREATE_STATISTICS) を無効化した状態で、
SharePoint 2010 製品が高いパフォーマンスを発揮することが出来ます。
しかしながら、利用シナリオによっては稀に非効率なクエリプランが作成され、パフォーマンスの問題が発生する場合があり、
統計の自動作成 (AUTO_CREATE_STATISTICS) が、有効な対処策となることがあります。
一例としては、クエリ実行プラン作成時に Table Spoolオペレータを使用しない実行プランを選択するケースです。
Table Spool オペレータが選択された場合、クエリ実行時のデータの処理が効率的に実施されるため、クエリの実行時間が短縮できます。
Table Spool オペレータ使用の可否はクエリヒントで指定ができないため、SQL Server のクエリ オプティマイザが使用の可否を判断します。
SharePoint 製品が事前に作成した統計に対して、クエリ オプティマイザが Table Spool オペレータを使用する必要が無いクエリであると判断する場合があり、
クエリの実行が遅くなる場合があります。このような場合、統計の自動作成 (AUTO_CREATE_STATISTICS) を有効化して運用することで、
Table Spool オペレータを使用する実行プランが作成され、クエリの実行時間が短縮できる場合があります。
4. 統計の自動更新について
運用中に実行したクエリにより、統計情報と実際のデータにかい離が生じる場合があり、クエリを実行するタイミングにより一時的に最適でないクエリプランが選択されることがあります。
その場合は統計情報を更新する必要がありますが、SharePoint 2010 製品では、Health Analyzer を使用して定期的に統計の更新を行っています。
そのため、通常は SQL Server で統計の自動更新 (AUTO_UPDATE_STATISTICS) も有効にする必要はありません。
しかしサポートでは、パフォーマンス改善を行う目的で統計情報を手動で更新することをご案内する場合があります。
Health Analyzer を使用して行う統計情報の更新は、タイマージョブにより定期的に行われますが、インデックスの断片化の状況や統計情報の作成状況により、常時最新の統計情報に出来ない可能性があります。
その場合は、クエリを使用して統計情報を定期的に更新することをご案内する場合があります。
統計情報を手動で更新するクエリは <SharePoint Server 2010 でコンテンツデータベースの統計情報を手動で更新するクエリ> をご参照ください。
< 統計に関する Health Analyzer について>
タイトル : SharePoint で使用されているデータベースに古いインデックス統計情報があります (SharePoint Server 2010)
アドレス : https://technet.microsoft.com/ja-jp/library/hh564122(v=office.14).aspx
タイトル : SharePoint で使用されているデータベースのインデックスが断片化されています
アドレス : https://technet.microsoft.com/ja-jp/library/ff805067(v=office.14).aspx
<SharePoint Server 2010 でコンテンツデータベースの統計情報を手動で更新するクエリ>
/* -- 以下のクエリを使用して、タスクスケジューラーなどから定期的に統計情報を更新することでパフォーマンスが改善できる場合があります。 */
/* -- <コンテンツ データベース名> の部分はパフォーマンスが低下しているサイトコレクションのコンテンツデータベースをご指定下さい */
use <コンテンツ データベース名>
go
/* 以下は SharePoint Server 2010 のコンテンツデータベースに含まれているテーブルです */
update statistics NameValuePair_Chinese_PRC_CI_AS
update statistics Deps
update statistics TimerLock
update statistics NameValuePair_Chinese_PRC_Stroke_CI_AS
update statistics AllDocs
update statistics DiskWarningDate
update statistics NameValuePair_Chinese_Taiwan_Bopomofo_CI_AS
update statistics Workflow
update statistics NameValuePair_Chinese_Taiwan_Stroke_CI_AS
update statistics NameValuePair_Croatian_CI_AS
update statistics WorkflowAssociation
update statistics NameValuePair_Cyrillic_General_CI_AS
update statistics NameValuePair_Czech_CI_AS
update statistics ScheduledWorkItems
update statistics NameValuePair_Danish_Norwegian_CI_AS
update statistics NameValuePair_Estonian_CI_AS
update statistics ComMd
update statistics NameValuePair_Finnish_Swedish_CI_AS
update statistics EventReceivers
update statistics NameValuePair_French_CI_AS
update statistics NameValuePair_Georgian_Modern_Sort_CI_AS
update statistics AllDocStreams
update statistics AllDocVersions
update statistics ContentTypes
update statistics NameValuePair_German_PhoneBook_CI_AS
update statistics NameValuePair_Greek_CI_AS
update statistics AuditData
update statistics NameValuePair_Hebrew_CI_AS
update statistics AllLinks
update statistics SiteQuota
update statistics NameValuePair_Hindi_CI_AS
update statistics NameValuePair_Hungarian_CI_AS
update statistics ContentTypeUsage
update statistics NavNodes
update statistics NameValuePair_Hungarian_Technical_CI_AS
update statistics RecycleBin
update statistics BuildDependencies
update statistics NameValuePair_Icelandic_CI_AS
update statistics DatabaseInformation
update statistics Solutions
update statistics NameValuePair_Japanese_CI_AS
update statistics AllSites
update statistics SolutionResourceUsageLog
update statistics NameValuePair_Japanese_Unicode_CI_AS
update statistics SolutionResourceUsageWindowed
update statistics SolutionResourceUsageDaily
update statistics NameValuePair_Korean_Wansung_CI_AS
update statistics SolutionResourceUsageDailyOrdinal
update statistics CollationNames
update statistics NameValuePair_Korean_Wansung_Unicode_CI_AS
update statistics AllUserDataJunctions
update statistics NameValuePair_Latin1_General_CI_AS
update statistics NameValuePair_Latvian_CI_AS
update statistics NameValuePair_Lithuanian_CI_AS
update statistics AllLookupRelationships
update statistics NameValuePair_Lithuanian_Classic_CI_AS
update statistics AllFileFragments
update statistics NameValuePair_Traditional_Spanish_CI_AS
update statistics SharedAccessRequests
update statistics Resources
update statistics NameValuePair_Modern_Spanish_CI_AS
update statistics AllListUniqueFields
update statistics StorageMetrics
update statistics NameValuePair_Polish_CI_AS
update statistics NameValuePair_Romanian_CI_AS
update statistics StorageMetricsChanges
update statistics HT_Settings
update statistics NameValuePair_Slovak_CI_AS
update statistics HT_Cache
update statistics NameValuePair_Slovenian_CI_AS
update statistics SiteDeletion
update statistics NameValuePair_Thai_CI_AS
update statistics NameValuePair_Turkish_CI_AS
update statistics SiteVersions
update statistics NameValuePair_Ukrainian_CI_AS
update statistics AllWebs
update statistics UserInfo
update statistics NameValuePair_Vietnamese_CI_AS
update statistics NameValuePair_Azeri_Cyrillic_90_CI_AS
update statistics WebMembers
update statistics NameValuePair_Azeri_Latin_90_CI_AS
update statistics Perms
update statistics NameValuePair_Chinese_Hong_Kong_Stroke_90_CI_AS
update statistics NameValuePair_Divehi_90_CI_AS
update statistics Groups
update statistics NameValuePair_Indic_General_90_CI_AS
update statistics NameValuePair_Kazakh_90_CI_AS
update statistics WebsPlus
update statistics GroupMembership
update statistics Roles
update statistics NameValuePair_Macedonian_FYROM_90_CI_AS
update statistics NameValuePair_Syriac_90_CI_AS
update statistics AllLists
update statistics RoleAssignment
update statistics NameValuePair_Tatar_90_CI_AS
update statistics NameValuePair_Uzbek_Latin_90_CI_AS
update statistics AllWebParts
update statistics AllListsPlus
update statistics WebPartLists
update statistics AllListsAux
update statistics Features
update statistics AllUserData
update statistics FeatureTracking
update statistics CustomActions
update statistics EventBatches
update statistics EventCache
update statistics EventLog
update statistics EventSubsMatches
update statistics ImmedSubscriptions
update statistics SchedSubscriptions
update statistics NameValuePair
update statistics Personalization
update statistics NameValuePair_Albanian_CI_AS
update statistics Versions
update statistics NameValuePair_Arabic_CI_AS
update statistics WelcomeNames
go
DBCC FREEPROCCACHE
GO
/* -- サンプルクエリ ここまで*/
*** 注意 ***
本稿は、統計の自動作成 (AUTO_CREATE_STATISTICS) と統計の自動更新 (AUTO_UPDATE_STATISTICS) をユーザー様やシステム管理者様の判断の下、有効にすることを推奨する内容ではありません。
お客様の環境の SharePoint 製品が十分なパフォーマンスを発揮していない時、その要因は多岐に渡ります。
パフォーマンスが発揮できない理由は、本稿記載のクエリに実行速度の低下以外にも、ネットワーク環境や各種サービスアプリケーション、そのほかの運用状況などが影響している可能性があります。
そのため、パフォーマンス改善を目的として、安易に統計の自動作成 (AUTO_CREATE_STATISTICS) および 統計の自動更新 (AUTO_UPDATE_STATISTICS) を有効化しないでください。
環境により、統計の自動作成 (AUTO_CREATE_STATISTICS) もしくは統計の自動更新 (AUTO_UPDATE_STATISTICS) を有効にしたことによりパフォーマンスが改善しない可能性もあります。
統計の自動作成を有効にした場合、元の状態に戻すことが実質的に出来ません。
パフォーマンスの問題が発生した場合には弊社サポートまでお問い合わせくださいますようお願いいたします。
5. おわりに
本稿では SharePoint 2010 製品についてのみ言及しています。SharePoint 2013 製品では、より効率的なクエリが実行されるため、
統計の自動作成 (AUTO_CREATE_STATISTICS) を有効にしなければならないケースはありません。
弊社サポートでもパフォーマンスに関して、弊社公開情報のご提供やご契約によりトラブルシュートを承っております。お困りの際にはご相談ください。
以下の公開情報では、パフォーマンス改善に関する資料を公開しておりますので、ご参考にしていただければ幸いです。
<参考情報>
タイトル : パフォーマンスと容量の管理 (SharePoint Server 2010)
アドレス : https://technet.microsoft.com/ja-jp/library/cc262971(v=office.14).aspx
タイトル : SharePoint Server 2010 での容量管理と規模設定の概要
アドレス : https://technet.microsoft.com/ja-jp/library/ff758647(v=office.14).aspx
タイトル : SharePoint Server 2010 の容量計画
アドレス : https://technet.microsoft.com/ja-jp/library/ff758645(v=office.14).aspx