SE の雑記

前回は再構成と再構築でページの再利用方法が異なるということを 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 の参照率が高いです。
??? 同じ内容を投稿しているのですが偏りが出ますね。

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

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

SQL Server 2008 R2 November CTP の UCP を少し触っているのですが、このバージョンでも、
SQL 2000 ～ 2008 のサポートはされていないみたいです。

UCP を使用するためには以下のクエリで、実行結果として [2] が取得できないといけないようです。

DECLARE @sqlbootvalue int? EXEC @sqlbootvalue = master.dbo.xp_qv ‘3090395820’, @@SERVICENAME? SELECT @sqlbootvalue

SQL Server 2008 R2 では実行結果が [2] となるため、有効なエディションとして認識されるのですが、
それ以外のバージョンですと [-1] (VALUE_ERROR) となってしまい、エディションのチェックではじかれます。

[3090395820] が何についての値を取得するための設定値なのかがつかめていないのですが、
現在は、SQL Server 2008 R2 でのみ有効な設定として値が返ってくるようです。

値が [2] 以外の場合は、以下のメッセージを返すようですので、エディションのチェックのような気がしますが、
UCP で管理可能なエディションは制限があるのでしょうか？？
Tech Ed では特にエディションの指定については言及していなかった気もするのですが。

メッセージ 37005、レベル 16、状態 1、行 1 ローカル インスタンスをユーティリティ コントロール ポイントで管理できません。 ‘xxxxx’ エディションではこの機能は有効ではありません。

?

xp_qv という拡張ストアドプロシージャの存在は今回知りました。
といってもどのように使うものなのかがピンと来ていませんが…。

ドキュメントとしては以下のものに少し記載がされています。

Guidance Addendum
Guidance Addendum / Installation / Startup

xp_qv でのチェックに至るまでには SQL Server 2008 の msdb に UCP で必要となるストアド等を
手動で登録してあげないといけませんでしたが。かなり無理やりこの状態まで持っていっています。
xp_qv のリターン値を無視するように細工して、UCP の管理対象として追加することはできたのですが、
情報のアップロードはうまくいきませんでした。

?

UCP に関しては TechNet Forum でも投稿があったみたいですね。
Can SQL Server 2008 (non-R2) instances by managed by a UCP?

CTP2 の時は R2 のみ追加できるということだったようですが、CTP3 でも同様のようです。
現状の仕組みでは、管理対象の msdb を拡張しないと情報は取得できないようですので、
簡単に統合管理できるというわけでもなさそうですね。

UCP 用のデータアップロードのステップは Power Shell のステップとして登録されているので、
2000 / 2005 では Active X スクリプトタスクあたりになるのでしょうか？

使えるようになる場合は何かしらのアップデートモジュールが提供されるのではないでしょうか。

今回、調べている中で、[xp_msver] という拡張ストアドプロシージャの存在を知りました。
実行すると以下のような情報が取得できます。

Index	Name	Internal_Value	Character_Value
1	ProductName	NULL	Microsoft SQL Server
2	ProductVersion	655410	10.50.1352.12
3	Language	1041	日本語 (日本)
4	Platform	NULL	NT x64
5	Comments	NULL	SQL
6	CompanyName	NULL	Microsoft Corporation
7	FileDescription	NULL	SQL Server Windows NT – 64 Bit
8	FileVersion	NULL	2009.0100.1352.012 ((KJ_PreRelease).091030-1757 )
9	InternalName	NULL	SQLSERVR
10	LegalCopyright	NULL	Microsoft Corp. All rights reserved.
11	LegalTrademarks	NULL	Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12	OriginalFilename	NULL	SQLSERVR.EXE
13	PrivateBuild	NULL	NULL
14	SpecialBuild	88604684	NULL
15	WindowsVersion	393281542	6.0 (6001)
16	ProcessorCount	4	4
17	ProcessorActiveMask	NULL	f
18	ProcessorType	8664	NULL
19	PhysicalMemory	2047	2047 (2146058240)
20	Product ID	NULL	NULL

