SE の雑記

SQL Server では、読み取り専用データベースを複数のサーバーで共有してスケールアウトする手法があります。
この方法を [スケーラブルな共有データベース] と言います。

SQL Server 2008 R2 の BOL にも記載されていますね。
スケーラブルな共有データベースの概要

この設定自体は知っていたのですが、正式な名称を今日初めて知りました…。

今回はその設定方法をまとめてみたいと思います。

■環境について

今回作成した環境がこちらになります。
[2008R2-SQL-01] ～ [2008R2-SQL-03] の 3 台のサーバーと NAS 上にデータベースを用意しました。
# iSCSI で接続をしています。

[2008R2-SQL-01] でデータベースの更新を実施して、そのデータベースを [2008R2-SQL-02] [2008R2-SQL-03] で
読み取り専用のデータベースとして使用するというシナリオです。
# 実際には 2 台の SQL Server があればテストできるのですがシナリオを考慮して 3 台用意しました。

– 環境概要図 –

■データベースの準備

まずは、[2008R2-SQL-01] でデータベースを準備します。

データベースで使用する LUN は [読み取り/書き込み] の両方が可能なように指定してあります。

– [2008R2-SQL-01] で使用している LUN –

今回は SQL Server の自習書でおなじみの [NorthwindJ] を用意しました。

スケーラブルな共有データベースを設定するためには、データベースを [読み取り専用] にする必要があります。

USE [master] GO ALTER DATABASE [NorthwindJ] SET? READ_ONLY WITH NO_WAIT GO

– 読み取り専用データベースにするための SQL –

データベースを読み取り専用にすると SSMS の表示が以下のように変わります。
– SSMS での読み取り専用データベースの表示 –

読み取り専用データベースに設定をしたら、データベースをタッチをします。
# LUN を読み取り専用にするのであればデタッチしなくても大丈夫だと思います。

USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N’NorthwindJ’ GO

これで準備完了です。

続いて参照用のサーバーでこのデータベースをアタッチします。

■読み取り専用データベースのアタッチ

今回の環境では、全 SQL Server で同一の LUN を参照しています。
読み取り専用データベースとしてアタッチするサーバーでは LUN を [読み取り専用] として設定しておきます。
# [2008R2-SQL-01] では [読み取り/書き込み] できるようにしていますが、他のサーバーでは読み取り専用とします。
?
– [2008R2-SQL-02] / [2008R2-SQL-03] の LUN の設定 –

LUN を読み取り専用にしているため、ディスクの管理でも [読み取り専用] として設定されています。

– ディスクの管理の表示 –

?
[2008R2-SQL-01] でデータベースを設定しているため、既にドライブ内にはデータベースのファイルが存在しています。
?
– ドライブの内容 –

それではこのデータベースをアタッチします。

USE [master] GO CREATE DATABASE [NorthwindJ] ON ( FILENAME = N’E:NorthwindJNorthwindJ.mdf’ ), ( FILENAME = N’E:NorthwindJNorthwindJ.ldf’ ) FOR ATTACH GO

– アタッチの SQL –

以下が同一のデータベースファイルを複数のサーバーでアタッチした状態になります。
?
– 複数のサーバーで同一ファイルをアタッチ –

これで、[スケーラブルな共有データベース] の設定は完了です。

■NLB で冗長化

この環境を NLB を設定することでクライアントからの接続ポイントを固定化して、冗長化することができます。

??
– 構成概要図 –

?
– NLB の設定 –

以上で設定は完了です。

読み取り専用のデータベースなので、参照系でしか使えませんがサーバー増強 (スケールアウト) によってパフォーマンスを
向上させる方法としては面白い構成なのかな～と思います。
# ディスクがホットスポットになるので、ディスク負荷を考慮したうえで構成を検討する必要がありますが。

久しぶりに SQL Server の勉強を。今日はインスタンスのポート番号の解決について。

今回は以下の環境を使って検証しています。
ポート番号をいろいろと変更してテストを行っていたので、
?

?

■SQL Server が使用するポートのデフォルト設定

SQL Server のインスタンスには既定のインスタンスと名前付きインスタンスの 2 種類があります。

初期設定では、既定のインスタンスの場合は、デフォルトで [TCP 1433] が使用され、名前付きインスタンスの場合は、
[動的ポート] となり使用されるポートは固定されません。

既定のインスタンスに接続する場合は [サーバー名] のみ

名前付きインスタンスに接続する場合は、[サーバー名インスタンス名] の形式で指定します。
?

?

■ポートの変更

ポートの設定は、[SQL Server 構成 マネージャ] で設定することができ、デフォルトでは以下の設定となっています。

