SE の雑記

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

Archive for the ‘Get The Fact’ tag

optimize for ad hoc workloads によるスタブのキャッシュについて

one comment

1 月に開催された Get The Fact セミナーoptimize for ad hoc workloads オプション についてのお話があったようですので今回はこの内容についてまとめてみたいと思います。

Read the rest of this entry »

Written by masayuki.ozawa

2月 14th, 2011 at 10:08 pm

Posted in SQL Server

Tagged with

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

leave a comment

今回は [パフォーマンスデータコレクション] について振り返っていきたいと思います。

SQL Server 2008 以降ではパフォーマンスデータコレクションという機能が追加されています。
パフォーマンスデータコレクションでは、SQL Server の状態をログとして記録し、現状 / 以前の状態をレポートとして確認することができます。

image

Read the rest of this entry »

Written by masayuki.ozawa

1月 2nd, 2011 at 2:37 pm

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

leave a comment

今回は [デタッチ / アタッチを使用したデータベースの移動] について振り返っていきたいと思います。

SQL Server にはデータベースを デタッチ (サーバーからデータベースのエントリを外す) / アタッチ (サーバーにデータベースのエントリを追加) することで、データベースを移動 (別サーバー / 同一サーバーの別ディレクトリ) することができます。

セミナーの中では、上位バージョンへのアタッチ / アタッチ後の統計情報についてのお話がありましたので、今回はその点をまとめてみたいと思います。

Read the rest of this entry »

Written by masayuki.ozawa

1月 1st, 2011 at 10:23 pm

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

leave a comment

今回は [データベースのリストア] について振り返っていきたいと思います。

バックアップポリシーの中で、以下のような図を使用してバックアップについての説明がありました。
image

セミナーの中で、障害発生のタイミングでデータベースの障害が発生した場合どのタイミングまでリストアすることが可能なのかという質問がありました。

この質問の内容を元にリストアについてまとめていきたいと思います。

Read the rest of this entry »

Written by masayuki.ozawa

1月 1st, 2011 at 7:29 pm

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

leave a comment

今回は [データベースの整合性確認の負荷] について振り返っていきたいと思います。

前回の振り返りでまとめていたデータベースの整合性確認 (DBCC CHECKDB) ですが、データベースの状態を確認するためには大事になる処理ですが、整合性を確認するためには負荷がかかります。

Read the rest of this entry »

Written by masayuki.ozawa

12月 26th, 2010 at 9:56 pm

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

leave a comment

今回は [データベースの整合性確認] について振り返っていきたいと思います。

[メンテナンス プラン ウィザード] では、 [データベースの整合性確認] のメンテナンス タスクを作成することが可能です。
image

このタスクを実行するとデータベースの整合性をチェックすることができます。
セミナーの中では、バックアップの取得前等に実行をすることでデータベースに問題が無いかを確認しバックアップを取得することが可能というようなお話がありました。

今回は、この整合性確認についてまとめていきたいと思います。

■データベースの整合性確認で実行される内容

まずはデータベースの整合性確認のタスクで実行される内容について確認していきたいと思います。
作成したデータベースの整合性確認のタスクで実行される T-SQL を確認してみます。
image
image

データベースの整合性確認のタスクでは、実行対象として指定したデータベースに対して [DBCC CHECKDB] が実行されます。
# [インデックスを含める] を外すと [NOINDEX] オプションを指定して実行されます。

以下は、BOL の DBCC CHECKDB の記載内容の引用になります。

次の操作を実行し、指定したデータベース内のすべてのオブジェクトの論理的および物理的な整合性をチェックします。

  • データベースに対して DBCC CHECKALLOC を実行。
  • データベース内にあるすべてのテーブルとビューに対して DBCC CHECKTABLE を実行。
  • データベースに対して DBCC CHECKCATALOG を実行。
  • データベース内にあるすべてのインデックス付きビューの内容を検証。
  • FILESTREAM を使用して varbinary(max) データをファイル システムに格納する場合のテーブルのメタデータとファイル システムのディレクトリおよびファイルの間のリンクレベルの一貫性を検証。
  • データベースの Service Broker データを検証。

DBCC CHECKDB では、上記の処理を実行してデータベースの整合性が取れているかを確認する処理になります。

■整合性確認を実施しないとどうなるか

データベースの整合性確認を行う事でどのようなことが分かるか、簡単に試してみたいと思います。

今回は、バイナリエディタでデータファイル (mdf) を開き、一部のデータを書き換え、データの内容とページのチェックサムが一致しないようにしてみました。
データを書き換える前のテーブルの全件を取得した情報がこちらになります。
image

SQL Server 2008 以降であれば、データがどのページに格納されているかは [sys.fn_PhysLocFormatter(%%physloc%%)] を使用することで確認することができます。
# この関数は、Undocument の関数です。
上のデータは、以下のクエリを実行して取得しています。

SELECT
    sys.fn_PhysLocFormatter(%%physloc%%) AS [PageNo],
    *
FROM
    Table_1

 