?

環境情報を一括で取得するものとして便利そうです。
[FileVersion] に [KJ] と出力されているところ [Kilimanjaro] のコードネームが見受けられますね。
SQL Server 2008 で実行すると [Katmai] が出力されたりします。

DBCC IND の再構成 / 再構築その 2 は明日以降に書くとして、その前に R2 の最新の CTP を
インストールしてみたいと思います。

現在、SQL Server の検証で使用している、2005 / 2008 がインストールされている環境に
名前付きインスタンスとしてインストールしたいと思います。
OS はまだバージョンアップしていないので Windows Server 2008 を使用しています。

1. インストールメディアをいれてインストーラーを起動します。
2. [インストール] → [新規インストールを実行するか～] をクリックします。
   ?
3. [OK] をクリックします。
   
4. [次へ] をクリックします。
   選択できるエディションは、[Enterprise Evaluation] [Express] [Express with Advanced Services] の
   3 種類となっています。
   今回は [Enterprise Evaluation] で。
   
5. [使用許諾契約書に同意する] を有効にして、[次へ] をクリックします。
   ?
6. [インストール] をクリックします。
   
7. [OK] をクリックし、手動で再起動します。
   
8. 再起動後、再度インストーラーを起動して、セットアップを継続します。
9. [次へ] をクリックします。
   
10. エディションを選択して、[次へ] をクリックします。
11. [使用許諾契約書に同意する] を有効にして、[次へ] をクリックします。
12. [SQL Server 機能のインストール] を選択して、[次へ] をクリックします。
    
13. インストールする機能を選択して、[次へ] をクリックします。
    ?
14. [次へ] をクリックします。
    
    今回は共存環境ですので共有コンポーネントで警告が発生しています。
    
15. インスタンス名を入力して、[次へ] をクリックします。
    
16. [次へ] をクリックします。
    
17. サービスアカウントと照合順序を選択して、[次へ] をクリックします。
    ??
    照合順序はデフォルトが [Japanese_CI_AS] となっています。
    最新の日本語の照合順序は SQL Server 2008 と同じで [Japanese_XJIS_100] みたいです。
18. 管理者アカウントと、ディレクトリ、FILESTREAM の設定をして [次へ] をクリックします。
    
    
19. 今回は Reporing Services もインストール対象としていたので設定画面が表示されます。
    Reporting Services は後で構成したいので、[レポート サーバーを構成せずにインストールする] を選択して、
    [次へ] をクリックします。
    
20. [次へ] をクリックします。
    
21. [次へ] をクリックします。
    
22. [インストール] をクリックします。
    
23. [OK] をクリックします。
    
24. [閉じる] をクリックします。
    
25. [OK] をクリックして、手動で再起動します。
    

以上でインストール完了です。

UCP の設定画面も日本語になっていますね～。

?

機能は少しずつ確認していきたいと思います。

SQL Server のインデックスのメンテナンスには

• 再構成 (REORGANIZE / DEFRAG)
• 再構築 (REBUILD)

の 2 種類があります。

2 種類の違いを 2 回の投稿に分けてまとめていきたいと思います。

まずは、前回使用した SQL データを挿入して、DBCC IND でページの情報を取得してみました。
# 一度 Truncate でデータをクリアして再挿入しています。

– 結果 1 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	3976	Tbl1	PK_Tbl1	1	0	3977	0
2	WORK	3977	Tbl1	PK_Tbl1	1	0	3978	3976
3	WORK	3978	Tbl1	PK_Tbl1	1	0	3979	3977
4	WORK	3979	Tbl1	PK_Tbl1	1	0	3980	3978
5	WORK	3980	Tbl1	PK_Tbl1	1	0	3981	3979
6	WORK	3981	Tbl1	PK_Tbl1	1	0	3982	3980
7	WORK	3982	Tbl1	PK_Tbl1	1	0	3983	3981
8	WORK	3983	Tbl1	PK_Tbl1	1	0	3984	3982
9	WORK	3984	Tbl1	PK_Tbl1	1	0	3985	3983
10	WORK	3985	Tbl1	PK_Tbl1	1	0	3986	3984
11	WORK	3986	Tbl1	PK_Tbl1	1	0	3987	3985
12	WORK	3987	Tbl1	PK_Tbl1	1	0	0	3986