既定のインスタンス	名前付きインスタンス

SQL Server のポート番号は任意に変更することができ、既定のインスタンスを動的ポート、名前付きインスタンスで [1433] を
設定するということもできます。

名前付きインスタンスを [TCP 1433] で動かすと、名前付きインスタンスにサーバー名だけで接続ができるようになったりもします。
下の画像のオブジェクトエクスプローラーでは [SQL-TEST] というサーバー名で接続をしているのですが、[@@servername] を
実行した結果は、[SQL-TESTINSTANCE1] となっています。

このようにポート 1433 で実行している SQL Server インスタンスに関しては、サーバー名で接続が可能になります。

■サーバー名だけで接続した際の挙動

Network Monitor を使ってサーバー名だけで接続した場合に使用されるポートを調べてみたいと思います。
SSMS で接続するより、SQLCMD で接続をした場合の方がポートの使用状況がわかりやすかったので、
今回は SQLCMD を使用しながら、挙動を確認していきたいと思います。

接続に使用するコマンドは以下になります。

SQLCMD ?E ?S “SQL-TEST”

この時の接続状況が以下になります。

クライアント [172.23.0.2] からサーバー [172.23.0.1] の [TCP 1433] に対して接続が行われていますね。

?

サーバー名だけで接続をする場合は、直接 1433 に対して接続をしているのが確認できます。

?

■[サーバー名インスタンス名]で接続した場合の挙動

続いては、[サーバー名インスタンス名] で確認をしてみます。

名前付きインスタンスに接続する場合のコマンドは以下になります。

SQLCMD ?E ?S “SQL-TESTINSTANCE1”

?

この時の Network Monitor の内容が以下になります。

現在、[INSTANCE1] の動的ポートでは [TCP 49151] が使用されています。

[SQLCMD.EXE] で、172.23.0.2 から、172.23.0.1 のポート [TCP 49151] に接続している個所が、SQL Server に
接続をしている内容になります。

先ほどの既定のインスタンスへの接続と異なる個所としては、[UDP 1434] の通信が発生しているところですね。

この時のフレームサマリーが以下になります。

172.23.0.2 から 172.23.0.1 に [UDP 1434] で接続をし、そのあとに 172.23.0.1 の [TCP 49151] に接続をしています。

この時の [UDP 1434] の通信に使用されるサービスが、[SQL Server Browser] サービスになります。
# サーバー名だけの接続では使われないようです。

?

■SQL Server Browser サービスについて

先ほどの内容で [SQL Server Browser] サービスで [UDP 1434] が使用されていると書きました。

[SQL Server Browser] サービスは、SQL Server を起動しているサーバーで実行することのできるサービスになります。
今回の検証では、[SQL-TEST] でサービスが起動しています。

タスクマネージャーで確認するとプロセス ID [2896] で起動しているのが確認できますね。
# 32ビットプロセスなんですよね。

[netstat] コマンドを使用して、プロセス ID [2896] のポート使用状況を確認してみたいと思います。
使用するコマンドはこちらです。

netstat ?ano | find “2896”

?

実行するとこのような結果が取得できます。
[UDP 1434] がプロセス ID [2896] で実行されていることが確認できますね。

このサービスが何をやっているかを確認する場合は、[SQL Server Browser] サービスをサービスではなく、
コンソールモードで起動するとわかりやすいです。

コンソールモードで起動するためには、[SQL Server Browser] サービスを停止して、コマンドプロンプトで
以下のコマンドを実行します。
コンソールモードで起動すると [UDP 1434] に対してのアクセスのログがコンソールに表示されるようになります。

"C:Program Files (x86)Microsoft SQL Server90Sharedsqlbrowser.exe" -c

この状態で再度名前付きインスタンスでの接続をするとコンソールに以下の用に表示されます。
?

172.23.0.2 から [UDP 1434] に対してアクセスがあったことを確認することができますね。

[SQL Server Browser] サービスは SQL Server のインスタンス名を指定した接続要求時に、インスタンスに対応するポートを
返答する機能になります。
# サーバー名だけで接続した場合は使用されません。そのため、既定のインスタンスのポートを変更した際には、
　 SQL Server Browser が使用されないので、接続時にポート番号を指定する必要があります。
特定のインスタンスの接続をする際にポート番号を指定しなかった場合はこの機能を使用して、ポート解決をする必要があります。

下の画像が、[INSTANCE1] 接続時にクライアントから SQL Server の [UDP 1434] に対してアクセスされた際の
ネットワークフレームの情報 (Frame Number 9 の情報) になります。
パースがうまくできなかったので、[Hex Details] をみる必要があるのですが、[instance1] という情報が読み取れますね。
クライアントからサーバーに [instance1] のポート解決に対してのリクエストがされています。

