Comments
Transcript
SQL Server 2008 R2 徹底検証シリーズ SQL Server 2008
SQL Server 2008 R2 徹底検証シリーズ
SQL Server 2008 R2 マルチ サーバー管理
環境の構築と運用ガド
【第 5 ～ 6 章】
発行日：2010 年 9 月 10 日
はじめに
© 2010 Microsoft Corporation. All rights reserved.
本書に記載した情報は、本書各項目に関する発行日現在の Microsoft の見解を表明するものです。Microsoft は絶
えず変化する市場に対応しなければならないため、ここに記載した情報に対していかなる責務を負うものではなく、
提示された情報の信憑性については保証できません。
本評価ガドは情報提供のみを目的としています。Microsoft は、明示的または暗示的を問わず、本書にいかなる
保証も与えるものではありません。
すべての当該著作権法を遵守することはユーザーの責務です。Microsoft から書面による明確な許可なく、本書の
如何なる部分についても、転載や検索システムへの格納または挿入を行うことは、どのような形式または手段 (電
子的、機械的、複写、レコーデゖング、その他)、および目的であっても禁じられています。これらは著作権保護さ
れた権利を制限するものではありません。
Microsoft は、本書の内容を保護する特許、特許出願書、商標、著作権、またはその他の知的財産権を保有する場
合があります。Microsoft から書面によるラセンス契約が明確に供給される場合を除いて、本書の提供はこれら
の特許、商標、著作権、またはその他の知的財産へのラセンスを与えるものではありません。
Microsoft、Windows、MSDN、Visual Studio、SQL Server は、米国および (または) その他の国において、
Microsoft Corporation の登録商標または商標です。
その他記載されている実際の社名および製品名は、各社の商標です。
Microsoft Corporation ・ One Microsoft Way ・ Redmond、 WA 98052-6399 ・ US
2
はじめに
はじめに
このホワト ペーパーは、SQL Server 2008 R2 早期検証プロジェクト「CQI」（Center of Quality Innovation）
の成果物です。
CQI
•SQL Server 2008 R2
(Center of Quality Innovation)
早期検証プロジェクト
実際のシステム ンテグレーション
•提案依頼書 (RFP)
プロジェクトを想定
• 構築検証
• 機能検証
• 運用検証
• 性能検証
検証内容
以下、会社名は 50 音順で記載
マルチ サーバー管理の運用標準化検証
PowerPivot for SharePoint 2010 の検証
日本ユニシス
新日鉄ソリューションズ
サーバー集約の設計と実装のための検証
PowerPivot for Excel 2010 の検証
富士通
日本電気
このプロジェクトは、新日鉄ソリューションズ様、日本電気様、日本ユニシス様、富士通様（以上、50 音順で
記載）の 4 社とマクロソフトにより共同実施しています。検証は、実際のユーザー要求に基づくシステム
ンテグレーション (SI) プロジェクトを想定し、記述された「RFP」(Request for Proposal：提案依頼書) に
対してソリューションのプロトタプを構築し、その機能や性能、および運用方法に関して検証しています。今
回のプロジェクトでは、次の 2 つのシナリオが実施されています。
マルチ サーバー管理シナリオ
日本ユニシス株式会社様と富士通株式会社様 (以上、50 音順で記載) は、マルチ サーバー管理をテー
マに、複数のサーバーを Hyper-V 仮想環境へ集約するための検証、およびマルチ サーバーの運用で
生じるさまざまな課題の解決策を検証しています。富士通株式会社様は、サーバー集約の設計と実装を
検証し、日本ユニシス株式会社様は、マルチ サーバー環境での運用標準化を検証しています。
PowerPivot シナリオ
新日鉄ソリューションズ株式会社様と日本電気株式会社様（以上、50 音順で記載）の共同検証プロジェ
クトで、SQL Server 2008 R2 で構築された Fast Track データ ウェゕハウスをデータソースとする
PowerPivot によるセルフ サービス BI 環境を構築し、その機能と性能を検証しています。日本電気
株式会社様は、PowerPivot for Excel 2010 を検証し、新日鉄ソリューションズ株式会社様は、
PowerPivot for SharePoint 2010 を検証しています。
3
はじめに
マルチ サーバー管理シナリオについて
本書は、架空の企業「コントソ社」の抱える課題に対して作成された 「RFP」
（Request for Proposal：提案依
頼書）に対し、日本ユニシス株式会社様と富士通株式会社様 (以上、50 音順で記載) の技術者が、SQL Server
2008 R2 製品出荷前から RC0 (Release Candidate 0) 版を使用して、ラボ環境にソリューションを構築し、
検証した結果を報告するものです。
本書の 1 章から 4 章までは、
「コントソ社」の課題と RFP、および RFP に対し提案されたソリューションを
説明しています。5 章と 6 章で検証結果を報告しています。また、構築したソリューションの実装手順は、付
録に記載しています。
5 章の性能検証では、複数の SQL Server 2000、2005 サーバーを稼働している販売管理システムを SQL
Server 2008 R2 に移行するとともに、2 台のサーバー ノードで構成される Hyper-V ホスト クラスター環
境に集約することを目的とし、物理サーバーから仮想サーバーに移行した場合の性能上の差異などを検証してい
ます。また、SQL Server 2008 R2 よりサポートされる ユーテゖリテゖ コントロール ポント (Utility
Control Point：UCP) や パフォーマンス データ コレクター、SQL Server 監査を使用した場合のサーバーに
かかる負荷なども測定しています。
6 章の運用検証では、企業のマルチ サーバー環境を効率よく管理する方法として、複数のサーバーのセキュリ
テゖ監査ログの収集と分析の方法、パフォーマンス データの収集と分析の方法、および、UCP の展開方法、お
よび、UCP 導入による性能上の負荷や必要となるストレージ サズを見積もり、運用中にどのようにストレー
ジが消費されていくかを観測し、その内容を報告しています。さらに、複数の SQL Server のバージョンが混
在するマルチ サーバー環境でのエラー ベントやパフォーマンス ベントの監視と通知方法についても、
バージョンごとに実装し、運用した内容を報告しています。(なお、仮想環境はサジングのため使用していま
すが、運用検証は物理環境で行っています。)
5 章の 「5.1 Hyper-V によるサーバー集約シナリオにおけるパフォーマンス検証」
、
「5.2.3 UCP サーバーの
負荷計測」
、
「5.2.4 UCP サーバーにおけるデゖスク領域のサジング」 の項は、富士通株式会社様が実施され
た検証結果を編纂し、5 章のそれ以外の項と 6 章は、日本ユニシス株式会社様の実施された検証結果を編纂し
ました。本書が、SQL Server を使用したソリューション構築を担う技術者の一助になれば幸いです。
監修：マクロソフト株式会社
マルチ サーバー管理の検証：日本ユニシス株式会社、富士通株式会社（以上、50 音順）
プロジェクト管理：株式会社 CSK Win テクノロジ
編集：エデゖフゖストラーニング株式会社
4
はじめに
検証プロジェクトの成果
詳細は、文書内に記載していますが、この検証プロジェクトをとおして、次のことを確認しています。
マルチ サーバー管理ソリューションの運用検証に関するまとめ
以前のバージョンが混在する SQL Server ンスタンスのマルチ サーバー環境で、ベント モニタリング
を行う場合、Windows と SQL Server が提供する複数のベント検知機能 (SQL Server エージェント
警告、SQL Server エージェント パフォーマンス警告、パフォーマンス モニター、eventtriggers コマ
ンド、Windows タスク) と通知機能 (SQL Server エージェント 通知、データベース メール、VBScript
のメール送信ロジック) を組み合わせることで、RFP 要件に対応できます。
複数サーバー環境のセキュリテゖ監査では、サーバー監査とデータベース監査の機能により、想定したシナ
リオに対し、要求される監査を実行できます。また、監査履歴をフゔル出力することで、
sys.fn_get_audit_file() システム関数を使用し、監査履歴をフゔルからテーブルにコピーできます。こ
れにより、複数のサーバーから取得した監査履歴を一か所に集めることができ、監査履歴に対してクエリで
検索し、重要なベントに対して通知を行うといった実装が可能となります。
UCP は、マルチ サーバー管理を意識した実装になっているため、管理対象の複数のンスタンスのヘルス
状態を分かりやすく確認する目的や、中長期に渡り、蓄積されたデータからパフォーマンスの傾向を確認す
るタスクでは、 データベース管理者の一助となることを確認しています。
UCP では、監視対象として登録された複数のンスタンス、およびデータベースごとの 「コンピューター
の CPU 使用率」「SQL Server インスタンスの CPU 使用率」「データベース ファイルの使用率」「記
憶域ボリュームの使用率」を 1 か所で確認できます。
UCP では、リソース正常性ポリシーで閾値を設定することで、監視対象の状態と比較されて「高い」、「適
正」、「低い」と評価され、表示されます。
UCP が提供するビューは、今日を基準に「1 日」、「1 週間」、「1 か月」、「1 年」といった単位で、
マクロ的にリソース使用傾向を把握するのに向いており、パフォーマンス データ コレクターのレポートは、
パフォーマンス上の問題発生箇所の特定と、その分析 (発生原因の切り分け) を行う場合に向いていること
を確認しています。
UCP では、監視対象がリソース正常性ポリシーの閾値を超えた場合に、管理者への自動通知を行うための作
り込みが必要となります。また、パフォーマンスの問題が発生した場合、ボトルネックの兆候は確認できま
すが、より詳細な分析では、パフォーマンス データ コレクターにより収集されるデータから作成される標
準レポート (「サーバーの利用状況の履歴」「ディスク使用量の概要」「クエリ統計の履歴」) を使用した
パフォーマンス分析が有効です。
5
はじめに
パフォーマンス データ コレクターは SQL Server Management Studio のオブジェクト エクスプロー
ラーから、すべての対象ンスタンスに接続し、ビューを切り替えることにより、一か所からパフォーマン
ス情報を分析することができます。
パフォーマンス データ コレクターが提供する既定のシステム データ コレクション セットには、標準的な
パフォーマンス分析を行うために必要な情報が網羅されており、グラフやテーブルからドリル ダウン操作に
より詳細を確認することができます。また、既定のシステム データ コレクション セットで収集できない項
目は、カスタム データ コレクション セットとカスタム レポートを作成することで対応できます。
パフォーマンス データ コレクターは、UCP と同様に閾値を逸脱するような状況が発生した場合、それを管
理者に通知する仕組みはありませんが、SQL Server エージェントの警告による通知を併用することで、実
装することができました。
Windows 環境と SQL Server ンスタンスには、運用管理の要件に対応する様々なツールセットが提供され
ています。本検証プロジェクトのレポートを確認し、各ツールの特性をよく理解した上で、運用管理に役立てて
ください。
シングル サーバー
イベント モニタリング
OS レベル
インスタンス レベル
イベント トリガー
タスク トリガー
SQL Server
エージェント 警告
エラー ベントの検知と通知
エラー ベントの検知と通知
パフォーマンス異常の検知と通知
システム モニター
パフォーマンス異常の検知と通知
セキュリティ監査
OS レベル
インスタンス レベル
Windows
セキュリティ ログ
サーバー監査
データベース レベル
データベース監査
監査結果の収集
監査結果の収集
監査結果の収集
監査結果の
フゖルタリング
監査結果の
フゖルタリング
監査結果の
フゖルタリング
パフォーマンス データ収集
状態情報
詳細情報
クリテゖカルなパフォーマンス
データの収集と蓄積
パフォーマンス
データ コレクター
詳細なパフォーマンス データ
の収集と蓄積
クリテゖカルなパフォーマンス
データの分析
詳細なパフォーマンス データ
の分析
中長期的な視点での傾向の観測
トラブルシューテゖングのため
のパフォーマンス分析
UCP
マルチ サーバー
6
はじめに
マルチ サーバー管理ソリューションの性能検証に関するまとめ
物理サーバーと Hyper-V 2.0 による仮想サーバー環境で、同じ OLTP トランザクション ワークロードを
実行し、性能比較した結果、今回の検証環境では、ほぼ同等のトランザクション量を処理できることを確認
しています。また、物理サーバーと仮想サーバー環境で、同じストレージを使用した場合、ホストから見た
物理デゖスクへの I/O 要求数、データ転送量、処理にかかった時間を比べてもほとんど差がないレベルです。
このことから、物理サーバーから仮想サーバー環境への移行では、ほぼ同等のシステム リソースを見積もれ
ばよいことを確認しています。
Hyper-V ホスト クラスター環境で、ゕクテゖブ/ゕクテゖブ構成を使用する場合、ライブ マイグレーショ
ンにより、フェールオーバーさせた時、CPU リソースがオーバー コミット状態になり、ボトルネックが発
生する可能性を考慮し、業務システムへのンパクトを最小化するようにサジングを行う必要があること
を確認しています。
今回の検証環境では、サーバー監査の設定時と未設定時を比較し、プロセッサ使用率は、ほとんど変わらな
いことを確認しています (サーバー監査未設定時 47.8%・サーバー監査設定時 44.3%)。また、サーバー
監査を設定しても、秒あたりのトランザクション量（スループット）も、ほとんど変わりません (監査未設
定時を 100% とした場合、サーバー監査とデータベース監査設定時 95.706%)。このことから、サーバー
監査の設定は、トランザクション処理には、ほとんど影響を与えないことが分かります。
今回の検証環境では、サーバー監査とデータベース監査の設定時と未設定時を比較し、プロセッサ使用率が
約 15% 上昇しています (監査未設定時 47.8%・サーバー監査とデータベース監査設定時 62.2%)。しか
し、サーバー監査とデータベース監査を設定しても、プロセッサ リソースに余裕があれば、秒あたりのトラ
ンザクション量（スループット）は、ほとんど変わりません (監査未設定時を 100% とした場合、サーバー
監査とデータベース監査設定時 97.015%)。この検証結果から、サーバー監査とデータベース監査を設定す
る場合、監査のために追加のプロセッサ リソースを見積もる必要があることが分かります。
「UCP」 を設定した場合と設定していない場合を比較すると、監視対象のデータベース サーバーのプロセッ
サ使用率は、ほとんど変わりません (UCP に未登録時 47.8%・UCP に登録時 46.2%)。また、秒あたり
のトランザクション数 （スループット） もほぼ変わりません (UCP に未登録時を 100% とした場合、UCP
登録時では、96.921%)。この検証結果から、SQL Server を UCP のマネージ インスタンスに登録しても、
パフォーマンスに与える影響は、ほとんどないといえます。
今回の検証で作成した 3 つのマネージ インスタンス (ンスタンスあたり、データベース数 8、DAC 数 4、
フゔルグループ数 8、データ フゔル数 8、ログ フゔル数 8) では、年間で収集される監視データ量
が約 110 MB となることを確認しています。この規模では、オンラン ブックの記載にある 1 ンスタ
ンスあたり 2 GB という推定値より、はるかに小さい値となることが分かります。
7
はじめに
SQL Server ンスタンスを UCP および、パフォーマンス データ コレクターに登録した場合と登録して
いない場合を比較すると、プロセッサ使用率は、ほとんど変わりません (未登録時 47.8%・登録時 46.4%)。
また、秒あたりのトランザクション数 (スループット) もほぼ変わりません (未登録時を 100% とした場合、
UCP および、パフォーマンス データ コレクターに登録時では、97.91%)。この検証結果から、UCP とパ
フォーマンス データ コレクターは、同時に使用しても SQL Server ンスタンスに対するパフォーマンス
上のンパクトを与えずに運用できることが分かります。
8
はじめに
カタカナ語の長音表記について
現在、マクロソフトは、カタカナ語の長音表記ルールを変更しています。そのため、UI 上でのカタカナ表記
の最後に長音表記が記述される場合とされない場合が混在しています。これは、Visual Studio 2008 などのルー
ル変更前のコンポーネントを使用している箇所が古い表記 (「ソリューション エクスプローラ」など) になっ
ているためです。なお、このホワトペーパーでは、以前のバージョンの製品に対する説明も含め、新しいカタ
カナ語の長音表記ルールに従い記載しています。
9
はじめに
目次
はじめに ............................................................................................................................... 3
マルチ サーバー管理シナリオについて
4
検証プロジェクトの成果
5
カタカナ語の長音表記について
9
目次 ................................................................................................................................... 10
第5章
マルチ サーバー管理ソリューションの性能検証.......................................................... 11
5.1
12
旧物理サーバーと仮想サーバーの性能比較
12
5.1.2
マルチ ンスタンス構成とマルチ仮想サーバー構成の性能比較
18
5.1.3
CPU オーバー コミット時の性能
25
5.2
第6章
「Hyper-V によるサーバー集約ソリューション」におけるパフォーマンス検証
5.1.1
「マルチ サーバー管理の標準化」ソリューションでのパフォーマンス検証
33
5.2.1
セキュリテゖ監査の負荷計測
33
5.2.2
UCP 環境の負荷計測
39
5.2.3
UCP サーバーの負荷計測
43
5.2.4
UCP サーバーにおけるデゖスク領域のサジング
48
5.2.5
パフォーマンス データ コレクターの負荷計測
61
「マルチ サーバー管理の標準化」ソリューションの運用検証 ........................................ 64
6.0
検証項目の策定と検証環境
64
6.0.1
検証項目の策定
64
6.0.2
検証環境
67
6.1
ベント モニタリングと障害通知
68
6.1.1
検証の目的と範囲
68
6.1.2
検証結果
73
6.1.3
考察と結論
80
6.2
セキュリテゖ監査の運用
82
6.2.1
検証の目的と範囲
82
6.2.2
検証環境
83
6.2.3
検証方法
84
6.2.4
検証結果
95
6.2.5
考察と結論
95
6.3
パフォーマンス データの収集
98
6.3.1
検証の目的と範囲
98
6.3.2
UCP の検証環境
99
6.3.3
UCP の検証方法
100
6.3.4
UCP の検証結果
116
6.3.5
UCP の考察と結論
117
6.3.6
パフォーマンス データ コレクターの機能と検証環境
121
6.3.7
パフォーマンス データ コレクターの検証方法
122
6.3.8
パフォーマンス データ コレクターの検証結果
137
6.3.9
パフォーマンス データ コレクターの考察と結論
138
10
第 5 章 マルチ サーバー管理ソリューションの性能検証
第5章 マルチ サーバー管理ソリューションの性能検証
この章では、ソリューション作成時のシステムへの影響（パフォーマンスへの影響）を測定した結果について説明します。
具体的には、次の検証を行いました。
5.1 Hyper-V によるサーバー集約におけるパフォーマンス検証
5.1.1 旧物理サーバーと仮想サーバーの性能比較
5.1.2 マルチ ンスタンス構成とマルチ仮想サーバー構成の性能比較
5.1.3 CPU オーバー コミット時の性能
5.2 マルチ サーバー管理の標準化におけるパフォーマンス検証
5.2.1 セキュリテゖ監査の負荷計測
5.2.2 UCP 環境の負荷計測
5.2.3 UCP サーバーの負荷計測
5.2.4 UCP サーバーにおけるデゖスク領域のサジング
5.2.5 パフォーマンス データ コレクターの負荷計測
11
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.1 「Hyper-V によるサーバー集約ソリューション」におけるパフォーマンス検証
5.1.1
旧物理サーバーと仮想サーバーの性能比較
販売管理システムのデータベース サーバーとして使用している物理サーバーは、耐用年数を超えているため、
Hyper-V で構成した仮想サーバー環境に移行します。このシナリオに対し、検証環境で負荷テストを実施し、
データベース サーバーを物理サーバー環境から仮想サーバー環境に移行した場合のパフォーマンス上の影響を
測定しました。
①. 検証環境
性能検証に使用したサーバー構成は次のとおりです。
ハートビート
物理サーバー(旧)
仮想サーバー(新)
Hyper-V ホスト
Hyper-V ホスト
SQL Server
ンスタンス
WSFC
Hyper-V
ゲスト
すべてのDB
Raid 1
FC
RAID
グループ
FC
OS
SystemDB
Backup
LUN ボリューム
Raid 5
UserDB
Raid 1+0
TempDB
Raid 1+0
TranLog
TempLog
Raid 1+0
ストレージ
クラゕント
(負荷ツール)
ドメン
コントローラ
仮想デゖスク
物理サーバー (旧) は、シングル サーバー構成でしたが、新しいデータベース サーバーを稼働させる
Hyper-V 仮想サーバー(新) は、RFP の可用性要件を満たすため、WSFC による 2 ノードのホスト クラス
ターで構成しました。
12
第 5 章 マルチ サーバー管理ソリューションの性能検証
検証で使用した各サーバーのハードウェゕとソフトウェゕの構成は、次の表のとおりです。なお、新旧サー
バー環境で稼働させた SQL Server ンスタンスは、いずれも 1 つです。
用途
コンピュー
OS
SQL Server
プロセッサ
Windows Server
---
Core 2 Quad
メモリ
ディスク
ター名
ドメイン
MSMDC01
コントローラー
4 GB
内蔵 250
2008 R2 Enterprise
Q6700
GB
(x64)
2.66 GHz ×
(RAID 1)
2
Hyper-V
ホスト
サーバー
クラスター
MSMHV01
MSMHV02
ゲスト
旧データベース
サーバー
MSM-VM01
MSMDB01
Windows Server
---
Xeon E5430
2008 R2 Enterprise
2.66 GHz ×2
(x64)
(8 Core)
Windows Server
---
Xeon E5430
2008 R2 Enterprise
2.66 GHz ×2
(x64)
(8 Core)
16 GB
外部
16 GB
外部
Windows Server
SQL Server 2008
Xeon
4 GB
2008 R2 Enterprise
R2 Enterprise
2.66 GHz ×
想ハード
(x64)
RC0
2
デゖスク
Windows 2000
SQL Server 2000
Core 2 Quad
Server Standard
Standard+SP4
Q6700
GB
SP4 (x86)
(x86)
2.66 GHz ×
(RAID 1)
2 GB
容量固定仮
内蔵 250
2
新旧どちらの環境でも同等の処理能力を持つプロセッサを同じコゕ数搭載しています。また、検証で使用し
た外部ストレージの仕様は、次のとおりです。
フゔバー チャネル (FC) 接続で転送レート 4 GB/s のンターフェス
コントローラー モジュール (CM) の数は、2 つ
CM ごとに FC 接続用ポートが 2 つ (合計で 4 ポート)
マルチ パス接続
ストレージは、15,000 rpm SAS デゖスクを使用した RAID 5 および RAID 1+0 構成
ユーザー データベースと tempdb システム データベースは、データ フゔルとログ フゔルを異なる
LUN ボリュームに分散して配置し、各フゔルは自動拡張を回避するため、あらかじめ大きなサズで初期
化しました。また、tempdb のデータ フゔル数は、CPU コゕ数分を作成しました。
13
第 5 章 マルチ サーバー管理ソリューションの性能検証
その他、システム、および SQL Server に対する設定は、共通で下表のとおりです。
設定の種類
内容
OS システム設定
サーバーの最適化：[ネットワーク ゕプリケーションのデータ スループットを最大にする] オプショ
ンを選択
パフォーマンス オプション（視覚効果）：[パフォーマンスを優先する] オプションを選択
パフォーマンス オプション（詳細設定）：[プロセッサのスケジュール] は、[バックグラウンド サー
ビス] オプションを選択
[メモリ使用量] は、[プログラム] オプションを選択 (この設定は Windows Server 2008 R2 にはあ
りません。)
ゕロケーション ユニット サズ：64 KB
SQL Server の
サーバー設定
CPU Affinity Mask：既定値
Max Server Memory：既定値
Recovery Interval：最大値にする (測定中の書き込みを抑止するため)
Max Degree Of Parallelism：1 にする (並列処理は使用しない）
②. 検証方法
新旧のサーバー環境に対して、下表のワークロードを実行しました。
シナリオ
内容
OLTP の負荷
CPU 負荷をかけるストレス ツールを使用して測定
旧物理サーバーで CPU 使用率が 20% (低負荷) と 70% (高負荷) になるストレス設定をベース
ラン (100) として使用し、仮想サーバーでのパフォーマンスを計測
30 分程度 負荷をかけて、処理されたトランザクション量を測定する
旧物理サーバーと仮想サーバーで処理されたトランザクション量の変化を確認する
バッチ処理の負荷
デゖスク負荷中心と CPU 負荷中心の 2 種類のバッチ処理を実行して測定
デゖスク負荷では、データベースに対して、大量データを読み込む SELECT ステートメント を実
行して負荷をかける
CPU 負荷では、計算処理の繰り返しにより負荷をかける
旧物理サーバーをベースランとし、それぞれ、10 分程度実行するバッチ処理を用意する
バッチ処理を実行し、実行時間を計測する
旧物理サーバーと仮想サーバーでの実行時間の変化を確認する
14
第 5 章 マルチ サーバー管理ソリューションの性能検証
③. 検証結果
(1).
「OLTP の負荷」シナリオでのトランザクション処理量
実行した OLTP ワークロードに対して処理されたトランザクション量は以下のグラフのとおりです。
OLTP 負荷時のトランザクション処理量
(%)
100
80
60
40
20
仮想サーバー
0
旧物理サーバー
低負荷時
旧物理サーバー
仮想サーバー
高負荷時
低負荷時
高負荷時
100.000 %
100.000 %
99.863 %
101.620 %
このグラフは、物理サーバー環境で、低負荷 (CPU 使用率が 20%) と高負荷 (CPU 使用率が 70%) の
OLTP ワークロード時に、処理されたトランザクション数を 100 とした場合の相対値で示しています。
検証結果として、OLTP ワークロードに対して、物理サーバー環境で処理されるトランザクション量を仮想
サーバー環境と比較した場合、低負荷時であっても、高負荷時であっても、ほぼ同等の処理能力が得られる
ことを確認しました。(低負荷時 ⇒ 旧物理サーバー：100%、仮想サーバー: 99.863% / 高負荷時 ⇒ 旧
物理サーバー：100%、仮想サーバー: 101.62%)
15
第 5 章 マルチ サーバー管理ソリューションの性能検証
(2).
「バッチ処理の負荷」シナリオでのバッチの実行時間
バッチ処理のワークロードに対する実行時間は以下のグラフのとおりです。バッチ処理は、
「CPU 負荷のバッ
チ処理」と「デゖスク負荷のバッチ処理」の 2 種類を実行しています。
CPU 負荷時のバッチ実行時間
ディスク負荷時のバッチ実行時間
（秒)
(秒)
600
700
500
600
400
500
300
400
300
200
200
100
100
0
0
CPU負荷
デゖスク負荷
旧物理サーバー
524 秒
旧物理サーバー
671 秒
仮想サーバー
535 秒
仮想サーバー
324 秒
OLTP ワークロードと同様に「CPU 負荷のバッチ処理」でも新旧の環境で、差はないことが分かりました。
使用した環境では、物理サーバーと仮想サーバーで CPU の個数を同じに設定しているため、CPU 負荷が中
心となる処理では、性能差はなかったと考えられます。デゖスク負荷のバッチ処理では、旧物理サーバーに
対して、仮想サーバーでは、半分ほどの実行時間で処理を終えています。
(3).
ディスク負荷のバッチ処理の解析
「デゖスク負荷のバッチ処理」を解析するために、さらに CPU 使用率とデータ R/W の平均時間 (ミリ秒)
を確認しました。データ R/W の平均時間は、Physical Disk パフォーマンス オブジェクトの Avg. Disk
sec/Transfer カウンターを使用して計測しています。結果は以下のグラフのとおりです。
(ミリ秒)
(%)
100
100
80
80
60
60
40
40
20
20
0
0
CPU使用率
データR/Wの平均時間 (ms)
CPU使用率
データR/Wの平均時間 (ms)
旧物理サーバー
22 %
旧物理サーバー
40.000 ミリ秒
仮想サーバー
46 %
仮想サーバー
37.000 ミリ秒
16
第 5 章 マルチ サーバー管理ソリューションの性能検証
また、「デゖスク負荷のバッチ処理」でのデゖスク I/O 回数とデータ転送量も調べました。I/O 回数は、
Physical Disk\Disk Transfers/sec カウンターを使用し、データ転送量は、Physical Disk\Disk Bytes/sec
カウンターを使用して計測しています。結果は以下のグラフのとおりです。
(Mbytes)
(回)
800
80
600
60
400
40
200
20
0
0
秒あたりのデータ転送量
秒あたりの I/O 回数
秒あたりのデータ転送量
秒あたりの I/O 回数
旧物理サーバー
471 回
旧物理サーバー
28 Mbytes
仮想サーバー
767 回
仮想サーバー
67 Mbytes
デゖスク負荷での性能差が生じたのは、旧物理サーバーはシナリオとして、内蔵デゖスクを使用したのに対
して、仮想サーバー環境では、外部ストレージを使用しているためと考えられます。使用したデゖスクの性
能差により実行時間にも差が生じたと考えられます。
17
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.1.2 マルチ インスタンス構成とマルチ仮想サーバー構成の性能比較
ここでは、販売管理システムで使用している耐用年数を超えた複数のサーバー環境を 1 台の物理サーバー上で
マルチ ンスタンス構成として移行する場合と Hyper-V で構成したマルチ仮想サーバー環境に移行する場合
とを比較するために、同じ負荷テストを実施し、パフォーマンス上の影響を計測しました。
①. 検証環境
このシナリオでの性能検証に使用したサーバー構成は次のとおりです。
ハートビート
マルチ ンスタンス サーバー
マルチ仮想サーバー
Hyper-V ホスト
SQL
ins1
SQL
ins2
RAID
グループ
LUN
WSFC
SQL
ins3
FC
Hyper-V ホスト
Hyper-V
Guest1
Hyper-V
Guest2
Hyper-V
Guest3
FC
SystemDB
Backup
SystemDB
Backup
UserDB
TempDB
FC
SystemDB
Backup
OS
SystemDB
Backup
TempDB
UserDB
TempDB
UserDB
TempDB
UserDB
TranLog
TempLog
TranLog
TempLog
ドメン
コントローラ
OS
SystemDB
Backup
OS
SystemDB
Backup
TempDB
Raid 5
クラゕント
(負荷ツール)
Raid 1+0
Raid 1+0
UserDB
TempDB
UserDB
Raid 1+0
TranLog
TempLog
TranLog
TempLog
Raid 1+0
フゔル
TranLog
TempLog
TranLog
TempLog
ストレージ
仮想デゖスク
マルチ ンスタンス サーバー環境は、シングル サーバー構成で SQL Server ンスタンスを 3 つ稼働さ
せています。また、マルチ仮想サーバー環境は、2 ノードの WSFC クラスター上に構成された Hyper-V サー
バーに 3 つの仮想サーバーを構築し、それぞれに SQL Server ンスタンスを 1 つ稼働させています。
また、ストレージは、サーバーごとに、各 RAID グループから 1 ドラブ (LUN) ずつ計 4 ドラブを割
り当てています。
18
第 5 章 マルチ サーバー管理ソリューションの性能検証
各サーバーのハードウェゕとソフトウェゕの構成は、下表のとおりです。
用途
コンピュー
OS
SQL Server
プロセッサ
Windows Server
---
Core 2 Quad
メモリ
ディスク
ター名
ドメイン
MSMDC01
コントローラー
Hyper-V
ホスト
サーバー
クラスター
MSMHV01
2008 R2
Q6700
Enterprise
2.66 GHz ×
(x64)
2
Windows Server
---
Xeon E5430
2008 R2
2.66 GHz ×
Enterprise
2 (8 Core)
4 GB
内蔵
16 GB
外部
16 GB
外部
(x64)
MSMHV02
Windows Server
---
Xeon E5430
2008 R2
2.66 GHz ×
Enterprise
2 (8 Core)
(x64)
ゲスト
MSM-VM01
Windows Server
SQL Server 2008
Xeon
4 GB
容量固定仮
2008 R2
R2 Enterprise
2.66 GHz ×
想ハード
Enterprise
RC0 (x64)
2
デゖスク
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Xeon
2.66 GHz × 2
Enterprise
RC0 (x64)
(x64)
MSM-VM02
4 GB
容量固定仮
想ハード
デゖスク
(x64)
MSM-VM03
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Enterprise
RC0 (x64)
Xeon
2.66 GHz × 2
4 GB
容量固定仮
想ハード
デゖスク
(x64)
マルチ インスタンス
構成サーバー
MSMHV03
Windows Server
SQL Server 2008
Xeon E5430
2008 R2
R2 Enterprise
2.66 GHz ×2
Enterprise
RC0 (x64) × 3
(8 Core)
(x64)
ンスタンス
また、検証で使用した外部ストレージの仕様は、次のとおりです。
フゔバー チャネル (FC) 接続で転送レート 4 GB/s のンターフェス
コントローラー モジュール (CM) の数は、2 つ
CM ごとに FC 接続用ポートが 2 つ (合計で 4 ポート)
マルチ パス接続
19
8 GB
外部
第 5 章 マルチ サーバー管理ソリューションの性能検証
ストレージは、15,000 rpm SAS デゖスクを使用した RAID 5 および RAID 1+0 構成
ユーザー データベースと tempdb システム データベースは、データ フゔルとログ フゔルを異なる
LUN ボリュームに分散して配置し、各フゔルは自動拡張を回避するため、あらかじめ大きなサズで初期
化しました。また、tempdb のデータ フゔル数は、CPU コゕ数分を作成しました。その他、システム、
および SQL Server に対する設定は、共通で下表のとおりです。
設定の種類
内容
OS システム設定
サーバーの最適化：[ネットワーク ゕプリケーションのデータ スループットを最大にする] オプショ
ンを選択
パフォーマンス オプション（視覚効果）：[パフォーマンスを優先する] オプションを選択
パフォーマンス オプション（詳細設定）：[プロセッサのスケジュール] は、[バックグラウンド サー
ビス] オプションを選択
ゕロケーション ユニット サズ：64 KB
SQL Server の
サーバー設定
Max Server Memory：2 GB
Recovery Interval：最大値にする (測定中の書き込みを抑止するため)
Max Degree Of Parallelism：1 にする (並列処理は使用しない）
なお、マルチ ンスタンス環境では、CPU Affinity Mask の設定で各ンスタンスに 2 CPU ずつを割り当
てていますが、マルチ仮想サーバー環境では、仮想 OS レベル 2 CPU ずつを割り当ていますので、CPU
Affinity Mask の設定は既定値を使用しています。
20
第 5 章 マルチ サーバー管理ソリューションの性能検証
②. 検証方法
マルチ ンスタンス環境とマルチ仮想サーバー環境に対して、下表のワークロードを実行しました。
シナリオ
内容
OLTP の負荷
CPU 負荷をかけるストレス ツールを使用して測定
マルチ ンスタンス環境で CPU 使用率が 20% (低負荷)と 70% (高負荷)になるストレス設定をベー
スラン (100) として使用し、仮想サーバーでのパフォーマンスを計測
30 分程度 負荷をかけて、処理されたトランザクション量を測定する
マルチ ンスタンス環境とマルチ仮想サーバー環境で処理されたトランザクション量の変化を確認す
る
バッチ処理の負荷
デゖスク負荷中心と CPU 負荷中心の 2 種類のバッチ処理を実行して測定
デゖスク負荷では、データベースに対して、大量データを読み込む SELECT ステートメントを実行して
負荷をかける
CPU 負荷では、計算処理の繰り返しにより負荷をかける
マルチ ンスタンス環境をベースランとし、それぞれ、10 分程度実行するバッチ処理を用意する
バッチ処理を実行し、実行時間を計測する
マルチ ンスタンス環境とマルチ仮想サーバー環境での実行時間の変化を確認する
21
第 5 章 マルチ サーバー管理ソリューションの性能検証
③. 検証結果
(1).「OLTP の負荷」シナリオでのトランザクション処理量
実行した OLTP ワークロードに対して処理されたトランザクション量は以下のグラフのとおりです。
OLTP 負荷時のトランザクション処理量
(%)
100
80
60
40
20
マルチ仮想サーバー環境
0
マルチ ンスタンス環境
低負荷時
高負荷時
低負荷時
高負荷時
マルチ ンスタンス環境
100.000 %
100.000 %
マルチ仮想サーバー環境
99.843 %
99.239 %
このグラフは、物理サーバー環境で、低負荷 (CPU 使用率が 20%) と高負荷 (CPU 使用率が 70%) の
OLTP ワークロード時に、処理されたトランザクション数を 100 とした場合の相対値で示しています。
検証結果として、OLTP ワークロードに対して、マルチ ンスタンス環境で処理されるトランザクション量
をマルチ仮想サーバー環境と比較した場合、低負荷時であっても、高負荷時であっても、ほぼ同等の処理能
力が得られることを確認しました。(低負荷時 ⇒ マルチ ンスタンス サーバー：100%、マルチ仮想サー
バー: 99.843% / 高負荷時 ⇒ マルチ ンスタンス サーバー：100%、マルチ仮想サーバー: 99.239%)
22
第 5 章 マルチ サーバー管理ソリューションの性能検証
(2).「バッチ処理の負荷」シナリオでのバッチの実行時間
バッチ処理のワークロードに対する実行時間は以下のグラフのとおりです。バッチ処理は、
「CPU 負荷のバッ
チ処理」と「デゖスク負荷のバッチ処理」の 2 種類を実行しています。
(秒)
CPU 負荷時のバッチ実行時間
(秒)
600
700
500
600
400
500
300
400
ディスク負荷時のバッチ実行時間
300
200
200
100
0
100
0
CPU 負荷
デゖスク負荷
CPU 負荷
デゖスク負荷
マルチンスタンス環境
510 秒
マルチンスタンス環境
621 秒
マルチ仮想サーバー環境
533 秒
マルチ仮想サーバー環境
621 秒
OLTP ワークロードと同様に、
「CPU 負荷のバッチ処理」と「デゖスク負荷のバッチ処理」とでも、差がな
いことが分かりました。
(3).ディスク負荷のバッチ処理の解析
「デゖスク負荷のバッチ処理」を解析するために、さらに CPU 使用率とデータ R/W の平均時間 (ミリ秒)
を確認しました。マルチ仮想サーバー環境での CPU 使用率の計測は、Hyper-V Hypervisor Virtual
Processor(_Total)\% Total Run Time カウンターを使用しています。また、データ R/W の平均時間は、
Physical Disk パフォーマンス オブジェクトの Avg. Disk sec/Transfer カウンターを使用しています。結
果は以下のグラフのとおりです。
(%)
(ミリ秒)
100
100
80
80
60
60
40
40
20
20
0
0
CPU 使用率 (%)
データ R/W の平均時間 (ms)
CPU 使用率 (%)
データ R/W の平均時間 (ms)
マルチ ンスタンス環境
41 %
マルチンスタンス環境
30 ミリ秒
マルチ仮想サーバー環境
47 %
マルチ仮想サーバー環境
39 ミリ秒
23
第 5 章 マルチ サーバー管理ソリューションの性能検証
また、「デゖスク負荷のバッチ処理」でのデゖスク I/O 回数とデータ転送量も調べました。I/O 回数は、
Physical Disk\Disk Transfers/sec カウンターを使用し、データ転送量は、Physical Disk\Disk Bytes/sec
カウンターを使用して計測しています。結果は以下のグラフのとおりです。マルチ仮想サーバー環境では、
ホスト OS のカウンターで計測しています。
ディスク負荷時のI/O回数とデータ転送量
(Mbyte)
(回)
800
700
600
500
400
300
200
100
0
60
40
20
0
秒あたりの I/O 回数
秒あたりのデータ転送量
秒あたりの I/O 回数
秒あたりのデータ転送量
マルチンスタンス環境
736 回
マルチンスタンス環境
65 Mbyte
マルチ仮想サーバー環境
736 回
マルチ仮想サーバー環境
65 Mbyte
今回の検証では、容量固定 VHD に対して、ストレス ツールを使用し、読み込み中心の高い負荷をかけてい
るため、I/O でボトルネックが発生している可能性があります。1 多重で単一のバッチ実行のため、仮に I/O
ボトルネックが発生したとしても、仮想では、オーバーヘッドが想定され、その分、物理に比べて、スルー
プット低下が生じることを予測しました。しかし、今回の検証結果では、物理と仮想で、I/O 要求数、デー
タ転送量、処理にかかった時間がほぼ同一の値でした。
Hyper-V は、バージョン 1.0 から 2.0 へのバージョンゕップで仮想ハードデゖスクの処理性能が向上して
います。このため、マルチ ンスタンス環境とマルチ仮想サーバー環境では、ホストから見た物理デゖスク
への I/O 要求数、データ転送量、処理にかかった時間を比べてもほとんど差がないレベルにあります。 (秒
あたりの I/O 回数 ⇒ マルチ ンスタンス サーバー：736 回/秒、マルチ仮想サーバー: 736 回/秒 / 秒
あたりのデータ転送量 ⇒ マルチ ンスタンス サーバー：65 Mbyte/秒、マルチ仮想サーバー: 65 Mbyte/
秒)
今回の仮想環境に対する測定条件では、物理環境と、ほぼ変わらない結果を得ることができました。
なお、別のプロジェクトで実施された Hyper-V 1.0/2.0 環境で SQLIO ツールを使用した性能検証でも仮
想環境と物理環境の I/O 性能差は僅少だったという結果が記載されています。
仮想環境におけるサーバー製品のパフォーマンスについては、次のホワト ペーパーも参考にして下さい。
「サーバー仮想化におけるシステム構成ガド ホワト ペーパー 第 2 版」
24
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.1.3 CPU オーバー コミット時の性能
Hyper-V で構成したマルチ仮想サーバー環境では、物理 CPU の数に対して、各仮想サーバーに割り当てる
CPU コゕの合計数を多くすることができます。この状態は CPU オーバー コミットと呼ばれています。この検
証では、CPU オーバー コミット状態とオーバー コミットしていない定常時とで、同じ負荷テストを実施し、
パフォーマンス上の影響を計測しました。
①. 検証環境
このシナリオでの性能検証に使用したサーバー構成は次のとおりです。
ハートビート
ノード1
ノード2
Hyper-V ホスト
Hyper-V ホスト
WSFC
Hyper-V
Guest1
Hyper-V
Guest2
Hyper-V
Guest3
Hyper-V
Guest4
Hyper-V
Guest5
Hyper-V
Guest6
FC
ドメン
コントローラ
FC
ストレージ
RAID
グループ
Raid 5
OS
SystemDB
Backup
Raid 1+0
UserDB
Raid 1+0
TempDB
Raid 1+0
Raid 1+0
TranLog
TempLog
OS
SystemDB
Backup
OS
SystemDB
Backup
OS
SystemDB
Backup
TempDB
UserDB
UserDB
TempDB
TempDB
UserDB
TranLog
TempLog
TranLog
TempLog
TranLog
TempLog
OS
SystemDB
Backup
OS
SystemDB
Backup
LUN
クラゕント
(負荷ツール)
TempDB
UserDB
TempDB
UserDB
TranLog
TempLog
TranLog
TempLog
仮想デゖスク
25
第 5 章 マルチ サーバー管理ソリューションの性能検証
また、各サーバーのハードウェゕとソフトウェゕの構成は、下表のとおりです。
用途
コンピュー
OS
SQL Server
プロセッサ
メモリ
ディスク
Windows Server
---
Core 2 Quad
4 GB
内蔵
ター名
ドメイン
MSMDC01
コントローラー
Hyper-V
ホスト
サーバー
クラス
MSMHV01
ター
2008 R2
Q6700
Enterprise
2.66 GHz ×
(x64)
2
Windows Server
---
Xeon E5430
2008 R2
2.66 GHz ×
Enterprise
2 (8 Core)
250 GB
16 GB
外部
16 GB
外部
2 GB
容量固定仮
(x64)
MSMHV02
Windows Server
---
Xeon E5430
2008 R2
2.66 GHz ×
Enterprise
2 (8 Core)
(x64)
ゲスト
MSM-VM01
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Enterprise
RC0 (x64)
Xeon
2.66 Ghz × 2
想ハード
デゖスク
(x64)
MSM-VM02
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Enterprise
RC0 (x64)
Xeon
2.66 Ghz × 2
2 GB
容量固定仮
想ハード
デゖスク
(x64)
MSM-VM03
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Enterprise
RC0 (x64)
Xeon
2.66 Ghz × 2
2 GB
容量固定仮
想ハード
デゖスク
(x64)
MSM-VM04
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Enterprise
RC0 (x64)
Xeon
2.66 Ghz × 2
2 GB
容量固定仮
想ハード
デゖスク
(x64)
MSM-VM05
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Enterprise
RC0 (x64)
Xeon
2.66 Ghz × 2
2 GB
容量固定仮
想ハード
デゖスク
(x64)
MSM-VM06
Windows Server
SQL Server 2008
2008 R2
R2 Enterprise
Enterprise
RC0 (x64)
(x64)
26
Xeon
2.66 Ghz × 2
2 GB
容量固定仮
想ハード
デゖスク
第 5 章 マルチ サーバー管理ソリューションの性能検証
Hyper-V サーバー環境は、8 コゕの物理プロセッサを持つ 2 ノードの WSFC クラスターで構成し、2 コ
ゕの論理プロセッサを使用する仮想サーバーを 6 つ作成しています。定常時の検証は、各ノードに仮想サー
バーを 3 つずつ (6 コゕ使用) 稼働させた状態で行いました。
定常時の稼働状態
仮想
Hyper-V サーバー ノード1
仮想
Hyper-V サーバー ノード2
6 論理プロセッサ
SQL Server
SQL Server
SQL Server
SQL Server
SQL Server
SQL Server
ゲスト OS
ゲスト OS
ゲスト OS
ゲスト OS
ゲスト OS
ゲスト OS
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
WSFC
ホスト OS
物理 CPU CPU
CPU
CPU
CPU
CPU
CPU
ホスト OS
物理 CPU CPU
CPU
CPU
ハードウェゕ
CPU
CPU
CPU
CPU
CPU
CPU
ハードウェゕ
8 物理プロセッサ
CPU オーバー コミット状態の検証は、Hyper-V ホスト クラスター環境でラブ マグレーション操作を
実行し、特定ノードに仮想サーバーを 6 つ (8 物理プロセッサに対して、論理プロセッサを 12 使用) 稼働
させた状態で行いました。
CPU オーバー コミット時の稼働状態
仮想
Hyper-V サーバー ノード1
SQL Server
SQL Server
SQL Server
ゲスト OS
ゲスト OS
ゲスト OS
CPU
物理 CPU CPU
CPU
CPU
CPU
CPU
CPU
SQL Server
ゲスト OS
ゲスト OS
CPU
CPU
CPU
WSFC
CPU
CPU
CPU
CPU
CPU
ハードウェゕ
27
CPU
12 論理プロセッサ
CPU
SQL Server
ゲスト OS
CPU
CPU
ホスト OS
物理 CPU CPU
CPU
ハードウェゕ
CPU
SQL Server
CPU
ホスト OS
仮想
Hyper-V サーバー ノード2
CPU
CPU
CPU
8 物理プロセッサ
第 5 章 マルチ サーバー管理ソリューションの性能検証
Hyper-V マネージャーの仮想マシンの設定ダゕログボックスで [プロセッサ] ページを使用し、各仮想マ
シンに仮想プロセッサを割り当てることができます。検証では、各仮想マシンの [論理プロセッサの数] を
「2」で設定しました。
なお、このページの [リソース コントロール] の設定を使用することで、各仮想マシンに割り当てた プロ
セッサ リソースに対する使用率の予約と使用可能な上限値をパーセンテージで設定できますが、本検証プロ
ジェクトでは、すべての仮想マシンで、既定値 (仮想マシンの予約 0%、仮想マシンの限度 100%) を使用
しました。
検証で使用した外部ストレージの仕様は、次のとおりです。
フゔバー チャネル (FC) 接続で転送レート 4 GB/s のンターフェス
コントローラー モジュール (CM) の数は、2 つ
CM ごとに FC 接続用ポートが 2 つ (合計で 4 ポート)
マルチ パス接続
ストレージは、15,000 rpm SAS デゖスクを使用した RAID 5 および RAID 1+0 構成
28
第 5 章 マルチ サーバー管理ソリューションの性能検証
ユーザー データベースと tempdb システム データベースは、データ フゔルとログ フゔルを異なる
LUN ボリュームに分散して配置し、各フゔルは自動拡張を回避するため、あらかじめ大きなサズで初期
化しました。また、tempdb のデータ フゔル数は、CPU コゕ数分を作成しました。その他、システム、
および SQL Server に対する設定は、共通で下表のとおりです。
設定の種類
内容
OS システム設定
サーバーの最適化：[ネットワーク ゕプリケーションのデータ スループットを最大にする] オプ
ションを選択
パフォーマンス オプション（視覚効果）：[パフォーマンスを優先する] オプションを選択
パフォーマンス オプション（詳細設定）：[プロセッサのスケジュール] は、[バックグラウンド
サービス] オプションを選択
ゕロケーション ユニット サズ：64 KB
SQL Server の
サーバー設定
CPU Affinity mask：既定値
Max Server Memory：既定値
Recovery Interval：最大値にする (測定中の書き込みを抑止するため)
Max Degree Of Parallelism：1 にする (並列処理は使用しない）
②. 検証方法
定常時 (各ノードに、ゲストを 3 つずつ稼働) と CPU オーバーコミット時 (特定のノードに、ゲストを 6
つ稼働) で、下表のワークロードを実行しました。
シナリオ
内容
OLTP の負荷
CPU 負荷をかけるストレス ツールを使用して測定
定常時で CPU 使用率が 20% (低負荷)と 70% (高負荷)になるストレス設定をベースラン
(100) として使用し、仮想サーバーでのパフォーマンスを計測
30 分程度 負荷をかけて、処理されたトランザクション量を測定する
定常時と CPU オーバーコミット時で処理されたトランザクション量の変化を確認する
バッチ処理の負荷
デゖスク負荷中心と CPU 負荷中心の 2 種類のバッチ処理を実行して測定
デゖスク負荷では、データベースに対して、大量データを読み込む SELECT ステートメント を実行
して負荷をかける
CPU 負荷では、計算処理の繰り返しにより負荷をかける
定常時をベースランとし、それぞれ、10 分程度実行するバッチ処理を用意する
バッチ処理を実行し、実行時間を計測する
定常時と CPU オーバーコミット時での実行時間の変化を確認する
29
第 5 章 マルチ サーバー管理ソリューションの性能検証
③. 検証結果
(1).「OLTP の負荷」シナリオでの CPU 使用率
実行した OLTP ワークロードに対してホスト OS の CPU 使用率は以下のグラフのとおりです。
(%)
OLTP 負荷時のホストマシンの CPU 使用率
100
80
60
40
20
0
低負荷
高負荷
低負荷
高負荷
定常時
17 %
53 %
CPU オーバーコミット時
37 %
99 %
ホスト OS の CPU 使用率は、Hyper-V Hypervisor Logical Processor(_total)\%Total Run Time カウ
ンターを使用して計測しています。OLTP ワークロードに対し CPU に余力がある状態では、CPU オーバー
コミット時でもトランザクション処理量に差はありませんが、高負荷状態では、ホストの CPU が限界とな
り、処理できるトランザクション量が減ることが分かりました。
なお、同じ OLTP ワークロードで、仮想 OS の CPU 使用率は以下のグラフのとおりです。
(%)
OLTP 負荷時の仮想マシンの CPU 使用率
100
80
60
40
20
0
低負荷
高負荷
低負荷
高負荷
定常時
20 %
67 %
CPU オーバーコミット時
23 %
63 %
仮想 OS の CPU 使用率は、Hyper-V Hypervisor Virtual Processor(_total)\%Total Run Time カウン
30
第 5 章 マルチ サーバー管理ソリューションの性能検証
ターを使用して計測します。ホスト OS の CPU 使用率が、およそ 99% の時に仮想 OS で、63% と示さ
れたのは、次の計算で算出される値に符合します。
8 [物理プロセッサのコゕ数] ÷ 12 [仮想プロセッサのコゕ数] = 0.66
物理ホストに必要なプロセッサ処理能力を知るには、搭載しているコゕ数に対する、稼働している仮想 OS
に割り当てたコゕ数から算出する必要があります。
また、物理ホストのプロセッサ使用率は、物理コンピューター全体の論理プロセッサ (Logical Processor) の
使用率を表示する Hyper-V Hypervisor Logical Processor (_Total)\% Total Run Time カウンターを使
用 し ま す 。 ホ ス ト ク ラ ス タ ー 構 成 で は 、 定 常 時 に 、 各 ホ ス ト の Hyper-V Hypervisor Logical
Processor(_total)\%Total Run Time カウンターの値を測定し、合算することで、CPU の限界を超える /
超えないを判別することできます。
(2).「OLTP の負荷」シナリオでのトランザクション処理量
実行した OLTP ワークロードに対して処理されたトランザクション量は、以下のグラフのとおりです。定常
時と OLTP ワークロード時で、複数のンスタンスを稼働させていますので、各環境で、処理されたトラン
ザクション量の平均値から計算しています。
(%)
OLTP 負荷時のトランザクション処理量
100
80
60
40
20
CPU オーバーコミット時
0
定常時
低負荷時
定常時
CPU オーバーコミット時
高負荷時
低負荷時
高負荷時
100.000 %
100.000 %
99.981 %
70.794 %
このグラフは、定常時の環境で、低負荷 (CPU 使用率が 20%) と高負荷 (CPU 使用率が 70%) の OLTP
ワークロード時に、処理されたトランザクション数を 100 とした場合の相対値を示しています。
検証結果として、CPU オーバーコミット状態にある仮想サーバー環境では、低負荷時での処理能力は、非
CPU オーバーコミット状態 (定常時) の環境と差がありませんが、高負荷時になると、定常時に処理できる
トランザクション量に対応できないことが分かりました。(低負荷時 ⇒ 定常時：100%、CPU オーバーコミッ
ト時: 99.981% / 高負荷時 ⇒定常時：100%、CPU オーバーコミット時: 70.794%)
31
第 5 章 マルチ サーバー管理ソリューションの性能検証
(3).「バッチ処理の負荷」シナリオでのバッチの実行時間
バッチ処理のワークロードに対する実行時間は、以下のグラフのとおりです。バッチ処理は、
「CPU 負荷の
バッチ処理」と「デゖスク負荷のバッチ処理」の 2 種類を実行しています。
CPU 負荷時のバッチ実行時間
ディスク負荷時のバッチ実行時間
(秒)
(秒)
900
900
800
800
700
700
600
600
500
400
300
200
100
0
500
400
300
200
100
0
デゖスク負荷
CPU負荷
定常時
535 秒
定常時
827 秒
CPU オーバーコミット時
804 秒
CPU オーバーコミット時
858 秒
デゖスク負荷の場合、同時にかかる CPU 負荷が高くなければ、CPU オーバー コミットの有無は影響しま
せんでした。
32
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.2 「マルチ サーバー管理の標準化」ソリューションでのパフォーマンス検証
5.2.1
セキュリティ監査の負荷計測
ここでは、SQL Server 2008 R2 で SQL Server 監査を使用し、監査ログを収集する際にかかるパフォーマン
ス上の影響について計測しましたので、結果を報告します。
①. 検証環境
このシナリオでの性能検証に使用したサーバー構成は次のとおりです。
CLUSTERSQLMET¥CLUSTERSQL
ADSRV
SQLNODE1SRV
SQLNODE2SRV
MGMTClient
監査ログ
ドメン
コントローラ
クラスター
ノード 1
クラゕント PC
クラスター
ノード 2
ストレージ
各サーバーのハードウェゕとソフトウェゕの構成は、下表のとおりです。
用途
コンピューター
OS
SQL Server
プロセッサ
Windows Server
---
Core 2 Quad
メモリ
ディスク
4 GB
500 GB
16 GB
300 GB
16 GB
300 GB
4 GB
500 GB
名
ドメイン
ADSRV
コントローラー
2008 R2 (x64)
Q6700
2.66 GHz
DB サーバー
SQLnode1SRV
Windows Server
SQL Server 2008
Xeon E5430
(クラスター)
2008 R2 (x64)
R2 Enterprise
2.66 GHz ×2
RC0
(8 Core)
---
Xeon E5430
SQLnode2SRV
Windows Server
(クラスター)
2008 R2 (x64)
2.66 GHz ×2
(8 Core)
管理用 PC
MGMTClient
Windows 7 (x64)
---
Core 2 Quad
Q6700
2.66 GHz
33
第 5 章 マルチ サーバー管理ソリューションの性能検証
SQLnode1SRV と SQLnode2SRV の 2 ノードで構成したクラスターの情報は、次のとおりです。
クラスター名
ClusterSQL
SQL Server インスタンスの仮想ネットワーク名
ClusterSQLNet
また、クラスター構成で使用した共有デゖスクの情報は、次のとおりです。
用途
ドライブ文字
容量
監視ディスク
Q
1 GB
SQL Server プログラム
S
50 GB
tempdb
T
100 GB
U
1 TB
V
1 TB
W
1 TB
ユーザー データベース
②. 検証方法
監査設定をしない場合と次の 2 パターンの設定をした場合とで、 OLTP 処理を実行し、CPU 使用率とトラ
ンザクション処理量を比較しました。
サーバー監査のみを設定
サーバー監査とデータベース監査を設定
なお、OLTP 処理の対象となるデータベースはクラスター サーバー上の SQL Server ンスタンスに作成
し、監査を設定しました。この SQL Server ンスタンスでは、UCP とパフォーマンス データ コレクター
を無効化しています。サーバー監査の仕様で監査対象に設定したゕクション グループは下表のとおりです。
監査アクションの種類
説明
DATABASE_ROLE_MEMBER_CHANGE_GROUP
データベース ロールにログンが追加、または削除されると
発生するベント
SERVER_ROLE_MEMBER_CHANGE_GROUP
固定サーバー ロールにログンが追加、または削除されると
発生するベント
BACKUP_RESTORE_GROUP
バックゕップ、または復元が実行されると発生するベント
SCHEMA_OBJECT_CHANGE_GROUP
スキーマが作成、変更、または削除されると発生するベント
SERVER_OBJECT_CHANGE_GROUP
サーバー オブジェクトが作成、変更、または削除されると発
生するベント
SERVER_PRINCIPAL_CHANGE_GROUP
サーバー プリンシパルが作成、変更、または削除されると発
生するベント
DATABASE_OPERATION_GROUP
チェックポントやクエリ通知のサブスクラブなどが実行
されたときに発生するベント
SERVER_OPERATION_GROUP
設定、リソース、外部ゕクセス、または承認の変更などのセキュ
リテゖ監査操作が使用されるときに発生するベント
34
第 5 章 マルチ サーバー管理ソリューションの性能検証
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
ゕプリケーション ロールのパスワードが変更されると発生す
るベント
LOGIN_CHANGE_PASSWORD_GROUP
ログンのパスワードが変更されると発生するベント
SERVER_STATE_CHANGE_GROUP
SQL Server サービスの状態が変更されると発生するベント
DATABASE_OWNERSHIP_CHANGE_GROUP
データベース所有権が変更されると発生するベント
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
サーバー スコープのオブジェクトの所有者が変更されると発
生するベント
また、データベース監査で設定した対象は下表のとおりです。
監査アクションの種類
オブジェクト クラス
オブジェクト スキーマ
オブジェクト名
プリンシパル名
SELECT
OBJECT
dbo
全ユーザテーブル
dbo
INSERT
OBJECT
dbo
全ユーザテーブル
dbo
UPDATE
OBJECT
dbo
全ユーザテーブル
dbo
DELETE
OBJECT
dbo
全ユーザテーブル
dbo
サーバー監査の仕様は、監査対象がサーバー全体となりますが、データベース監査では、データベースごと
に対象となるオブジェクトとプリンシパルを絞り込む設定をすることができます。
③. 検証結果
(1).サーバー監査を設定する場合のパフォーマンス上の影響
「監査設定していない場合」と「サーバー監査を設定した場合」のデータベース サーバーのプロセッサ使用
率は、次のとおりです。
(%)
100.0%
80.0%
60.0%
40.0%
20.0%
0.0%
プロセッサ使用率
プロセッサ使用率
監査設定なし
47.8%
サーバー監査設定時
44.3%
35
第 5 章 マルチ サーバー管理ソリューションの性能検証
監査を設定しない場合とサーバー監査を設定した場合を比較し、プロセッサ使用率は、ほとんど変わらない
ことが分かりました。
（監査未設定時 47.8%・サーバー監査設定時 44.3%）
また、
「監査設定していない場合」と「サーバー監査を設定した場合」のデータベース サーバーのトランザ
クション処理量は、次のとおりです。
秒あたりのトランザクション量(相対値)
100.0%
80.0%
60.0%
40.0%
20.0%
0.0%
秒あたりのトランザクション量
監査設定なし
サーバー監査設定時
100.000%
95.706%
このグラフは、監査設定をしていない環境で、処理されたトランザクション数を 100 とした場合の相対値を
示しています。検証結果として、サーバー監査を設定した場合でも、秒あたりのトランザクション量（スルー
プット）は、ほとんど変わらないことがわかりました。
（監査未設定時 100%・サーバー監査とデータベー
ス監査設定時 95.706%）
このことから、サーバー監査の設定は、トランザクション処理には、ほとんど影響を与えないことが確認で
きました。
36
第 5 章 マルチ サーバー管理ソリューションの性能検証
(2).サーバー監査を設定する場合のパフォーマンス上の影響
「監査を設定していない場合」と「サーバー監査とデータベース監査を設定した場合」のデータベース サー
バーのプロセッサ使用率は、次のとおりです。
(%)
100.0%
80.0%
60.0%
40.0%
20.0%
0.0%
プロセッサ使用率
プロセッサ使用率
監査設定なし
47.8%
サーバー監査・データベース監査設定
62.2%
監査を設定しない場合とサーバー監査とデータベース監査を設定した場合を比較し、プロセッサ使用率が約
15% 上昇したことがわかります。
（監査未設定時 47.8%・サーバー監査とデータベース監査設定時 62.2%）
また、
「監査設定していない場合」と「サーバー監査とデータベース監査を設定した場合」のデータベース サー
バーのトランザクション処理量は、次のとおりです。
秒あたりのトランザクション量(相対値)
100.0%
80.0%
60.0%
40.0%
20.0%
0.0%
秒あたりのトランザクション量
監査設定なし
サーバー監査+データ
ベース監査設定時
100.000%
97.015%
このグラフは、監査設定をしていない環境で、処理されたトランザクション数を 100 とした場合の相対値を
示しています。検証結果として、サーバー監査とデータベース監査を設定した場合でも、秒あたりのトラン
37
第 5 章 マルチ サーバー管理ソリューションの性能検証
ザクション量（スループット）は、ほとんど変わらないことがわかりました。
（監査未設定時 100%・サー
バー監査とデータベース監査設定時 97.015%）
検証結果から、サーバー監査とデータベース監査を設定した場合、プロセッサ使用率が上昇することが分か
ります。今回の検証ではプロセッサ リソースに余裕があったため、監査の設定が、トランザクション量に、
影響しませんでしたが、より負荷が高くなり、プロセッサにボトルネックが発生すると、処理されるトラン
ザクション量が減少すると予測されます。
38
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.2.2
UCP 環境の負荷計測
ここでは、SQL Server 2008 R2 で UCP を使用し、状態情報を収集する際にかかるパフォーマンス上の影響
について、監視側の UCP サーバーと監視対象側のマネージ サーバーで計測しましたので、結果を報告します。
①. 検証環境
今回、UCP の検証で利用したサーバー構成は、次のとおりです。
マネージ インスタンス
CLUSTERSQLMET¥CLUSTERSQL
状態情報
ADSRV
SQL2008R2SRV
UCPSRV
SQLNODE1SRV
SQLNODE2SRV
MGMTClient
マネージ インスタンス
SQL2008R2SRV¥SQL20008
状態情報
ドメン
コントローラ
DB サーバー
管理者用 PC
UCP サーバー
クラスタ ー
ノード2
クラスタ ー
ノード1
ストレージ
検証で利用した OS およびソフトウェゕは、次のとおりです。
用途
コンピューター
OS
SQL Server
プロセッサ
メモリ
名
ドメイン
ADSRV
コントロー
デ ィ ス
ク
Windows Server
Core 2 Quad
2008 R2 (x64)
Q6700 2.66 GHz
4 GB
500 GB
8 GB
1 TB
16 GB
300 GB
16 GB
300 GB
4 GB
1 TB
4 GB
500 GB
ラー
DB サーバー
SQL2008R2SRV
Windows Server
SQL Server 2008
Core 2 Quad
2008 R2 (x64)
R2 Enterprise RC0
Q6700 2.66 GHz
SQLnode1SRV
Windows Server
SQL Server 2008
Xeon E5430
(クラスター)
2008 R2 (x64)
R2 Enterprise RC0
2.66 GHz ×2
(8 Core)
SQLnode2SRV
Windows Server
Xeon E5430
(クラスター)
2008 R2 (x64)
2.66 GHz ×2
(8 Core)
管理用 PC
UCPSRV
Windows Server
SQL Server 2008
Core 2 Quad
(UCP サーバー)
2008 R2 (x64)
R2 Enterprise RC0
Q6700 2.66 GHz
MGMTClient
Windows 7
-
Core 2 Quad
Q6700 2.66 GHz
39
第 5 章 マルチ サーバー管理ソリューションの性能検証
SQLnode1SRV と SQLnode2SRV の 2 ノードで構成したクラスターの情報は、次のとおりです。
クラスター名
ClusterSQL
SQL Server インスタンスの仮想ネットワーク名
ClusterSQLNet
また、クラスター構成で使用した共有デゖスクの情報は、次のとおりです。
用途
ドライブ文字
容量
監視ディスク
Q
1 GB
SQL Server プログラム
S
50 GB
tempdb
T
100 GB
U
1 TB
V
1 TB
W
1 TB
ユーザー データベース
②. 検証方法
OLTP 処理を実行し、UCP を使用しない場合と使用する場合とで、CPU 使用率とトランザクション処理量
を比較しました。UCPSRV を UCP サーバーとして構成し、クラスター化された ClusterSQLNet ンスタ
ンス、および UCPSRV と SQL2008R2SRV で稼働するンスタンスをマネージ ンスタンスとして登録
しました。なお、OLTP 処理の対象となるデータベースは、クラスター化された ClusterSQLNet ンスタ
ンスに作成し、パフォーマンス情報を収集しました。これらのンスタンスでは、パフォーマンス データ コ
レクターは使用していません。
40
に 10% 程度になります。
41
05/13/2010 10:58:41.628
05/13/2010 10:57:11.617
05/13/2010 10:55:41.621
05/13/2010 10:54:11.625
05/13/2010 10:52:41.630
05/13/2010 10:51:11.618
05/13/2010 10:49:41.622
05/13/2010 10:48:11.626
05/13/2010 10:46:41.615
05/13/2010 10:45:11.619
05/13/2010 10:43:41.623
05/13/2010 10:42:11.628
05/13/2010 10:40:41.616
05/13/2010 10:39:11.620
05/13/2010 10:37:41.625
05/13/2010 10:36:11.629
05/13/2010 10:34:41.617
05/13/2010 10:33:11.621
05/13/2010 10:31:41.626
05/13/2010 10:30:11.630
05/13/2010 10:28:41.618
05/13/2010 10:27:11.623
05/13/2010 10:25:41.627
05/13/2010 10:24:11.615
05/13/2010 10:22:41.619
05/13/2010 10:21:11.624
05/13/2010 10:19:41.628
05/13/2010 10:18:11.616
50
05/13/2010 10:16:41.621
60
05/13/2010 10:15:11.625
05/13/2010 10:13:41.629
05/13/2010 10:12:11.618
05/13/2010 10:10:41.622
05/13/2010 10:09:11.626
05/13/2010 10:07:41.630
05/13/2010 10:06:11.619
05/13/2010 10:04:41.623
05/13/2010 10:03:11.627
05/13/2010 10:01:41.616
05/13/2010 10:00:11.620
05/13/2010 09:58:41.624
第 5 章 マルチ サーバー管理ソリューションの性能検証
③. 検証結果
(1).UCP サーバーのプロセッサ使用率の推移
UCP サーバーのプロセッサ使用率の推移は、次のとおりです。
100
プロセッサー使用率の推移
90
80
70
\\UCPSRV\Processor(_Total)\% Processor Time
40
30
20
10
0
経過時間
マネージ ンスタンスの数が 3 の場合、UCP サーバーは、システム リソースをほとんど使用していない
ことが分かります。データ収集のための UCP の定期ジョブが実行されると、プロセッサ使用率は、一時的
第 5 章 マルチ サーバー管理ソリューションの性能検証
(2).データベース サーバーの負荷
SQL Server ンスタンスを UCP に登録した場合と、登録していない場合のサーバーのプロセッサ使用率
(平均) 、および処理されたトランザクション量 (平均) の比較は、次のとおりです。処理されたトランザク
ション量 (平均) のグラフは、UCP に未登録のンスタンスで、処理されたトランザクション数を 100 と
した場合の相対値を示しています。
CPU 使用率
秒あたりのトランザクション量(相対値)
100.00%
100.0%
80.00%
80.0%
60.00%
60.0%
40.00%
40.0%
20.00%
20.0%
0.00%
0.0%
CPU 使用率
秒あたりのトランザクション量
UCP に未登録
47.89%
UCP に未登録
UCP に登録
46.29%
UCP に登録
100.000%
96.921%
UCP を設定した場合と設定していない場合を比較すると、監視対象のデータベース サーバーのプロセッサ
使用率は、ほとんど変わりませんでした (UCP に未登録時 47.8%・UCP に登録時 46.2%)。また、秒あ
たりのトランザクション数 （スループット） もほぼ変わりませんでした (UCP に未登録時を 100% とし
た場合、UCP 登録時では、96.921%)。
このことから、SQL Server を UCP マネージ ンスタンスに登録しても、パフォーマンスに与える影響は、
ほとんどないといえます。
42
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.2.3
UCP サーバーの負荷計測
「5.2.2 UCP 環境の負荷計測」では、UCP サーバーを 含めた 3 つのマネージ ンスタンスが存在する環
境でのパフォーマンス計測を行いました。マネージ ンスタンスの数が 3 の場合、UCP が行うデータ収集
のための定期ジョブ実行時に、プロセッサ使用率は、一時的に 10% 程度になることが分かりました。ここ
では、監視するマネージ ンスタンスの数が、さらに増えた場合の負荷を確認します。また、CPU 使用率
に加え、ネットワーク帯域、およびメモリに対する負荷も計測しました。
①. 検証方法
UCP 関連で、UCP サーバーにデータを格納するのは、次のジョブです。
#
ジョブ名
実行場所
実行
内容
間隔
1
sysutility_mi_collect_and_upload
マネージ
15 分
ンスタンス
2
sysutility_get_views_data_into_cache_tables
UCP サーバー
UCP サーバーにデータをゕッ
プロード
15 分
取得した CPU と記憶域の使
用率の情報をテーブルに保存
ポリシーと比較して、正常性状
態を更新 (既定で、毎時 0、
15、30、および 45 分に実行
される)
3
sysutility_get_cache_tables_data_into_aggregate_tables_hourly
UCP サーバー
1 時間
ジョブ実行時刻から、過去 1
時間のデータの平均値を テー
ブルに挿入 (既定で 毎時 1
分に実行される）
4
sysutility_get_cache_tables_data_into_aggregate_tables_daily
UCP サーバー
1 日
ジョブ実行時刻から、過去 1
日のデータの平均値を テーブ
ルに挿入し、保存期間を超過し
たデータを削除（既定で毎日
00：01 に実行される）
これらのジョブの実行時のパフォーマンス負荷を計測しました。監視しているマネージ ンスタンス数は、
1、2、4、8 と増やして計測しました。なお、各ンスタンスに同じデータベースを作成し、共通のストレ
ス ツールから、同じパラメーターを使用し、一定時間、負荷をかけています。
43
第 5 章 マルチ サーバー管理ソリューションの性能検証
②. 検証結果
(1).UCP サーバーのプロセッサ使用率の推移
sysutility_mi_collect_and_upload による CPU 負荷の計測
このジョブは、各マネージ ンスタンスにて 15 分間隔で実行されます。ジョブは 3 ステップで構成され、
最終ステップで UCP サーバーにデータをゕップロードします。各マネージ ンスタンスのジョブ スケ
ジュールは、1 分間隔でずれて設定されるため、15 ンスタンス以上登録しないと同時に起動されません。
しかし、実行時間に 2～3 分を要するため、異なるタミングで起動されても、ゕップロードが重なる場合
があります。今回の検証では、各ンスタンスのジョブ スケジュールを同じ時間間隔で実行されるように設
定し、各ンスタンスからかけられる負荷が重なるようにしました。以降は、このジョブにより、UCP サー
バー側にかけられた CPU 負荷を計測したグラフです。
sysutility_mi_collect_and_upload
(%)
(2インスタンス)
400.00
350.00
350.00
300.00
300.00
250.00
250.00
200.00
200.00
150.00
150.00
100.00
sysutility_mi_collect_and_upload
(%)
(1インスタンス)
400.00
100.00
Process(sqlservr) % Processor Time
50.00
Process(sqlservr) % Processor Time
50.00
18:38:30
18:38:25
18:38:20
18:38:15
18:38:10
18:38:05
18:38:00
18:37:55
18:37:50
18:37:45
18:37:40
18:37:35
18:37:30
18:37:25
18:37:20
18:37:15
sysutility_mi_collect_and_upload
(%)
(4インスタンス)
400.00
18:37:10
13:16:47
13:16:42
13:16:37
13:16:32
13:16:27
13:16:22
13:16:17
13:16:12
sysutility_mi_collect_and_upload
(%)
7.81
0.00
13:16:07
13:16:02
13:15:57
13:15:47
13:15:42
13:15:37
13:15:32
13:15:27
0.00
13:15:52
15.63
(8インスタンス)
400.00
350.00
350.00
300.00
300.00
250.00
250.00
200.00
200.00
150.00
150.00
100.00
100.00
Process(sqlservr) % Processor Time
Process(sqlservr) % Processor Time
50.00
13:44:06
13:43:58
13:43:50
13:43:42
13:43:34
13:43:26
13:43:18
13:43:10
13:43:02
13:42:54
13:42:46
13:42:22
13:42:14
13:42:06
13:41:58
13:41:50
11:20:55
11:20:50
11:20:40
11:20:45
44
13:42:38
14.06
0.00
11:20:35
11:20:30
11:20:20
11:20:15
11:20:10
11:20:05
11:20:00
11:19:55
11:19:50
11:19:45
11:19:40
11:19:35
11:20:25
10.94
0.00
13:42:30
50.00
第 5 章 マルチ サーバー管理ソリューションの性能検証
sysutility_mi_collect_and_upload は、実行するンスタンス数にかかわらず、CPU 負荷が低く、実行時
間も非常に短いことが分かります。8 ンスタンスによる実行では、処理時間に差が生じているため、値は
小さいが負荷が分散していると考えられます。マネージ ンスタンスの数を増やすことで、負荷のかかる回
数も増えるが、負荷そのものが低いため、UCP サーバーに対する影響は、少ないと考えられます。
sysutility_get_views_data_into_cache_tables による CPU 負荷の計測
UCP サーバーにて 15 分間隔で実行されます。このジョブは、SQL Server データベース エンジンと
SQLPS (SQL PowerShell) の 2 つのプロセスで実行されます。以降は、このジョブにより、UCP サーバー
側にかけられた CPU 負荷を計測したグラフです。
sysutility_get_views_data_into_cache_tables
(%)
sysutility_get_views_data_into_cache_tables
(%)
(1 インスタンス)
400.00
(2 インスタンス)
400.00
350.00
350.00
300.00
300.00
250.00
250.00
Process(sqlservr) % Processor Time
Process(sqlservr) % Processor Time
Process(SQLPS) % Processor Time
200.00
Process(SQLPS) % Processor Time
200.00
CPU使用率合計
150.00
CPU使用率合計
150.00
sysutility_get_views_data_into_cache_tables
(%)
350.00
300.00
300.00
18:40:52
18:40:49
18:40:46
18:40:43
18:40:40
18:40:37
18:40:34
18:40:31
18:40:28
18:40:25
18:40:22
18:40:19
18:40:16
18:40:13
18:40:10
18:40:07
18:40:04
70.31
51.56
sysutility_get_views_data_into_cache_tables
(8 インスタンス)
400.00
350.00
78.13
62.50
(%)
(4 インスタンス)
400.00
98.44
18:39:58
13:17:25
13:17:22
13:17:19
13:17:16
13:17:13
13:17:10
13:17:07
13:17:04
13:17:01
13:16:58
13:16:55
13:16:52
13:16:49
13:16:46
13:16:43
13:17:34
0.00
13:17:31
0.00
13:17:28
50.00
100.00
96.92
78.46
62.50
50.00
82.81
18:40:01
100.00
250.00
250.00
Process(sqlservr) % Processor Time
Process(sqlservr) % Processor Time
200.00
Process(SQLPS) % Processor Time
200.00
Process(SQLPS) % Processor Time
CPU使用率合計
CPU使用率合計
150.00
150.00
100.00
93.75
76.56
56.92 50.00
100.00
50.00
95.31
81.25
57.81
53.13
34.38
13:47:12
13:47:09
13:47:06
13:47:03
13:47:00
13:46:57
13:46:54
13:46:51
13:46:48
13:46:45
13:46:42
13:46:39
13:46:36
13:46:33
13:46:30
13:46:27
13:46:24
11:23:46
11:23:43
11:23:40
11:23:37
11:23:34
11:23:31
11:23:28
11:23:25
11:23:22
11:23:19
11:23:16
11:23:13
11:23:10
11:23:07
11:23:04
11:23:01
11:22:58
13:46:21
0.00
0.00
sysutility_get_views_data_into_cache_tables は、ンスタンス数の違いによって負荷の大きさと処理時
45
第 5 章 マルチ サーバー管理ソリューションの性能検証
間に多少のバラツキが見られましたが、ンスタンス数の違いによる顕著な差は見られませんでした。負荷
の大きさは SQL Server と SQLPowerShell の 2 つのプロセスの合計で、最大約 25% の使用率となりま
した。ただし、15 分に一回の頻度で、実行時間も短いため、ジョブ実行時のパフォーマンスへの影響は小さ
いと考えられます。
sysutility_get_cache_tables_data_into_aggregate_tables_hourly と
sysutility_get_cache_tables_data_into_aggregate_tables_daily による CPU 負荷の計測
sysutility_get_cache_tables_data_into_aggregate_tables_hourly ジョブは、UCP サーバーで 1 時間間
隔 で
実 行 さ れ 、 31
日 間 保 持 す る デ ー タ の 格 納 を 行 い ま す 。 ま た 、
sysutility_get_cache_tables_data_into_aggregate_tables_daily ジョブは、UCP サーバーで 1 日間隔
で 実行され、366 日間保持するデータの格納を行います。これらのジョブは、負荷が小さく、実行時間も非
常に短いため、数秒の間隔をはさんで、2 つのジョブを連続して実行し、CPU 負荷を計測しました。以降は、
2 つのジョブにより UCP サーバー側にかけられた CPU 負荷を計測したグラフです。
400.00
400.00
18:41:08
18:41:07
18:41:06
18:41:05
18:41:04
18:41:03
18:41:02
18:41:01
18:41:00
18:40:49
400.00
18:40:59
0.00
18:40:58
50.00
0.00
18:40:57
100.00
50.00
18:40:56
100.00
18:40:55
150.00
18:40:54
200.00
150.00
18:40:53
250.00
200.00
18:40:52
300.00
250.00
13:17:36
13:17:37
13:17:38
13:17:39
13:17:40
13:17:41
13:17:42
13:17:43
13:17:44
13:17:45
13:17:46
13:17:47
13:17:48
13:17:49
13:17:50
13:17:51
13:17:52
13:17:53
13:17:54
13:17:55
13:17:56
300.00
18:40:51
2インスタンス
350.00
18:40:50
１インスタンス
350.00
400.00
4インスタンス
350.00
8インスタンス
350.00
300.00
300.00
250.00
250.00
200.00
200.00
150.00
150.00
100.00
100.00
50.00
50.00
2 つのジョブともにンスタンス数による負荷の差がほとんど生じず、値も小さいため、これらのジョブの
実行時にパフォーマンスへの影響はないと考えられます。
46
13:47:21
13:47:20
13:47:19
13:47:18
13:47:17
13:47:16
13:47:15
13:47:14
13:47:13
13:47:12
13:47:11
13:47:10
13:47:09
11:24:01
11:24:00
11:23:59
11:23:58
11:23:57
11:23:56
11:23:55
11:23:54
11:23:53
11:23:52
11:23:51
11:23:50
11:23:49
11:23:48
11:23:47
13:47:08
0.00
0.00
第 5 章 マルチ サーバー管理ソリューションの性能検証
(2).UCP サーバーのネットワーク使用量の推移
sysutility_mi_collect_and_upload によるネットワーク負荷の計測
ゕップロードに使用される sysutility_mi_collect_and_upload ジョブが実行される際のネットワーク負荷
を計測しました。ネットワーク負荷はネットワーク帯域幅に対する使用率を確認しました。ンスタンス数
により、差異はありましたが、値が非常に小さいため、ネットワークに対する負荷はまったくないものと思
われます。次のグラフは、最もゕップロード頻度の重なりが多い 8 ンスタンスでのジョブ実行時の状況を
示しています。
100.00
90.00
80.00
70.00
60.00
50.00
40.00
30.00
20.00
10.00
0.00
13:47:08
13:46:52
13:46:36
13:46:20
13:46:04
13:45:48
13:45:32
13:45:16
13:45:00
13:44:44
13:44:28
13:44:12
13:43:56
13:43:40
13:43:24
13:43:08
13:42:52
13:42:36
13:42:20
13:42:04
13:41:48
13:41:32
13:41:16
13:41:00
13:40:44
13:40:28
13:40:12
13:39:56
13:39:40
13:39:24
13:39:08
0.21
メモリ負荷に関しても計測していますが、まったく負荷がかかっていないため、グラフの掲載は省略します。
47
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.2.4
UCP サーバーにおけるディスク領域のサイジング
UCP サーバーは、マネージ ンスタンスから収集したデータを「sysutility_mdw」という名前のデータベー
スに保存します。また、構成情報やポリシー違反の情報は、msdb システム データベースに保存します。マネー
ジ ンスタンスから 15 分間隔でゕップロードされるデータは、すべて sysutility_mdw データベースに格納
されます。UCP を使用したマルチ サーバー管理を行う場合、このデータベースのサズが、時間とともに増加
していくため、事前にサジングを行い、適正なデゖスク領域を確保しておく必要があります。
msdb システム データベースには、データ収集の際に、データを一時保存するためのテーブルも作成されます
が、使用される領域は小さく、ここに監視データが累積されることはありません。そのため、今回は、
sysutility_mdw データベースに対するサジングのために次の作業を行いました。
sysutility_mdw データベースに作成されるテーブルは、次の表のとおりです。これらのテーブルは、 SQL
Server ユーテゖリテゖ コレクション セットによりデータが収集されるものと、パフォーマンス データ コレ
クターのシステム コレクション セットによりデータが収集されるものがあります。
スキーマ名
テーブル名
UCP
DC
Core
performance_counter_report_group_items
○
○
Core
purge_info_internal
○
○
Core
snapshot_timetable_internal
○
○
Core
snapshots_internal
○
○
Core
source_info_internal
○
○
Core
supported_collector_types_internal
○
○
Core
wait_categories
○
○
Core
wait_types
○
○
Snapshots
active_sessions_and_requests
○
Snapshots
disk_usage
○
Snapshots
distinct_queries
○
Snapshots
distinct_query_to_handle
○
Snapshots
io_virtual_file_stats
○
Snapshots
log_usage
○
Snapshots
notable_query_plan
○
Snapshots
notable_query_text
○
Snapshots
os_latch_stats
○
Snapshots
os_memory_clerks
○
Snapshots
os_memory_nodes
○
Snapshots
os_process_memory
○
Snapshots
os_schedulers
○
Snapshots
os_wait_stats
○
Snapshots
performance_counter_instances
○
48
第 5 章 マルチ サーバー管理ソリューションの性能検証
Snapshots
performance_counter_values
○
Snapshots
query_stats
○
Snapshots
sql_process_and_system_memory
○
Snapshots
sysutility_ucp_batch_manifests_internal
○
Snapshots
sysutility_ucp_cpu_memory_configurations_internal
○
Snapshots
sysutility_ucp_dac_collected_execution_statistics_internal
○
Snapshots
sysutility_ucp_smo_properties_internal
○
Snapshots
sysutility_ucp_volumes_internal
○
Snapshots
trace_data
○
Snapshots
trace_info
○
sysutility_ucp_core
computers_internal
○
sysutility_ucp_core
cpu_utilization_internal
○
sysutility_ucp_core
dacs_internal
○
sysutility_ucp_core
databases_internal
○
sysutility_ucp_core
datafiles_internal
○
sysutility_ucp_core
filegroups_internal
○
sysutility_ucp_core
logfiles_internal
○
sysutility_ucp_core
smo_servers_internal
○
sysutility_ucp_core
space_utilization_internal
○
sysutility_ucp_core
volumes_internal
○
sysutility_ucp_staging
consistent_batch_manifests_internal
○
表の UCP 列は、UCP によりデータが収集されるテーブルに「○」を記入しています。また、DC 列は、パフォー
マンス データ コレクターによりデータが収集されるテーブルに「○」を記入しています。
core スキーマのテーブルには、収集されたデータを編成し、識別するために使用するスキーマ情報が格納され
ます。これらのテーブルは、コレクター型ごとに作成されるデータ テーブルで共有されます。UCP は、
snapshots スキーマの一部のテーブルと sysutility_ucp_core スキーマのテーブルに収集したデータを格納
します。
以降で、次のトピックスを通じて、sysutility_mdw データベースにデータが収集されるプロセスを説明します。
sysutility_mdw データベースのデータ保存プロセス
2 日間保存されるデータのサズ見積もり
実機でのデータ増加量の計測 (1 回のゕップロード)
2～366 日間保存されるデータのサズ見積もり
実機でのデータ増加量の計測 (5 日間のゕップロード)
msdb のサズ調査
49
第 5 章 マルチ サーバー管理ソリューションの性能検証
①. sysutility_mdw データベースのデータ保存プロセス
UCP サーバーの sysutility_mdw データベースのデータ保存プロセスは、次のとおりです。
Step.
1
実行間隔
15 分
処理内容
マネージ ンスタンスの sysutility_mi_collect_and_upload ジョブにより、各ンスタン
スの情報は、UCP サーバーの sysutility_mdw データベースの一時保存用テーブルに格納
される
2
15 分
UCP サーバーの sysutility_get_views_data_into_cache_tables ジョブによって Step.1
で格納された各ンスタンスのデータは 2 日間保存される監視データとして、
[aggregation_type] 列に「0」をセットして、オブジェクト別にテーブルに格納される
(このタミングで、Step.1 で格納した一時データは削除される）
3
1 時間
UCP サ ー バ ー の
sysutility_get_cache_tables_data_into_aggregate_tables_hourly
ジョブによって、Step.2 で格納されたデータのうち、「CPU 使用率」と「デゖスク使用率」
のデータに対して過去 1 時間のデータの平均値を算出し [aggregation_type] 列に「1」を
セットして格納する（1 時間間隔で実行されるため、1 日当たり 24 回分の監視データが保
存される）
4
24 時間
UCP サーバーの sysutility_get_cache_tables_data_into_aggregate_tables_daily ジョ
ブによって、 Step.2 で格納されたデータのうち、「CPU 使用率」と「デゖスク使用率」の
データに対して過去 1 日のデータの平均値を算出し [aggregation_type] 列に「2」をセッ
トして格納する（1 日間隔で実行されるため、1 年当たり 366 回分の監視データが保存さ
れる）
5
24 時間
UCP サーバーの mdw_purge_data_[sysutility_mdw] ジョブによって、Step.2 で格納さ
れたデータのうち、2 日前の 00:00:00 以前のデータが削除される
ただし、
「CPU 使用率」と「デゖスク使用率」のデータで [aggregation_type] 列が「1」 の
場合、
31 日以上経過したデータのみを削除し、[aggregation_type] 列が
「2」の場合は 366
日以上経過したデータのみが削除される。
50
第 5 章 マルチ サーバー管理ソリューションの性能検証
次 の 図 は 、 CPU 使 用 率 を 格 納 す る
sysutility_ucp_core.cpu_utilization_internal テ ー ブ ル の
[aggregation_type] 列を検索した結果を示しています。
[aggregation_type] = 0
15 分ごとに蓄積され 2 日間保存
[aggregation_type] = 1
1 時間ごとに蓄積され 31 日間保存
[aggregation_type] = 2
1 日ごとに蓄積され 366 日間保存
なお、既定で 366 日間保存されるデータは 31 日間、92 日間、183 日間、732 日間に変更することがで
きます。変更方法は、
「6.3.3 UCP の検証方法」の「⑦.収集データの自動的な削除」を参考にしてください。
テーブルに格納されるデータは、mdw_purge_data_[sysutility_mdw] ジョブによって削除されるため、
稼働開始から 2 日後に最も増加した状態になりますが、ジョブによる削除が実行されるとサズは減少しま
す。また、既定の設定では、1 年以上経過したデータは、テーブルからすべて削除されます。
②. 2 日間保存されるデータのサイズ見積もり
2 日間保存されるデータのラフサクルは、次のとおりです。
15 分間隔で、データの保存が実行され、保存される対象オブジェクトごとに 1 行が生成され
sysutility_mdw データベースのテーブルに保存されます。
例：マネージ ンスタンスにデータベースが 10 個ある場合
1 度のデータ収集で sysutility_ucp_core.databases_internal テーブルに 10 行のデータを追加
1 日間隔で、2 日前の 00:00:00 以前のデータは削除されます。
51
第 5 章 マルチ サーバー管理ソリューションの性能検証
次の表は、sysutility_mdw データベースで 2 日間分のデータを保存するテーブルの一覧です。
テーブル名
関連オブジェクト名
sysutility_ucp_core.computers_internal
サーバー
sysutility_ucp_core.volumes_internal
ボリューム
sysutility_ucp_core.smo_servers_internal
ンスタンス
sysutility_ucp_core.databases_internal
データベース
sysutility_ucp_core.dacs_internal
DAC
sysutility_ucp_core.filegroups_internal
フゔル グループ
sysutility_ucp_core.datafiles_internal
データ フゔル
sysutility_ucp_core.logfiles_internal
ログ フゔル
2 日間保存されるデータのサズを概算で見積もる方法は、次のとおりです。
(1).15 分に 1 度のジョブ実行で、保存されるデータの増加量をテーブル別に算出
各テーブルの 1 行あたりのデータ量は、次の表のとおりです。
1 行あたり
テーブル（対象オブジェクト）
の 最 大
文字列 7
固定長
可変長
データ量
割のデー
タ増加量
sysutility_ucp_core.computers_internal（サーバー）
17,496
72
17,424
12,269
sysutility_ucp_core.volumes_internal（ボリューム）
1,328
32
1296
939
sysutility_ucp_core.smo_servers_internal（インスタンス）
17,713
77
17,636
12,422
sysutility_ucp_core.databases_internal（データベース）
10,474
32
10,442
7,341
sysutility_ucp_core.dacs_internal（DAC）
24,816
136
24,780
17,482
sysutility_ucp_core.filegroups_internal（ファイル グループ）
11,382
20
11,362
7,973
sysutility_ucp_core.datafiles_internal（データ ファイル）
15,202
38
15,164
10,653
sysutility_ucp_core.logfiles_internal（ログ ファイル）
14,944
38
14,906
10,472
各列のデータ量はデータ型から算出します。nvarchar(max) 型は、nvarchar(4000) と同様に扱い、8,000
バトに換算しました。
監視データが保存される際、各テーブルで、マネージ ンスタンスの対象オブジェクト数だけ行が追加され
ます。
52
第 5 章 マルチ サーバー管理ソリューションの性能検証
例：監視ンスタンスのボリューム数が 2、データベース数が 8 の場合、一回のデータ保存において、
sysutility_ucp_core.volumes_internal に 2 行、sysutility_ucp_core.databases_internal に 8 行の
データを追加
よって、この例では、最大で 2 × 1,328 バト 、8 × 10,474 バト が増えることになります。
なお、可変長データが、格納できる最大サズの文字列長になることは考えにくいため、
「文字列 7 割のデー
タ増加量」列に最大サズの 7 割が埋められていると仮定したバト数を記載しています。
(2).上記表の「文字列 7 割のデータ増加量」列の値に基づき必要なストレージサイズを計算
まず、2 日間のデータ保存回数を次の計算式から求めます。
24 [時間] × 4 [1 時間の実行回数] × 2 [保存期間] = 192
つまり、ゕップロードジョブは、15 分に一度実行されるため、テーブルには、192 回分のデータが格納さ
れていることになります。よって、表の「文字列 7 割のデータ増加量」列の値に基づき増加量を次の計算式
から求めることができます。
オブジェクトに対応するテーブルの 1 行のデータ増加量 × オブジェクト数 × 192
下表は、計算式を使用した例です。
オブジェクト
オブジェクト数
文字列 7 割の
データ増加量
2 日間のデータ増加量
単位データ増加量
サーバー
1
12,269
12,269
2,355,610
ボリューム
2
939
1,878
360,653
インスタンス
1
12,422
12,422
2,385,062
データベース
8
7,341
58,731
11,276,390
DAC
4
17,482
69,928
13,426,176
ファイル グループ
8
7,973
63,787
12,247,142
データ ファイル
8
10,653
85,222
16,362,701
ログ ファイル
8
10,472
83,778
16,085,299
総データ増加量
74,499,034
この例では 2 日間で約 75 MB が必要となることが分かります。なお、データベースには、システム デー
タベースの数も含める必要があることに注意してください。
53
第 5 章 マルチ サーバー管理ソリューションの性能検証
③. 実機でのデータ増加量の計測 (1 回のアップロード)
UCP を構成した検証用のサーバー環境で、1 回のゕップロードあたりのデータ増加量を計測しましたので、
結果を報告します。
マネージ ンスタンスからのデータのゕップロードを行うジョブと、UCP サーバーのデータベースにゕッ
プロードされたデータを保存するジョブをセットで 1 回とし、マネージ ンスタンスの数を変化させた場
合と、1 ンスタンスでデータベースの数を変化させた場合とで、1 回の処理あたりのデータ増加量を計測
しました。
マネージ ンスタンス数による増加量の変化
次の内容で測定しました。
項目
設定、または操作
マネージ インスタンスの数
1、2、4、8 で実行
各インスタンスに作成したデータべース オブジェクトの数
共通
採取した値
database_size と index_size の合計値を計算
計測方法
複数回ジョブを実行し、データ増加量の平均値を算出
検証結果は、次のとおりです。
(KB)
549
600
500
400
268
300
200
100
0
136
56
database + index_size
1 ンスタンス
56
2 ンスタンス
136
4 ンスタンス
268
8 ンスタンス
549
54
第 5 章 マルチ サーバー管理ソリューションの性能検証
データベース数による増加量の変化
次の内容で測定しました。
項目
設定、または操作
マネージ インスタンスの数
1 で実行
データベースの数
5、10、15、20 で実行
各インスタンスに作成したデータべース オブジェクトの数
共通
採取した値
database_size と index_size の合計値を計算
計測方法
複数回ジョブを実行し、データ増加量の平均値を算出
検証結果は、次のとおりです。
データベース数によるデータ増加量の変化
(KB)
136
140
108
120
100
72
80
60
40
40
20
0
5
database + index_size
40
10
72
15
108
20
136
ンスタンスの数、または、データベースの数が増加した場合、格納されるデータ量も、リニゕに増
加しています。sysutility_mdw データベースの各テーブルにオブジェクト数の変化によって増える行
数が決まっていることから、他のオブジェクトが変化した場合でも同様に、リニゕに変化することが
予測できます。
55
第 5 章 マルチ サーバー管理ソリューションの性能検証
④.
2～366 日間保存されるデータのサイズ見積もり
sysutility_ucp_core.cpu_utilization_internal テーブルに格納される「CPU 使用率」の集計データと
sysutility_ucp_core.space_utilization_internal テーブルに格納される「デゖスク使用率」の集計データは、
2～366 日間保存されます。各テーブルに保持されるオブジェクトの種類は、次の表のとおりです。
テーブル名
情報が格納されるオブジェクトの種類
sysutility_ucp_core.cpu_utilization_internal
サーバー
ンスタンス
DAC
sysutility_ucp_core.space_utilization_internal
サーバー
ボリューム
ンスタンス
データベース
フゔル グループ
データベース フゔル
ログ フゔル
各オブジェクトが 1 つ増えるごとに、保存されるデータ行数が 1 行増加します。また、各テーブルには、
aggregation_type という名前の列があります。この列の値から、データ行が保持される期間を知ることが
できます。
aggregation_type 列の値
生成されるタイミング
保持される期間
0
sysutility_get_views_data_into_cach
2 日間
e_tables ジョブによって 15 分ごとに収
集されたデータ
1
sysutility_get_cache_tables_data_int
31 日間
o_aggregate_tables_hourly ジョブに
よって 1 時間ごとに集計されたデータ
2
sysutility_get_cache_tables_data_int
366 日間 (変更することができま
o_aggregate_tables_daily ジ ョ ブ に
す)
よって 1 日ごとに集計されたデータ
sysutility_ucp_core.cpu_utilization_internal と sysutility_ucp_core.space_utilization_internal テ ー
ブルのオブジェクトごとの増加量を算出します。なお、可変長データが最大数の文字列をとることは考えに
くいため、
「文字列 7 割のデータ増加量」列に可変長文字型列では、最大サズの 7 割が埋められていると
仮定したサズを記載しています。
56
第 5 章 マルチ サーバー管理ソリューションの性能検証
sysutility_ucp_core.cpu_utilization_internal（CPU 使用率を格納）(単位：Byte)
オブジェクト の種類
最大データ量
不可変データ
可変データ
文字列 7 割のデータ増加量
サーバー
274
16
258
197
インスタンス
274
16
258
197
DAC
532
16
516
377
sysutility_ucp_core.space_utilization_internal（記憶域の使用率を格納）(単位：Byte)
オブジェクト の種類
最大データ量
オブジェクトなし
不可変データ
可変データ
文字列 7 割のデータ増加量
28
28
0
28
サーバー
286
28
258
209
ボリューム
544
28
516
389
ンスタンス
286
28
258
209
データベース
1,088
56
1,032
778
802
28
774
570
1,060
28
1,032
750
802
28
774
570
フゔルグループ
データ フゔル
ログ フゔル
上記表の「文字列 7 割のデータ増加量」列の値に基づき計算することができます。まず、次の計算式で、2
日間、31 日間、および 366 日間保持されるデータの保存回数を合計します。
24 [時間] × 4 [1 時間の実行回数] × 2 [保存期間：2 日間]
+ 24 [1 日の実行回数] × 31 [保存期間：31 日間]
+ 366 [1 年間の実行回数])
= 1,302
つまり、テーブルには、1,302 回分のデータが格納されていることになります。よって、表の「文字列 7 割
のデータ増加量」列の値に基づき増加量を次の計算式から求めることができます。
オブジェクトに対応するテーブルの 1 行のデータ増加量 × オブジェクト数 × 1,302
57
第 5 章 マルチ サーバー管理ソリューションの性能検証
下表は、計算式を使用した例です。
オブジェクト
オブジェクト数
CPU 使用率の格納サイズ(Byte)
記憶域の使用率の格納サイズ
(Byte)
オブジェクトなし
---
---
36,456
サーバー
1
256,494
272,118
ボリューム
4
---
2,031,120
ンスタンス
1
256,494
272,118
データベース数
8
---
8,124,480
DAC
4
1,968,624
---
フゔルグループ
8
---
5,947,536
データ フゔル
8
---
7,832,832
ログ フゔル
8
---
5,947,536
2,481,612
30,464,196
合計
⑤. 実機でのデータ増加量の計測 (5 日間のアップロード)
検証用に構築した 3 台の仮想サーバー環境に SQL Server を 1 ンスタンスずつ構成し、UCP を使用し
た 5 日間のデータ収集を行い、sysutility_mdw データベースの増加量を監視いたしましたので、結果を報
告します。
計測は、sp_spaceused システム ストゕド プロシージャーを使用し、1 時間ごとに、sysutility_mdw デー
タベース サズを調べました。ジョブのスケジュールは既定値を使用しています。なお、マネージ ンス
タンスから取集される各オブジェクトの数は、次の表のとおりです。
オブジェクト
1 インスタンスあた
総オブジェクト数
備考
りのオブジェクト数
サーバー
1
3
ボリューム
4
12
---
3
データベース数
8
24
4 つのシステム データベースを含む
DAC
4
12
ユーザー データベースはすべて DAC に登録
ファイル グループ
8
24
プラマリ フゔルグループのみを作成
データ ファイル
8
24
ログ ファイル
8
24
インスタンス
58
仮想サーバー
第 5 章 マルチ サーバー管理ソリューションの性能検証
sysutility_mdw データベースのサズを 5 日間計測したグラフは、次のとおりです。
(KB)
60,000
50,000
reserved
data
40,000
index
30,000
20,000
10,000
14:00:00
16:00:00
18:00:00
20:00:00
22:00:00
0:00:00
2:00:00
4:00:00
6:00:00
8:00:00
10:00:00
12:00:00
14:00:00
16:00:00
18:00:00
20:00:00
22:00:00
0:00:00
2:00:00
4:00:00
6:00:00
8:00:00
10:00:00
12:00:00
14:00:00
16:00:00
18:00:00
20:00:00
22:00:00
0:00:00
2:00:00
4:00:00
6:00:00
8:00:00
10:00:00
12:00:00
14:00:00
16:00:00
18:00:00
20:00:00
22:00:00
0:00:00
2:00:00
4:00:00
6:00:00
8:00:00
10:00:00
12:00:00
14:00:00
16:00:00
18:00:00
20:00:00
22:00:00
0:00:00
2:00:00
4:00:00
6:00:00
8:00:00
10:00:00
12:00:00
14:00:00
0
2 日後の 02:00:00 になった時点で mdw_purge_data_[sysutility_mdw] ジョブが実行され、データの
削除が行われています。このため、オンラン ブックに「UMDW (sysutility_mdw) のデゖスク領域の増加
率は、最初の 2 日間で最大になります。」(http://msdn.microsoft.com/ja-jp/library/ee210548.aspx)
と記載がありますが、このグラフから、その挙動が理解できます。以降、削除は、1 日に 1 回実行されてい
ます。1 時間ごとに格納され、31 日間保存されるデータと 1 日 1 回格納され、366 日間保存されるデー
タがあるため、すぐに削除されないデータもありますが、そのサズは僅かであることも分かります。
今回、検証した 3 つのンスタンス構成（下表）の場合では短期、長期の合計で年間の監視データ量は約 110
MB となります。この値は、オンラン ブックで推奨とされる 1 ンスタンスあたり 2 GB というサズ
より、はるかに小さい値です。
オブジェクト
オブジェクト数
サーバー
1
ボリューム
2
ンスタンス
1
データベース
8
DAC
4
フゔル グループ
8
データ フゔル
8
ログ フゔル
8
計算式から逆算した場合、データベース、DAC、フゔルグループ、データベースフゔル、ログフゔル
59
第 5 章 マルチ サーバー管理ソリューションの性能検証
が約 120 あるンスタンスの場合、推奨となる 2 GB を必要することになります。このことから、UCP は、
かなりの大規模なデータベースの監視を想定していることが考えられます。
⑥. msdb のサイズ
msdb システム データベースに作成される UCP 用のテーブルにはンスタンスの登録情報と監視データ
をゕップロードするときに使用される一時的なデータしか置かれないため、長期的にデータが増加していく
ことはありませんが、dbo.sysjobhistory テーブルには、ジョブの実行履歴が累積するため、短期的には、
データが増加しているように見えます。
既定の設定では、ジョブの実行履歴は、1,000 行まで保存されますが、UCP サーバーを構成すると 15,000
行まで保存するように内部的に変更されます。そのため、ジョブの実行回数が 15,000 回に達するまでデー
タ サズは増加し続けます。dbo.sysjobhistory テーブルに 15,000 行の履歴が保存された時点でのデー
タ サズは、書き込まれる内容によって差異がありますが、およそ 50 MB になります。なお、格納するジョ
ブの実行履歴のサズは、次の手順で変更することができます。
SQL Server Management Studio のオブジェクト エクスプローラーで、SQL Server データベース エン
ジンのンスタンスに接続し、[SQL Server エージェント] ノードを右クリックして、表示されるメニュー
から [プロパテゖ] を選択します。
そうすると [SQL Server エージェントのプロパテゖ] が表示されるので、[履歴] ページを選択し、[ジョブ
履歴ログのサズを制限する] チェック ボックスを選択して、[ジョブ履歴ログの最大サズ (行)] ボック
スで、ジョブ履歴ログに使用できる最大行数を入力し、[ジョブごとのジョブ履歴の最大行数] ボックスで、
1 つのジョブが保存できるジョブ履歴の最大行数を入力します。
60
第 5 章 マルチ サーバー管理ソリューションの性能検証
5.2.5
パフォーマンス データ コレクターの負荷計測
「5.2.2 UCP 環境の負荷計測」で OLTP 処理を行う SQL Server ンスタンスを UCP のマネージ ンスタ
ンスとして登録しても、パフォーマンス上の影響が、ほとんどないことを確認しました。また、
「5.2.3 UCP サー
バーの負荷計測」では、UCP サーバー側の負荷も少ないことを確認しました。
ここでは、UCP のマネージ ンスタンスに対して、さらに詳細なパフォーマンス データ収集を行うために、
パフォーマンス データ コレクターの構成を追加するというシナリオで、その負荷を計測しました。
①. 検証環境
このシナリオでの性能検証に使用したサーバー構成は次のとおりです。
マネージ インスタンス
パフォーマンス
データ
ADSRV
SQL2008R2SRV
UCPSRV
CLUSTERSQLMET¥CLUSTERSQL
SQLNODE1SRV
SQLNODE2SRV
MGMTClient
マネージ インスタンス
SQL2008R2SRV¥SQL20008
パフォーマンス
データ
ドメン
コントローラ
DB サーバー
UCP サーバー
〒
パフォーマンス
データ コレクター
管理者用 PC
クラスタ ー
ノード2
クラスタ ー
ノード1
ストレージ
検証で利用した OS およびソフトウェゕは、次のとおりです。
用途
コンピューター
OS
SQL Server
プロセッサ
Windows Server
---
Core 2 Quad
メモリ
ディスク
名
ドメイン
ADSRV
コントロー
2008 R2 (x64)
4 GB
500 GB
8 GB
1 TB
4 GB
1 TB
4 GB
500 GB
Q6700 2.66 GHz
ラー
DB サーバー
SQL2008R2SRV
Windows Server
SQL Server
Core 2 Quad
2008 R2 (x64)
2008 R2
Q6700 2.66 GHz
Enterprise RC0
UCPSRV
Windows Server
SQL Server
Core 2 Quad
2008 R2 (x64)
2008 R2
Q6700 2.66 GHz
Enterprise RC0
(UCP サーバー)
管理用 PC
MGMTClient
Windows 7
---
Core 2 Quad
Q6700 2.66 GHz
61
第 5 章 マルチ サーバー管理ソリューションの性能検証
②. 検証方法
OLTP 処理を実行し、パフォーマンス データ コレクターを使用しない場合と使用する場合とで、CPU 使用
率とトランザクション処理量を比較しました。UCPSRV を UCP サーバーとして構成し、クラスター サー
バーで稼働する ClusterSQLNet ンスタンス、および UCPSRV と SQL2008R2SRV で稼働するンスタ
ン ス を マ ネ ー ジ ン ス タ ン ス と し て 登 録 し ま し た 。 さ ら に 、 ClusterSQLNet ン ス タ ン ス と
SQL2008R2SRV で稼働するンスタンスで、パフォーマンス データ コレクターを有効化しました。
なお、OLTP 処理の対象となるデータベースはクラスター サーバー上の SQL Server ンスタンスに作成し、
パフォーマンス情報を収集しました。クラスター サーバー上の SQL Server ンスタンスでは、SQL Server
監査は無効にしています。
③. 検証結果
(1).監視サーバーのプロセッサ使用率の推移
UCP を使用している環境で、パフォーマンス データ コレクターを有効化するとパフォーマンス データも
UCP サーバーの sysutility_mdw データベースに収集されます。つまり、UCP サーバーは、パフォーマン
ス データ コレクターの監視サーバーの機能も兼ねて動作することになります。なお、監視サーバーのプロ
セッサ使用率の推移は、次のとおりです。
(%)
100
監視サーバーのプロセッサー使用率の推移
90
80
70
60
Processor(_Total)\% Processor Time
50
40
30
20
10
14:54:57
14:56:12
14:57:27
14:58:42
14:59:57
15:01:12
15:02:27
15:03:42
15:04:57
15:06:12
15:07:27
15:08:42
15:09:57
15:11:12
15:12:27
15:13:42
15:14:57
15:16:12
15:17:27
15:18:42
15:19:57
15:21:12
15:22:27
15:23:42
15:24:57
15:26:12
15:27:27
15:28:42
15:29:57
15:31:12
15:32:27
15:33:42
15:34:57
15:36:12
15:37:27
15:38:42
15:39:57
15:41:12
15:42:27
15:43:42
15:44:57
15:46:12
15:47:27
15:48:42
15:49:57
15:51:12
15:52:27
15:53:42
15:54:57
0
経過時間
監視サーバーでは、UCP および、パフォーマンス データ コレクターを設定しても、定期ジョブが実行され
るタミングで、一時的にプロセッサ使用率が 10% 程度まで上昇するだけで、定常時は、システム リソー
スをほとんど使用していません。
監視対象の SQL Server ンスタンスを UCP および、パフォーマンスデータ コレクターに登録した場合
62
第 5 章 マルチ サーバー管理ソリューションの性能検証
と、登録していない場合のサーバーのプロセッサ使用率 (平均) 、および処理されたトランザクション量 (平
均) の比較は、次のとおりです。処理されたトランザクション量 (平均) のグラフは、UCP に未登録のン
スタンスで、処理されたトランザクション数を 100 とした場合の相対値を示しています。
CPU 使用率
秒あたりのトランザクション量(相対値)
100%
100%
80%
80%
60%
60%
40%
40%
20%
20%
0%
0%
CPU 使用率
UCP、データ
コレクターに未登録
UCP、データ
コレクターに登録
秒あたりのトランザクション量
UCP、データ
47.89%
コレクターに未登録
UCP、データ
46.40%
コレクター に登録
100.00%
97.91%
SQL Server ンスタンスを UCP および、パフォーマンス データ コレクターに登録した場合と登録して
いない場合を比較すると、プロセッサ使用率は、ほとんど変わりませんでした(未登録時 47.8%・登録時
46.4%)。また、秒あたりのトランザクション数（スループット）もほぼ変わりませんでした (未登録時を
100% とした場合、UCP および、パフォーマンス データ コレクターに登録時では、97.91%)。
このことから、UCP とパフォーマンス データ コレクターは、同時に使用しても SQL Server ンスタン
スに対するパフォーマンス上のンパクトを与えずに運用できることが分かります。
63
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
第6章 「マルチ サーバー管理の標準化」ソリューションの運用検証
この章では、ソリューションの運用方法を検証した結果を説明します。具体的には、次の検証を行いました。
6.1 ベントのモニタリングと通知
6.2 セキュリテゖ監査
6.3 パフォーマンス データの収集
6.0 検証項目の策定と検証環境
6.0.1
検証項目の策定
CQI プロジェクトでは、
「コントソ社」から提示されたマルチ サーバー環境での SQL Server 運用管理標準の
確立要求に対し、データベース管理者が担当している業務と抱えている課題の確認から始めました。プロジェク
トで洗い出したデータベース管理者の業務と課題は次の表のとおりです。
区分
DB 管理者の業務
作業概要
現在の課題
何ができればよいのか?
定常
ンストールと設
ンスタンスのンス
変更を個別に行わなければな
一度の作業でまとめて変更し
定
トールと データベース
らない
たい
ネーミングと照合順序管
個別に表示しなければならな
一箇所で一覧を表示したい
理
い
更新プログラムとセキュ
バージョンごとにンター
ンターフェスを統一した
リテゖ修正プログラムの
フェスが異なる
い
個別に変更されてもわからな
個別環境での変更を把握した
い
い
オブジェクト管理 (バー
各オブジェクトを直接確認し
オブジェクト定義のバージョ
ジョン管理)
なければ、定義がわからない
ンを管理したい
定義物と実際のオブジェクト
定義物と実際のオブジェクト
が同じかどうかの確認が難し
のバージョン管理をしたい
プロパテゖの設定
適用
リリース管理
い
リリースの履歴管理ができて
リリース履歴を管理したい
いない
誰がリリース作業を行ったか
がわからない
64
リリース実施者を把握したい
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
ベント モニタ
エラー監視、ゕラート通
エラー発生状況を対象サー
エラー発生状況を一箇所で確
リング
知、およびパフォーマンス
バーごとに確認しなければな
認したい
監視
らない
すべてのエラーがベント
対応が必要なエラーは、すべ
ログに出力されるわけではな
て検知したい
い
セキュリテゖ管理
監査とユーザー管理
問題の発生が自動通知される
閾値を超えた値に対してゕ
機能がない
ラート通知をしたい
監査結果を一箇所で確認する
監査結果を収集したい
ことができない
監査結果をレポート表示でき
監査結果をレポートしたい
ない
必要な監査結果のみを抽出し
監査結果をフゖルタリングし
たい
たい
データを収集したい
パフォーマンス
パフォーマンス データ収
障害が発生したときに必要な
データ収集
集とレポーテゖング
データが採取できていない
障害が発生したときに必要な
データを蓄積したい
データがそろっていない、失
われている
管理対象のンスタンスが増
管理対象の増加を吸収したい
える度に、パフォーマンス
データの収集や蓄積処理を追
加している
詳細な分析を行うためには作
多面的な切り口で分析したい
りこみが必要となる
過去から現在の状態はわかる
が、将来どうなるかがわから
ないため、予防策が検討しに
くい
緊急
チューニング
改善案の提示
トラブル シュー
障害履歴管理
テゖング
65
将来の傾向などを予測したい
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
今回の CQI プロジェクトでは、上記の表のうち、特に重要と考えられる次の定常業務について、運用検証の対
象としました。
ベント モニタリング
セキュリテゖ管理
パフォーマンス データ収集
66
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.0.2
検証環境
RFP に対する運用標準化の検証で使用したサーバー構成と主要な管理データのフローは、次のとおりです。
ADSRV
ドメン
コントローラ
RSSRV
UCPSRV
レポート
サーバー
UCP サーバー
SQLNODE1SRV
CLUSTERSQLNet
SQLNODE2SRV
クラスター
ノード1
SQL2008R2SRV
クラスター
ノード2
SQL2008SRV
SQL2005SRV
DB サーバー
SQL2000SRV
DB サーバー
兼 メールサーバー
MGMTClient
管理者用 PC
ストレージ
(共有デゖスク)
障害発生
SQL Server ンスタンスとストレージの状態情報
障害ベント、監査情報
パフォーマンス データ、レポート
各サーバーのハードウェゕとソフトウェゕ構成は、次のとおりです。
用途
コンピューター名
OS
SQL Server
プロセッサ
ドメイン
ADSRV
Windows Server
---
Core 2 Quad
コントローラー
レポート
2008 R2 (x64)
RSSRV
DB サーバー
SQL Server 2008
Core 2 Quad
2008 R2 (x64)
R2 Enterprise RC0
Q6700 2.66 GHz
Windows Server
SQL Server 2000
Core 2 Quad
2003 R2 SP2 (x86)
Enterprise SP4
Q6700 2.66 GHz
Windows Server
SQL Server 2005
Core 2 Quad
2003 R2 SP2 (x64)
Enterprise SP3
Q6700 2.66 GHz
Windows Server
SQL Server 2008
Core 2 Quad
2008 R2 (x64)
Enterprise SP1
Q6700 2.66 GHz
Windows Server
SQL Server 2008
Core 2 Quad
2008 R2 (x64)
R2 Enterprise RC0
Q6700 2.66 GHz
SQLnode1SRV
Windows Server
SQL Server 2008
Xeon E5430 2.66
(クラスター)
2008 R2 (x64)
R2 Enterprise RC0
GHz ×2
SQL2000SRV
SQL2005SRV
SQL2008SRV
SQL2008R2SRV
ディスク
4 GB
500 GB
4 GB
500 GB
8 GB
1 TB
8 GB
1 TB
8 GB
1 TB
8 GB
1 TB
16 GB
300 GB
16 GB
300 GB
4 GB
1T B
Q6700 2.66 GHz
Windows Server
サーバー
メモリ
(8 Core)
SQLnode2SRV
Windows Server
(クラスター)
2008 R2 (x64)
---
Xeon E5430 2.66
GHz ×2
(8 Core)
UCPSRV
Windows Server
67
SQL Server 2008
Core 2 Quad
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
管理用 PC
(UCP サーバー)
2008 R2 (x64)
R2 Enterprise RC0
Q6700 2.66 GHz
MGMTClient
Windows 7 (x64)
---
Core 2 Quad
4 GB
500 GB
Q6700 2.66 GHz
SQLnode1SRV と SQLnode2SRV の 2 ノードで構成したクラスターの情報は、次のとおりです。
クラスター名
ClusterSQL
SQL Server インスタンスの仮想ネットワーク名
ClusterSQLNet
6.1 イベント モニタリングと障害通知
6.1.1
検証の目的と範囲
①. 要求されているイベント モニタリングとは?
「コントソ社」がマルチサーバー環境でのデータベース サーバーのベント モニタリングの業務で抱えている
課題は次のとおりです。
標準化の対象
作業概要
現在の課題
何ができればよいのか?
イベント モニタリング
エラー監視、ゕラート通
エラー発生状況を対象サー
エラー発生状況を一箇所で確
知、およびパフォーマンス
バーごとに確認しなければな
認したい
監視
らない
すべてのエラーがベント ロ
対応が必要なエラーは、すべて
グに出力されるわけではない
検知したい
問題の発生が自動通知される
閾値を超えた値に対してゕ
機能がない
ラート通知をしたい
ベント モニタリングでは、システムから特定のルールを満たすベント メッセージが生じた場合や特定のパ
フォーマンス値の変化が検知された場合、その内容が、速やかに管理者に通知されることが要求されています。
また、通知メッセージには、発生したベント情報 (エラー内容や警告の概要) が記載されなければなりません。
検証プロジェクトでは、この課題に対し、SQL Server 管理で選択できるベント モニタリングで、もっとも
効果的な方法を確認するために複数の選択肢を比較して検証しました。
68
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
②. SQL Server の対象バージョン
ベント モニタリングの検証では、次の SQL Server のバージョンを対象としました。
SQL Server 2000 SP4
SQL Server 2005 SP3
SQL Server 2008 SP1
SQL Server 2008 R2 RC0
③. 検証範囲
ベント モニタリングの検証では、Windows 環境、および SQL Server 環境で選択できる次の方法について、
実装し、確認しました。
テスト対象
モニタリング手法
通知方法
ベント発生条件
詳細確認方法
エラー メッセージ
SQL Server エージェント
SMTP メール
デッドロック
ベント サブスクリプ
パフォーマンス異常
eventtriggers コマンド
ション
OS タスク
リモート デスクトップ
SQL Server エージェント
SMTP メール
パフォーマンス モニター
継続的な CPU 使用率の高騰
ベント サブスクリプ
メモリプレッシャー
ション
(メモリ不足による性能低下)
リモート デスクトップ
なお、検証範囲の決定に際しては、事前調査により次の 2 点の事項を考慮しています。
現在、SQL Server エージェントの通知方法としてサポートされている NET SEND コマンドは、将来廃止され
る予定にあるため、SMTP ベースのデータベース メールを使用した通知を検証しました。また、メッセージ ID
を持たないベント メッセージは、Windows、SQL Server ともに標準的に使用されるモニタリング方法では
検知できないため、今回の検証対象から除外しています。
69
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
メッセージ ID を持たないベント メッセージには、SQL Server トレース フラグにより表示される情報な
どがあります。次のログは、トレース フラグ 1204 を使用することで出力されるデッドロックの詳細情報です。
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
2010-03-07 13:23:08.22 spid6s
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
RID: 7:1:93:0
CleanCnt:2 Mode:X Flags: 0x3
Grant List 1:
Owner:0x06F65340 Mode: X
Flg:0x40 Ref:0 Life:02000000 SPID:53 ECID:0 XactLockInfo: 0x088FE2A8
SPID: 53 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: select * from t1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x050C0EE0 Mode: S SPID:52 BatchID:0 ECID:0 TaskProxy:(0x085E6354) Value:0x4cca980 Cost:(0/236)
Node:2
RID: 7:1:109:0
CleanCnt:2 Mode:X Flags: 0x3
Grant List 1:
Owner:0x06F64E20 Mode: X
Flg:0x40 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x050C0F08
SPID: 52 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: select * from t2
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x088FE280 Mode: S SPID:53 BatchID:0 ECID:0 TaskProxy:(0x08B16354) Value:0x4cca840 Cost:(0/236)
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x050C0EE0 Mode: S SPID:52 BatchID:0 ECID:0 TaskProxy:(0x085E6354) Value:0x4cc a980 Cost:(0/236)
その他にも次のようなメッセージ ID を持たないベント メッセージがあります。
2010-03-10 16:23:18.22 spid1
2010-03-10 16:23:18.68 spid1
2010-03-10 16:23:18.68 spid1
2010-03-10 16:23:18.68 spid1
2010-03-10 16:23:18.68 spid1
2010-03-10 16:23:18.68 spid1
2010-03-10 16:23:18.68 spid1
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=89202 Free=0 Procedures=1382
Inram=0 Dirty=56747 Kept=0
I/O=0, Latched=1710, Other=1620034
Buffer Counts: Commited=1769075 Target=1769075 Hashed=1678491
InternalReservation=162 ExternalReservation=61902 Min Free=2048 Visible= 450064
Procedure Cache: TotalProcs=57 TotalPages=1382 InUsePages=1279
Dynamic Memory Manager: Stolen=14947 OS Reserved=792
OS Committed=763
OS In Use=737
Query Plan=1653 Optimizer=0
General=4468
Utilities=5709 Connection=251
Global Memory Objects: Resource=1921 Locks=3664
SQLCache=64 Replication=2
LockBytes=2 ServerGlobal=7
Xact=200
Query Memory Manager: Grants=4 Waiting=0 Maximum=217613 Available=80074
2010-03-18 23:14:55.11 spid2s
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file
[E:¥Microsoft SQL Server¥MSSQL.1¥MSSQL¥DATA¥testdblog.LDF] in database [testdb] (8). The OS file handle is 0x0000000000000914. The offset of the
latest long I/O is: 0x0000001ab20c00j
2010-03-18 07:47:57.52 サーバー
Process 2:0:0 (0x340) Worker 0x000000008007C1C0 appears to be non-yielding on Scheduler 3. Thread creation
time: 12868639737168. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 99%. Interval: 70002 ms.
70
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
今回の検証では、エラー メッセージやパフォーマンス異常に対して、管理者への通知が送信できることまでを
検証範囲とし、その後のトラブルシューテゖングやパフォーマンス チューニングは、データベース管理者の定
常業務外とし、検証対象から除外しています。なお、管理者が、メール通知を受信した場合、ベント サブス
クリプションによりベント ログを管理用 PC に転送して確認するか、または、リモート デスクトップで対
象ホストに接続し、直接確認することとします。
イベント サブスクリプション
リモート デスクトップ
リモート デスク
トップ接続を許可
WINRM を実行
管理者用 PC
SQL2000SRV
ベント データ
管理者用 PC
SQL2000SRV
MGMTClient
WINRM を実行
ベント データ
ベント ビューゕ
コレクター マシン
MGMTClient
リモート デスク
トップ接続を許可
カスタム ビュー
でログを表示
リモート デスク
トップで接続し
て表示
SQL2005SRV
SQL2005SRV
ソース マシン
④. 検証方法
エラーメッセージの検知では、次の 2 種類の方法が選択できます。
SQL Server エージェント警告を定義して、検知する
SQL Server のエラーメッセージを Windows ゕプリケーション ログに出力し、OS の機能で検知する
検証内容としては、実際にこれらの監視と通知のための設定が行えることと、デッドロック エラー ベント
(エラー番号 1205) を検知できることを確認しました。なお、SQL Server エージェント警告、および OS の
機能によるエラー メッセージの検知では、検知する必要がある特定のエラー番号に対し、事前に SQL Server
ンスタンス側で sp_altermessage ストゕド プロシージャーを使用して Windows ゕプリケーション ロ
グに出力されるように定義しておきました。また、ベント ログに書き込まれたエラーは、Windows Server
2003 R2 環境では eventtriggers コマンドを使用し、Windows Server 2008 R2 ではタスクを使用して検知
しました。
71
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
パフォーマンス異常の検知では、次の 2 種類の方法が選択できます。
SQL Server エージェントのパフォーマンス条件警告を定義して検知する
Windows Server 2003 R2 のパフォーマンス警告、および、Windows Server 2008 R2 の、データ コ
レクター セットを定義して検知する
検証内容としては、実際にこれらの監視と通知のための設定が行えることと、一定期間の CPU 使用率の高騰や
メモリプレッシャー (メモリ不足による性能低下) を検知できることを確認しました。
72
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.1.2
検証結果
①. SQL Server 2000 のイベント モニタリング
SQL Server 2000 でのエラー ベントのモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2003 R2 SP2
2000 SP4
監視設定
デッドロック検知
イベント サブスク
SQL Server
Windows
SQL Server
Windows
△ ※1
× ※2
△ ※1
× ※2
リプションによるイ
ベントの転送
○ ※3
○:検知することができ、動的なベント情報を通知できる
SQL Server 2000 のエラーは、Windows ベ
△:検知することができるが、静的なベント情報しか通知できない
ント ログのベント ID に 1 対 1 で関連つけ
×:検知または、設定できない、または、対象ベント以外が検知される
られていない場合があるため
※1 SQL Server 2000 標準のメール通知機能は、拡張 MAPI ベースの SQL Mail のみですが、今回の検証
環境では、SMTP ベースのメール サーバーを使用したため、SQL Mail は検証の対象外としました。代替
手段として、SMTP メール送信スクリプトを記述しましたが、メール本文に詳細情報を渡すことができな
かったため「△」として評価しました。
※2 SQL Server 2000 では、ベント ログに出力されるメッセージが、ベントに 1 対 1 で関連付けら
れていない場合があり、ベント特定が困難なため、Windows ベント ログのベント ID に対応した
操作を定義する eventtriggers コマンドでの警告通知は、実用的でなく「×」として評価しました。なお、
今回の検証では、デッドロック発生と sp_configure 実行が、Windows ベント ログのベント ID で
同じ「17052」として出力されることを確認しました。
※3 既定の Windows Server 2003 R2 構成では、ベント サブスクリプションによる Windows 7 (管理
用 PC) へのベント転送が動作しませんでした。この機能を使用するには、[付録 C] 「C.5 Windows
Server 2003 R2 (SP2) ベントサブスクリプション設定」の手順に従い「KB968929」を適用後に、実
73
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
行する必要があります。
SQL Server 2000 でのパフォーマンス異常のモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2003 R2 SP2
2000 SP4
監視設定
CPU 使用率の高騰
メモリプレッシャー
SQL Server
Windows
SQL Server
Windows
SQL Server
Windows
△※1
○
×※2
○
△※1
○
○:検知することができ、動的なベント情報を通知できる
CPU 使 用 率 は 、
△:検知することができるが、静的なベント情報しか通知できない
OS で監視しなけ
×:検知または、設定できない、または、対象ベント以外が検知される
ればならない
Windows Server 2003 R2 の [パフォーマンス ログと警告] の [警告] を使った仕組みにより、パフォーマ
ンス異常の検知、通知ともに問題なく動作しました。[コマンドランの引数] を設定することで通知メールの
本文に、日付、時刻、カウンター名、測定値、制限値、テキストメッセージを記載し、送信することができまし
た。なお、SQL Server エージェント によるパフォーマンスの監視は 20 秒間隔固定ですが、Windows Server
2003 R2 の [警告] の既定値は 5 秒間隔で、変更が可能です。
※1 SQL Server 2000 標準のメール通知機能は、拡張 MAPI ベースの SQL Mail のみですが、今回の検証
環境では、SMTP ベースのメール サーバーを使用したため、SQL Mail は検証の対象外としました。代替
手段として、SMTP メール送信スクリプトを記述しましたが、メール本文に詳細情報を渡すことができな
かったため「△」として評価しました。
※2 SQL Server エージェントのパフォーマンス条件警告は、SQL Server 以外のパフォーマンス オブジェク
ト カウンターをモニタリングできないことに注意してください。SQL Server エージェントでは、OS の
CPU 使用率を検知できないため、
「×」として評価しました。
メモリプレッシャーは SQL Server エージェント、
パフォーマンス ログと警告ともに検知できますが、
通知メー
ルの本文に、動的な詳細情報を記載できたのは、OS の仕組みだけでした。
74
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
②. SQL Server 2005 のイベント モニタリング
SQL Server 2005 でのエラー ベントのモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2003 R2 SP2
2005 SP3
監視設定
デッドロック検知
イベント サブスク
SQL Server
Windows
SQL Server
Windows
○
△ ※1
○
△ ※1
リプションによるイ
ベントの転送
○ ※2
○:検知することができ、動的なベント情報を通知できる
△:検知することができるが、静的なベント情報しか通知できない
×:検知または、設定できない、または、対象ベント以外が検知される
SQL Server エージェントによるデッドロックの検知と通知は正常に動作しました。
※1 eventtriggers コマンドを使った仕組みで、エラー ベントを検知できますが、起動トリガーに引数を渡
せないため、ベント検知後に起動するメール送信スクリプトに詳細情報を記載できませんでした。この
問題は、エラー メッセージごとにメール送信スクリプトを記述することで回避可能です。
※2 既定の Windows Server 2003 R2 構成では、ベント サブスクリプションによる Windows 7 (管理
用 PC) へのベント転送が動作しませんでした。この機能を使用するには、この機能を使用するには、[付
録 C] 「C.5 Windows Server 2003 R2 (SP2) ベントサブスクリプション設定」の手順に従い
「KB968929」を適用後に、実行する必要があります。
75
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
SQL Server 2005 でのパフォーマンス異常のモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2003 R2 SP2
2005 SP3
監視設定
CPU 使用率の高騰
メモリプレッシャー
SQL Server
Windows
SQL Server
Windows
SQL Server
Windows
○
○
× ※1
○
○
○
○:検知することができ、動的なベント情報を通知できる
△:検知することができるが、静的なベント情報しか通知できない
×:検知または、設定できない、または、対象ベント以外が検知される
※1 SQL Server エージェントのパフォーマンス条件警告は、SQL Server 以外のパフォーマンス オブジェク
ト カウンターをモニタリングできないことに注意してください。SQL Server エージェントでは、OS の
CPU 使用率を検知できないため、
「×」として評価しました。
③. SQL Server 2008 のイベント モニタリング
SQL Server 2008 でのエラー ベントのモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2008 R2
2008 SP1
監視設定
デッドロック検知
イベント サブスク
SQL Server
Windows
SQL Server
Windows
○
△ ※1
○
△ ※1
リプションによるイ
ベントの転送
○
○:検知することができ、動的なベント情報を通知できる
△:検知することができるが、静的なベント情報しか通知できない
×:検知または、設定できない、または、対象ベント以外が検知される
SQL Server エージェント によるデッドロックの検知と通知は正常に動作しました。
※1 Windows Server 2003 R2 では eventtriggers コマンドを使用して定義していたベント トリガーは、
Windows Server 2008 以降 Windows タスクのタスク トリガーに置き換わっています。タスクを使っ
た仕組みで、エラー ベントを検知できますが、タスク トリガーに引数を渡せないため、ベント検知
後に起動するメール送信スクリプトに詳細情報を記載できませんでした。この問題は、エラー メッセージ
ごとにメール送信スクリプトを記述することで回避可能です。
76
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
SQL Server 2008 でのパフォーマンス異常のモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2008 R2
2008 SP1
監視設定
CPU 使用率の高騰
メモリプレッシャー
SQL Server
Windows
SQL Server
Windows
SQL Server
Windows
○
△ ※1
× ※2
△ ※1
○
△ ※1
○:検知することができ、動的なベント情報を通知できる
△:検知することができるが、静的なベント情報しか通知できない
×:検知または、設定できない、または、対象ベント以外が検知される
メモリプレッシャーについては SQL Server エージェントのパフォーマンス条件警告を使用して検知と通知が
可能でした。
※1 Windows Server 2008 R2 のパフォーマンス データ コレクター セットの警告のタスクでは、メールに
詳細情報を渡すことができませんでした。
※2 SQL Server エージェントのパフォーマンス条件警告は、SQL Server 以外のパフォーマンス オブジェク
ト カウンターをモニタリングできないことに注意してください。SQL Server エージェントでは、OS の
CPU 使用率を検知できないため、
「×」として評価しました。
77
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
④. SQL Server 2008 R2 のイベント モニタリング
SQL Server 2008 R2 では、シングル サーバー構成とクラスター構成の両方で、検証しました。エラー ベ
ントのモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2008 R2
2008 R2 RC0
Windows Server
SQL Server
2008 R2
2008 R2 RC0
監視設定
デッドロック検知
イベント サブスク
リプションによるイ
SQL Server
Windows
SQL Server
Windows
○
△ ※1
○
△ ※1
○
○
△ ※1
○
△ ※1
○
ベントの転送
(クラスター構成)
○:検知することができ、動的なベント情報を通知できる
△:検知することができるが、静的なベント情報しか通知できない
×:検知または、設定できない、または、対象ベント以外が検知される
SQL Server エージェント によるデッドロックの検知と通知は正常に動作しました。
※1 Windows Server 2003 R2 では eventtriggers コマンドを使用して定義していたベント トリガーは、
Windows Server 2008 以降 Windows タスクのタスク トリガーに置き換わっています。タスクを使っ
た仕組みで、エラー ベントを検知できますが、タスク トリガーに引数を渡せないため、ベント検知
後に起動するメール送信スクリプトに詳細情報を記載できませんでした。この問題は、エラー メッセージ
ごとにメール送信スクリプトを記述することで回避可能です。
SQL Server 2008 R2 でのパフォーマンス異常のモニタリングの検証結果は次の表のとおりです。
OS
SQL Server
Windows Server
SQL Server
2008 R2
2008 R2 RC0
Windows Server
SQL Server
2008 R2
2008 R2 RC0
監視設定
CPU 使用率の高騰
メモリプレッシャー
SQL Server
Windows
SQL Server
Windows
SQL Server
Windows
○
△ ※1
× ※2
△ ※1
○
△ ※1
○
△ ※1
× ※2
△ ※1
○
△ ※1
(クラスター構成)
○:検知することができ、動的なベント情報を通知できる
△:検知することができるが、静的なベント情報しか通知できない
×:検知または、設定できない、または、対象ベント以外が検知される
メモリプレッシャーについては SQL Server エージェントのパフォーマンス条件警告を使用して検知と通知が
可能でした。
78
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
※1 Windows Server 2008 R2 のパフォーマンス データ コレクター セットの警告のタスクでは、メールに
詳細情報を渡すことができませんでした。
※2 SQL Server エージェントのパフォーマンス条件警告は、SQL Server 以外のパフォーマンス オブジェク
ト カウンターをモニタリングできないことに注意してください。SQL Server エージェントでは、OS の
CPU 使用率を検知できないため、「×」として評価しました。CPU 使用率は、OS の監視ツールを使用し
ます。
79
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.1.3
考察と結論
ここまでの検証結果に基づき、データベース管理者業務の課題に対するソリューションは、次の表の内容を提示
しました。
現在の課題
何ができればよいのか?
ソリューション
エラー発生状況を対象サーバー
エラー発生状況を一箇所
ベント サブスクリプションを設定することで、複数の
ごとに確認しなければならない
で確認したい
サーバーからコレクター マシンに、ベントを収集可能
また、エラー発生が同時多発していないのであれば、リモー
ト デスクトップで接続し、確認可能
すべてのエラーがイベント ログ
対応が必要なエラーは、す
メッセージ ID が付けられたエラーメッセージは、ベン
に出力されるわけではない
べて検知したい
ト トリガー、タスク トリガー、SQL Server エージェン
ト 警告の仕組みで検知可能
メッセージ ID がないエラー メッセージは 3rd Party
のツール導入や、別途作り込みが必要
サーバーでパフォーマンス上の
閾値を超えたパフォーマ
Windows レベルのパフォーマンス オブジェクト カウン
問題が生じた場合、自動通知され
ンス オブジェクト カウ
ターは、システム モニターを使用して検知でき、VBScript
る機能がない
ンターを通知したい
コードの呼び出しなどでベント通知が可能
SQL Server レベルのパフォーマンス オブジェクト カウ
ンターは、SQL Server エージェントのパフォーマンス条
件警告を使用して検知でき、SQL Server エージェントの
通知機能でメッセージ転送が可能
検証結果から、問題が残る検知のパターンがありましたが、OS と SQL Server 環境で選択肢が提供されてお
り、複数の検知方法を組み合わせることで、課題に対応できました。今回の検証結果は、特に異なる複数のバー
ジョンの SQL Server を運用管理する技術者にとって、有用な情報となるでしょう。
なお、パフォーマンス オブジェクト カウンターが閾値を一定時間、逸脱した場合に、障害とみなしたいと考え
ても、その設定を行うことができません。したがって、一時的なスパクによる閾値超過でも通知が送信される
ため、注意が必要です。この場合は、一定数の通知が着信した場合にパフォーマンス異常と見なすといった運用
ルールが必要となります。
今回は、CPU 使用率の高騰、メモリプレッシャー、デッドロックといったシナリオを設定して検証しました。
実際のシステムでは、すべてのメッセージや、パフォーマンス カウンターに検知と通知の仕組みを実装するこ
とは現実的ではありません。なぜなら、対応する必要のない警告による通知は、データベース管理者の仕事を増
やすだけだからです。十分にシステム テストを行い、管理上、注意が必要な項目のみモニタリングをすべきで
しょう。
80
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
より優れたモニタリング方法としては、SQL Server 固有のベント監視は、SQL Server エージェントを使用
し、CPU 使用率のような OS でしか検知できないベントは、OS 機能で監視するという組み合わせによる運
用が必要となるといえます。しかし、必ず 2 つの方法を利用しなければいけないということではありません。
OS での検知機能は、SQL Server での検知機能を包含しています。システム モニター、OS タスクと SQL
Server エージェントの機能は、警告から通知される情報の質に差が生じているだけです。
今回の検証から、これまで、データベース サーバーのモニタリングをまったく行っていない管理者が、最初に
検討すべき監視項目として、次の方法をお勧めします。
Windows ベント ログに出力される種類がエラーのベント
SQL Server でエラーの重大度レベルが 16 以上のメッセージ (重大度レベル 19 ～ 25 のエラー メッ
セージは、既定でベント ログに書き込まれます)
パフォーマンス データ コレクターで採取しているパフォーマンス オブジェクト カウンター
パフォーマンス オブジェクト カウンターに対する閾値は、オンラン ブックなどに公開されている値を
参照
81
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.2 セキュリティ監査の運用
6.2.1
検証の目的と範囲
①. 要求されているセキュリティ監査とは?
「コントソ社」が、マルチ サーバー環境でのデータベース セキュリテゖ監査で、抱えている課題は次のとおり
です。
標準化の対象
作業概要
現在の課題
何ができればよいのか?
セキュリティ管理
監査とユーザー管理
監査結果を一箇所で確認することが
監査結果を収集したい
できない
監査結果をレポート表示できない
監査結果をレポートしたい
必要な監査結果のみを抽出したい
監査結果をフゖルタリングしたい
管理者は、監査ログを一か所から参照できることを要求しています。また、監査ログを文字データとして表示す
るだけでなく、分かりやすいレポートで表示したり、参照したい対象を絞りこめることを要求しています。検証
プロジェクトでは、この課題に対し、SQL Server 2008 以降で選択できる SQL Server 監査をベースとしたソ
リューションを構築し、どこまで要件を満たせるかを検証しました。
②. SQL Server の対象バージョン
セキュリテゖ監査の検証では、次の SQL Server のバージョンを対象としました。
SQL Server 2008 R2 RC0
③. 検証範囲
SQL Server 監査のサーバー監査とデータベース監査をベースとしたソリューションとします。
82
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.2.2
検証環境
「コントソ社」の要件に対応するため、監査対象となるサーバー以外に、次のサーバーを使用しました。
監査履歴データをゕーカブするためのサーバー (SQL Server データベース エンジン サービス)
レポーテゖングのためのサーバー (SQL Server Reporting Services：SSRS)
メール サーバー (SMTP、POP3)
使用したサーバー構成のメージは次のとおりです。
監査履歴データ アーカイブ用サーバー
監
査
履
歴
の
ア
ー
カ
イ
ブ
レポート サーバー (SSRS)
(
定監
期査
ポ結
ー果
リの
ン監
グ視
監査の通知
(
監
査
履
歴
の
参
照
過
去
分
)
)
メールサーバー
監査履歴の参照(当日分)
監査履歴の抽出
・
DBA
監査対象サーバー
・
・
監査対象サーバーで、SQL Server 監査のサーバー監査とデータベース監査を構成しました。また、監査履歴デー
タのゕーカブ用サーバーとレポート サーバーに作成したオブジェクトは、次のとおりです。
オブジェクト
作成場所
参照
監査履歴を格納するアーカイ
監査履歴データ ゕーカブ
「①.監査履歴を格納するゕーカブ用テーブルの作成」
ブ用テーブル
用サーバー
監査履歴のアーカイブ ジョブ
監査履歴データ ゕーカブ
用サーバー
監査履歴の削除ジョブ
「②.SQL Server 監査の設定」
「③.監査履歴のゕーカブ」
監査履歴データ ゕーカブ
「④.監査履歴の自動的な削除」
用サーバー
監査レポート
レポートサーバー
「⑤フゔルからの監査履歴の参照(当日分)」
「⑥.ゕーカブ テーブルからの監査履歴の参照(過去分)」
監査の通知ジョブ
監査履歴データ ゕーカブ
用サーバー
83
「⑦ 監視結果の通知方法」
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.2.3
検証方法
「コントソ社」の要件に対応するため、次の手順でソリューションを作り込み、機能を検証しました。
①. 監査履歴を格納するアーカイブ用テーブルの作成
監査履歴データ ゕーカブ用サーバーに、監査フゔルに出力された監査データから、項目を絞り込み、監査
履歴を格納するゕーカブ テーブルを作成しました。テーブル定義は以下の表のとおりです。
列名
データ型
NULL 値の許可
event_time
datetime
NULL
action_id
varchar(4)
NOT NULL
Statement
nvarchar(4000)
NOT NULL
class_type
varchar(2)
NOT NULL
sequence_number
int
NOT NULL
Succeeded
bit
NOT NULL
session_id
int
NOT NULL
server_principal_name
sysname
NULL
database_principal_name
sysname
NULL
target_server_principal_name
sysname
NULL
target_database_principal_name
sysname
NULL
server_instance_name
nvarchar(120)
NULL
database_name
sysname
NULL
schema_name
sysname
NULL
object_name
sysname
NULL
84
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
②. SQL Server 監査の設定
SQL Server 監査の設定手順の概要は、次のとおりです。
オブジェクト
設定
監査オブジェクト
監査結果をフゔルに保存するように定義します。
サーバー監査の仕様
ログンの成功、失敗、オブジェクトの変更、DBCC コマンドの実行などを監査す
るように定義します。
データベース監査の仕様
DML 操作、EXECUTE ステートメントの実行などを監査するように定義します。
SQL Server 監査の設定手順の詳細は、
「[付録 A] A.2 SQL Server 監査によるセキュリテゖ監査の設定」を参
考にしてください。
監査結果をフゔルに保存するように定義することで、fn_get_audit_file テーブル値関数を使用するクエリを
記述して、監査履歴を参照できます。sys.fn_get_audit_file テーブル値関数の構文は、次のとおりです。
fn_get_audit_file ( file_pattern, {default | initial_file_name | NULL }, {default | audit_file_offset | NULL } )
関数の引数は、下表を参照してください。
引数
内容
file_pattern
監査フゔルの UNC 名を指定します。パス (ドラブ文字またはネットワーク共有) とフゔ
ル名の両方を記述する必要があります。フゔル名にはワルドカードを指定できます。ゕ
スタリスク (*) を使用することで、ロールオーバーした複数の監査フゔルからデータを参
照できます。
initial_file_name
監査フゔル内で、最初に読むフゔルのパスと名前を指定します。特定のフゔルを指定し
ない場合、「default」または、「NULL」を指定します。
audit_file_offset
この引数を使用すると initial_file_name で指定したフゔルの指定されたオフセットの直後
にあるバッフゔーの最初のレコードから読み取りを開始できます。特定のオフセットを指定し
ない場合、「default」または、「NULL」を指定します。
85
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
③. 監査履歴のアーカイブ
監査履歴をフゔルに保存することで、クエリから検索することができますが、フゔル サズが大きくなる
と、検索結果の出力に時間がかかるようになります。監査履歴を参照する場合、定期的に監査フゔルから履歴
を抽出し、テーブルにゕーカブします。ロールオーバー フゔルの機能を使用し、ゕーカブ済みの領域か
ら再使用されるようにすることで、フゔルに保持される監査データをできる限り少なくします。
フゔルに保存した監査履歴を定期的にテーブルへ抽出する操作は、標準機能として提供されていないため、今
回の検証プロジェクトでは、テーブルへ抽出する仕組みとして、以下の方法を検討し、実装しました。
監査履歴を保存するフゔルは最低一日分の監査結果が、すべて収まるように、監査オブジェクトの設定
で、フゔルの最大サズを「100 MB」、ロールオーバー フゔルの最大数を「24」と設定
監査履歴のゕーカブは、前日分のすべてのデータを対象として一日一回実施
テーブルにゕーカブした監査履歴データは 1 か月を経過した後、自動的に削除される
監査履歴データ アーカイブ用サーバー
③監査履歴の自動的な削除
②
監
査
履
歴
の
ア
ー
カ
イ
ブ
① 監査履歴の抽出
監査対象サーバー
・
86
・・
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
監査履歴を抽出するために使用したクエリは、次のとおりです。
INSERT INTO [監査履歴ゕーカブ テーブル名]
SELECT
dateadd(HOUR,9, [event_time]) AS "event_time", [action_id],
[statement],
[class_type],
[sequence_number],
監査履歴の event_time 列は、世界協定時刻（UTC）形式
[succeeded],
で保存されるため、テーブル格納時に 9 時間加えて日本時
[session_id],
間に変換します。
[server_principal_name],
[database_principal_name],
[target_server_principal_name],
[target_database_principal_name],
[server_instance_name],
監査履歴の event_time 列は UTC 形式で保存されるため、
[database_name],
9 時間引いて日本時間に変換します。なお、ここでは開始時
[schema_name],
刻を指定する必要があるため、さらに 1 日引いています。
[object_name]
FROM sys.fn_get_audit_file (‘[監査フゔルの保存先パス]\*.sqlaudit',default,default)”
WHERE [event_time] >= dateadd(dd,-1,dateadd(HOUR,-9,[現在の日付]))
AND [event_time] < dateadd(HOUR,-9,[現在の日付])
監査履歴の event_time 列は UTC 形式で保存されるため、
9 時間引いて日本時間に変換します。
87
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
④. 監査履歴の自動的な削除
データベースに蓄積した監査履歴は、保持する期間を定めて一定期間経過後に削除します。検証プロジェクトで
は、SQL Server エージェント ジョブを使用し、次のリストのクエリを 1 日 1 回スケジュール実行し、1 か
月を経過した監査履歴データを削除するようにしました。
SET ROWCOUNT 500
delete_more:
DELETE FROM [監査履歴ゕーカブ テーブル名] WHERE [event_time] < DATEADD(M,-1,GETDATE())
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
削除時のテーブル ロック発生を抑止するため、対象データを 500 件ずつ削除するように記述しています。
88
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑤. ファイルからの監査履歴の参照(当日分)
SQL Server Management Studio のオブジェクト エクスプローラーで [セキュリテゖ] - [監査] - [監査名] [監査ログの表示] を選択し、フゔル、または Windows ベント ログに出力された監査履歴を参照できま
す。
ここで、監査履歴をフゔル出力した場合は、[監査コレク
ション]を選択し、ベントログに出力した場合は、
[Windows NT] を選択します
監査ログを表示するンターフェスのツール バーから、[フゖルター] ボタンをクリックすることで、検索条
件を絞り込むことができます。
なお、表示される監査履歴の日付は、UTC 形式であることに注意してください。
89
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑥. アーカイブ テーブルからの監査履歴の参照(過去分)
テーブルにゕーカブした監査履歴は、前述の [監査ログの表示] で表示されるビューゕーからは、参照できま
せん。したがって、ゕーカブした履歴を参照する仕組みが必要となります。検証プロジェクトでは、SQL Server
Reporting Services を使用し、ゕーカブした監査履歴を参照するようにしました。作成したレポートの種類
は、次の表のとおりです。
レポートタイトル
レポートの種類
概要
監査データの絞込み検索用レポート
表
日付、action_id、成功/失敗を選択して、条件に合致す
る結果の一覧表を表示する
監査イベントごとの発生回数
グラフ
日付と時間帯を選択して、action_id ごとに発生した回数
をグラフで表示する
次の画面は、表形式の「監査データの絞込み検索用レポート」を使用し、ゕーカブされた監査履歴データを日
付と監査ベント、成功/失敗で絞込みを行った例です。
90
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
次のリストは、このレポートのデータセット定義で記述したクエリの一部です。
SELECT * ,
SUBSTRING(CONVERT(varchar, event_time, 121), 1, 4) + '/' +
SUBSTRING(CONVERT(varchar, event_time, 121), 6, 2) + '/' +
SUBSTRING(CONVERT(varchar, event_time, 121), 9, 2) AS Date,
database_name + '.' + schema_name + '.' + object_name AS object
FROM [監査履歴ゕーカブ テーブル名]
WHERE
SUBSTRING(CONVERT(varchar, event_time, 121), 1, 4) + '/' +
SUBSTRING(CONVERT(varchar, event_time, 121), 6, 2) + '/' +
SUBSTRING(CONVERT(varchar, event_time, 121), 9, 2) = @time
AND action_id IN (@id) AND succeeded IN (@success)
ORDER BY event_time ASC
表示された内容は、Reporting Services の機能を使用して、ローカルにフゔルとして保存できます。保存可
能なフゔル形式は、XML、CSV、PDF、MHTML、Excel、TIFF、Word を選択できます。
91
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
次の画面は、グラフ形式の「監査ベントごとの発生回数」レポートを使用し、一日あたりの監査ベントごと
の発生回数をグラフで表示した例です。
次のリストは、このグラフ レポートのデータセット定義で記述したクエリの一部です。
SELECT
collection_time
,formatted_value
FROM snapshots.performance_counter_values
WHERE performance_counter_instance_id IN (
SELECT
performance_counter_id
FROM [sysutility_mdw].[snapshots].[performance_counter_instances]
WHERE counter_name = 'Transactions/sec'
AND instance_name = @instance_name)
AND collection_time BETWEEN @start_time AND DATEADD(day, 1, @start_time)
AND SUBSTRING(CONVERT(varchar,[collection_time]), 12, 2) = @hour
92
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑦. 監査結果の通知方法
SQL Server の監査機能には、監査結果を通知する機能が用意されていません。監査結果を通知する方法として、
監査結果をベント ログに出力し、SQL Server エージェント 警告でベント ログを監視する方法を検討し
ましたが、ベント ログに出力されるエラー番号が「監査ゕクションの種類」に関係なく共通 (エラー番号：
33205)のため、特定の監査に対して通知を行うことができませんでした。このため、検証プロジェクトでは、
監査結果の通知方法として、監査フゔルに対して定期的にクエリを実行し、通知が必要な結果を受け取った場
合、メール通知する方法を実装しました。
監査履歴データ アーカイブ用サーバー
②DB 管理者への通知
メールサーバー
①
監
査
結
果
の
監
視
データベース管理者
0. 事前決定項目
問題発生時に即座に通知されるべき項目 (例えば、同一ゕカウントでログンの失敗が多発しているなど) を絞
り込む必要があります。検証プロジェクトでは以下の項目を事前に定義しました。
項目
内容
通知する監査アクションの種類
ログンの失敗
ポーリングする時間間隔
1分
閾値 (ポーリング時間間隔中に何回、監査対象のアクションがなされたか？)
5 回以上発生した場合
93
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
1. 監査結果の監視方法
監査フゔルに対し、次のリストのクエリを実行し、対象とする監査ゕクションが、直近の 1 分間で何回発生
しているかを確認しました。
SELECT count(action_id)
FROM fn_get_audit_file([監視フゔルのパス],default,default)
WHERE action_id = [監視項目]
監査履歴の event_time 列は UTC 形式で保存されるため、
AND dateadd(hh,9,event_time)
現在時刻に合わせるため、9 時間 追加しています。
BETWEEN dateadd(mi,-1,[現在時刻]) AND [現在時刻])
1 分前から現在までの監査履歴を取得します。
[現在時刻] は、GETDATE() 関数で取得した値をローカル変数にセットして使用します。
2.閾値超過時のデータベース管理者通知
上記クエリの実行結果が、5 以上の場合、sp_send_dbmail システム ストゕド プロシージャーを使用して、
データベース管理者にメール送信します。sp_send_dbmail システム ストゕド プロシージャーの構文は、次
のリストのとおりです。
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = [データベース メールのメールプロフゔル],
@recipients = [送信先メールゕドレス],
@subject = [件名],
@body=[本文],
@query=[実行するクエリ],
@attach_query_result_as_file=1,
@query_attachment_filename=[メールに添付されるフゔル名],
@query_result_header=0;
メールの添付フゔルに下記クエリの実行結果を添付します。
--[実行するクエリ]
select event_time, server_instance_name,action_id, statement
from fn_get_audit_file([監視フゔルのパス],default,default)
where action_id = [監視項目] and dateadd(hh,9,event_time)
between dateadd(mi,-1,[現在時刻]) and [現在時刻])
94
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.2.4
検証結果
検証プロジェクトで、作成されたセキュリテゖ監査のためのソリューションを評価したサマリーは、次のとおり
です。
現在の課題
項目
セキュリティ監査履歴の収集
各ンスタンスでの監査結果の収集
○
監査結果をデータベースに収集
○
監査情報の閲覧
○
Reporting Services (SSRS) レポート
○
セキュリティ監査履歴の閲覧/フィルタリング
操作
結果
のパラメーターによるフゖルタリング
セキュリティ監査の運用
実装の有無よるパフォーマンス変化
○
監査結果によるメール通知
△
監査データの自動的な削除
○
○:意図したとおり、標準機能で実装できた
△:作りこみをすることで実装できた
×:実装できなかった
6.2.5
考察と結論
検証結果に基づき、セキュリテゖ監査業務の課題に対するソリューションは、次の表の内容を提示しました。
現在の課題
何ができればよいのか?
ソリューション
監査結果を一箇所で確認することができ
監査結果を収集したい
管 理 端 末 の SQL Server Management
ない
Studio より複数のンスタンスに接続し、
対象サーバーを切り替えながら参照可能
監査結果をレポート表示できない
監査結果をレポートしたい
Reporting Services (SSRS) レポートを作
成することにより、Web ブラウザから監査
履歴を確認可能
必要な監査結果のみを抽出したい
監査結果をフゖルタリングし
管理ツールの [監査ログの表示] で起動され
たい
るビューゕーでは、
「ユーザー」、
「コンピュー
ター」、「テキストに含まれるメッセージ」、
「ソース」、「開始日」、「終了日」でフゖ
ルター設定が可能
また、フゔルに出力させた監査ログをテー
ブルにコピーすることによりクエリによる
フゖルター設定が可能
95
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
各インスタンスでの監査結果の収集
SQL Server 監査を使用することにより、各ンスタンス上で、設定された項目に対する監査を収集することが
できました。収集した監査結果は、フゔル、Windows セキュリテゖ ログ、ゕプリケーション ログのいずれ
かに出力できます。監査項目を設定する際の除外条件については、データベース レベルの監査では、特定の SQL
ステートメント (SELECT、DELETE、INSERT、UPDATE、EXECUTE、RECEIVE、REFERENCES) の実行に
対して、テーブル名、ビュー名、プリンシパル名などを指定して、採取する監査情報を絞り込むことができます。
監査履歴をデータベースに収集
監査履歴が出力されたフゔルに対して sys.fn_get_audit_file システム テーブル値関数を使用することで、
監査ログをテーブル形式で参照でき、それをソースとして INSERT ステートメントを実行し、テーブルに結果
セットを保存できました。監査結果を Windows セキュリテゖログやゕプリケーションログに出力していた場
合、sys.fn_get_audit_file 関数では読み込めず、テーブルに保存することはできませんでした。テーブルに挿
入するステートメントに WHERE 句を記述すれば、特定のゕプリケーションから発行された DML 文を除外す
るなどといったフゖルター処理を実行できました。
監査情報の閲覧
監査ログは、SQL Server Management Studio のオブジェクト エクスプローラーで [セキュリテゖ] – [監査]
ノードを選択し、表示する監査オブジェクトを右クリックして、表示されるメニューで [監査ログの表示] を選
択することにより閲覧できました。監査履歴の出力先は、フゔル、Windows セキュリテゖ ログ、およびゕ
プリケーション ログのいずれでも使用できます。管理端末から SQL Server Management Studio を起動し、
複数のンスタンスに接続し、各ンスタンスに対して [監査ログの表示] を実行することで複数のンスタン
スの監査ログを一台の端末から閲覧することができました。また、テーブルに保存した監査結果を閲覧する場合、
クエリを記述できるため、action_id の種類を指定したり、発生回数を表示するなどといった柔軟な検索操作を
行えました。
Reporting Services (SSRS) によるフィルタリング
フゔルに記録した監査結果をテーブルに抽出し、Reporting Services のレポートで表示することで、収集し
た監査データを容易にリスト表示することができました。レポートに、パラメーターを設定し、日付や監査項目
で絞り込むことや、監査項目ごとの発生回数をグラフ化するなどのゕレンジを加えることも可能でした。レポー
96
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
トに出力された結果は、XML、CSV、PDF、MHTML、Excel、TIFF、Word の形式でフゔル出力することが
できました。
監査結果によるメール通知
取得した監査結果について問題がある場合に、それを通知する仕組みは SQL Server 監査には備わっていませ
ん。監査結果を保存してあるテーブルに対して、条件と通知方法を記述したクエリをポーリングすることで、監
査内容の自動通知が可能でした。また、監査結果をセキュリテゖ ログやゕプリケーション ログに設定していた
場合には、取得した監査項目全てがベント ID 33205 として記録されるため、条件として指定することができ
ませんでした。
監査データの自動的な削除
監査履歴をフゔル出力する場合、既定の設定では、フゔル サズが無制限に増加し続けます。フゔル サ
ズの上限値を設定することで、サズが上限に到達した場合、古い情報から自動的に削除させることができま
す。検証プロジェクトでは、フゔルからテーブルに出力された監査履歴に対し、指定条件 (今回の例では、一
定期間を過ぎた監査履歴レコード) を満たしたデータのみを削除するジョブを作成し、定期的にスケジュール実
行することで、監査履歴の自動削除を実装しました。
考察と結論のまとめ
今回の検証では、想定したシナリオに対して、監査が行えないという問題は発生しませんでした。また、今回の
セキュリテゖ監査の標準機能を拡張する作り込みは、データベース管理者の一助になるでしょう。監査結果を記
録し、保存することだけが必要な場合、SQL Server 監査を設定するだけでよいでしょう。しかし、取得した結
果を検索したり、監査結果から、何らかのゕクションを起こすのであれば、監査履歴をフゔルに出力した上で、
テーブル値関数を使用し、フゔルからテーブルへのコピーが必要となります。監査項目は多岐に渡り設定でき
ますが、対象となるゕクションの中には、収集されるデータが膨大になる場合があります。このようなゕクショ
ンを監査する場合、サーバー負荷や出力フゔル容量に対する考慮が必要です。取得した監査履歴に対する通知
の仕組みは SQL Server 監査の標準機能として搭載されないため、作り込みが必要となります。作り込みの方
法によっては、サーバーへの負荷を考慮する必要があります。監査の仕組みを実装する際には、記録すべき監査
項目をよく検討し、サーバー負荷やフゔル容量に対する十分な検証を行った上で運用するべきでしょう。
97
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3 パフォーマンス データの収集
6.3.1
検証の目的と範囲
①. 要求されているパフォーマンス データの収集とは?
「コントソ社」がマルチ サーバー環境でのデータベース サーバーのパフォーマンス データの収集の業務で抱
えている課題は次のとおりです。
標準化の対象
作業概要
現在の課題
何ができればよいのか?
パフォーマンス
パフォーマンス
障害が発生したときに必要なデータが採
データを収集したい
データ収集
データ収集とレポー
取できていない
テゖング
障害が発生したときに必要なデータがそ
データを蓄積したい
ろっていない、失われている
管理対象のンスタンスが増える度に、
管理対象の増加を吸収したい
パフォーマンスデータの収集や蓄積処理
を追加している
詳細な分析を行うためには作りこみが必
多面的な切り口で分析したい
要となる
過去から現在の状態はわかるが、将来ど
将来の傾向などを予測したい
うなるかがわからないため、予防策が検
討しにくい
検証プロジェクトでは、上記の課題に対し、SQL Server 2008 R2 からの新機能である SQL Server ユーテゖ
リテゖ コントロール ポント (UCP) と SQL Server 2008 以降のバージョンで選択できるパフォーマンス
データ コレクターをベースとしたソリューションを構築し、どこまで要件を満たせるかを検証しました。
②. SQL Server の対象バージョン
セキュリテゖ監査の検証では、次の SQL Server のバージョンを対象としました。
SQL Server 2008 R2 RC0
③. 検証範囲
SQL Server ユーテゖリテゖ コントロール ポント (UCP) とパフォーマンス データ コレクターをベース
としたソリューションとします。
98
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.2
UCP の検証環境
UCP の検証で使用したサーバー構成は、次のとおりです。
ユーティリティ コントロール
ポイント (UCP) サーバー
インスタンス情報の収集
収集データの蓄積
管理用PC
インスタンス情報の参照
DB 参照
データベース管理者
msdb
システム データベース
sysutility_mdw
データベース
SQL Server 2008 R2
マネージ インスタンス
監視対象のインスタンス
監視対象のンスタンスの情報は、各ンスタンス側のジョブにより、ユーテゖリテゖ コントロール ポント
(UCP) サーバー上の sysutility_mdw データベースに収集されます。また、UCP サーバーの msdb システム
データベースには、UCP の構成情報やパフォーマンス情報に基づくリソース正常性のフラグ値などが格納され
ます。
99
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.3
UCP の検証方法
以降で、UCP が「コントソ社」の要件にどこまで対応できるかを検証した内容をまとめています。
①. 収集されたデータの参照
UCP で収集されたデータは、SQL Server Management Studio から起動したユーテゖリテゖ エクスプ
ローラーで表示されるビューから確認できます。トップ ページで、各ンスタンスの正常性ポリシーの状
態を一目で把握できます。ページの左側には、マネージ ンスタンス、右側はデータ層ゕプリケーション
の使用率のサマリーが表示されています。
マネージンスタンス・データ層ゕプリケーションの使用状況
閾値を超過したンスタンス／デ
ータ層ゕプリケーションの数
閾値に満たなかったンスタンス／デー
タ層ゕプリケーションの数
全てのンスタンスの
記憶域使用履歴（合算値）
データ層ゕプリケーション、およびマネージ ンスタンスのページ (これらのページ構成は同じです。)
では、登録されたデータ層ゕプリケーション、またはマネージ ンスタンスの正常性ポリシー結果をペー
ジ上部で確認できます。また、ページ下部で、より詳細な情報を閲覧することができます。
正常性ポリシーの表示
：正常に動作
：閾値より低い
：閾値より高い
：ンスタンス未検知
各ンスタンスの詳細情報
100
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
ページ上部で選択された、正常性が表示されたマネージ ンスタンスに対して、ページ下部のタブを選択
しながら、各リソース状況の詳細を閲覧することができます。
① CPU使用率
② 記憶域使用率
③ ポリシーの詳細
④ プロパティの詳細
[CPU 使用率] ページでは、指定した SQL Server ンスタンス、およびデータ層ゕプリケーションのン
スタンスの CPU 使用率の推移を線グラフで閲覧できます。推移の線グラフは、
「1 日」、
「1 週間」
、
「1 か
月」
、
「1 年」の 4 種類の時間間隔で切り替えることが可能です。
内部的には、[間隔]でオプションを選択した場合、sysutility_ucp_core.cpu_utilization_internal テーブ
ルの [aggregation_type] 列にセットされた値を使用して、表示するレコードが選択されます。[間隔]オ
プションと参照される[aggregation_type] 列の値の関係は、次の表のとおりです。
[間隔] オプション
aggregation_type 列の値
1 日
0
1 週間
1
1 か月
2
1年
2
101
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
例えば、[間隔] で [1 週間] オプションを選択した場合、[aggregation_type] 列に「1」がセットされ
たレコードが使用されグラフ化されます。なお、sysutility_ucp_core.cpu_utilization_internal テーブル
の [aggregation_type] 列についての詳細は、
「5.2.4 UCP サーバーにおけるデゖスク領域のサジング」
を参照してください。
[記憶域使用率] ページでは、[フゔルのグループ化] オプションを使用して、データベース フゔル、
およびログ フゔルのフゔルごとにグループ化された領域の使用率と記憶域ボリュームごとにグルー
プ化された領域の使用率を表示できます。[CPU 使用率] ページと同様、領域使用の推移を示す面グラフは、
「1 日」
、
「1 週間」
、
「1 か月」
、
「1 年」の 4 種類の時間間隔で切り替えることが可能です。
データベースごとにグループ化
記憶域ボリュームごとにグループ化
内部的には、sysutility_ucp_core.space_utilization_internal テーブルが参照されます。
[プロパテゖの詳細] ページを選択すると、ンスタンスの構成情報を参照できます。
102
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
②. 監視ポリシーの定義
ンスタンスとデータ層ゕプリケーションのパフォーマンス、および構成の状態は、定義されている監視
ポリシーに基づき評価され、ゕコン表示されます。監視ポリシーは、すべてのマネージ ンスタンスを
対象とする「グローバル ポリシー」と各ンスタンス レベルの個別のポリシーを設定することができま
す。グローバル ポリシーは、ユーテゖリテゖ エクスプローラーで [ユーテゖリテゖ管理] ノードを選択
して表示されるビューで [ポリシー] タブを選択して設定します。
個別のポリシーは、[マネージ ンスタンス] ノードを選択することで表示されるマネージ ンスタンス
のリストから、対象のンスタンスを選択し、下に表示されるビューから [ポリシーの詳細] タブを選択
して設定します。
103
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
このページで、マネージ ンスタンスごとに CPU とストレージの使用率に対して閾値を設定できます。
既定では、標準的な値が設定されたグローバル ポリシーの値がセットされますが、このページの [グロー
バル ポリシーより優先する] オプションを使用することで、グローバル ポリシーの値を上書きすること
ができます。また、[データ層ゕプリケーション] ノードを選択すれば、DAC に対するポリシー設定も行
えます。
104
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
[ユーテゖリテゖ管理] ノードでは、グローバル ポリシーを設定する [ポリシー] タブ以外に、セキュリ
テゖ設定のための [セキュリテゖ] タブと収集データの保存期間を設定するための [データウェゕハウス]
タブがあります。
[セキュリテゖ] タブでは、UCP ダッシュボードとビューポントへの読み取り権限の設定が行えます。
[データ ウェゕハウス] タブでは、[UMDW のデータ保有期間を選択してください] で、収集データの保
存期間を「1 か月」、
「3 か月」
、
「6 か月」、
「1 年」
、
「2 年」の単位から選択できますが、ここでの設定で
内部的には、31 日間、92 日間、183 日間、732 日間として設定されます。この詳細は、「⑦.収集デー
タの自動的な削除」で説明します。
105
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
CPU 使用率のノイズの発生と対処方法
ンスタンスとコンピューターの CPU 使用率は、ジョブにより、データ収集とゕップロードが実行され
ると、使用率がグラフ表示されますが、使用率の評価結果を示すゕコンの表示は、値が閾値を越えてい
た場合でも、ゕコン表示で使用率が高い状態を示すとは限りません。
ゕコンによる使用率が高い、または低いといった表示は一定時間内のポリシー評価において、閾値を逸
脱した回数の割合で決定されます。この設定は、[ユーテゖリテゖ管理] ノードの [ポリシー] タブ [揮発
性リソースのポリシー評価] で設定します。
ポリシー評価は 15 分に 1 度実行されますが、これは変更することができません。評価期間は、1 時間、
6 時間、12 時間、1 日、1 週間から設定します。ポリシー違反の比率を 0 から 100% の間で 10% 刻
みで設定します。以下に設定例を示します。
例 1 評価期間 1 時間 違反割合 20% で設定した場合
ポリシー評価の間隔
判定
1 時間に 4 回
そのうち 1 回閾値を越えた場合は 25% の割合で違反したことになるため 1 時間以内で 1
回のポリシー違反で使用率ゕコン「高」が表示される。
例 2 評価期間 12 時間 違反割合 50% で設定した場合
ポリシー評価の間隔
判定
12 時間に 48 回
そのうち 24 回閾値を越えた場合は 50% の割合で違反したことになるため 12 時間以内で
24 回のポリシー違反で使用率ゕコン「高」が表示される。
106
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
トップ ページのリンクから、評価項目ごとにポリシー違反しているンスタンスをまとめて表示できます。
クリックすると画面遷移し、閾値に違反したンス
タンスのみを表示できます
フゖルターを使用すると指定した条件で情報を絞り込むことができます。[フゖルター設定] は、ユーテゖ
リテゖ エクスプローラーのツリーで [マネージ ンスタンス] ノードを右クリックし、表示されるメ
ニューから [フゖルター] - [フゖルターの設定] を選択します。
フゖルターの条件を設定すると、マネージ ンスタンスのページには、条件を満たすンスタンスのみが
表示されるようになります。
107
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
③. 収集されたデータのレポート出力
UCP の標準機能として、レポート結果を Excel フゔルなどに出力するンターフェスは、提供されていま
せんが、Reporting Services でレポートを定義することで、UCP 情報から、マネージ ンスタンスのリソー
ス正常性状態を抽出して表示することができました。
検証で使用したレポート定義フゔルの入手先 URL：
http://blogs.msdn.com/hutch/archive/2010/02/21/sql-server-2008-r2-accessing-utility-control-point
-data.aspx
④. 将来の傾向予測
UCP の標準機能として、将来の傾向を表示するンターフェスは、提供されていませんが、UCP で収集した
データ履歴の推移の傾向を確認することで、将来の傾向予測をある程度は判断できます。
このビューからは、
記憶域使用率が上昇し続けているという
傾向が確認できる
108
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑤. データ収集の実行履歴の確認
データ収集の実行履歴は、SQL Server Management Studio オブジェクト エクスプローラーから [SQL
Server エージェント] の下にある [ジョブ] ノードを展開し、UCP 関連のジョブを選択し、右クリックして、
表示されるメニューから [履歴の表示] を選択することでビューゕーから確認できます。UCP 関連ジョブには、
次の 6 種類があります。
collection_set_4_noncached_collect_and_upload
sysutility_get_cache_tables_data_into_aggregate_tables_daily
sysutility_get_cache_tables_data_into_aggregate_tables_hourly
sysutility_get_views_data_into_cache_tables
sysutility_mi_collect_and_upload
sysutility_mi_collect_performance
次の画面は、sysutility_get_views_data_into_cache_tables ジョブの履歴を表示しています。
SQL Server エージェント ジョブにより 15 分間隔で、データ収集されていることが分かります。
109
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑥. データ収集時のエラー通知
データ収集時にエラーが発生した場合、それを自動で管理者に通知するためには、UCP が使用している SQL
Server エージェント ジョブに対して、ジョブ失敗時のメール通知を設定する必要があります。本プロジェク
トで検証されたメール通知のメッセージは、次のとおりです。
なお、SQL Server エージェント ジョブからメール通知する場合は、次の 3 つの作業を行う必要があります。
1.データベース メールのアカウント設定
実装手順の詳細は、[付録 B] の「③.データベース メールの設定」を確認してください。
データベース メール ゕカウント設定
2.オペレーターの設定
実装手順の詳細は、[付録 B] の「②.SQL Server エージェント オペレーターの作成」を確認してください。
オペレーターの設定
110
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
3.ジョブのメール通知設定
SQL Server Management Studio オブジェクト エクスプローラーから [SQL Server エージェント] の下に
ある [ジョブ] ノードを展開し、UCP 関連のジョブを選択し、右クリックして表示されるメニューから [プロ
パテゖ] を選択して表示される [ジョブのプロパテゖ] で、[電子メール] チェックボックスを選択し、通知す
るオペレーターを指定します。
ジョブのメール通知設定
111
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑦. 収集データの自動的な削除
UCP
に
よ
り
、
収
集
さ
れ
た
デ
ー
タ
の
自
動
的
な
削
除
は
、
sysutility_get_cache_tables_data_into_aggregate_tables_daily ジョブが実行されるタミングで行われ
ます。このジョブの実行時刻は、既定で 0 時 1 分に設定されています。この設定値は、ジョブのプロパテゖ
で変更が可能です。
自動的な削除の対象と方法は、次のとおりです。
収集間隔
[aggregation_type] 列
削除の対象
15 分ごとのデータ
0
ジョブ実行時間から 2 日以上経過したデータを削除する
1 時間ごとのデータ
1
ジョブ実行時間から 31 日以上経過したデータを削除する
1 日ごとのデータ
2
ジョブ実行時間から 366 日以上経過したデータを削除する
なお、sysutility_mdw データベースのテーブルに定義される [aggregation_type] 列についての詳細は、
「5.2.4 UCP サーバーにおけるデゖスク領域のサジング」を参照してください。
データの保有期間は変更できます。1 日ごとのデータの保有期間は、ユーテゖリテゖ エクスプローラーの [ユー
テゖリテゖ管理] より、[データウェゕハウス] タブを選択して表示されるページで変更できます。
設定できる保有期間は 1 か月、3 か月、6 か月、1年、2年
112
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
保有期間を変更することで、内部的には、msdb..sysutility_ucp_configuration_internal テーブルの name 列
に「MdwRetentionLengthInDaysForMinutesHistory」がセットされた行の [current_value] 列の値が指定さ
れた保有期間を示す日数に変更されます。
1 年に設定した場合
6 か月に設定した場合
⑧. バックアップと復元操作
UCP の運用を開始する前に、マネージ ンスタンスを登録し、ポリシーを構成した時点で msdb システム
データベースと sysutility_mdw データベースのバックゕップを取得してください。その後、UCP の構成を変
更した場合、再度 msdb のバックゕップを取得してください。バックゕップ手順を実施する前に、 SQL Server
エージェント サービスを停止しておく必要があります。他は、通常のデータベースのバックゕップ、復元操作
と変わりません。バックゕップからの復元を行うと、一時的に正常性状態に不整合が発生しますが、正常性状態
の更新後、正常な状態になります。
復元直後
正常性状態更新後
113
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑨. 正常性ポリシーの閾値逸脱によるメール通知
マネージ ンスタンスが、UCP で設定された正常性ポリシーの閾値を逸脱した場合、それを自動で管理者に通
知する機能は、正常性状態の結果を格納しているテーブルに対して、条件と通知方法を記述したクエリを 15 分
間隔でポーリングさせることで、実装することができます。正常性状態の結果は、以下のテーブルに格納されて
います。
監視対象
テーブル名
データ層アプリケーション
[msdb].[dbo].[sysutility_ucp_dac_health]
マネージ インスタンス
[msdb].[dbo].[sysutility_ucp_mi_health]
マネージ ンスタンスの状態に対するテーブルに格納された値は、次のとおりです。
マネージンスタンスの状態
マネージンスタンスの状態値が格納されるテーブル（『sysutility_ucp_mi_health』）
[状態とテーブルの値]
：1
：2
：3
：テーブルに出力されない
次の Transact-SQL スクリプトは、正常性ポリシーの閾値より高くなっている場合、メール通知を行うクエリ
の例です。
IF EXISTS
(SELECT
[mi_name] AS [ンスタンス名],
[mi_processor_health_state] AS [ンスタンス CPU],
[computer_processor_health_state] AS [コンピューターCPU],
[file_space_health_state] AS [フゔル領域],
[volume_space_health_state] AS [ボリューム領域],
[processing_time] as [収集実行時刻]
FROM [msdb].[dbo].[sysutility_ucp_mi_health]
WHERE [mi_processor_health_state] = 3 OR
[computer_processor_health_state] = 3 OR
[file_space_health_state] = 3 OR
[volume_space_health_state] = 3
114
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
)
THEN EXECUTE msdb.dbo.sp_send_dbmail [sp_send_dbmail のパラメーター]
メールによる通知
115
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.4
UCP の検証結果
検証プロジェクトで、テストされた UCP によるパフォーマンス データ収集のためのソリューションを評価し
たサマリーは、次のとおりです。
カテゴリ
項目
収集/蓄積設定
パフォーマンスデータ収集
○
パフォーマンスデータ蓄積
○
管理対象ンスタンスの追加
○
パフォーマンスデータ分析
○
将来の傾向予測
×
UCP の有無によるパフォーマンス変化
○
実行ログの確認
○
情報収集時のエラー通知
○
リソース正常性ポリシーの評価結果によるメール通知
△
収集データの自動的な削除
○
UCP のバックゕップと復元
○
分析
運用
結果
○:意図したとおり、標準機能で実装できた
△:作りこみをすることで実装できた
×:実装できなかった
116
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.5
UCP の考察と結論
検証結果に基づき、UCP をベースとしたデータ収集に対するソリューションは、次の表の内容を提示しました。
現在の課題
何ができればよいのか?
ソリューション
障害が発生したときに必要なデータが
データを収集したい
管理対象となる複数のンスタンス (マネージ
採取できていない
ンスタンス) やデータベース (DAC) からシ
ステムにとって、最もクリテゖカルなリソースと
なる CPU と記憶域の使用率を収集可能
障害が発生したときに必要なデータが
データを蓄積したい
そろっていない、失われている
パフォーマンス データは、SQL Server エー
ジェント ジョブにより、15 分間隔で収集され、
コントロール ポントとして定義されたンス
タ ン ス の 管 理 デ ー タ ウ ェ ゕ ハ ウ ス
(sysutility_mdw) に格納される
管理対象のインスタンスが増える度
管理対象の増加に対する設定
ユーテゖリテゖ エクスプローラーからウゖザー
に、パフォーマンス データ収集の設定
の負荷を削減したい
ドを起動して、管理対象となるンスタンスの容
や蓄積処理を追加している
詳細な分析を行うためには作り込みが
易な登録が可能
多面的な切り口で分析したい
必要となる
CPU と記憶域の使用率が各サーバー リソース
の許容範囲にあるかどうかを状態情報として表
示可能
また、収集されたデータは、時系列を縦軸とする
グラフ表示が可能
Reporting Services レポートを定義すること
で、カスタムな表やグラフ形式のレポートを作成
可能
過去から現在の状態はわかるが、将来
将来の傾向などを予測したい
将来の傾向予測を表示する機能ないが、UCP で
どうなるかがわからないため、予防策
収集されたデータの表示期間を長い期間に変更
が検討しにくい
し、データ履歴の推移の傾向を確認することで、
そこから、将来の傾向を推測可能
データの収集
管理対象ンスタンスである「マネージ ンスタンス」
、および、配置済みのデータ層ゕプリケーションで
ある「DAC」、各々の一部のパフォーマンス情報を、UCP のコントロール ポントに収集することができ
ました。収集できる情報は、
「コンピューターの CPU 使用率」
「SQL Server ンスタンスの CPU 使用率」
「データベース フゔルの使用率」
「記憶域ボリュームの使用率」の 4 種類でした。SQL Sever エージェ
ント ジョブにより、これらの情報が、15 分間隔でコントロール ポントに収集されました。
117
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
データの蓄積
UCP により蓄積されるデータは全て、コントロール ポントとなるンスタンス上の sysutility_mdw
データベースに蓄積されました。蓄積するンスタンスやデータベース名は変更することはできませんでし
た。
管理対象インスタンスの追加
管理対象のンスタンスはユーテゖリテゖ エクスプローラーにより、容易に追加できました。管理対象
ンスタンスとして追加可能なバージョンは、検証の実施時点では、SQL Server 2008 R2 のみでした。同
一ンスタンスを、管理対象として複数のコントロール ポントに登録することはできませんでした。
パフォーマンス データ分析
ユーテゖリテゖ エクスプローラーを使用することにより複数のンスタンスの状態を表示できました。ま
た、ンスタンス単位、リソース単位でも表示させることができました。表示期間は、
「1 日」
、
「1 週間」、
「1 か月」
、
「1 年」のいずれかを指定することができました。ユーテゖリテゖ エクスプローラー上で Excel
や PDF などへのレポート出力する機能は備わっていませんでした。Reporting Services を使用すること
により、UCP 情報の一部であるマネージ ンスタンスのヘルス状態サマリーを表示するレポートを作成す
ることができました。ヘルス状態サマリーを表示するレポートのサンプルは、MSDN のブログ サトから
ダウンロードすることができます。
実行ログの確認
情報収集の日時やステータスのログは、管理対象ンスタンスの SQL Server エージェントに登録された、
以下のジョブの「ジョブの履歴の表示」を選択することで確認することができました。
collection_set_4_noncached_collect_and_upload
sysutility_get_cache_tables_data_info_aggregate_tables_daily
sysutility_get_cache_tables_data_info_aggregate_tables_hourly
sysutility_get_views_data_into_cache_tables
sysutility_mi_collect_and_upload
118
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
sysutility_mi_collect_performance
情報収集時のエラー通知
情報収集時にエラーが発生した際に自動通知する仕組みは、UCP で使用している上記 SQL Server エー
ジェント ジョブに対して、ジョブ失敗時にメール通知を行うように設定することで実装できました。
リソース正常性ポリシーの評価結果によるメール通知
リソース正常性ポリシーの評価結果に問題がある場合、それをメール通知する仕組みは UCP には備わって
いません。リソース正常性ポリシーの評価結果が格納されている msdb システム データベースのテーブ
ルに対して、条件と通知方法を記述したクエリをポーリングすることで、問題発生の自動通知が可能でした。
本部分が理由で検証結果のサマリーでは、
「△」と評価しました。
将来の傾向予測
将来傾向を自動的に表示させる機能ないが、UCP で収集したデータの表示期間を変更して、データ履歴の
推移の傾向を確認することで、将来の傾向予測をある程度は判断できます。
収集データの自動的な削除
UCP が収集したデータは、テーブル上に「15 分」、「1 時間」、「1 日」の間隔で記録されます。15 分間
隔の情報は 2 日ごとに削除され、1 時間間隔の情報は 31 日ごとに削除されます。この時間は、管理ツー
ルを使用して、変更できませんでした。1 日ごとのサマリー情報は、管理ツールを使用して、保有期間を「1
か月」、「3 か月」、「6 か月」、「1 年」、「2 年」のいずれかに設定することができました。既定の保有期間
は、1 年間（366 日）の設定になっています。
保有期間外となったデータは sysutility_get_cache_tables_data_info_aggregate_tables_daily ジョブ
実行時に削除されました。このジョブは既定では、1 日 1 回 実行されます。
UCP のバックアップと復元
UCP を 運 用 す る 際 に は 、 構 成 し た 時 点 で 各 ン ス タ ン ス の msdb シ ス テ ム デ ー タ ベ ー ス と
119
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
sysutility_mdw データベースのバックゕップを取得し、UCP の構成値を変更した際には再度 msdb シス
テム データベースのバックゕップを取得してください。sysutility_mdw データベースのバックゕップの
タミングは、データ損失リスクや記憶域領域などの考慮により異なります。sysutility_mdw をバック
ゕップし、sysutility_mdw にのみ不整合が発生した場合、sysutility_mdw のみを復元してください。同
一ンスタンスへ sysutility_mdw を復元することができます。復元時に一時的にヘルスチェックの結果
に不整合が発生しますが、次回、ヘルスチェック動作後、正常に反映されました。
考察と結論のまとめ
UCP は、「マルチ サーバー管理」を意識した実装になっているため、管理対象の複数のンスタンスのヘ
ルス状態を容易に確認する目的や、長期に蓄積したデータの推移を確認する目的のためには、 データベー
ス管理者の行うタスクの一助となることを確認しました。UCP を使用することで、監視対象として登録さ
れた複数のンスタンスの 「コンピューターの CPU 使用率」
「SQL Server ンスタンスの CPU 使用率」
「データベース フゔルの使用率」
「記憶域ボリュームの使用率」といった状態情報を 1 か所で監視できる
ことを確認しました。「リソース正常性ポリシー」で閾値を設定することで、監視対象の状態と比較されて
「高い」
、
「適正」
、
「低い」といった評価が表示されます。
監視対象がリソース正常性ポリシーの閾値を超えた場合に、管理者への自動通知を行うには、作り込みが必
要となります。作り込む内容によっては、サーバーに対する負荷を考慮する必要があります。
また、パフォーマンスの問題が発生した場合、ボトルネックの兆候は確認できますが、より詳細な分析では、
パフォーマンス データ コレクターにより収集されるデータから作成される標準レポート (「サーバーの利
用状況の履歴」
「デゖスク使用量の概要」
「クエリ統計の履歴」) の使用が有効であることを確認しました。
120
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.6
パフォーマンス データ コレクターの機能と検証環境
通常のパフォーマンス データ コレクターの構成では、ウゖザードで指定されたデータベース (管理データウェ
ゕハウスと呼ばれています。) にパフォーマンス データが収集されます。データベース名は、任意で設定でき
ます。
パフォーマンス
監視対象DBサーバ #1
データ コレクター レポートの
参照
DB参照
パフォーマンス情報の記録
パフォーマンス
監視対象DBサーバ #2
DB参照
データベース管理者
任意のデータベース名が
指定可能
また、複数のンスタンスのパフォーマンス データを単一のデータベースに集約するように構成することもで
きます。パフォーマンス データ コレクターと同時に UCP の管理対象データベースに登録した場合、パフォー
マンス データ コレクターのデータベースは、UCP の sysutility_mdw データベースに統合されます。
「sysutility_mdw」というデータベース名は、変更することができないので注意してください。検証プロジェ
クトで使用したパフォーマンス データ コレクターのサーバー構成でも、UCP サーバーを併用しました。検証
で使用したサーバー構成は、次のとおりです。
パフォーマンス情報の記録
パフォーマンス
監視対象DBサーバ #1
ユーティリティ
コントロール ポイント
(UCP)
DB参照
sysutility_mdw
データベース
データ コレクタ レポートの
参照
パフォーマンス
監視対象DBサーバ #2
DB参照
データベース管理者
121
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.7
パフォーマンス データ コレクターの検証方法
以降は、パフォーマンス データ コレクターが「コントソ社」の要件にどこまで対応できるかを検証した内容を
まとめています。
①. 収集されたデータの参照
パフォーマンス データ コレクターで収集されたデータは SQL Server Management Studio のオブジェクト
エクスプローラーのツリーから [管理] - [データ コレクション] を右クリックして、[レポート] メニューから
起動する管理レポートにより参照することができます。
既定で提供される閲覧可能なレポートは次の 3 種類です。
デゖスク使用量の概要
サーバーの利用状況の履歴
クエリ統計の履歴
122
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
デゖスク使用量の概要では、ンスタンスに作成されているすべてのデータベースのデータ フゔルとログ
フゔルの使用状況に関する情報をグラフ表示で確認することができます。
サーバーの利用状況の履歴では、サーバーおよび SQL Server ンスタンスから収集したリソース使用状況の
統計とパフォーマンス データを確認することができます。
123
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
クエリ統計の履歴レポートでは、クエリ統計情報、個々のクエリ テキスト、クエリ プランなどの情報を確認す
ることができます。
各レポートに表示されるグラフや文字をクリックすることで、より詳細なページにドリル ダウンすることがで
きます。
124
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
また、SQL Server Management Studio で表示するンスタンスを切り替えて表示することで、1 つのツー
ル上から複数のンスタンスを監視することが可能です。
②. 将来の傾向予測
SQL Server Management Studio から表示される各レポートのトップ ページにあるカレンダー コントロー
ルを使用して、レポート表示する時間の選択（開始日、開始時刻、期間）ができます。
125
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
ここで設定できる期間は、15 分、1 時間、4 時間、12 時間、24 時間です。
パフォーマンス データ コレクターでは、UCP で収集されるデータに比べると、多くの種類のパフォーマンス
情報を参照できますが、収集したパフォーマンス データから、各項目の推移を表示できる時間間隔は、最長で
24 時間となります。それ以上の長期間に渡るパフォーマンスの推移は、1 ページで確認できないため、中長期
の傾向を把握するには不向きともいえます。
126
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
③. カスタム データ コレクション セットの実装と閲覧
標準提供される 3 種類のシステム データ コレクション セットで収集されないパフォーマンス項目について
は、独自にカスタム データ コレクション セットを作成することで対応できます。カスタム データ コレクショ
ン セットを定義することで、パフォーマンス カウンターや T-SQL クエリからの追加のパフォーマンス デー
タを収集できます。カスタム データ コレクション セットの作成手順は、次のとおりです。
1.
コレクション セットの定義と作成
2.
コレクション ゕテムの定義と作成
3.
コレクション セットとコレクション ゕテムが作成されたことを確認
1. コレクション セットの定義と作成
コレクション セットの定義と作成は、sp_syscollector_create_collection_set システム ストゕド プロシー
ジャーを使用します。sp_syscollector_create_collection_set システム ストゕド プロシージャーの主要なパ
ラメーターは次のとおりです。
パラメーター名
説明
@name
コレクション セットの名前を指定します。
@collection_mode
データの収集方法と格納方法を指定します。キャッシュ モードの場合、0 をセッ
トします。キャッシュなしモードの場合、1 をセットします。
@collection_set_id
コレクション セットの一意なローカル識別子を指定します。
@days_until_expiration
収集したデータを管理データ ウェゕハウスに保存する日数を指定します。
@description
コレクション セットの説明を記述します。
@schedule_uid
デ ー タ を ゕ ッ プ ロ ー ド す る た め の ス ケ ジ ュ ー ル を GUID で 指 定 し ま す 。
schedule_uid のデータ型は uniqueidentifier 型です。
@schedule_uid は、sysschedules_localserver_view システム ビューの name 列を検索し、schedule_uid
列を取得します。sysschedules_localserver_view の name 列の値は、次のとおりです。
スケジュール名
説明
CollectorSchedule_Every_5min
5 分間隔
CollectorSchedule_Every_10min
10 分間
隔
CollectorSchedule_Every_15min
15 分間
127
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
隔
CollectorSchedule_Every_30min
30 分間
隔
CollectorSchedule_Every_60min
60 分 間
隔
CollectorSchedule_Every_6ｈ
6 時間間
隔
2. コレクション アイテムの定義と作成
コレクション ゕテムの定義と作成は、sp_syscollector_create_collection_item ストゕド プロシージャー
を使用します。sp_syscollector_create_collection_item システム ストゕド プロシージャーの主要なパラ
メーターは次のとおりです。
パラメーター名
説明
＠name
コレクション ゕテムの名前を指定します。
@collection_set_id
コレクション セットの一意なローカル識別子を指定します。
@collector_type_uid
コレクション ゕテムに使用するコレクター型を識別する GUID を指定します。
@parameters
コレクター型の入力パラメーターを指定します。
@frequency
データを収集する頻度を秒単位で指定します。既定値および最小値は 5 秒です。
@collection_item_id
コレクション セット項目を識別する一意な識別子を指定します。
@collector_type_uid にセットできる GUID については、次の表を参照してください。
コレクター型名
GUID
Generic T-SQL Query Collector Type
302E93D1-3424-4BE7-AA8E-84813ECF2419
Generic SQL Trace Collector Type
0E218CF8-ECB5-417B-B533-D851C0251271
Query Activity Collector Type
14AF3C12-38E6-4155-BD29-F33E7966BA23
Performance Counters Collector Type
294605DD-21DE-40B2-B20F-F3E170EA1EC3
パフォーマンス カウンターからのデータ収集を行うコレクション セットをカスタムで作成する場合は、
@collector_type_uid に「294605DD-21DE-40B2-B20F-F3E170EA1EC3」をセットします。データは、
snapshots.performance_counter_instances テーブルに取集されることになります。また、Transact-SQL
ステートメントでデータ収集を行うコレクション セットをカスタムで作成する場合は、@collector_type_uid
に「302E93D1-3424-4BE7-AA8E-84813ECF2419」をセットします。出力テーブルは事前に作成しなくても、
「custom_snapshots.[テーブル名]」という名前でテーブルが追加されます。なお、データ コレクションの詳
128
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
細については、次のオンラン ドキュメントを参考にしてください。
データ コレクション
また、コレクター型の詳細については、次のオンランド キュメントを参考にしてください。
コレクター型
3. コレクション セットとコレクション アイテムが作成されたことを確認
作成したカスタム コレクション セットを開始してデータ収集する前に、次のクエリを実行し、新しいコレク
ション セットと、そのコレクション ゕテムが作成されたことを確認しておきます。
USE msdb
SELECT * FROM syscollector_collection_sets
SELECT * FROM syscollector_collection_items
GO
作成したコレクション セットは、SQL Server Management Studio のオブジェクト エクスプローラーで、[管
理] ノードを展開し、[データ コレクション] を展開しても確認できます。
次のスクリプト コードは、Transactions/sec パフォーマンス カウンターの情報を収集するコレクション
セットを作成する例です。
use msdb
declare @schedule_uid uniqueidentif ier
select @schedule_uid = (select schedule_uid f rom sysschedules_localserver_view where name=N'CollectorSchedule_Every_15min')
declare @collection_set_id int;
exec dbo.sp_syscollector_create_collection_set
@name = N'Perf ormanceCountersCollector',
@schedule_uid = @schedule_uid,
@collection_mode = 1,
@days_until_expiration = 15,
@description = N'This is a test collection set',
@collection_set_id = @collection_set_id output;
① アップロードのスケジュール構成
② コレクションセットの定義、作成
③ コレクションアイテムの定義
declare @collector_type_uid uniqueidentif ier;
select @collector_type_uid = N'294605DD-21DE-40B2-B20F-F3E 170EA1E C3'
declare @params xml;
select @params = convert(xml,
N'<ns:Perf ormanceCountersCollector xmlns:ns="DataCollectorType">
<Perf ormanceCounters Objects="$(INSTANCE):Databases" Counters="Transactions/sec" Instances="SQLNET" />
</ns:Perf ormanceCountersCollector>')
declare @collection_item_id int;
exec dbo.sp_syscollector_create_collection_item
@collection_set_id = @collection_set_id,
@collector_type_uid = @collector_type_uid,
@name = 'Perf ormanceCountersCollector',
@f requency = 30,
@parameters = @params,
@collection_item_id = @collection_item_id output;
④ コレクションアイテムの作成
129
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
また、次のスクリプト コードは、Transact-SQL クエリにより、dm_os_wait_stats 動的管理ビューから情報
を収集するコレクション セットを作成する例です。
use msdb
declare @schedule_uid uniqueidentif ier
select @schedule_uid = (select schedule_uid f rom sysschedules_localserver_view where name=N'CollectorSchedule_Every_15min')
declare @collection_set_id int;
exec dbo.sp_syscollector_create_collection_set
@name = N'DMV os_wait_stats',
@schedule_uid = @schedule_uid,
@collection_mode = 1,
@days_until_expiration = 15,
@description = N'This is a test collection set',
@collection_set_id = @collection_set_id output;
① アップロードのスケジュール構成
② コレクションセットの定義、作成
③ コレクションアイテムの定義
declare @collector_type_uid uniqueidentif ier;
select @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419'
declare @params xml;
select @params = convert(xml,
N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
<Value>select * f rom sys.dm_os_wait_stats</Value>
<OutputTable>dm_os_wait_stats</OutputTable>
</Query></ns:TSQLQueryCollector>')
declare @collection_item_id int;
exec dbo.sp_syscollector_create_collection_item
@collection_set_id = @collection_set_id,
@collector_type_uid = @collector_type_uid,
@name = 'DMV os_wait_stats',
@f requency = 30,
@parameters = @params,
@collection_item_id = @collection_item_id output;
④ コレクションアイテムの作成
次のクエリは、カスタム コレクション セットで収集された Transactions/sec パフォーマンス カウンターの
値を参照するためのサンプル コードです。
SELECT
collection_time,
formatted_value
FROM
snapshots.performance_counter_values
WHERE
performance_counter_instance_id
IN (SELECT
performance_counter_id
FROM
[sysutility_mdw].[snapshots].[performance_counter_instances]
WHERE
counter_name = Transactions/sec' AND
instance_name = @database_name
)
AND snapshot_id IN
(SELECT
snapshot_id
FROM
[sysutility_mdw].[core].[snapshots]
WHERE
instance_name = @instance_name
)
AND
collection_time BETWEEN
dateadd(hour,@start_hour,TODATETIMEOFFSET (@start_day, '+09:00')) AND
dateadd(hour,@end_hour,TODATETIMEOFFSET (@end_day, '+09:00'))
130
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
上記のようなクエリを記述し、データセットを定義することで、Reporting Services を使用して、カスタムで
作成されたコレクション セットに対応するレポートを作成できます。
131
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
④. パフォーマンス データ収集の実行ログ確認
パフォーマンス データ コレクターが使用しているジョブを右クリックし、[履歴の表示] を選択することでロ
グの確認が行えます。標準のシステム データ コレクション セットに関連付いたジョブは、次の 5 種類があ
ります。
ジョブ名
利用用途
collection_set_1_noncached_collect_and_upload
「デゖスクの使用量」のデータ収集と蓄積
collection_set_2_collection
「サーバーの利用状況」のデータ収集
collection_set_2_upload
「サーバーの利用状況」のデータ蓄積
collection_set_3_collection
「クエリ統計情報」のデータ収集
collection_set_3_upload
「クエリ統計情報」のデータ蓄積
132
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
次 の 画 面 で は 、「 デ ゖ ス ク 使 用 量 」 (collection_set_1_collection シ ス テ ム コ レ ク シ ョ ン セ ッ ト ) の
collection_set_1_upload ジョブの履歴を表示しています。
次の画面では、「サーバーの利用状況」 (collection_set_2_collection システム コレクション セット) の
collection_set_2_upload ジョブの履歴を表示しています。
133
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
次 の 画 面 で は 、「 ク エ リ 統 計 」 (collection_set_3_collection シ ス テ ム コ レ ク シ ョ ン セ ッ ト ) の
collection_set_3_upload ジョブの履歴を表示しています。
134
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑤. パフォーマンス データ収集時のエラー通知
パフォーマンス データ コレクターのジョブによるデータ収集時にエラーが発生した場合、それを自動で管理者
に通知するためには、UCP と同様に SQL Server エージェント ジョブに対して、ジョブ失敗時のメール通知
を設定する必要があります。本プロジェクトで検証されたメール通知のメッセージは、次のとおりです。
135
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
⑥. パフォーマンス データの自動的な削除
パフォーマンス データ コレクターのジョブにより、収集されたパフォーマンス データの自動的な削除は、
mdw_purge_data_[管理データウェゕハウス名] ジョブの実行タミングで行われます。ジョブの名前は、
UCP の管理対象に登録されている場合、mdw_purge_data_[sysutility_mdw] となります。このジョブの実
行時刻は、既定で 2 時に設定されています。この設定値は、ジョブのプロパテゖで変更可能です。なお、デー
タ コレクション セットのデータ保持期間は、次の方法で変更できます。
オブジェクト エクスプローラーから対象のデータ コレクションセットを右クリックして [プロパテゖ]を選択
し表示されるダゕログボックスで [管理データウェゕハウスにデータを保持する日数を指定] の [データ保
持日数] を変更したい値にセットします。既定の [データ保持日数] は、[デゖスクの使用量] が 730 日、[サー
バー利用状況]、および [クエリ統計情報] は 14 日です。
なお、データ保有期間が 14 日に設定されている場合、自動的な削除が実行される時間から遡って 14 日前の
同時刻より古いデータが削除対象となります。
136
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.8
パフォーマンス データ コレクターの検証結果
検証プロジェクトでテストされたパフォーマンス データ コレクターによるデータ収集のソリューションを評
価したサマリーは、次のとおりです。
カテゴリ
項目
収集/蓄積設定
パフォーマンス データの収集
○
パフォーマンス データの蓄積
○
管理対象ンスタンスの追加
○
パフォーマンス データ分析
○
将来の傾向予測
×
データ コレクターの有無によるパフォーマンス変化
○
実行ログの確認
○
情報収集時のエラー通知
○
収集データの自動的な削除
○
データ コレクター データベースのバックゕップ
○
カスタム データ コレクターの作成
○
分析
運用
カスタマイズ
結果
○:意図したとおり、標準機能で実装できた
△:作りこみをすることで実装できた
×:実装できなかった
137
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
6.3.9
パフォーマンス データ コレクターの考察と結論
検証結果に基づき、パフォーマンス データ コレクターをベースとしたデータ収集ソリューションとして、次の
表の内容を提示しました。
現在の課題
何ができればよいのか?
ソリューション
障害が発生したときに必要なデータが
データを収集したい
既定で作成される 3 種類のシステム コレクショ
採取できていない
ン セット (「デゖスクの使用量」、「サーバーの利
用状況」、「クエリ統計情報」)により、パフォーマ
ンス分析に必要なデータが収集される
障害が発生したときに必要なデータが
データを蓄積したい
そろっていない、失われている
パフォーマンス データは、各システム コレクショ
ン セットで設定され、エージェント ジョブのスケ
ジュールに適用される時間間隔で収集され、管理
データウェゕハウスに格納される
UCP を併用する場合 、管理データウェゕハウス
は、sysutility_mdw データベースに統合される
管理対象のインスタンスが増える度
管理対象の増加に対する設定
単 一 の 管 理 用 マ シ ン か ら
に、パフォーマンス データ収集の設定
の負荷を削減したい
Management Studio を起動し、パフォーマンス
や蓄積処理を追加している
SQL Server
データ収集対象となる各ンスタンスに接続し、オ
ブ ジ ェク ト エク ス プロ ーラ ーか ら 管理 デ ータ
ウェゕハウス構成ウゖザードを起動して、パフォー
マンス データ コレクターの設定が可能
詳細な分析を行うために、作り込みが
多面的な切り口で分析したい
必要となる
3 種類のシステム コレクション セット 「
( デゖス
クの使用量」
、
「サーバーの利用状況」
、
「クエリ統計
情報」) に対応したドリル ダウン可能な標準レ
ポートを使用してパフォーマンス チューニングに
必要な情報を参照可能
過去から現在の状態はわかるが、将来
将来の傾向などを予測したい
パフォーマンス データ コレクターの機能はパ
どうなるかがわからないため、予防策
フォーマンスの問題が発生した後の分析に役立つ
が検討しにくい
が、将来予測には向いていない
パフォーマンス データの収集
管理対象ンスタンスごとにクエリ統計 (実行回数や CPU 時間)、利用状況（CPU、メモリ、デゖスク負荷、
ネットワーク）
、データベースのフゔル サズやログ フゔル サズの推移を収集できました。
パフォーマンス データの蓄積
パフォーマンス データを専用のデータベースに蓄積することができました。パフォーマンス データの蓄積先は、
138
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
任意のンスタンス名、データベース名を指定できますが、UCP のマネージ ンスタンスとして登録されると、
UCP の sysutility_mdw データベースに統合されました。
管理対象の追加
一元化されたビューから管理できる仕組みにはなっていません（リモート サーバーを指定して管理対象の追加
はできません）が、一台の 管理用 PC から SQL Server Management Studio を使用して各ンスタンスへ
接続し、データ コレクターの設定を一か所から行うことはできます。
パフォーマンスデータ分析
複数サーバーを一元管理できないため、SQL Server Management Studio でサーバーを切り替えながら「サー
バーの利用状況の履歴」を確認することで対応しました。ンスタンス単位、データ単位で収集した情報が確認
できました。レポート画面からグラフを選択し、詳細情報をドリル ダウンにより取得できました。レポートか
ら、日時指定や期間指定ができ、時間の早送りや巻き戻しも可能でした。
将来の傾向予測
収集したパフォーマンス データを 1 ページで確認できる期間は、最長で 24 時間でした。そのため長期的な
傾向の推移を確認することが難しく、パフォーマンス劣化の未然防止のために将来の傾向を予測するには、不向
きです。
実行ログの確認
次のジョブの履歴を確認することで実行ログは確認ができました。
デゖスクの使用量：collection_set_1_noncached_collect_and_upload
サーバーの利用状況：collection_set_2_collection,collection_set_2_upload
クエリ統計：collection_set_3_collection,collection_set_3_upload
情報収集時のエラー通知
SQL Server エージェントジョブに対してジョブ失敗時のエラー通知を設定することで可能でした。
139
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
収集データの自動的な削除
自動的な削除のタミングは、[管理データウェゕハウスにデータを保持する日数を指定] を変更することで確
認できました。mdw_purge_data_[管理データウェゕハウス名] ジョブの実行時に自動的な削除が行われます。
データ保有期間を 14 日と設定した場合、削除ジョブ実行時の 14 日前の同時刻より古いデータが削除されま
す。既定のデータ保持日数は、[デゖスクの使用量] システム データ コレクション セットが 730 日、[サー
バー利用状況]、および、[クエリ統計情報] システム データ コレクション セットは、14 日です。
データ コレクター データベースのバックアップと復元
バックゕップ、復元ともに可能でした。バックゕップ操作は、UCP データベースに対する運用と同様です。
カスタム データ コレクション セットの作成
システム データ コレクション セットでは、収集できないパフォーマンス項目については、独自にカスタム
データ コレクション セットを作成することができます。今回の検証では、データ コレクション セットにより、
パフォーマンス モニターのカウンター値と Transact-SQL クエリからの結果セットが収集できることを確認
しました。収集したパフォーマンス データは、既定で用意されているレポートでは表示できないため、SQL
Server Reporting Services を使用したカスタム レポートを別途、作成しました。
考察と結論のまとめ
検証結果から、既定で用意されているシステム データ コレクション セットには、標準的なパフォーマンス分
析を行うために必要な情報が網羅されており、グラフ表示からドリル ダウンして詳細を解析することができま
した。また、システム データ コレクション セットで収集できない項目は、カスタム データ コレクション セッ
トとカスタム レポートを作成することで対応できました。そのため、パフォーマンス データ コレクターによ
るパフォーマンス データ収集は、データベース管理者のタスクに対し、有用なツールであることが分かりまし
た。
パフォーマンス データ コレクターを解析したことで、動作ロジック（データの格納方法や動作しているジョブ）
が分かりました。また動作が理解できたことで、どのように運用（データの蓄積と自動的な削除）が行われるか
も理解できました。
パフォーマンス データ コレクターは、UCP と同様に閾値を逸脱するようなパフォーマンス上の問題が発生し
140
第 6 章 「マルチ サーバー管理の標準化」ソリューションの運用検証
た場合、それを管理者に通知する仕組みは持っていません。パフォーマンス オブジェクト カウンターの閾値の
逸脱に対しては、SQL Server エージェントの「パフォーマンス条件警告」による通知の方法を併用する必要が
あります。
また、パフォーマンス データ コレクターは、UCP のように、一画面で複数ンスタンスの集約された状態情
報を確認することはできませんでした。ただし、SQL Server Management Studio のオブジェクト エクスプ
ローラーを使用し、対象となるすべてのンスタンスに接続し、ビューを切り替えることにより、一か所からパ
フォーマンス情報を確認することができます。
UCP は今日を基準に「1 日」
、
「1 週間」
、
「1 か月」、
「1 年」といった単位でマクロ的に「リソースの使用傾向」
を把握するのに向いており、データ コレクターはパフォーマンス問題の発生箇所の特定と、その分析（発生原
因の切り分け）を行う場合に向いています。
141