SE の雑記

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 へのアップグレードは数ステップでできるみたいです。

Exchange 2010 を少しずつですが触り始めています。

Exchange 2000 と Exchange 2010 の共存はできないのですが、どのタイミングではじかれるかが
気になったので試してみました。

Exchange 2000 が残っていると Exchange 2010 の組織の準備ではじかれるようですね。

組織の準備ですが、AD の構成パーティションの Exchange のサーバーの情報からバージョン情報を取得しているみたいですね。

具体的には以下の情報になるようです。

CN=<サーバー名>,CN=Servers,CN=<管理グループ名>,CN=Administrative Group,CN=<組織名>, CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=<ドメイン名>

?

Exchange 2000 のプロパティを開くと、以下の画像のように設定がされています。
[serialNumber] が Exchange のバージョン情報のようです。

ちなみに Exchange 2007 の場合は以下の設定となっています。

?

どの情報を確認しているのかが気になったので少し調べてみました。
# serialNumber を書き換えると Exchange 2000 が残っていても組織の準備を完了させることができるのですが、
　非サポート環境ですので、きっと正常に動かないかと。

日があいてしまいましたがインデックスの再構築時のロックの状態を見てみたいと思います。

– 再構築中のロックの取得 –

— ロックの取得状態をわかるようにトランザクションを開始 BEGIN TRAN — インデックスの再構築の実施 ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD — ロックの情報を取得 SELECT ??? DB_NAME(resource_database_id) AS [database_name], ??? RTRIM([resource_description]) AS [resource_description], ??? [resource_type],? ??? [resource_subtype], ??? [request_mode], ??? [request_type] FROM ??? [sys].[dm_tran_locks] WHERE ??? [request_session_id] = @@SPID — トランザクションの終了 — 再構築はトランザクション処理可能ですのでロールバックできます。 ROLLBACK TRAN

?

– 結果 –

database_name	resource_description	resource_type	resource_subtype	request_mode	request_type
WORK	?	OBJECT	?	IX	LOCK
WORK	?	DATABASE	DDL	S	LOCK
WORK	?	DATABASE	?	S	LOCK
WORK	?	OBJECT	?	IX	LOCK
WORK	?	OBJECT	?	IX	LOCK
WORK	?	DATABASE	ENCRYPTION_SCAN	S	LOCK
WORK	(0000f26a94c1)	KEY	?	X	LOCK
WORK	?	OBJECT	?	IX	LOCK
WORK	1:55	PAGE	?	X	LOCK
～ 省略 ～	?	?	?	?	?
WORK	1:118	PAGE	?	X	LOCK
WORK	1:119	PAGE	?	X	LOCK
WORK	1:114	PAGE	?	X	LOCK
WORK	1:115	PAGE	?	X	LOCK
WORK	1:115:4	RID	?	X	LOCK
WORK	1:115:5	RID	?	X	LOCK
WORK	1:127	PAGE	?	X	LOCK
WORK	1:115:6	RID	?	X	LOCK
WORK	1:115:7	RID	?	X	LOCK
WORK	1:115:0	RID	?	X	LOCK
WORK	1:115:1	RID	?	X	LOCK
WORK	1:115:2	RID	?	X	LOCK
WORK	1:115:3	RID	?	X	LOCK
WORK	1:120	PAGE	?	X	LOCK
WORK	1:151	PAGE	?	X	LOCK
WORK	1:148	PAGE	?	X	LOCK
～ 省略 ～	?	?	?	?	?
WORK	(0100274d67b6)	KEY	?	X	LOCK
WORK	(f200646e752e)	KEY	?	X	LOCK
WORK	(00005c180047)	KEY	?	X	LOCK
WORK	1:464	EXTENT	?	X	LOCK
WORK	1:501	PAGE	?	X	LOCK
WORK	1:503	PAGE	?	X	LOCK
WORK	1:502	PAGE	?	X	LOCK
WORK	(f100c9417efa)	KEY	?	X	LOCK
WORK	(0100e3362a07)	KEY	?	X	LOCK

?

何行かは省略していますが、RID や、PAGE 単位でロックがかかっています。
再構築の場合はかなり細かい粒度でロックが取得されていますね。

RID が取得されている個所は混合エクステントだからかと思ったのですが、ページ ID 114 も混合エクステントのため、
PAGE と RID の取得の違いまではつかめていません…。