次ネットワークフレームの情報 (Frame Number 10 の情報) が SQL Server からクライアントに対してのレスポンスになります。
こちらもパースができなかったので [Hex Details] で確認する必要があるのですが、SQL Server の情報がクライアントに渡されています。
この中にポート番号 (49195) が入っているのが確認できますね。

[SQL Server Browser] サーバーは、インスタンス名が指定されており、ポート番号が指定されていない場合に使用されます。
そのため、インスタンス名を指定していても、ポート番号を指定している場合は使用されません。
# [UDP 1434] にたいしてアクセスがされません。

ポート番号を指定する場合は以下のコマンドになります。

SQLCMD ?E ?S “SQL-TESTINSTANCE1,49195”

?

この場合の Network Monitor の取得内容が以下になるのですが、指定したポートに直接接続がされており、[UDP 1434] の
通信は発生していません。

普段何気なく使用している、[サーバー名インスタンス名] の接続ですがきちんと調べると勉強になります。

以前投稿した内容に SQL Server 2008 Express Edition でメンテナンスプランの利用についてコメントをいただきました。

残念ながら SQL Server の Express Edition では、メンテナンスプランが使用できないんですよね。
SQL Server 2008 の各エディションがサポートする機能

また、SQL Server の Express Edition では SQL Server Agent も使用できないため、定期的なジョブを SQL Server 側で
実行することができません。

スキルチャージプログラムで貸与されている環境は Community Server をインストールしてあり、データベースには
SQL Server 2008 Express Edition を使用しています。
この環境ですが日次で 1 世代のバックアップだけは自動で取得する用に設定をしてあります。

メンテナンスプランと SQL Server Agent を使用できない状態で定期的にバックアップを取得するため、
タスクスケジューラ + SQLCMD.EXE を使ってバックアップのスケジューリングを設定しています。

[タスク スケジューラ] は管理ツールの中に入っています。?

このタスク スケジューラで以下のようなタスクを作成しています。
?

操作には以下のような設定を登録してあります。

操作	プログラムの開始
プログラム/スクリプト	"C:Program FilesMicrosoft SQL Server100ToolsBinnSQLCMD.EXE"
引数の追加 (オプション)	-S localhost<インスタンス名> -E -i "E:ToolsBackupCSDBBackup.sql"

?

CSDBBackup.sql には以下の内容を記載しています。
# SSMS で生成したスクリプトを記載しているだけですが。

BACKUP DATABASE [CommunityServer] TO? DISK = N’E:BackupCommunityServer.bak’ WITH NOFORMAT, INIT, NAME = N’CommunityServer-完全 データベース バックアップ’, SKIP, NOREWIND, NOUNLOAD,? STATS = 10 GO

?

同様の方法でインデックスのメンテナンスも可能です。
ざっくりと作ったインデックスの再構築のクエリです。
これを SQLCMD でタスクスケジューラから実行すると定期的にインデックスの断片化を解消することができます。
# アドホッククエリで書いてしまっていますが…。

SET NOCOUNT ON USE [CommunityServer] GO DECLARE @name sysname DECLARE @SQL nvarchar(500) SELECT * INTO [#tables] FROM [sys].[tables] DECLARE [table_cursor] CURSOR FOR SELECT [name] FROM [#tables] OPEN [table_cursor] FETCH NEXT FROM [table_cursor] INTO @name WHILE @@FETCH_STATUS = 0 BEGIN ??? SET @SQL = N’ALTER INDEX ALL ON [‘ + @name + ‘] REBUILD’ ??? EXECUTE sp_executesql @SQL ??? FETCH NEXT FROM [table_cursor] INTO @name END CLOSE [table_cursor] DEALLOCATE [table_cursor]

?

自分でクエリを書くのが面倒な場合は、定期的に実行したい操作を SSMS からスクリプト化しておけばよいと思います。

Express Edition を使っていてもデータベースのメンテナンスは定期的に実行しないと駄目ですが、無償版なので
この辺の機能がないのは仕方ないですね。

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

今回のテスト用のテーブルには 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 コマンドを使用することでページの情報を取得することもできますので、
次はこのコマンドを紹介してみたいと思います。

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

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

— ロックの取得状態をわかるようにトランザクションを開始 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] は単一エクステントの領域となります。
混合エクステントに排他ロックをかけてしまうと他のオブジェクトにも影響が出てしまうので、
インデックスの再構成の対象は単一エクステントになっているようですね。
軽く確認はしてみましたが混合エクステント内は同一エクステント内での並び替えは行われていないように見受けられました。

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

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