SQL Server のページにはチェックサムがあり、データの内容を元に計算したチェックサムとデータの内容が一致しているかを確認することで、ページが破損しているかを検出する機能がありますのでページに一貫性が無いとエラーとして検出することができます。
# データベースオプションで [CHECKSUM] / [TORN_PAGE_DETECTION] / [NONE] のいずれかを設定することが可能です。([TORN_PAGE_DETECTION] は下位互換となっていますが。)

今回はバイナリエディタでデータベースのファイルを開いて、 (1:114:0) のページを壊してみました。

ページを壊した後に、データを全件取得すると以下のデータまでは取得できるのですが、 (1:114:0) のデータを読み込もうとした際にエラーが発生します。
image

メッセージ 824、レベル 24、状態 2、行 1
SQL Server で、一貫性に基づいた論理 I/O エラーが検出されました: 正しくないチェックサム (必要なチェックサム: 0x8b1229d0、実際のチェックサム: 0x831a29d0)。
このエラーは、ファイル ‘F:SQL2008R2TEST.mdf’ のオフセット 0x000000000e4000 にあるデータベース ID が 5 のページ (1:114) の 読み取り 中に発生しました。
SQL Server エラー ログまたはシステム イベント ログ内の別のメッセージで詳細情報が報告されることもあります。
このエラー状態は深刻で、データベースの整合性を損なう可能性があるので、すぐに解決する必要があります。
完全なデータベース一貫性確認 (DBCC CHECKDB) を実行してください。
このエラーには多くの要因があります。
詳細については、SQL Server オンライン ブックを参照してください。

(1:114) という情報が表示されていますので、ファイル ID 1 (mdf ファイル) の 114 ページに一貫性のエラー (チェックサムとデータが不整合) が発生していることが確認できます。

今回、私が壊したページ番号と一致しますね。

破損ページの操作をした場合、イベント ビューアーのアプリケーションにも同様の情報が出力されますので、監視ソフト等でログ監視をしているのであれば、エラーを検知することもできます。
image

SQL Server のログにも情報は出力されています。
image

今回はデータを全件読み込みしたので破損ページも読み込まれエラーが発生しましたが、破損したページにアクセスをしなければ正常にデータを読み込むことができます。
# Col = 4 のデータを読もうとするとエラーになってしまうので飛ばしています。(先行読み込みの可能性が)
image

破損したページを操作しないとエラーにはなりませんので、データベースに異常があるかは分かりません。

DBCC CHECKDB を定期的に実行することで障害により発生した潜在的に潜んでいるデータベースの異常をチェックすることが可能です。
[DBCC CHECKDB(N’TEST’)] を実行して、今回整合性のエラーが発生している [TEST] データベースでデータベースのチェックをしてみます。