PFS の内容が [0x70 IAM_PG MIXED_EXT ALLOCATED] か [0x60 MIXED_EXT ALLOCATED] の違いはあるのですが。

再構成と再構築では処理が違いますので、ロックのかけ方が違うということは確認ができたのかなと思います。
再構成と違ってあっさりとした投稿になってしまいました…。

DBCC IND で確認する違いのその 3 として Books Online に記載されている以下の動作を確認しようと考えていました。

再編成プロセスでは、システム リソースの使用が最小限に抑えられます。 また、再編成は自動的にオンラインで実行されます。 このプロセスでは、ブロッキング ロックは長時間保持されません。 したがって、実行中のクエリまたは更新はブロックされません。

試行錯誤していたのですがどうしてもブロッキングの状態で終わってしまうんですよね…。
READPAST のような動作をするのであれば、DBCC IND で確認できるのではと考えていたのですが。

ロックされているレコードのページだけ、再構成で断片化が解消されていないような、
ページの情報が取得できればと考えていたのですが企画倒れとなってしまいました…。
# 私のスキルが不足しているだけなのですが。

ロックされているレコードに対しての断片化解消は取れなかったのですが、再構成と再構築の
ロックの状態は少し面白いなと思ったので、そちらについて投稿したいと思います。
今回は再構成時のロックの状態について。

[再構成中に取得されるロック]

前回まで使用していたテーブルでインデックスの再編成を実行する際に以下のクエリを使用して
再編成時のロックの取得状態を確認します。
# テーブルは前回までに使用していたものをそのまま使用しています。

– 再編成中のロックの情報取得 –

— ロックの取得状態をわかるようにトランザクションを開始 BEGIN TRAN — インデックスの再構成の実施 ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REORGANIZE — ロックの情報を取得 SELECT ??? DB_NAME(resource_database_id) AS [database_name], ??? RTRIM([resource_description]) AS [resource_description], ??? [resource_type],? ??? [resource_subtype], ??? [request_mode], ??? [request_type] FROM ??? [sys].[dm_tran_locks] WHERE ??? [request_session_id] = @@SPID — トランザクションの終了 — ロールバックしていますが、再編成はロールバックされません。 ROLLBACK TRAN

?

以下のような結果が取得できます。

– ロックの取得 1 –

database_name	resource_description	resource_type	resource_subtype	request_mode	request_type
WORK	?	DATABASE	?	S	LOCK
WORK	?	OBJECT	?	X	LOCK
WORK	1:232	EXTENT	?	X	LOCK
WORK	1:256	EXTENT	?	X	LOCK
WORK	1:272	EXTENT	?	X	LOCK

?
DBCC IND の情報があると分かりやすいので取得したものが以下になります。

– DBCC IND の情報 1 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	232	Tbl1	PK_Tbl1	1	0	259	235
2	WORK	234	Tbl1	PK_Tbl1	1	0	263	239
3	WORK	235	Tbl1	PK_Tbl1	1	0	232	236
4	WORK	236	Tbl1	PK_Tbl1	1	0	235	260
5	WORK	237	Tbl1	PK_Tbl1	1	0	256	258
6	WORK	238	Tbl1	PK_Tbl1	1	0	0	263
7	WORK	239	Tbl1	PK_Tbl1	1	0	234	257
8	WORK	248	Tbl2	PK_Tbl2	1	0	255	251
9	WORK	250	Tbl2	PK_Tbl2	1	0	265	269
10	WORK	251	Tbl2	PK_Tbl2	1	0	248	264
11	WORK	252	Tbl2	PK_Tbl2	1	0	280	265
12	WORK	253	Tbl2	PK_Tbl2	1	0	268	267
13	WORK	254	Tbl2	PK_Tbl2	1	0	267	255
14	WORK	255	Tbl2	PK_Tbl2	1	0	254	248
15	WORK	256	Tbl1	PK_Tbl1	1	0	261	237
16	WORK	257	Tbl1	PK_Tbl1	1	0	239	259
17	WORK	258	Tbl1	PK_Tbl1	1	0	237	272
18	WORK	259	Tbl1	PK_Tbl1	1	0	257	232
19	WORK	260	Tbl1	PK_Tbl1	1	0	236	261
20	WORK	261	Tbl1	PK_Tbl1	1	0	260	256
21	WORK	262	Tbl1	PK_Tbl1	1	0	272	0
22	WORK	263	Tbl1	PK_Tbl1	1	0	238	234
23	WORK	264	Tbl2	PK_Tbl2	1	0	251	0
24	WORK	265	Tbl2	PK_Tbl2	1	0	252	250
25	WORK	266	Tbl2	PK_Tbl2	1	0	0	280
26	WORK	267	Tbl2	PK_Tbl2	1	0	253	254
27	WORK	268	Tbl2	PK_Tbl2	1	0	271	253
28	WORK	269	Tbl2	PK_Tbl2	1	0	250	270
29	WORK	270	Tbl2	PK_Tbl2	1	0	269	271
30	WORK	271	Tbl2	PK_Tbl2	1	0	270	268
31	WORK	272	Tbl1	PK_Tbl1	1	0	258	262
32	WORK	280	Tbl2	PK_Tbl2	1	0	266	252