?

この状態で、前回使用したクラスタ化インデックス更新の SQL を実行して断片化を発生させます。

– 結果 2 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	3976	Tbl1	PK_Tbl1	1	0	3988	0
2	WORK	3977	Tbl1	PK_Tbl1	1	0	4042	4040
3	WORK	3978	Tbl1	PK_Tbl1	1	0	4044	4043
4	WORK	3979	Tbl1	PK_Tbl1	1	0	4046	4045
5	WORK	3980	Tbl1	PK_Tbl1	1	0	3989	4046
6	WORK	3981	Tbl1	PK_Tbl1	1	0	4049	4048
7	WORK	3982	Tbl1	PK_Tbl1	1	0	4051	4049
8	WORK	3983	Tbl1	PK_Tbl1	1	0	4052	4050
9	WORK	3984	Tbl1	PK_Tbl1	1	0	4055	4053
10	WORK	3985	Tbl1	PK_Tbl1	1	0	3990	4055
11	WORK	3986	Tbl1	PK_Tbl1	1	0	4056	3990
12	WORK	3987	Tbl1	PK_Tbl1	1	0	0	4056
13	WORK	3988	Tbl1	PK_Tbl1	1	0	4040	3976
14	WORK	3989	Tbl1	PK_Tbl1	1	0	4047	3980
15	WORK	3990	Tbl1	PK_Tbl1	1	0	3986	3985
16	WORK	4040	Tbl1	PK_Tbl1	1	0	3977	3988
17	WORK	4041	Tbl1	PK_Tbl1	1	0	4043	4042
18	WORK	4042	Tbl1	PK_Tbl1	1	0	4041	3977
19	WORK	4043	Tbl1	PK_Tbl1	1	0	3978	4041
20	WORK	4044	Tbl1	PK_Tbl1	1	0	4045	3978
21	WORK	4045	Tbl1	PK_Tbl1	1	0	3979	4044
22	WORK	4046	Tbl1	PK_Tbl1	1	0	3980	3979
23	WORK	4047	Tbl1	PK_Tbl1	1	0	4048	3989
24	WORK	4048	Tbl1	PK_Tbl1	1	0	3981	4047
25	WORK	4049	Tbl1	PK_Tbl1	1	0	3982	3981
26	WORK	4050	Tbl1	PK_Tbl1	1	0	3983	4051
27	WORK	4051	Tbl1	PK_Tbl1	1	0	4050	3982
28	WORK	4052	Tbl1	PK_Tbl1	1	0	4054	3983
29	WORK	4053	Tbl1	PK_Tbl1	1	0	3984	4054
30	WORK	4054	Tbl1	PK_Tbl1	1	0	4053	4052
31	WORK	4055	Tbl1	PK_Tbl1	1	0	3985	3984
32	WORK	4056	Tbl1	PK_Tbl1	1	0	3987	3986

?

[PagePID] が 3976 ～3990 , 4040 ～ 4056 が使用されているのが確認できます。

この状態で再構成と再構築を実行して違いを確認したいと思います。

[インデックスの再構成]

以下の SQL を実行してインデックスを再構成してみます。

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

再構成後に DBCC IND を実行して、ページの情報を取得します。