‘TEST’ の DBCC 結果。
Service Broker メッセージ 9675、状態 1: 分析されるメッセージ型: 14。
Service Broker メッセージ 9676、状態 1: 分析されるサービス コントラクト: 6。
Service Broker メッセージ 9667、状態 1: 分析されるサービス: 3。
Service Broker メッセージ 9668、状態 1: 分析されるサービス キュー: 3。
Service Broker メッセージ 9669、状態 1: 分析されたメッセージ交換のエンドポイント: 0。
Service Broker メッセージ 9674、状態 1: 分析されたメッセージ交換グループ: 0。
Service Broker メッセージ 9670、状態 1: 分析されるリモート サービス バインド: 0。
Service Broker メッセージ 9605、状態 1: 分析されたメッセージ交換の優先度: 0。
‘sys.sysrscols’ の DBCC 結果。
オブジェクト "sys.sysrscols" の 7 ページには 637 行あります。
‘sys.sysrowsets’ の DBCC 結果。
オブジェクト "sys.sysrowsets" の 1 ページには 93 行あります。
‘sys.sysallocunits’ の DBCC 結果。
オブジェクト "sys.sysallocunits" の 2 ページには 107 行あります。
‘sys.sysfiles1’ の DBCC 結果。
オブジェクト "sys.sysfiles1" の 1 ページには 2 行あります。
‘sys.syspriorities’ の DBCC 結果。
オブジェクト "sys.syspriorities" の 0 ページには 0 行あります。
‘sys.sysfgfrag’ の DBCC 結果。
オブジェクト "sys.sysfgfrag" の 1 ページには 2 行あります。
‘sys.sysphfg’ の DBCC 結果。
オブジェクト "sys.sysphfg" の 1 ページには 1 行あります。
‘sys.sysprufiles’ の DBCC 結果。
オブジェクト "sys.sysprufiles" の 1 ページには 2 行あります。
‘sys.sysftinds’ の DBCC 結果。
オブジェクト "sys.sysftinds" の 0 ページには 0 行あります。
‘sys.sysowners’ の DBCC 結果。
オブジェクト "sys.sysowners" の 1 ページには 14 行あります。
‘sys.sysprivs’ の DBCC 結果。
オブジェクト "sys.sysprivs" の 1 ページには 130 行あります。
‘sys.sysschobjs’ の DBCC 結果。
オブジェクト "sys.sysschobjs" の 1 ページには 56 行あります。
‘sys.syscolpars’ の DBCC 結果。
オブジェクト "sys.syscolpars" の 8 ページには 488 行あります。
‘sys.sysnsobjs’ の DBCC 結果。
オブジェクト "sys.sysnsobjs" の 1 ページには 1 行あります。
‘sys.syscerts’ の DBCC 結果。
オブジェクト "sys.syscerts" の 0 ページには 0 行あります。
‘sys.sysxprops’ の DBCC 結果。
オブジェクト "sys.sysxprops" の 0 ページには 0 行あります。
‘sys.sysscalartypes’ の DBCC 結果。
オブジェクト "sys.sysscalartypes" の 1 ページには 34 行あります。
‘sys.systypedsubobjs’ の DBCC 結果。
オブジェクト "sys.systypedsubobjs" の 0 ページには 0 行あります。
‘sys.sysidxstats’ の DBCC 結果。
オブジェクト "sys.sysidxstats" の 2 ページには 158 行あります。
‘sys.sysiscols’ の DBCC 結果。
オブジェクト "sys.sysiscols" の 2 ページには 309 行あります。
‘sys.sysbinobjs’ の DBCC 結果。
オブジェクト "sys.sysbinobjs" の 1 ページには 23 行あります。
‘sys.sysaudacts’ の DBCC 結果。
オブジェクト "sys.sysaudacts" の 0 ページには 0 行あります。
‘sys.sysobjvalues’ の DBCC 結果。
オブジェクト "sys.sysobjvalues" の 22 ページには 159 行あります。
‘sys.sysclsobjs’ の DBCC 結果。
オブジェクト "sys.sysclsobjs" の 1 ページには 16 行あります。
‘sys.sysrowsetrefs’ の DBCC 結果。
オブジェクト "sys.sysrowsetrefs" の 0 ページには 0 行あります。
‘sys.sysremsvcbinds’ の DBCC 結果。
オブジェクト "sys.sysremsvcbinds" の 0 ページには 0 行あります。
‘sys.sysxmitqueue’ の DBCC 結果。
オブジェクト "sys.sysxmitqueue" の 0 ページには 0 行あります。
‘sys.sysrts’ の DBCC 結果。
オブジェクト "sys.sysrts" の 1 ページには 1 行あります。
‘sys.sysconvgroup’ の DBCC 結果。
オブジェクト "sys.sysconvgroup" の 0 ページには 0 行あります。
‘sys.sysdesend’ の DBCC 結果。
オブジェクト "sys.sysdesend" の 0 ページには 0 行あります。
‘sys.sysdercv’ の DBCC 結果。
オブジェクト "sys.sysdercv" の 0 ページには 0 行あります。
‘sys.syssingleobjrefs’ の DBCC 結果。
オブジェクト "sys.syssingleobjrefs" の 1 ページには 146 行あります。
‘sys.sysmultiobjrefs’ の DBCC 結果。
オブジェクト "sys.sysmultiobjrefs" の 1 ページには 106 行あります。
‘sys.sysguidrefs’ の DBCC 結果。
オブジェクト "sys.sysguidrefs" の 0 ページには 0 行あります。
‘sys.syscompfragments’ の DBCC 結果。
オブジェクト "sys.syscompfragments" の 0 ページには 0 行あります。
‘sys.sysftstops’ の DBCC 結果。
オブジェクト "sys.sysftstops" の 0 ページには 0 行あります。
‘sys.sysqnames’ の DBCC 結果。
オブジェクト "sys.sysqnames" の 1 ページには 97 行あります。
‘sys.sysxmlcomponent’ の DBCC 結果。
オブジェクト "sys.sysxmlcomponent" の 1 ページには 99 行あります。
‘sys.sysxmlfacet’ の DBCC 結果。
オブジェクト "sys.sysxmlfacet" の 1 ページには 112 行あります。
‘sys.sysxmlplacement’ の DBCC 結果。
オブジェクト "sys.sysxmlplacement" の 1 ページには 18 行あります。
‘sys.sysobjkeycrypts’ の DBCC 結果。
オブジェクト "sys.sysobjkeycrypts" の 0 ページには 0 行あります。
‘sys.sysasymkeys’ の DBCC 結果。
オブジェクト "sys.sysasymkeys" の 0 ページには 0 行あります。
‘sys.syssqlguides’ の DBCC 結果。
オブジェクト "sys.syssqlguides" の 0 ページには 0 行あります。
‘sys.sysbinsubobjs’ の DBCC 結果。
オブジェクト "sys.sysbinsubobjs" の 1 ページには 3 行あります。
‘sys.syssoftobjrefs’ の DBCC 結果。
オブジェクト "sys.syssoftobjrefs" の 0 ページには 0 行あります。
‘sys.queue_messages_1977058079’ の DBCC 結果。
オブジェクト "sys.queue_messages_1977058079" の 0 ページには 0 行あります。
‘sys.queue_messages_2009058193’ の DBCC 結果。
オブジェクト "sys.queue_messages_2009058193" の 0 ページには 0 行あります。
‘sys.queue_messages_2041058307’ の DBCC 結果。
オブジェクト "sys.queue_messages_2041058307" の 0 ページには 0 行あります。
‘sys.filestream_tombstone_2073058421’ の DBCC 結果。
オブジェクト "sys.filestream_tombstone_2073058421" の 0 ページには 0 行あります。
‘sys.syscommittab’ の DBCC 結果。
オブジェクト "sys.syscommittab" の 0 ページには 0 行あります。
‘LockEscalationEvent’ の DBCC 結果。
オブジェクト "LockEscalationEvent" の 1 ページには 1 行あります。
‘Table_1’ の DBCC 結果。
メッセージ 8928、レベル 16、状態 1、行 1
オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data): ページ (1:114) を処理できませんでした。詳細については、他のエラーを参照してください。
メッセージ 8939、レベル 16、状態 98、行 1
テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)、ページ (1:114)。テスト (IS_OFF (BUF_IOERR, pBUF->bstat)) が失敗しました。値は 12716041 と -4 です。
メッセージ 8976、レベル 16、状態 1、行 1
テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)。ページ (1:114) がスキャンでは見つかりませんでしたが、このページは親ページ (1:93) と前ページ (1:110) から参照されています。以前に発生したエラーをすべて確認してください。
メッセージ 8978、レベル 16、状態 1、行 1
テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)。ページ (1:115) に前ページ (1:114) からの参照がありません。チェーン リンケージに問題がある可能性があります。
オブジェクト "Table_1" の 9 ページには 9 行あります。
CHECKDB により、テーブル ‘Table_1’ (オブジェクト ID 2137058649) に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。
CHECKDB により、データベース ‘TEST’ に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。
repair_allow_data_loss は DBCC CHECKDB (TEST) で見つかったエラーの最小修復レベルです。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。

