データ コレクションによる定期的なデータ収集

SQL Server 2008 からは、データ コレクション機能がサポートされました。これは、定期的なデータ収集が行える機能で、パフォーマンス チューニング時に大変役立つ機能です。データ コレクションでは、任意の Transact-SQL ステートメントと、パフォーマンス カウンタ、トレースからデータを収集することができるので、パフォーマンス状況を監視できる動的管理ビュー（DMV：Dynamic Management View）を定期的にクエリしたり、CPU やメモリなどのパフォーマンス カウンタを定期的に取得するといった使い方ができます。

データ コレクションは、次のように設定することができます。
まずは、［管理］フォルダを展開して［データ コレクション］を右クリックし、［管理データウェアハウスの構成］をクリックします。

［管理データ ウェアハウス構成ウィザード］の最初のページでは、［管理データウェアハウスを作成またはアップグレードする］を選択して、［次へ］ボタンをクリックします。


データ コレクションでは、収集したデータをデータベースへ格納するので、その格納先となるデータベースを作成しておく必要があります。ここでは、次のように［新規作成］ボタンをクリックして、「DataCollectionDWH」という名前で作成しておきます。



続いて、データ コレクションの管理者アカウントとして追加したいログイン アカウントを任意で指定し、［次へ］ボタンをクリックします。


最後のページ［ウィザードの完了］では、［完了］ボタンをクリックします。


次に、もう一度［管理データ ウェアハウス構成ウィザード］を起動します。

［構成タスクの選択］画面では、今度は、［データ コレクションをセットアップする］を選択して、［次へ］ボタンをクリックします。

次の［管理データ ウェアハウス ストレージの構成］画面では、前の手順で作成したデータベースと任意の Windows 上のフォルダを指定して、［次へ］ボタンをクリックします。


最後の［ウィザードの完了］画面では、［完了］ボタンをクリックします。


ウィザードの完了後、オブジェクト エクスプローラで、次のように DataCollectionDWH データベース内のテーブルを参照すると、ウィザードによって多くのテーブルが自動作成されていることを確認できます。


続いて、Collection Set と Collection Item を作成するために、次の SQL ステートメントを実行します。

-- CollectorSchedule_Every_5min のuid を取得
USE msdb
DECLARE @schedule_uid uniqueidentifier
SELECT @schedule_uid = 
		(SELECT schedule_uid 
 		  FROM sysschedules_localserver_view
		   WHERE name=N'CollectorSchedule_Every_5min')
 
-- Collection Set の作成
DECLARE @collection_set_id int
EXEC dbo.sp_syscollector_create_collection_set
    @name = N'DCtest1',
    @schedule_uid = @schedule_uid,
    @collection_mode = 1,
    @days_until_expiration = 30, 
    @description = N'テスト',
    @collection_set_id = @collection_set_id output

DECLARE @params XML
SELECT @params = CONVERT(XML, 
    N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
        <Query>
          <Value>SELECT GETDATE() AS 現在時刻</Value>
          <OutputTable>DCtest_GetDate</OutputTable>
        </Query>
      </ns:TSQLQueryCollector>')

-- Collection Item の作成
-- @collector_type_uid の'302E〜' は、T-SQL Query Collector Type
DECLARE @collection_item_id int
EXEC dbo.sp_syscollector_create_collection_item
    @collection_set_id = @collection_set_id,
    @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',
    @name = 'DMVtest1',
    @frequency = 30,
    @parameters = @params,
    @collection_item_id = @collection_item_id output;

これで GETDATE 関数で取得した結果を DCtest_GetDate テーブルへ格納できるようになります。
実行後、作成した データ コレクション セットを起動するために、次のように「DCtest1」を右クリックして、［データ コレクション セットの開始］をクリックします。


次に、手動でデータを収集するために、DCtest1 を右クリックして［今すぐ収集してアップロード］をクリックします。


正常にデータが収集されたかどうかを確認するために、DCtest1 を右クリックして［ログの表示］をクリックし、ログを参照します。


収集されたデータを確認するために、DataCollectionDWH データベース内のテーブルを参照します。

custom_snapshot スキーマの「DCtest_GetDate」テーブルが作成され、GETDATE の実行結果が格納されていることを確認できます。
このように データ コレクション機能を利用すると、任意の Transact-SQL ステートメントを定期的に実行して、データを収集することができるので、パフォーマンス チューニング時に大変役立ちます。また、データ コレクションでは、パフォーマンス カウンタとトレースからもデータを収集することができるので、これらも便利です。
また、データ コレクションでは、実は「システム データ コレクション」と呼ばれる組み込みの データ コレクションが用意されていて、主要な動的管理ビューとパフォーマンス カウンタが自動的に取得されています。この機能は「パフォーマンス データ コレクション」と呼ばれ、グラフィカルなレポートも表示可能になります。これについては、弊社執筆の SQL Server 2008 自習書シリーズ（下記 URL）の「SQL Server 2008 の注目の新機能をイチ早く試してみよう! 」編を参考にしてみてください。
http://www.microsoft.com/japan/sqlserver/2008/self-learning/default.mspx