SE の雑記

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

Archive for 12月 16th, 2010

第 1 回 Get The Fact セミナーの振り返り その 4

leave a comment

今回は [パフォーマンス条件警告] について振り返ってみたいと思います。

セミナーの中で SQL Server のパフォーマンス条件警告をしようして、ログの使用状況が一定の閾地に達した場合にログのバックアップを取得して、ログを切り捨てるという方法が紹介されました。

それでは、パフォーマンス条件警告についてまとめていきたいと思います。

■パフォーマンス条件警告設定の流れ

パフォーマンス条件警告ですが、[SQL Server Agent] を使用して動作します。
image

設定は

  1. 警告の条件を設定
  2. 警告が発生した際に実行する SQL Server Agent のジョブを設定
  3. 警告が発生したことの通知方法を設定

という流れになります。
# 実際の設定は順不同でジョブと通知方法は必須ではないですが。

 

■パフォーマンス条件警告を設定

それでは、実際にパフォーマンス条件警告を設定していきたいと思います。
今回はセミナーでお話しのあったものと同じ設定を行っていきます。

  1. [警告] を右クリックして、[新しい警告] をクリックします。
    image
  2. 警告の条件を設定します。
    今回は以下の内容で設定を行っています。
    image
    TEST データベースのログが 200,000KB 使用された場合に警告を発生するように設定しています。
  3. [応答] の [ジョブの実行] を有効にして、[新しいジョブ] をクリックします。
    image
    今回は警告を作成する作業の流れで、新しいジョブを作成していますが、事前に作成したジョブを使う事も可能です。
  4. ジョブの [名前] を入力します。
    image
  5. [ステップ] の [新規作成] をクリックします。
    image
  6. ログのバックアップを取得するためのステップを作成し、[OK] をクリックします。
    # 今回のパフォーマンス条件警告は後でスクリプト化したものを記載しますのでコマンドの内容はここでは割愛します。
    image
  7. [OK] をクリックします。
    image
  8. [OK] をクリックします。
    image

以上で設定は完了です。
image

今回は通知設定はしていませんが、通知は以下の方法で行う事ができます。
image

 

それでは実際にデータを追加して挙動がどのように変わるかを試してみたいと思います。
今回も自動チェックポイントは無効にしています。

パフォーマンス条件警告を無効にしている場合の状態はこのようになります。
# 作成した警告は、個別に無効化することができます。
image

ログの書き込み待ちとバックアップ処理時間は 2 軸にしていますが、これらは発生していないことが確認できます。

それでは、パフォーマンス条件警告を有効にして同じ処理を実行してみます。
image
こちらも、バックアップ処理時間は 2 軸に表示しています。
ログの書き込み待ちに関しては、カウンタがあがっていることを示すために、元の値に 10,000 をかけています。

パフォーマンス条件警告の設定内容が有効になっていますので、ログファイルの使用状況が一定に達したタイミングで自動的にログのバックアップが行われています。(バックアップ処理時間はデータ/ログのバックアップを取得すると発生します。)

今回作成したパフォーマンス条件警告のサンプルスクリプトがこちらになります。

USE [msdb]
GO