DBCC CHECKDB を実行することで、データベースの状態をチェックしてくれますので、破損している領域を操作しなくても異常があるかどうかの確認をしてくれます。

イベントビューアーと SQL Server のログにも結果の概要が出力されますので、後で結果を再確認することも可能です。
image
image

SQL Server のダンプのテキストも出力されています。
image

[msdb..suspect_pages] を SELECT することで直近の情報を取得することもできます。
image

 

DBCC CHECKDB をバックアップ実行前に実施する理由ですが、[障害が発生しているデータベースのバックアップは障害が発生した状態] となるからです。

障害が発生していない状態のデータベースのバックアップがないと破損している領域を修復することができません。
# SQL Server 2008 以降のデータベースミラーリングであれば、破損ページを読み込んだ際にミラー側から修復することも可能ですが。

そのため、バックアップを取得する前にデータベース自体に異常が無いかを確認して、その後にバックアップを取ることが推奨されます。

セミナーの中で質問 / 回答があったのですが、DBCC CHECKDB によるデータベースの整合性確認はデータベースのサイズが大きいとかなり負荷がかかります。
そのため、バックアップのたびに毎回実行するのが難しいかもしれませんね。

次の投稿では [データベースの整合性確認の負荷] についてまとめてみたいと思います。

Written by masayuki.ozawa

12月 22nd, 2010 at 8:24 pm

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

3 comments

今回は [バックアップポリシー] について振り返っていきたいと思います。
# 参加から 2 週間経過したのでテンポよくまとめていかないとだいぶ記憶が薄れてきました…。

セミナーでは以下のような図を元にバックアップポリシーについてのお話しがありました。

image

 

■バックアップの基本的な設定

上の図は復旧モデルを [完全] または [一括ログ] にした場合のバックアップの基本的な設定になります。
# [単純] 復旧モデルの場合は、ログのバックアップが取れませんので、データベースのバックアップのみになります。

セミナーでは日に一度のデータベースバックアップと、一時間おきのログのバックアップで設定というような例でお話をされていたと思います。

SQL Server のバックアップは大きく分けて 3 種類あります。
# ファイル / ファイルグループでのバックアップも取得ができるのですが、今回は割愛しておきます。

  • 完全
  • 差分
  • トランザクションログ

完全 / トランザクションログは上の図で [データベース] [ログ] と書かれているバックアップになります。

[完全バックアップ] は、その時点 (バックアップを実行したタイミング) でのデータまで復元することが可能なバックアップとなります。
完全バックアップはそのバックアップ単体で戻すことが可能ですのでバックアップにはログも含まれています。

 

[差分バックアップ] は、前回の完全バックアップからの差分データのみを含んだバックアップになります。
完全バックアップを元にした差分になりますので、単体で戻すことはできず必ず前回の完全バックアップが必要となります。
image
また、差分バックアップは前回の完全バックアップからの差分データですので、完全バックアップを取得してからの期間が長くなり、全データが更新された状態になってしまうと、完全バックアップ相当のバックアップファイルのサイズとなります。

 

