SE の雑記

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

独自のデータ コレクションを作成

leave a comment

ちょっとメモとして残しておきたいと思います。

SQL Server 2008 以降ではデータ コレクションというパフォーマンス情報を定期的に取得できる仕組みが搭載されています。

デフォルトで作成されているシステム データ コレクション セットでは以下の 3 種類が定義されています。

  • クエリ統計
  • サーバーの利用状況
  • ディスク使用量
  • (ユーティリティ情報)

SQL Server 2008 R2 で搭載されている UCP (ユーティリティ コントロール ポイント) もシステム データ コレクションの一種として作成さがされているようで、2008 R2 だと [ユーティリティ情報] というシステムデータコレクションが作成されています。

今回はこのデータ コレクションをユーザー独自に定義してカスタムしたデータ コレクションを作成してみたいと思います。

SQL Server の自習書だと、[監視ツールの基本操作] にこのあたりの手順はまとめられています。

■データ コレクションの構成

データコレクションですが以下の情報で構成されています。

  • コレクション セットのスケジュール
  • コレクション セット
  • コレクション アイテム

[コレクション セットのスケジュール]
スケジュールに関しては作成したデータ コレクションをどのようなタイミングでアップロードするかを定義したものになります。
デフォルトでは以下のアップロードスケジュールが事前に定義されています。

  • CollectorSchedule_Every_5min
  • CollectorSchedule_Every_10min
  • CollectorSchedule_Every_15min
  • CollectorSchedule_Every_30min
  • CollectorSchedule_Every_60min
  • CollectorSchedule_Every_6h

これ以外の間隔アップロードする場合にはユーザーが独自にスケジュールを作成する必要があります。
このスケジュールはコレクション セットを [キャッシュ モード] で設定した場合に使用します。
[非キャッシュ モード] の場合にはデータを取得したタイミングで情報をアップロードしますので、この場合はスケジュールを作成する必要はありません。

[コレクション セット]
コレクションセットはスケジュールとキャッシュの設定 / データの保存日数 / スケジュールを定義したものになります。

[コレクション アイテム]
コレクション アイテムは取得するデータと取得のタイミングを定義したものになります。

この 3 種類を独自に定義することで、ユーザー独自の カスタム データ コレクションを作成することが可能となります。

それでは、各設定について見ていきたいと思います。

 

■スケジュールの設定

定義されているスケジュールは [sysschedules_localserver_view] で確認をすることが可能です。
GUI から確認をする場合の操作は以下の技術情報に記載されています。
スケジュールの作成とジョブへのアタッチ

楽な管理は SSMS で GUI を使った方ですので今回はこちらで。

現在設定されているスケジュールを管理するためには、

[SQL Server エージェント] → [ジョブ] → 右クリック → [スケジュールの管理] をクリックします。
image

設定されているスケジュールをこのように確認することが可能です。
image

スケジュールはキャッシュ モードの場合に使われます。そのため、データ収集のタイミングとは独立しています。
データ コレクションのデータを頻繁に見るということは少ないかと思います。
# 頻繁に見る場合は DMV や、パフォーマンスモニタを直接見てしまうのではないでしょうか

大体の場合はデフォルトで定義されているアップロード間隔で賄えてしまうかと。
週次や月次でアップロードしたい場合には新規にスケジュールを作ることがあるかもしれないですね。

■コレクションセットの設定

データをアップロードするスケジュールが決まったら次に [sp_syscollector_create_collection_set] を使用してコレクションセットを作成します。

クエリの形式は以下のようになります。

USE [msdb]
GO
DECLARE @collection_set_id int;
EXEC dbo.sp_syscollector_create_collection_set
    @name = N’User Collection Set’,
    @collection_mode = 0,
    @days_until_expiration = 100,
    @description = N’ユーザー定義のカスタムデータコレクション’,
    @logging_level = 2,
    @schedule_name = N’CollectorSchedule_Every_30min’,
    @collection_set_id = @collection_set_id OUTPUT

 