?

再構成の場合は既に使用されているページを使用して断片化の解消を行います。
エクステントでロックをかけて、エクステント内のページを並べ替えていく形でしょうか。
# [resource_description] と [PagePID] は対応しています。
[PK_Tbl1] が含まれているエクステントがロックされているのが確認できると思います。

上の結果は [単一エクステント] のテーブルで実行したものになります。
単一エクステントの場合は、エクステント内には一つのオブジェクトのみ格納されていなすので、排他ロックを取得しても
他のオブジェクトには影響しません。

これを [混合エクステント] のテーブルで実行するとどのようになるか気になったので試してみました。

– DBCC IND の情報 2 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	15	Tbl1	PK_Tbl1	1	0	114	236
2	WORK	22	Tbl2	PK_Tbl2	1	0	119	80
3	WORK	78	Tbl1	PK_Tbl1	1	0	118	89
4	WORK	80	Tbl2	PK_Tbl2	1	0	22	264
5	WORK	89	Tbl1	PK_Tbl1	1	0	78	93
6	WORK	90	Tbl2	PK_Tbl2	1	0	251	250
7	WORK	93	Tbl1	PK_Tbl1	1	0	89	234
8	WORK	94	Tbl2	PK_Tbl2	1	0	264	115
9	WORK	109	Tbl1	PK_Tbl1	1	0	0	256
10	WORK	110	Tbl2	PK_Tbl2	1	0	115	255
11	WORK	114	Tbl1	PK_Tbl1	1	0	237	15
12	WORK	115	Tbl2	PK_Tbl2	1	0	94	110
13	WORK	118	Tbl1	PK_Tbl1	1	0	235	78
14	WORK	119	Tbl2	PK_Tbl2	1	0	0	22
15	WORK	232	Tbl1	PK_Tbl1	1	0	233	0
16	WORK	233	Tbl1	PK_Tbl1	1	0	234	232
17	WORK	234	Tbl1	PK_Tbl1	1	0	93	233
18	WORK	235	Tbl1	PK_Tbl1	1	0	236	118
19	WORK	236	Tbl1	PK_Tbl1	1	0	15	235
20	WORK	237	Tbl1	PK_Tbl1	1	0	238	114
21	WORK	238	Tbl1	PK_Tbl1	1	0	239	237
22	WORK	239	Tbl1	PK_Tbl1	1	0	256	238
23	WORK	248	Tbl2	PK_Tbl2	1	0	249	0
24	WORK	249	Tbl2	PK_Tbl2	1	0	250	248
25	WORK	250	Tbl2	PK_Tbl2	1	0	90	249
26	WORK	251	Tbl2	PK_Tbl2	1	0	252	90
27	WORK	252	Tbl2	PK_Tbl2	1	0	253	251
28	WORK	253	Tbl2	PK_Tbl2	1	0	254	252
29	WORK	254	Tbl2	PK_Tbl2	1	0	255	253
30	WORK	255	Tbl2	PK_Tbl2	1	0	110	254
31	WORK	256	Tbl1	PK_Tbl1	1	0	109	239
32	WORK	264	Tbl2	PK_Tbl2	1	0	80	94

?

太字下線のページは混合エクステント上に格納されているページになります。
[DBCC PAGE] を使用すると確認ができるのですが、これは別の機会にまとめたいと思います。

この状態で再構成を実行してみます。