[トランザクションログバックアップ] は、ログレコードのバックアップになります。
復旧モデルのときにもお話しがあったのですが、復旧モデルを [完全] [一括ログ] にしている場合は必須になるバックアップとなります。
これらの復旧モデルに関しては、ログレコードは自動的に切り捨てはされず、バックアップを取得したタイミングで切り捨てが行われ、切り捨てられた個所が再利用可能となります。
そのため、ログのバックアップを取得しないとログがずっと蓄積された状態となり、ログ領域のディスクが枯渇することになります。
ログのバックアップは障害発生時に重要となるバックアップのため、できるだけ短い間隔で取得することが重要であるとセミナーではお話がありました。
# 1 時間おきのログのバックアップというお話はこのタイミングであった気がします。
データベースの完全バックアップを取得していない状態ではログバックアップは取得することはできません。
image

 

バックアップについてですが、以下の資料がわかりやすいと思います。
SQL Server 2000 の資料ですが、それ以降のバージョンでも考え方は同じですので。
第 4 章 バックアップと復元

 

■定期的なバックアップの設定

定期的なバックアップの設定方法として、[メンテナンスプランウィザード] によるバックアップの設定が紹介されていました。

ワンタイムのバックアップであれば、データベースを右クリックして、[タスク] → [バックアップ] からバックアップを取得することが可能です。
image

image

このバックアップに関しては、ワンタイムになりますので定期的なバックアップの取得の設定はできません。

定期的なバックアップをウィザード形式で設定する場合は、セミナーで紹介のあった [メンテナンスプランウィザード] を使用する事で設定ができます。

メンテナンスプランウィザードは [管理] → [メンテナンス プラン] → [メンテナンス プラン ウィザード] から起動することができます。
image

メンテナンス プラン ウィザードを使用することで、データベースのメンテナンスをする仕組みを数ステップで設定が可能となります。
image

今回のセミナーでお話しのあったメンテナンスプランに似たものを実際に作ってみたいと思います。

  1. [次へ] をクリックします。
    image
  2. [タスクごとに個別のスケジュールを使用する] を選択して、[次へ] をクリックします。
    image
  3. [データべすのバックアップ (完全)] [データベースのバックアップ (トランザクション ログ)] を選択して、[次へ] をクリックします。
    image
  4. [次へ] をクリックします。
    image
  5. [完全] バックアップの取得対象とバックアップのスケジュールを設定し、[次へ] をクリックします。
    今回は [すべてのデータベース] を毎日 [22:00] に取得するように設定をしています。
    image
  6. トランザクションログのバックアップを取得するデータベースとスケジュールを選択し、[次へ] をクリックします。
    今回は、[すべてのユーザー データベース] を [毎日1 時間置き] にバックアップを取得するように設定しています。
    すべてのユーザー データベースですが復旧モデルが単純以外のデータベースが対象となります。
    image
  7. ログの出力場所を設定し、[次へ ]をクリックします。
    image
  8. [完了] をクリックします。
    image
  9. [閉じる] をクリックします。
    image

以上でメンテナンスプランの作成は完了です。

今回は、[完全 バックアップ] の取得と、[トランザクションログ] のバックアップの取得を設定していますので二つのジョブが SQL Server エージェントに作成されています。

image

作成した 2 つのジョブを手動で実行して、バックアップの取得状況を確認してみたいと思います。
ジョブを手動実行する場合は、対象のジョブを右クリックして、[ステップでジョブを開始] をクリックします。
image

今回は、H ドライブにアックアップを取得しているのですが、データベースのバックアップ (.bak) とトランザクションログのバックアップ (.trn) が取得されていることが確認できます。
image

今回のメンテナンスプランですが、バックアップを取得するたびにタイムスタンプが設定されたバックアップファイルが蓄積されていきます。
image

また、ログに関してもバックアップを実行するたびに蓄積されていきます。
image

このバックアップとログの履歴 (世代) 管理ですが、[メンテナンス クリーンアップ タスク] を使用することで管理することができます。
先ほど作成したメンテナンスプランにこの履歴クリーンアップタスクを組み込んでみたいと思います。

作成したメンテナンスプランは、[管理] → [メンテナンス プラン] の下に作成がされますので、作成したプランをダブルクリックします。
image

そうするとメンテナンスプランのデザインウィンドウが開きます。
image

先ほど作成したプランが、[Subplan_1] [Subplan_2] として設定されています。
今回は [サブプランの追加] をクリックして、[Subplan_3] として[メンテナンス クリーンアップ タスク] を設定します。
image

タスクをダブルクリックすると設定画面になります。
このタスクを使用することで、バックアップファイルとメンテナンスプランのレポートを削除することが可能です。
拡張子単位で指定はできるのですが、ワイルドカードは使えないので、[bak] [tran] 用のバックアップファイル削除タスクを用意します。
imageimage

テキストレポートのクリーンアップタスクとしては以下の内容を設定しました。
image

今回はスケジュールも設定し、このような形でサブプランを設定してみました。
image

今回は 4 週経過したファイルが対象になっていますので、以下の PowerShell を実行して、ファイルの更新日付を変更してみました。
# クリーンアップタスクでファイル操作をする際の日付はファイルの更新日付が検索対象となります。

