SE の雑記

SQL Server のクラスタ構成では Is Alive のチェックで定期的な間隔 (デフォルトは 1 分) で

SELECT @@servername

が実行され、クラスタリソースの起動状況の確認がされます。

Windows Server 2003 上で SQL Server を MSCS で構築した場合は、Is Alive のチェックには、
クラスタサービスのサービスアカウント (ドメインユーザー) が使用されていました。
そのため、SQL Server のログインからクラスタのサービスアカウントを消してしまうとリソースが
起動できなくなります。

Windows Server 2008 の WSFC では、クラスタのサービスの起動アカウントはドメインユーザーではなく、
ローカルシステムアカウントで起動されます。

Windows Server 2008 だと、Is Alive のチェックはローカルシステムアカウントで実行されているのか
気になって調べてみました。

手っ取り早く調べるため、SQL Server Profiler を実行して確認してみました。

WSFC + SQL Server ではローカルシステムアカウントで Is Alive のチェックが実行されているようですね。

クラスタのサービスアカウントでチェックがされるという動作は WSFC でも変わらないようです。

すこし時間が空いてしまいましたが、件数を絞って削除するパターンで時間を計測してみたいと思います。

今回のテスト用のテーブルには date 型のフィールドを設定しています。

レコード削除時に日付を指定して削除を実行してみます。

– TOP を指定しないで範囲削除 –

SET NOCOUNT ON USE [WORK] DELETE FROM ??? [dbo].[Tbl1] WHERE ??? [Col3] BETWEEN ‘2040-01-01’ AND ‘2100-12-31’

– 削除の処理時間 –

1 回目	11 秒
2 回目	10 秒
3 回目	14 秒

?

続いて TOP と ROWCOUNT を指定して削除してみます。

– 10,000 件ずつ削除 –

SET NOCOUNT ON USE [WORK] WHILE(0=0) BEGIN ??? DELETE TOP(10000) ??? FROM ??????? [dbo].[Tbl1] ??? WHERE ??????? [Col3] BETWEEN ‘2040-01-01’ AND ‘2100-12-31’ ??? IF @@ROWCOUNT = 0 ??? BEGIN ??????? BREAK ??? END END

?

– 削除の処理時間 –

1 回目	28 秒
2 回目	23 秒
3 回目	20 秒

特定件数ずつ削除したほうが遅いですね。

単純復旧モデルでチェックポイントを明示的に発生させながら、ログを切り捨てる場合以外は、
件数を指定して削除させるメリットはないかもしれないですね。

今まで処理効率が良いのかなと考えていたのですが違っていたようで。

SQL Server 2005 以降では DELETE TOP と ROWCOUNT を使用してループの中で指定した件数ずつ
削除することができるようになっています。
# 2000 では SET ROWCOUNT で実施できますね。

一度の DELETE で一括削除した場合と TOP で指定した件数ずつ削除していった場合でどれくらい
処理時間に差がでるか気になったので試してみました。

まずは全件削除をした場合から。
# TRUNCATE でページのビットマップを解除するのが一番早いですが。

今回使用するテーブルのスクリプトは以下になります。

– テストテーブルのスクリプト –

USE [WORK] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Tbl1]( ??? [Col1] [uniqueidentifier] NOT NULL, ??? [Col2] [nchar](4000) NULL, ??? [Col3] [date] NULL, CONSTRAINT [PK_Tbl1] PRIMARY KEY CLUSTERED ( ??? [Col1] ASC )WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_Tbl1] ON [dbo].[Tbl1] ( ??? [Col3] ASC )WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY] GO

?

今までは GUID と nchar の単純なテーブルだったのですが、今回は date を追加して、非クラスタ化インデックスを
設定しています。

こちらのテーブルデータを挿入します。
# 復旧モデルを単純にしてログを自動切り捨てにし、断片化を解消しています。

– データの挿入 –

SET NOCOUNT ON USE [WORK] ALTER DATABASE [WORK] SET RECOVERY SIMPLE TRUNCATE TABLE [dbo].[Tbl1] DECLARE @i INT = 0 DECLARE @date date = ‘2000-01-01’ WHILE (@i < 50000) BEGIN ??? INSERT INTO [dbo].[Tbl1] VALUES (NEWID(), NCHAR(@i), DATEADD(day, @i, @date)) ??? SET @I += 1 END ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD ALTER INDEX [IX_Tbl1] ON [dbo].[Tbl1] REBUILD

?
各削除の処理を実行する前に、チェックポイントとキャッシュのクリアしてから処理時間を計測しています。

– チェックポイントとキャッシュのクリア –

CHECKPOINT DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS

?

この状態でデータを一括削除してみます。

– データの一括削除 –

SET NOCOUNT ON USE WORK DELETE FROM [dbo].[Tbl1]

?