– ロックの取得 2 –

database_name	resource_description	resource_type	resource_subtype	request_mode	request_type
WORK	?	DATABASE	?	S	LOCK
WORK	1:232	EXTENT	?	X	LOCK
WORK	?	OBJECT	?	X	LOCK
WORK	1:256	EXTENT	?	X	LOCK

?

[232] [256] は単一エクステントの領域となります。
混合エクステントに排他ロックをかけてしまうと他のオブジェクトにも影響が出てしまうので、
インデックスの再構成の対象は単一エクステントになっているようですね。
軽く確認はしてみましたが混合エクステント内は同一エクステント内での並び替えは行われていないように見受けられました。

混合エクステントの断片化解消は再構成ではなく、再構築で実施する必要があるようですね。

再構築の場合はロックが変わってきます。
そちらは次の投稿でまとめていきたいと思います。

前回は再構成と再構築でページの再利用方法が異なるということを DBCC IND で確認しました。
今回はエクステントの並び替えの違いについて確認をしてみたいと思います。
# 最初にスクリプトを書いてから何日か経ちましたので、今回の投稿には使用するスクリプトを記載してみました。

[前段]

– ページ –

SQL Server のデータはページに格納されていきます。
1 ページは 8KB で構成されており、ひとつのページに格納できるデータは 8KB となります。
# それ以上格納したい場合は BLOB を使用しますが今回は通常のデータを対象とします。

1 ページに格納できるデータは同一のテーブルのデータになります。
そのため、1 ページに複数のテーブルのデータが格納されるということはありません。

– エクステント –

ページはエクステントという単位で管理されています。
エクステントは 8 ページで構成され、ページに空きが無くなった場合は、ページ単位で領域を増やすのではなく、
エクステント単位 (8KB * 8 = 64 KB)で増やしていきます。

– 単一エクステントと混合エクステント –

エクステントには以下の 2 種類があります。

1. 単一エクステント
2. 混合エクステント

8 ページで構成されるエクステントですが、使用されているページが一つのオブジェクトで構成される場合と、
複数のオブジェクトが含まれた形で構成される場合があります。
前者を単一エクステント、後者を混合エクステントと呼びます。

?

最初は単一エクステントで構成され、8 ページまで拡張されたら、混合エクステントに切り替わります。

[本題]?

再構成と再構築ではページの並び替えだけでなく、エクステントの並び替えにも差があります。

この違いについて DBCC IND を使用して確認していきたいと思います。
この確認は単一エクステントになるようにして確認をした方がわかりやすいので、トレースフラグ [1118] を設定して
確認をしています。
# このトレースフラグは混合エクステントを使用しないようにするためのトレースフラグです。

今回はエクステントの並び替えの検証をしたいので検証用のテーブルは 2 つ作成します。
# 1 レコードで 1 ページ使うように nchar を 4000 で設定しています。

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

DROP TABLE [dbo].[Tbl1] DROP TABLE [dbo].[Tbl2] CREATE TABLE [dbo].[Tbl1]( ??? [Col1] [uniqueidentifier] NOT NULL, ??? [Col2] [nchar](4000) 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] CREATE TABLE [dbo].[Tbl2]( ??? [Col1] [uniqueidentifier] NOT NULL, ??? [Col2] [nchar](4000) NULL, CONSTRAINT [PK_Tbl2] 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]

?

テーブルの作成が完了したらデータを挿入します。

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

TRUNCATE TABLE [dbo].[Tbl1] TRUNCATE TABLE [dbo].[Tbl2] DECLARE @i int = 1 WHILE (@i <= 16) BEGIN ??? INSERT INTO [dbo].[Tbl1] VALUES(NEWID(), NCHAR(@i)) ??? INSERT INTO [dbo].[Tbl2] VALUES(NEWID(), NCHAR(@i)) ??? SET @i +=1 END

?

この状態で DBCC IND を実行してページの情報を確認してみます。

– DBCC IND のスクリプト –