Get-Item "C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLLogMaintenancePlan_Subplan_1_20101220221815.txt" | Set-ItemProperty -name LastWriteTime -Value (Get-Date).AddDays(-28)

これで、メンテナンスレポートの日付が 28 日前 (4 週間前) になりました。
image

それでは、作成したクリーンアップタスクんのジョブを実行してみます。
ジョブを実行したことで、先ほどファイルの更新日付を変更したメンテナンスレポートが削除されていることが確認できます。
image

バックアップ ファイルに関しても更新日付が条件に一致するのであれば削除されます。
image
image

メンテナンス プラン ウィザードを使用することでバックアップのメンテナンスプランを GUI のウィザードベースで柔軟に設定することが可能となります。

今回の投稿では設定をしなかったのですが、メンテナンスプランには [データベースの整合性確認] というメンテナンス タスクがあります。

セミナーの中ではこのタスクについてもお話がありました。

次の投稿では、データベースの整合性確認についてまとめてみたいと思います。

Written by masayuki.ozawa

12月 20th, 2010 at 9:52 pm

第 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

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

leave a comment

今回は [自動拡張] について振り返ってみたいと思います。。

セミナーのログ管理のセッションでは [自動拡張] についてのお話がありました。

SQL Server のデータファイル / ログファイルには [自動拡張] という設定があります。
自動拡張を設定することでファイルの空きが無くなったときにディスクに空きがある場合、自動的に拡張するように設定することができます。
image

自動拡張のサイズは、[現在のサイズに対しての比率 (%)]  と [MB 単位] で、最大サイズに関しては [MB 単位][無制限] で指定することができます。

それでは、ログファイルの自動拡張についてまとめていきたいと思います。

■ログファイルの自動拡張について

ログファイルは以下の構成となっています。

image

自動拡張が発生すると以下のように拡張がされます。
# 手動拡張でも同様です。
image

拡張が発生すると拡張分のディスクサイズが確保されます、その拡張分が仮想ログ (VLF) で分割されます。
上の絵では、VLF が 4 つで構成された形になっていますが、拡張するサイズによって 4 / 8 / 16 のどの個数で分割するかが決まります。
そのため、細かな拡張を繰り返すとログファイルを構成する VLF の個数が多くなりパフォーマンスに影響が出てきます。
# これに関しては別の機会にまとめようと思っています。

それでは、実際の環境で自動拡張を見ていきたいと思います。

現在、ログは 13,041,644 Byte の 2 個の VLF で構成がされています。
# VLF は作成 / 拡張時は最小で 4 個になるのですが、ファイルの圧縮 (SHRINK FILE) をすることで最小で 2 個にできます。
image

データベースのプロパティからみた設定はこのようになっています。
image

それでは、データを追加 (INSERT) して、ログファイルの自動拡張を見ていきたいと思います。
image

このグラフはパフォーマンスモニタで、[Log File(s) Used (KB)] と、[Log Growths] を取得したものになります。
# Log Growths に関しては SQL Server のサービスが最後に起動してからの値だったはずなので、0 からは始まっていません。

ログファイルの現在のサイズと使用状況がこちらになります。
image

25MB だったものが、925MB になっています。
今回は、100MB 単位で自動拡張をするように設定していますので、 9 回の拡張が発生したことになります。
上のグラフでも自動拡張の発生回数は 4 → 13 になっていますので回数は合っていますね。

仮想ログの状態も見てみたいと思います。
image

この下にもデータは続いており、全部で 74 個の仮想ログがあります。
100MB の拡張だと一度の拡張に対して仮想ログが 8 個作成されます。
そのため
8 個 × 拡張 9 回 = 72 個 + 初期の 2 個 = 74 個
の仮想ログが作成されたことになります。

 

自動拡張に関しては、SQL Server のデフォルト トレースにも出力がされます。
デフォルト トレースは SQL Server をインストールしたインスタンスのディレクトリの [LOG] フォルダに出力がされます。
私が今回使用している環境では以下のディレクトリになります。
[C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLLog]

デフォルト トレースは SQLTrace の .trc ファイルですので [SQL Server Profiler] で開くか [fn_trace_gettable ] を使用してクエリで情報を取得することができます。

今回はfn_trace_gettable を使用して情報を取得したいと思います。

DECLARE @filename varchar(500)
SELECT @filename = path FROM sys.traces WHERE is_default = 1
SELECT @filename
SELECT
    convert(int, EventClass) as EventClass,
    DatabaseName,
    Filename,
    (Duration/1000) as Duration,
    StartTime,
    EndTime,
    (IntegerData*8.0/1024) as ChangeInSize
FROM
    sys.fn_trace_gettable(@filename, default)
WHERE
    EventClass >=  92
    AND
    EventClass <=  95
    AND
    DatabaseName = DB_NAME()
ORDER BY
    StartTime DESC

 

実行後の結果はこちらになります。
image

今回は、20:35 ~ 20:37 に実行していますので、デフォルト トレースにもこの時間帯のログは 9 件出力されています。

自動拡張を設定していない場合は、以下のようになります。
image

ログファイルの上限まで達したら拡張ができないため、上限に達した後の追加処理はエラーとなります。

 