– 結果 3 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	Nex tPagePID	PrevPagePID
1	WORK	3976	Tbl1	PK_Tbl1	1	0	3977	0
2	WORK	3977	Tbl1	PK_Tbl1	1	0	3978	3976
3	WORK	3978	Tbl1	PK_Tbl1	1	0	3979	3977
4	WORK	3979	Tbl1	PK_Tbl1	1	0	3980	3978
5	WORK	3980	Tbl1	PK_Tbl1	1	0	3981	3979
6	WORK	3981	Tbl1	PK_Tbl1	1	0	3982	3980
7	WORK	3982	Tbl1	PK_Tbl1	1	0	3983	3981
8	WORK	3983	Tbl1	PK_Tbl1	1	0	3984	3982
9	WORK	3984	Tbl1	PK_Tbl1	1	0	3985	3983
10	WORK	3985	Tbl1	PK_Tbl1	1	0	3986	3984
11	WORK	3986	Tbl1	PK_Tbl1	1	0	3987	3985
12	WORK	3987	Tbl1	PK_Tbl1	1	0	0	3986

?

[PagePID] 3976 ～ 3987 を使用して断片化が解消されています。

それでは再度断片化を発生させて、インデックスの再構築をしてみたいと思います。
断片化を発生させたところ、[PagePID] 3976 ～ 3989 , 4064 ～ 4079 が使用されていました。

[インデックスの再構築]

以下の SQL を実行してインデックスを再構築します。

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

DBCC IND を実行してページの情報を取得します。

– 結果 4 –

No	FILEName	PagePID	ObjectName	name	PageType	IndexLevel	NextPagePID	PrevPagePID
1	WORK	4080	Tbl1	PK_Tbl1	1	0	4088	0
2	WORK	4088	Tbl1	PK_Tbl1	1	0	4089	4080
3	WORK	4089	Tbl1	PK_Tbl1	1	0	4090	4088
4	WORK	4090	Tbl1	PK_Tbl1	1	0	4091	4089
5	WORK	4091	Tbl1	PK_Tbl1	1	0	4092	4090
6	WORK	4092	Tbl1	PK_Tbl1	1	0	4093	4091
7	WORK	4093	Tbl1	PK_Tbl1	1	0	4094	4092
8	WORK	4094	Tbl1	PK_Tbl1	1	0	4095	4093
9	WORK	4095	Tbl1	PK_Tbl1	1	0	4096	4094
10	WORK	4096	Tbl1	PK_Tbl1	1	0	4097	4095
11	WORK	4097	Tbl1	PK_Tbl1	1	0	4098	4096
12	WORK	4098	Tbl1	PK_Tbl1	1	0	0	4097

?

[PagePID] 4080 ～ 4098 を使用して断片化が解消されています。

?

インデックスの再構成と再構築では、既に割り当てられている領域を使用するか、新規に領域を割り当てるかの違いがあります。

インデックスの再構成では既に割り当てられている領域を利用して断片化の解消を行います。
そのため、断片化が発生した際に割り当てられていた [PagePID] と同様のページが使用されています。
断片化解消のために新たなエクステントが割り当てられることがありません。

それに対してインデックスの再構築は新規のページを使用して断片化の解消が行われます。
インデックスの再作成に近い形で断片化の解消が行われていたはずです。
新規のエクステントに断片化解消後のインデックスを作成し、作成が終了したらそれまで使用していた領域を
空きページとして解放するようです。

再構成も再構築も断片化の解消により、未使用になったページは他のオブジェクトでも使用可能なように解放されますが、
既存ページの利用か新規ページの割り当てかの違いがありますので、処理終了後の未使用領域には差が出てきます。

?

再構成は既存のページ内での並び替えですが、再構築は再作成を行います。
そのため、再構成と再構築では処理終了後に使用している [PagePID] に違いが出てきます。
あまりにも断片化が発生していると再構成の並び替えに時間がかかり、再構築のほうが早い場合もあったりします。

再構成と再構築の違いは領域の再利用だけでなく、エクステントの並び替えでの動作に関しても違いがあります。
これに関しては次回の投稿でまとめたいと思います。