— DBCC IND の実行結果を格納するテーブル変数の定義 DECLARE @Tbl TABLE( PageFID int, PagePID int, IAMFID int, IAMPID int, ObjectID int, IndexId int, PartitionNumber bigint, PartitionID bigint, iam_chain_type sysname, PageType int, IndexLevel int, NextPageFID int, NextPagePID int, PrevPageFID int, PrevPagePID int ) — テーブル変数に DBCC IND の実行結果を格納 — 1 を指定することでデータページ (ヒープまたはクラスタ化インデックス) を対象とする INSERT INTO @Tbl EXEC (‘DBCC IND(N”WORK”,N”dbo.Tbl1”, 1)’) INSERT INTO @Tbl EXEC (‘DBCC IND(N”WORK”,N”dbo.Tbl2”, 1)’) — DBCC IND の結果から必要な列を取得 SELECT ??? ROW_NUMBER() OVER (ORDER BY [PagePID] ASC) AS [No], ??? FILE_NAME([PageFID]) AS [FILEName], ??? [PagePID], ??? OBJECT_NAME([ObjectID]) AS [ObjectName], ??? [name], ??? [PageType], ??? [IndexLevel], ??? [NextPagePID], ??? [PrevPagePID] FROM ??? @Tbl ??? LEFT JOIN ??? [sys].[indexes] ??? ON ??? [@Tbl].[ObjectID] = [sys].[indexes].[object_id] ??? AND ??? [@Tbl].[IndexId] = [sys].[indexes].[index_id] WHERE ??? [PageType] = 1 ORDER BY ??? [PagePID] ASC

?

– 結果 1 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	848	Tbl1	PK_Tbl1	1	0	871	868
2	WORK	850	Tbl1	PK_Tbl1	1	0	851	871
3	WORK	851	Tbl1	PK_Tbl1	1	0	880	850
4	WORK	852	Tbl1	PK_Tbl1	1	0	869	866
5	WORK	853	Tbl1	PK_Tbl1	1	0	868	0
6	WORK	854	Tbl1	PK_Tbl1	1	0	865	880
7	WORK	855	Tbl1	PK_Tbl1	1	0	870	867
8	WORK	856	Tbl2	PK_Tbl2	1	0	873	0
9	WORK	858	Tbl2	PK_Tbl2	1	0	879	861
10	WORK	859	Tbl2	PK_Tbl2	1	0	863	874
11	WORK	860	Tbl2	PK_Tbl2	1	0	877	878
12	WORK	861	Tbl2	PK_Tbl2	1	0	858	873
13	WORK	862	Tbl2	PK_Tbl2	1	0	874	872
14	WORK	863	Tbl2	PK_Tbl2	1	0	876	859
15	WORK	864	Tbl1	PK_Tbl1	1	0	866	865
16	WORK	865	Tbl1	PK_Tbl1	1	0	864	854
17	WORK	866	Tbl1	PK_Tbl1	1	0	852	864
18	WORK	867	Tbl1	PK_Tbl1	1	0	855	869
19	WORK	868	Tbl1	PK_Tbl1	1	0	848	853
20	WORK	869	Tbl1	PK_Tbl1	1	0	867	852
21	WORK	870	Tbl1	PK_Tbl1	1	0	0	855
22	WORK	871	Tbl1	PK_Tbl1	1	0	850	848
23	WORK	872	Tbl2	PK_Tbl2	1	0	862	888
24	WORK	873	Tbl2	PK_Tbl2	1	0	861	856
25	WORK	874	Tbl2	PK_Tbl2	1	0	859	862
26	WORK	875	Tbl2	PK_Tbl2	1	0	878	876
27	WORK	876	Tbl2	PK_Tbl2	1	0	875	863
28	WORK	877	Tbl2	PK_Tbl2	1	0	0	860
29	WORK	878	Tbl2	PK_Tbl2	1	0	860	875
30	WORK	879	Tbl2	PK_Tbl2	1	0	888	858
31	WORK	880	Tbl1	PK_Tbl1	1	0	854	851
32	WORK	888	Tbl2	PK_Tbl2	1	0	872	879

?

Tbl1 が使用しているページを太字にしています。
今回は必ず単一エクステントが使用されるようにしていますので、交互にデータを挿入してもページがエクステント内では
連続した形で格納されます。
1～7 / 8 ～ 14 / 15 ～ 22 / 23 ～ 30 / 31 / 32 の 6 エクステントで構成がされています。

エクステント内のページは連続していますが、交互にデータを挿入しているのでエクステントが連続していません。
エクステントの断片化が発生している状態ですね。

ではこの状態で、インデックスの再構成 (REORGANIZE / INDEXDEFRAG) を実行してみます。