自動拡張を設定しておくことで、当初想定していたログファイルでは足りなくなったときでも処理を継続することができるようになります。
セミナーではログの拡張時には [ログの書き込み待ち] が発生するため、拡張は発生させないように最初から適切なサイズを設定するというお話がありました。

続いてログの書き込み待ちについてみていきたいと思います。

■拡張時のログの書き込み待ち

自動拡張だけに限らずログの拡張が行われている際にはログの書き込みがブロックされ、拡張が終わるまで待ち状態となります。
[自動拡張のログファイルの使用状況] のグラフで拡張が発生している際に、ログファイルの使用状況が一瞬止まって入るのが確認できます。
# さほど負荷をかけていない検証なので、本当に一瞬ですが。

自動拡張ではログの書き込み待ちを確認するのが難しいので、データの追加を行っている最中に、10GB のファイル拡張を行って検証をしてみたいと思います。
# 自動チェックポイントの実行は無効にしています。

拡張をしないでデータの追加を行った場合は以下のようなグラフになります。
ログファイルの使用状況も停止することなく上がっており、ログの書き込み待ちに関しては発生していません。

image

データの追加中に10GB のログの拡張を行ったものがこちらになります。
ログファイルの使用状況が平らになっている部分が拡張を実行したタイミングになります。
ログの拡張中は使用状況の上がりも鈍り、書き込み待ちの秒数が発生しているのが確認できます。

image

それでは、この書き込み待ちを [WRITELOG] の待ち事象からも見てみたいと思います。
WRITELOG の待ち事象を取得するには以下のクエリを実行します。

SELECT * FROM sys.dm_os_wait_stats WHERE wait_type = ‘WRITELOG’

こちらが通常の追加を行う前後の待ち事象の状態になります。

image

image

処理を行っている間に
waiting_tasks_count = 501,013 – 400,927 = 100,086
wait_time_ms = 306,407 – 248,635 = 57,772
の WRITELOG に関しての待ち事象が発生していたのが確認できます。

それでは、拡張をした場合はどうなるでしょう。
image
image
waiting_tasks_count = 607,419 – 507,039 = 100,380
wait_time_ms = 378,033 – 310,391 = 67,642

というようにログの書き込み待ちによる待ち事象が増加していることが確認できます。

image

image

余談ですが、ファイルの瞬時初期化 (SQL Server のサービス起動アカウントに [ボリュームの保守タスクを実行] を付与) に関しては BOL に [ログ ファイルの初期化ではそのまま空にする必要がありますが~]  と書かれているように有効にしても効果は出ません。

image
image

waiting_tasks_count = 100,397 – 13  = 100384
wait_time_ms = 67,068 – 86 = 66,982

ログ管理に置いて自動拡張は保険として考え、運用中は発生しないようにすることでログの書き込み待ちを減らし、パフォーマンスの向上につなげることが可能となります。

セミナーでは、ログ管理のもしもの時の備えとして、[パフォーマンス条件警告] を使用したログの管理のお話がありました。

次の投稿では、パフォーマンス条件警告とその簡単な応用についてまとめてみたいと思います。

Written by masayuki.ozawa

12月 15th, 2010 at 10:38 pm

Posted in セミナー

Tagged with ,

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

leave a comment

ログ管理の中でお話頂いた [復旧モデル] について振り返ってみます。
今回は復旧モデルによる、ログの使用状況の違いをまとめて見たいと思います。

SQL Server には復旧モデルという考えがあり、この復旧モデルがリストア時に復旧可能なタイミングとトランザクションログの管理 (解放のタイミング) に影響します。

復旧モデルには、以下の 3 種類があります。

  • 完全
  • 一括ログ
  • 単純

完全復旧モデルと一括ログ復旧モデルに関しては、定期的なバックアップによるログの管理が必要となり、単純復旧モデルに関しては特定のタイミングで自動的に解放 (チェックポイント時のログの切り捨て) が行われます。
そのため、完全 / 一括ログ復旧モデルでないとログを使用したリストアを実施することはできません。

まずは、完全復旧モデルと単純復旧モデルのログの利用のされ方について確認してみたいと思います。
今回は、ログファイルを 30MB にして自動拡張を無効にした状態で大量のデータを追加 (INSERT) してテストをしています。

■チェックポイント発生時のログの切り捨て

完全復旧モデルの場合には、チェックポイントが発生してもログは切り捨てられません。そのため、トランザクションログの使用状況が上限に達した際には、トランザクションログが上限に達したというメッセージが表示されそれ以上のデータの追加ができなくなります。
# 一括ログ復旧モデルもチェックポイント時のログ切り捨ては行われないので、同じトレンドになります。

image
メッセージとしては以下のエラーが出力されます。

メッセージ 9002、レベル 17、状態 2、行 2
データベース ‘TEST’ のトランザクション ログがいっぱいです。ログの領域を再利用できない理由を確認するには、sys.databases の log_reuse_wait_desc 列を参照してください。