/****** Object:  Job [TEST Database Log Backup]    Script Date: 12/16/2010 21:09:09 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/16/2010 21:09:09 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’TEST Database Log Backup’,
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N’使用できる説明はありません。’,
        @category_name=N'[Uncategorized (Local)]’,
        @owner_login_name=N’WIN-1QM471JPL04Administrator’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [TEST Database Log Backup]    Script Date: 12/16/2010 21:09:09 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’TEST Database Log Backup’,
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N’TSQL’,
        @command=N’BACKUP LOG [TEST] TO  DISK = N”F:BackupSQL2008R2TEST.bak” WITH NOFORMAT, INIT,  NAME = N”TEST-トランザクション ログ  バックアップ”, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
‘,
        @database_name=N’TEST’,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

 

EXEC msdb.dbo.sp_add_alert @name=N’TEST Database Log Auto Backup’,
        @message_id=0,
        @severity=0,
        @enabled=0,
        @delay_between_responses=0,
        @include_event_description_in=0,
        @category_name=N'[Uncategorized]’,
        @performance_condition=N’MSSQL$SQL2008R2:Databases|Log File(s) Used Size (KB)|TEST|>|200000′,
        @job_id=N’e5518186-0c48-4c08-a14c-3c5c285730ef’
GO

このスクリプトですが、[INIT] を指定してログのバックアップを取得しています。
本来の運用では、この指定は NG です。
# テスト用でログのバックアップを蓄積したくなかったので INIT でバックアップを上書きしています。
ログのバックアップはログチェーンという考えがあり、連続したログのバックアップが担保されている必要があります。
ログチェーンが途切れてしまうとログを使用したリストアができませんので、本番運用で使う場合は [NOINIT] かログファイルの名称を動的に生成するようにしてログを上書きしないようにする必要があります。

■Twitter での質問について

セミナー中に Twitter で以下のような質問がありました。
「オンライン中のトランザクションログバックアップのパフォーマンス劣化はどれくらいなのでしょうか？」

ログのバックアップをするとログの書き込み待ちが発生しているのが上のグラフで確認することができます。
# 今回の環境では 200 MB 程度のログのバックアップを書き込む際には、書き込み待ちは最大で 7ms 程度になっていました。

グラフではなくWRITELOG の待ち事象でも比較をし手みたいと思います。

パフォーマンス条件警告を設定していない場合はこのような待ち事象になります。

image
image

waiting_tasks_count = 619,844 ? 519,766 = 100,078
wait_time_ms = 459,265 ? 396,561 = 62,704

パフォーマンス条件警告を設定した場合はこのようになります。

image
image

waiting_tasks_count = 720,408 ? 619,881 = 100,527
wait_time_ms = 532,001 ? 459,405 = 72,596

ログのバックアップをしている最中はログの使用状況がなだらかになりますのでログの書き込みにも多少の影響が発生します。

また、BOL に以下のように書かれているようにバックアップの実行を行うと CHECKPOINT が発生します。

チェックポイントは次の状況で作成されます。

  • CHECKPOINT ステートメントが明示的に実行された場合。接続を確立するために、現在のデータベースでチェックポイントが作成されます。
  • データベースで最小ログ記録操作が実行された場合。たとえば、一括ログ復旧モデルを使用しているデータベースで一括コピー操作が実行された場合です。
  • ALTER DATABASE を使用して、データベース ファイルが追加または削除された場合。
  • SHUTDOWN ステートメント、または SQL Server (MSSQLSERVER) サービスを停止することによって、SQL Server のインスタンスが停止された場合。どちらの場合でも、SQL Server のインスタンスの各データベースでチェックポイントが作成されます。
  • データベースの復旧にかかる時間を短縮するために、SQL Server のインスタンスにより、各データベースで定期的に自動チェックポイントが作成されている場合。
  • データベースのバックアップが作成された場合。
  • データベースのシャットダウンが必要な動作が実行された場合。たとえば、AUTO_CLOSE が ON に設定されていて、データベースへの最後のユーザー接続が終了した場合、またはデータベースの再起動が必要なデータベース オプションが変更された場合です。

データベースのバックアップにはログのバックアップも含まれています。
先ほどのグラフにチェックポイントの発生状況も追加してみます。
image

バックアップのタイミングに合わせてチェックポイントが発生していることが確認できます。
チェックポイントが発生するとメモリ上のダーティーページをデータファイルに書きだしますので、データファイルに対しても負荷がかかることになります。

この辺はディスクの性能にも依存するのでどれくらいというのは難しいところなのですが、ログの書き込み待ち + チェックポイントによるデータファイルへの書き出しを劣化分として見込んでおく必要があると思います。
# この辺はディスク性能とドライブ構成に依存するところがあるのですよね…。

 

■WMI イベント警告

これは警告のちょっとした応用なのですが、パフォーマンス条件警告ではなく、[WMI イベント警告] を使用することでイベントを受信してジョブを実行することが可能となります。

例えば、ロックエスカレーションが発生した場合にそのイベントを受信してテーブルにロックエスカレーションが発生した時に実行されたクエリを保存してくれるような警告が作れると運用で便利そうですよね。

WMI イベント警告を使用すると、イベントを受信して特定のジョブを実行するということができるようになります。
# イベントを取得してログに出すだけであれば SQL トレースでもできるのですけどね。

BOL には [サンプル : WMI Provider for Server Events の使用による SQL Server エージェント警告の作成] という形でデッドロックをトラップしてテーブルにデータを書きだすサンプルが紹介されています。
# サンプル : WMI Provider for Server Events の使用による SQL Server エージェント警告の作成

ただし、内容に一か所誤りがあり、

@command= N’INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph) VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData))))’

を

@command= N’INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph) VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData)))”)’

にしないと動かなかったはずですが。
# シングルクォート × 2 が足りていないのですよね…。

WMI イベント警告を使用するための事前準備として 2 つの作業があります。

  1. 対象のデータベースで [Service Broker] を有効にする。
    WMI イベントを受信するために対象のデータベースで [Service Broker] を有効にします。
    BOL に以下のように記載されています。

    SQL Server エージェントが WMI イベントを受信するには、msdb および AdventureWorks2008R2 で Service Broker が有効化されている必要があります。

    msdb に関してはデフォルトで Service Broker が有効になっているはずなのですが、ユーザーデータベースではデフォルトでは無効になっています。
    Service Broker を有効にするためにはデータベースのプロパティを開いて、[Broker が有効] を [True] にする必要があります。
    image
    ただし、この変更をする際にはデータベースに対して [LCK_M_X] (排他ロック) を取得しにいきますので、対象のデータベースに接続中のユーザーがいると変更できません。
    今回は、SQL Server のサービスを再起動して、サクッと全ロックを外してから実行してしまいました。
    クエリで設定を変更する場合は以下のクエリを実行します。
    # DB 名は適宜変更する必要があります。

    USE [master]
    GO
    ALTER DATABASE [TEST] SET  ENABLE_BROKER WITH NO_WAIT
    GO
  2. SQL Server エージェントで [トークンの置き換え] を有効化
    WMI イベント警告を使用した場合、SQL Server のジョブステップの中で WMI のイベントのプロパティを使用することが可能です。
    WMI のプロパティを使用する場合は、SQL Server エージェントで [トークンの置き換え] を有効にする必要があります。
    たとえば、ロックエスカレーションが発生した際クエリ (SQL) を取得する場合には、WMI(TextData) というように記述をするのですが、[トークンの置き換え] が有効になっていないとこの設定がそのまま文字データ (WMI(TextData) という単純な文字列) として認識されてしまいます。
    WMI(TextData) → 実行されていたクエリ に変換するためには、トークンの置き換えを有効にする必要があります。
    # ジョブ ステップでのトークンの使用

    ロックエスカレーション時のクエリは SQL Server 2008 からでないと取得できなかった気もします…。

    トークンの置き換えはSQL Server エージェントのプロパティから [警告システム] を選択して、[警告に応答するすべてのジョブのトークンを置き換える] を有効にします。
    image
    クエリで有効にする場合は、以下のような内容になります。

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
            @alert_replace_runtime_tokens=1

以上で、事前準備は完了です。

基本的な流れはパフォーマンス条件警告と変わりません。
WMI イベント警告特有な内容としては、WMI の名前空間を指定するのとクエリを WQL ステートメントで記載する箇所になります。

名前空間に関しては通常の WMI の名前空間を指定すれば問題ありません。
SQL Server のサーバーイベントに関しては以下の指定をします。

\.rootMicrosoftSqlServerServerEvents<インスタンス名>

私の環境ではこのような指定ですね。
[\.rootMicrosoftSqlServerServerEventsSQL2008R2]

WQL ステートメントを記載するためには、イベント名を記載しなくてはいけないのですが、イベント名は [wbemtest.exe] (Windows Management Instrumentation テスト) を使用すると簡単に確認することができます。
# Windows Server 2008 R2 だと標準で入っています。
image

[接続] をクリックして、SQL Server の名前空間に接続をします。
image

名前空間に接続をしたら、[クラスの列挙] をクリックします。
[再帰] 選択して [OK] をクリックするとクラスの一覧が取得できます。
image

クラスとして表示されたものが WMI イベント警告で受信できるイベントになります。
image
今回はロックエスカレーションを受信したいので、[LOCK_ESCALATION] というイベントを受信することになります。
LOCK_ESCALATION をダブルクリックするとプロパティを取得することができます。
このプロパティ名がトークンの置き換えで使用できる WMI のプロパティ名になります。
image

 

WMI イベント警告としては以下のような設定を行います。
image

続いて応答で使用するジョブを作成します。
今回は発生時刻 / データベース名 / 実行していたクエリの情報をテーブルに格納したいと思います。
そのため、以下のテーブルを事前に作成しておきます。
# 今回は [TEST] というデータベース上に作成しています。

CREATE TABLE dbo.LockEscalationEvent
    (
    EventDate datetime NULL,
    DatabaseName nvarchar(128) NULL,
    SQL nvarchar(MAX) NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO

ジョブの名前は適当なものを設定します。
image

ジョブには以下のような内容を設定します。
image

コマンドにはトークンの置き換えを使用して、WMI のプロパティを ESCAPE_SQUOTE を使って、プロパティ内にシングルクォートが存在している場合は修飾するように指定をしたクエリを設定しています。

以上で、WMI イベント警告の設定は完了です。

それでは実際にロックエスカレーションを発生させて正常に動作するかを見ていきたいと思います。
image
UPDLOCK を設定して、トランザクション内で大量のデータを SELECT したためロックエスカレーションが発生しています。
そのあとに、JobStep が実行されていますね。
# JobStep は SQL Server Agent サービスの起動アカウントで SQL Server に接続して実行されています。
今回は LocalSystem アカウントで実行しています。

最後の JobStep を確認すると、INSERT 文が実行されていることが確認できます。
image

INSERT 文ですが、トークンの置き換えにより WMI のプロパティが文字列に置き換えられて実行されているのが確認できますね。
作成したテーブルを確認してみます。
image

イベントが発生した日と対象のデータベース、クエリがテーブルに格納されています。

WMI イベント警告を使用することで特定のイベント受信時にジョブを動かくすことができますのでうまく使うと運用が楽になるかもしれないですね。
今回作成した警告とジョブのサンプルスクリプトは以下になります。

USE [msdb]
GO

/****** Object:  Job [JOB LockEscalation]    Script Date: 12/16/2010 23:10:33 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/16/2010 23:10:33 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’JOB LockEscalation’,
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N’使用できる説明はありません。’,
        @category_name=N'[Uncategorized (Local)]’,
        @owner_login_name=N’WIN-1QM471JPL04Administrator’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step LockEscalation]    Script Date: 12/16/2010 23:10:33 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step LockEscalation’,
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N’TSQL’,
        @command=N’INSERT INTO
    dbo.LockEscalationEvent
VALUES(
    GETDATE(),
    N”$(ESCAPE_SQUOTE(WMI(DatabaseName)))”,
    N”$(ESCAPE_SQUOTE(WMI(TextData)))”
)
‘,
        @database_name=N’TEST’,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

 

USE [msdb]
GO

/****** Object:  Alert [WMI Lock Escalation]    Script Date: 12/16/2010 23:10:16 ******/
EXEC msdb.dbo.sp_add_alert @name=N’WMI Lock Escalation’,
        @message_id=0,
        @severity=0,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=0,
        @category_name=N'[Uncategorized]’,
        @wmi_namespace=N’\.rootMicrosoftSqlServerServerEventsSQL2008R2′,
        @wmi_query=N’SELECT * FROM LOCK_ESCALATION’,
        @job_id=N’6315f399-7921-4431-a4d1-89135a792fa9′
GO

パフォーマンス条件警告と WMI イベント警告を使用することでデータベースの特定の状態を受信してジョブを実行することが可能になります。
# WMI イベント警告は良い機会だったのでついでにまとめたものですが。

パフォーマンス条件警告でログのバックアップを取るのは今まで考えていなかったので、なるほどと思いました。

この後には [バックアップポリシーの検討] ということで、バックアップタクトの一例や障害発生時の復元の概要についてのお話がありました。

次の投稿では、バックアップポリシーについてまとめてみたいと思います。

Written by Masayuki.Ozawa

12月 16th, 2010 at 7:39 pm

Posted in SQL Server,セミナー

Tagged with ,