– インデックスの再構成のスクリプト –

ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REORGANIZE ALTER INDEX [PK_Tbl2] ON [dbo].[Tbl2] REORGANIZE

?

再構成が終了したら DBCC IND を実行してページの状態を確認します。

– 結果 2 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	848	Tbl1	PK_Tbl1	1	0	850	0
2	WORK	850	Tbl1	PK_Tbl1	1	0	851	848
3	WORK	851	Tbl1	PK_Tbl1	1	0	852	850
4	WORK	852	Tbl1	PK_Tbl1	1	0	853	851
5	WORK	853	Tbl1	PK_Tbl1	1	0	854	852
6	WORK	854	Tbl1	PK_Tbl1	1	0	855	853
7	WORK	855	Tbl1	PK_Tbl1	1	0	864	854
8	WORK	856	Tbl2	PK_Tbl2	1	0	858	0
9	WORK	858	Tbl2	PK_Tbl2	1	0	859	856
10	WORK	859	Tbl2	PK_Tbl2	1	0	860	858
11	WORK	860	Tbl2	PK_Tbl2	1	0	861	859
12	WORK	861	Tbl2	PK_Tbl2	1	0	862	860
13	WORK	862	Tbl2	PK_Tbl2	1	0	863	861
14	WORK	863	Tbl2	PK_Tbl2	1	0	872	862
15	WORK	864	Tbl1	PK_Tbl1	1	0	865	855
16	WORK	865	Tbl1	PK_Tbl1	1	0	866	864
17	WORK	866	Tbl1	PK_Tbl1	1	0	867	865
18	WORK	867	Tbl1	PK_Tbl1	1	0	868	866
19	WORK	868	Tbl1	PK_Tbl1	1	0	869	867
20	WORK	869	Tbl1	PK_Tbl1	1	0	870	868
21	WORK	870	Tbl1	PK_Tbl1	1	0	871	869
22	WORK	871	Tbl1	PK_Tbl1	1	0	880	870
23	WORK	872	Tbl2	PK_Tbl2	1	0	873	863
24	WORK	873	Tbl2	PK_Tbl2	1	0	874	872
25	WORK	874	Tbl2	PK_Tbl2	1	0	875	873
26	WORK	875	Tbl2	PK_Tbl2	1	0	876	874
27	WORK	876	Tbl2	PK_Tbl2	1	0	877	875
28	WORK	877	Tbl2	PK_Tbl2	1	0	878	876
29	WORK	878	Tbl2	PK_Tbl2	1	0	879	877
30	WORK	879	Tbl2	PK_Tbl2	1	0	888	878
31	WORK	880	Tbl1	PK_Tbl1	1	0	0	871
32	WORK	888	Tbl2	PK_Tbl2	1	0	0	879

?

ページの配置状況は何も変わっていないですね。

では次に、再構築 (REBUILD) を実行してみます。

– インデックスの再構築 –

ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD ALTER INDEX [PK_Tbl2] ON [dbo].[Tbl2] REBUILD

?

再構築が終了したら DBCC IND を実行します。

– 結果 3 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	896	Tbl1	PK_Tbl1	1	0	904	0
2	WORK	904	Tbl1	PK_Tbl1	1	0	905	896
3	WORK	905	Tbl1	PK_Tbl1	1	0	906	904
4	WORK	906	Tbl1	PK_Tbl1	1	0	907	905
5	WORK	907	Tbl1	PK_Tbl1	1	0	908	906
6	WORK	908	Tbl1	PK_Tbl1	1	0	909	907
7	WORK	909	Tbl1	PK_Tbl1	1	0	910	908
8	WORK	910	Tbl1	PK_Tbl1	1	0	911	909
9	WORK	911	Tbl1	PK_Tbl1	1	0	912	910
10	WORK	912	Tbl1	PK_Tbl1	1	0	913	911
11	WORK	913	Tbl1	PK_Tbl1	1	0	914	912
12	WORK	914	Tbl1	PK_Tbl1	1	0	915	913
13	WORK	915	Tbl1	PK_Tbl1	1	0	916	914
14	WORK	916	Tbl1	PK_Tbl1	1	0	917	915
15	WORK	917	Tbl1	PK_Tbl1	1	0	918	916
16	WORK	918	Tbl1	PK_Tbl1	1	0	0	917
17	WORK	944	Tbl2	PK_Tbl2	1	0	952	0
18	WORK	952	Tbl2	PK_Tbl2	1	0	953	944
19	WORK	953	Tbl2	PK_Tbl2	1	0	954	952
20	WORK	954	Tbl2	PK_Tbl2	1	0	955	953
21	WORK	955	Tbl2	PK_Tbl2	1	0	956	954
22	WORK	956	Tbl2	PK_Tbl2	1	0	957	955
23	WORK	957	Tbl2	PK_Tbl2	1	0	958	956
24	WORK	958	Tbl2	PK_Tbl2	1	0	959	957
25	WORK	959	Tbl2	PK_Tbl2	1	0	960	958
26	WORK	960	Tbl2	PK_Tbl2	1	0	961	959
27	WORK	961	Tbl2	PK_Tbl2	1	0	962	960
28	WORK	962	Tbl2	PK_Tbl2	1	0	963	961
29	WORK	963	Tbl2	PK_Tbl2	1	0	964	962
30	WORK	964	Tbl2	PK_Tbl2	1	0	965	963
31	WORK	965	Tbl2	PK_Tbl2	1	0	966	964
32	WORK	966	Tbl2	PK_Tbl2	1	0	0	965