実行にかかった時間が以下になります。
処理時間は3 回計測しています。

– データの一括削除の処理時間 –

1 回目	16 秒
2 回目	15 秒
3 回目	13 秒

?

次は TOP と ROWCOUNT を使用して 1,000 件ずつ削除してみます。

– 1,000 件ずつ削除 –

SET NOCOUNT ON USE WORK WHILE(0=0) BEGIN ??? DELETE TOP(1000) FROM [dbo].[Tbl1] ??? IF @@ROWCOUNT = 0 ??? BEGIN ??????? BREAK ??? END END

?

– 1,000 件ずつ削除した場合の処理時間 –

1 回目	1 分 41 秒
2 回目	1 分 42 秒
3 回目	1 分 39 秒

?

1,000 件ずつ削除するとかなり処理が遅くなっていますね。

削除件数を増やして試してみました。

– 10,000 件ずつ削除した場合の処理時間 –

1 回目	13 秒
2 回目	13 秒
3 回目	21 秒

– 20,000 件ずつ削除した場合の処理時間 –

1 回目	9 秒
2 回目	14 秒
3 回目	15 秒

– 30,000 件ずつ削除した場合の処理時間 –

1 回目	13 秒
2 回目	17 秒
3 回目	15 秒

– 40,000 件ずつ削除した場合の処理時間 –

1 回目	7 秒
2 回目	13 秒
3 回目	16 秒

今まで、件数を絞って複数回削除したほうが早いのかとなんとなく思っていたのですがそれほど差は出ないみたいです。
# あまりにも細かい件数で削除すると処理時間が劣化するのはわかっていたのですが。

途中でトランザクションログを切り捨てるためにチェックポイントを発生させたい場合や、トランザクションの
セーブポイントを作りたい場合以外は件数を絞る必要はないかもしれないですね。

次は件数を絞って削除した際の比較をしてみたいと思います。

SQL Server のデータベースバックアップですが、完全バックアップではトランザクションログの切り捨てはされません。
トランザクションログの切り捨てを行うためには、トランザクションログのバックアップまたは、復旧モードを [単純] に
設定する必要があります。
ただし例外が一つだけあり、初回のデータベースバックアップ時にはトランザクションログが切り捨てられるようです。
まずはデータを挿入して、トランザクションログが使用されている状態にしてみました。
– トランザクションログの使用状況の取得 –

DECLARE @logspace TABLE( ??? DatabaseName sysname, ??? LogSize int, ??? LogSpaceUsed int, ??? Status int ) INSERT INTO @logspace EXEC (‘DBCC SQLPERF(”LOGSPACE”)’)SELECT * FROM @logspace WHERE DatabaseName = ‘WORK’

?
– 実行結果? –

DatabaseName	LogSize	LogSpaceUsed	Status
WORK	110	77	0

?
現在は 110 MB のトランザクションログに対して 77 % が使用されている状況です。
完全バックアップはまだ取得していませんので、差分バックアップのベース LSN も設定はされていません。
– 差分バックアップのベース LSN の取得 –

SELECT ??? [file_id], ??? [name], ??? [differential_base_lsn] FROM ??? [sys].[master_files] WHERE ??? [database_id] = DB_ID(N’WORK’)

?
– 実行結果? –

file_id	name	differential_base_lsn
1	WORK	NULL
2	WORK_log	NULL

?
それでは初回の完全バックアップを取得してみます。
– 完全バックアップの取得 –

BACKUP DATABASE [WORK] TO? DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLBackupWORK.bak’ WITH FORMAT, INIT,? NAME = N’WORK-完全 データベース バックアップ’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO

?
バックアップ取得後に DBCC SQLPERF(‘LOGSPACE’) を実行して再度、トランザクションログの使用状況を取得します。
– 実行結果 –

DatabaseName	LogSize	LogSpaceUsed	Status
WORK	110	3	0

?
初回のバックアップではログが切り捨てられているのが確認できます。
# 77 % の利用から 3 % の利用となっています。
完全バックアップを取得したので、差分バックアップのベース LSN も設定がされています。
– 実行結果? –

file_id	name	differential_base_lsn
1	WORK	62000000412300000
2	WORK_log	NULL

?
この状態で再度データを挿入して、データベースのバックアップを取得し、ログの使用状況を確認してみます。
– 実行結果 (バックアップの取得前) –

DatabaseName	LogSize	LogSpaceUsed	Status
WORK	110	79	0

?
– 実行結果 (バックアップの取得後) –

DatabaseName	LogSize	LogSpaceUsed	Status
WORK	110	79	0

?
– 実行結果 (バックアップ取得後の差分バックアップのベース LSN) –

file_id	name	differential_base_lsn
1	WORK	100000000353400043
2	WORK_log	NULL