[sys.databases] を確認すると以下の情報を取得することができます。
image
今回の場合、ログの領域が再利用できない理由はログ領域がフルになってしまい、バックアップの必要があるという事を確認できます。

 

それでは、単純復旧モデルにした場合にはログの使用状況はどうなるでしょう。
image

データの追加操作としては全く同じ内容を実行したのですが、単純復旧モデルの場合はチェックポイントの発生タイミングに合わせてログの使用状況が下がっていることが確認できます。

この挙動を [チェックポイント発生時のログの切り捨て] と呼びます。
復旧モデルが単純復旧モデルとなっているデータベースはログのバックアップを取得することができません。
そのため、チェックポイントが発生してメモリ上のダーティーページをディスクに書き込んだタイミングでそれまでのログレコードが不要 (変更箇所がデータファイルに書き込まれているため) となり切り捨てることが可能になります。
チェックポイントの発生ですが、バックアップの取得 / 手動による [CHECKPOINT] ステートメントの実行 / [復旧間隔] の設定に応じた自動チェックポイントなどで発生がします。
今回発生しているチェックポイントは自動チェックポイントにより実行されています。
# 最後の一回だけは私が手動でチェックポイントを発生させているので山の形が少し変わっています。
余談ではありますが自動チェックポイントはトレースフラグ [3505] を設定することで無効にすることもできます。
INF: Use Trace Flag 3505 to Control SQL Server Checkpoint Behavior

 

■完全復旧モデルと一括ログ復旧モデルのログの使用状況の違い

それでは、次に完全復旧モデルと一括ログ復旧モデルの違いを確認してみたいと思います。
この復旧モデルの違いですが、最小ログ記録が可能な操作をした際のログの書き込みが変わってきます。

完全復旧モデルでは、すべての操作のログが完全に記録されますが、一括ログ復旧モデルでは最初ログ記録が可能な操作をした場合は、ログは最小限のもののみ記録がされます。
最小ログ記録が可能な操作
# インデックス系の操作は一括ログ復旧モデルでないと、最小ログにならないと思っていたのですが上記の内容を読むとそうでもないらしいですね。別の機会で検証したいと思います。また、操作方法によっては完全復旧にしていても最小ログになってしまったので今回は完全復旧モデルでは意図的に最小ログ記録にならない操作をしています。

 

今回は、[BULK INSERT] を使って違いを確認してみたいと思います。
image
image

どちらの復旧モデルでも波形は一緒になってしまっていますね。
これですが、今回は以下のようなクエリを実行していました。

BULK INSERT Table_1 FROM ‘F:Dataexport.txt’

BULK INSERT を実行する場合、上記のクエリでは最小ログ記録での動作にはなりません。
そのため、一括ログ復旧モデルでも完全なログの記憶動作となり、両復旧モデルで同じ波形となっています。

BULK INSERT に限った話ではないのですが、一括インポート時のログを最小ログ記録にするためには条件があり、[テーブルロックを指定] する必要があります。
一括インポートで最小ログ記録を行うための前提条件
# 他にも条件はあるのですが、今回はインデックスを持たない 1 列の単純なヒープ構造のテーブルを使用しています。

それでは、クエリを以下のように書き換えて実行をしてみます。

BULK INSERT Table_1 FROM ‘F:Dataexport.txt’ WITH (TABLOCK)

image

波形だけをみると一緒の波形になっているのですが、ログファイルの使用状況には大きな差が出ています。
テーブルロックを使用する前はログファイルの使用状況は 30MB まで上昇していました。
テーブルロックを使用した場合は、20KB 程度の使用で処理を完了することができています。
# 実は、テーブルロックを使用しなかった場合は、ログがフルになっていたりもしたのですが。

それでは、実際のログレコードから完全ログ記録と最小ログ記録の違いを比較してみたいと思います。

[完全ログ記録]
image

[最小ログ記録]
image

完全ログ記録の場合は [LOP_INSERT_ROWS] という形で、追加した行単位でログレコードを書き込んでいきます。
最小ログ記録の場合は、[LOP_SET_BITS] という形で行を追加するにあたって [変更のあったエクステント] の情報のみを書き込んでいます。
最小ログ記録が発生する、一括ログ操作に関しての変更は BCM (一括変更マップ : Bulk Changed Map) という領域を使用して管理されるためこのような動きになります。

使用する復旧モデルによって、ログの使用状況に差が出てきます。
単純復旧モデルを使用した場合はチェックポイント発生時に解放できるログは解放されますが、それ以外の復旧モデルに関してはログのバックアップを取得ないと使用している領域は解放されません。

ログはログのバックアップのタイミングを考慮したうえで、そのタイミングで解放される領域で処理がうまく回るようなサイズを指定しておくことで、ログがフルにならず処理を継続することが可能となります。

ただし、一時的なトランザクションの増加に備えて、[自動拡張] を設定して保険とすることも考えられれます。
セミナーの中では自動拡張時のログの書き込み待ちについても触れられていました。

次の投稿では [自動拡張] についてお話しいただいたことについて振り返ってみたいと思います。

Written by masayuki.ozawa

12月 14th, 2010 at 11:50 pm