?

ページが並び替えられていますね。
また今回のデータは各テーブルで複数のエクステントが使用されているのですが、再構築後はページ ID が連続しています。
このことからエクステントの並び替え行われていることが確認できます。

インデックスの再構成ではエクステントの並び替えが行われません。
# 再構成は現在使用されているページ内で断片化を解消します。そのためエクステントの並び替えは行われません。
インデックスの再構築はインデックスの再作成に近い処理が実行されるため、エクステントも並び替えられます。

エクステントの断片化を解消するためには再構築が必要となります。

エクステントの断片化の状態は下位互換として残されている [DBCC SHOWCONTIG] でないと見れないみたいなんですよね。
DMV の [sys.dm_db_index_physical_stats] でも断片化の状況は確認できるのですが、リーフレベルの断片化の状態だけで、
エクステントの断片化の状態は出力されていなかったはずです。
# ヒープの場合はページの断片化はないので、エクステントの断片化の状態が出力されます。

[DBCC SHOWCONTIG] でないと割り当てられているエクステントの数も見ることができません。
[sys.dm_db_index_physical_stats] の情報はページを基準に出力されているんですよね。

その 1 / その 2 の 2 回に分けてインデックスの再構成と再構築のデータの並び替えの違いについて投稿してみました。

並び替えの違いだけでなく、ロックされているデータの取り扱いも再構成と再構築では異なります。
2 回の予定だったのですが、その 3 としてロックされているデータの取り扱いの違いについても確認していきたいと思います。

本日でブログの初投稿から 1 年が経過しました。

自宅での検証用の ML115 G5 を購入したことを機会に始めたブログなのですが、三日坊主にならずに
本日まで続けることができました。

最初は

• ML115 G5 × 1
• ThinkPad T61 × 1

を検証環境として使っていたのですが、現在は

• ML115 G5 × 3
• ML110 G5 × 1 (スキルチャージプログラム)
• ThinkPad T60 × 1
• ThinkPad T61 × 1

を使用して検証ができるようになり、 この 1 年で検証用の環境もだいぶ整ってきたと思います。

ブログを書くようになって、個人で検証した結果が後からも確認できるようになり、
備忘録としてもとても役に立っています。
また、定期的に投稿する習慣をつけることで、勉強をする機会が増えたようにも思えます。

この一年間で 15 万?アクセスぐらいはあったようですので、自分が投稿した内容がいろいろな方の
目にとまっているみたいです。自分の知っていることを私蔵しないことって重要ですね。
# Live Space とスキルチャージプログラムのサーバーのアクセス数を合わせて上記の数字となっています。
??? アクセスの比率としては 7:3 ぐらいで Live Space の参照率が高いです。
??? 同じ内容を投稿しているのですが偏りが出ますね。

初期構築方法の投稿が多く、特定の分野で深い情報を出すことができないところで、エンジニアとして
まだまだ未熟だな～と痛感しています。
特定分野のスペシャリストになるための道のりは遠いですね。

勉強したい内容はたくさんありますので、これからもこまめに投稿できるように頑張っていきたいです！！