?
2 回目以降の完全バックアップではトランザクションログの切り捨てはされていないことが確認できます。
差分バックアップのベース LSN は完全バックアップを取得したので更新されています。
# バックアップを取得したことをあらわそうと思い情報を取得しています。
トランザクションログのバックアップは完全バックアップが存在していない状況では取得することができません。
初回の完全バックアップではそれまでのトランザクションログのバックアップが存在しておらず、トランザクション
ログの切り捨てが行われてもログチェーンとしては問題がないためこのような動作になっているのかと。
SQL Server に慣れていない人が完全バックアップでトランザクションログが切り捨てられると考えてしまう理由が、
この動きにあるのかな～と電車の中でふと思ったので投稿してみました。
2010/12/9 追加
SQL CAT のブログで本件について記載されていました。
Transaction Log size does not match the size of the data being loaded.

ページの情報を確認するための DBCC コマンドとして、[DBCC PAGE] があります。

このコマンドですが、非公開 DBCC コマンドですのでヘルプを確認するためには以下のコマンドを実行します。

– ヘルプの確認 –

DBCC TRACEON(2588) DBCC HELP(‘PAGE’) DBCC TRACEOFF(2588)

?

– ヘルプの内容 –

dbcc PAGE ( {’dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

?

プリントオプションの設定は以下のなります。

• 0 : ページヘッダのみ表示
• 1 : ページヘッダ + データ部を表示
• 2 : ページヘッダ + データ部を 16 進数のダンプで表示
• 3 : 各行を個別に出力

DBCC PAGE でページ情報を出力するためには、トレースフラグ 3604 を有効にする必要があります。

試しに DCM のページ情報を出力してみたいと思います。

– ページ情報の取得 –

DBCC TRACEON(3604) DBCC PAGE (N’WORK’, 1, 6, 1) DBCC TRACEOFF(3604)

?

– 実行結果 –

PAGE: (1:6) BUFFER: BUF @0x00000000AAFD2A00 bpage = 0x00000000AA4A8000?????????? bhash = 0x0000000000000000?????????? bpageno = (1:6) bdbid = 5??????????????????????????? breferences = 0????????????????????? bUse1 = 33908 bstat = 0x2c00009??????????????????? blog = 0x9a212159??????????????????? bnext = 0x0000000000000000 PAGE HEADER: Page @0x00000000AA4A8000 m_pageId = (1:6)???????????????????? m_headerVersion = 1????????????????? m_type = 16 m_typeFlagBits = 0x0???????????????? m_level = 0????????????????????????? m_flagBits = 0x200 m_objId (AllocUnitId.idObj) = 99???? m_indexId (AllocUnitId.idInd) = 0??? Metadata: AllocUnitId = 6488064 Metadata: PartitionId = 0??????????? Metadata: IndexId = 0??????????????? Metadata: ObjectId = 99 m_prevPage = (0:0)?????????????????? m_nextPage = (0:0)?????????????????? pminlen = 90 m_slotCnt = 2??????????????????????? m_freeCnt = 6??????????????????????? m_freeData = 8182 m_reservedCnt = 0??????????????????? m_lsn = (15522:2954:10)????????????? m_xactReserved = 0 m_xdesId = (0:0)???????????????????? m_ghostRecCnt = 0??????????????????? m_tornBits = -1654911162 Allocation Status GAM (1:2) = ALLOCATED??????????????? SGAM (1:3) = NOT ALLOCATED?????????? PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED???????????????? ML (1:7) = NOT MIN_LOGGED??????????? DATA: Slot 0, Offset 0x60, Length 94, DumpStyle BYTE Record Type = PRIMARY_RECORD???????? Record Attributes =????????????????? Record Size = 94 Memory Dump @0x000000000B2EA060 0000000000000000:?? 00005e00 00000000 00000000 00000000 †..^…………. 0000000000000010:?? 00000000 00000000 00000000 00000000 †……………. 0000000000000020:?? 00000000 00000000 00000000 00000000 †……………. 0000000000000030:?? 00000000 00000000 00000000 00000000 †……………. 0000000000000040:?? 00000000 00000000 00000000 00000000 †……………. 0000000000000050:?? 00000000 00000000 00000000 0000††††††…………..?? Slot 1, Offset 0xbe, Length 7992, DumpStyle BYTE Record Type = PRIMARY_RECORD???????? Record Attributes =????????????????? Record Size = 7992 Memory Dump @0x000000000B2EA0BE 0000000000000000:?? 0000381f ffffffff ffffffff ffffffff †..8…………. 0000000000000010:?? ffffffff ffffffff ffffffff ffffffff †……………. 0000000000000020:?? ffffffff ffffffff ffffffff ffffffff †……………. ～ 省略 ～ 0000000000001F10:?? ffffffff ffffffff ffffffff ffffffff †……………. 0000000000001F20:?? ffffffff ffffffff ffffffff ffffffff †……………. 0000000000001F30:?? ffffffff ffffffff †††††††††††††††††††……..????????

?

[m_type = 16] となっていますので、このページは DCM です。

通常運用でページ情報を確認することはないと思いますが、差分バックアップや一括ログ操作の動作を確認するときには、
ページ情報を確認すると理解しやすくなります。
バックアップに関してはどこかのタイミングで投稿したいと思っていますので、その際に DCM / BCM についても
記載していきたいと思っています。

基本的な情報は以下の URL に記載されています。
ページとエクステントのアーキテクチャ

Books Online を見ながら SQL Server のデータページの管理領域についてまとめてみたいと思います。
SQL Server のデータページを管理するためのページとして以下の領域があります。

– 管理ページの種類 –

1. File Header Page
   そのファイルの属性に関する情報が格納されています
   
2. PFS (Page Free Space)
   各ページの割り当て状態、個々のページが割り当て済みかどうか、および各ページの空き領域の量が記録されます。
   0%、1 ～ 50%、51 ～ 80%、81 ～ 95%、96 ～ 100% の 5 段階で示します
   
3. GAM (Global Allocation Map)
   どのエクステントが既に割り当てられているかが記録されます。
   1 つの GAM で 64,000 のエクステント、つまり約 4 GB のデータが対象となります。
   
4. SGAM (Shared Global Allocation Map)
   混合エクステントとして使用中であり、1 ページ以上が未使用であるエクステントが記録されます。
   1 つの SGAM で 64,000 のエクステント、つまり約 4 GB のデータが対象となります。
   
5. DCM (Differential Changed Map)
   最後の BACKUP DATABASE ステートメント以降に変更されたエクステントが追跡されます。
   差分バックアップでは、DCM ページを読み取るだけで、変更されているエクステントを判断します。
   
6. BCM (Bulk Changed Map)
   最後の BACKUP LOG ステートメント以降に、一括ログ記録操作によって変更されたエクステントが追跡されます。
   データベースで一括ログ復旧モデルを使用している場合にのみ使用します。
   
7. IAM (Index Allocation Map)
   アロケーション ユニットが使用する 4 GB 分のデータベース ファイルのエクステントがマップされます。
   
8. Boot Page
   データベースの属性情報が格納されているデータベース ブート ページです。

SQL Server では上記のページを使用して使用済みのエクステント / 差分バックアップの取得対象
最小ログ操作時にトランザクションログのバックアップで取得されるエクステントが判断されます。

IAM が配置されるページ番号はランダムのようなのですが、他の管理領域に関してはページが決まっているようです。
# IAM はデータページの使用状況によって配置される場所が変わりますので。

ページ番号と種別をまとめると以下のようになります。
ページタイプは [DBCC PAGE] の実行結果の [m_type] の値です。

– ページの配置 –

ページ番号	0	1	2	3	4	5	6	7	8	9
種別	File Header Page	PFS	GAM	SGAM	?	?	DCM	BCM	?	Boot Page
ページタイプ	15	11	8	9	?	?	16	17	?	13

?

上記は MDF (プライマリ データファイル) の場合のページの配置になります。

NDF (セカンダリ データファイル) の場合は、

ページ番号	0	1	2	3	4	5	6	7
種別	File Header Page	PFS	GAM	SGAM	?	?	DCM	BCM
ページタイプ	15	11	8	9	?	?	16	17

?

となります。
NDF も基本的なページの配置は同じなのですが、[Boot Page] に関しては、プライマリデータファイルにしか存在していません。

ページの配置状況をみるのに便利なツールとして CodePlex の Internals Viewer というものがあります。
インストールも簡単にできますので、SQL Server のページ配置に関して興味があるかたはお試しください。
# SQL Server 2008 R2 November CTP の SSMS で試したらエラーになってしまいましたが、2005 / 2008 の SSMS で動作します。

DBCC PAGE という DBCC コマンドを使用することでページの情報を取得することもできますので、
次はこのコマンドを紹介してみたいと思います。

自宅の検証環境は Exchange 2010 RC で一部の役割を構築してあります。

RC を RTM にアップグレードできるかが気になったので試してみました。

RC → RTM へはアップグレードインストールができるみたいですね。

?
Exchange 2007 以降はマイグレーションが移行の基本だったのでアップグレード画面は新鮮でした。
以下はエッジトランスポートをアップグレードした時の手順となります。

1. [次へ] をクリックします。
   
2. [使用許諾契約書に同意します] を選択し、[次へ] をクリックします。
   
3. [アップグレード] をクリックします。
   
4. アップグレードが完了したら [終了] をクリックします。
   

以上でアップグレードは完了です。

RC から RTM へのアップグレードは数ステップでできるみたいです。