必須の OUTPUT として、collection_set_id を戻しますので、INT の変数を定義してから実行する必要があります。
この戻り値をコレクション アイテムを作成するときに使用します。

collection_set_id を改めて確認したい場合は、[syscollector_collection_sets_internal] を参照することで確認することができます。
image

スケジュールに関しては schedule_name で名称を直接指定していますが、[sysschedules_localserver_view] を参照して、schedule_uid を取得して schedule_uid を指定することでもスケジュールを設定することができます。

これでコレクション
セットが作成されました。
image

この状態のコレクション セットですが、コレクション アイテムは設定されていないためどのようなアイテムを取得するかは定義されていません。
image

 

■コレクションアイテムの設定

最後に、どのような情報を取得するかというコレクション アイテムを設定します。

コレクションアイテムでは、[コレクタ型] を使用して様々な情報を取得することが可能です。

使用するコレクタ型が決まったら [sp_syscollector_create_collection_item] を使用して、コレクション セットにコレクション アイテムを設定します。

USE [msdb]
GO
DECLARE @collection_item_id int
DECLARE @collection_set_id int = (SELECT collection_set_id FROM syscollector_collection_sets WHERE name = N’User Collection Set’)
DECLARE @collector_type_uid uniqueidentifier = (SELECT collector_type_uid FROM syscollector_collector_types WHERE name = N’Generic T-SQL Query Collector Type’)

DECLARE @params XML =
N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
<Value>
SELECT
DB_NAME(database_id) AS database_name,
page_type,
COUNT(*) * 8192 as size_bytes,
SUM(row_count) AS row_count,
SUM(free_space_in_bytes) AS free_space_in_bytes,
is_modified,
numa_node
FROM
sys.dm_os_buffer_descriptors
GROUP BY
database_id,
page_type,
is_modified,
numa_node
ORDER BY
database_id ASC
</Value>
<OutputTable>bufferdescription_data</OutputTable>
</Query>
</ns:TSQLQueryCollector>’

EXEC sp_syscollector_create_collection_item
@collection_set_id = @collection_set_id,
@collector_type_uid = @collector_type_uid,
@name = ‘Bufferdescription collector item’,
@frequency = 60,
@parameters = @params,
@collection_item_id = @collection_item_id OUTPUT

 

今回は、[ジェネリック T-SQL Query コレクター型] を使用しているので、[syscollector_collector_types] から [Generic T-SQL Query Collector Type] の uid を取得して、設定をしています。

設定が終わると先ほど作成したコレクション セットにコレクション アイテムが設定されます。
image

 

これで準備は完了です。

SSMS で作成したデータ コレクションを右クリックして [データ コレクション セットの開始] をクリックします。
imageimage

データコレクションを開始したタイミングで、SQL Server Agent のジョブとして作成したデータ コレクションのジョブが作成され、起動されます。
image

作成したデータ コレクションを [今すぐ収集してアップロード] をクリックして実行します。
image
image

正常に実行されていると、データ コレクション用に設定しているデータベースに [custom_snapshots] というスキーマのテーブルとして [OutputTable] で設定したテーブルが作成されています。
image

データが取得できていれば、設定は正常に動作しています。
image

作成したデータ コレクションのレポートを自動で作成してくれるような機能は無いので帳票とするためには SSRS を使用するか、SSMS のカスタムレポートを使用する必要があります。

SQL Server 2008 R2 までの SSMS のカスタムレポートは Report Builder 1.0 や SQL Server 2005 の BIDS で作成しないといけないのでちょっと使いまわしがよくなかったりします…。
# Denali CTP1 であれば Report Builder 2.0 が使えるのですが出来れば SSRS と同じバージョンで作成できるとうれしいのですが…。

簡単ではありますがユーザー独自のカスタム データ コレクションを作成するための一連の流れをまとめてみました。

Share

Written by Masayuki.Ozawa

4月 29th, 2011 at 10:32 pm

Posted in SQL Server

Tagged with

«
»

Leave a Reply