SE の雑記

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

Archive for 11月, 2009

WSFC + SQL Server の Is Alive で使用されるアカウント

leave a comment

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

SELECT @@servername

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

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

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

image

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

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

image

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

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

Written by Masayuki.Ozawa

11月 29th, 2009 at 3:04 pm

Posted in MSCS/WSFC(MSFC)

TOP と ROWCOUNT を使用したデータ削除 その 2

leave a comment

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

今回のテスト用のテーブルには 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 秒

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

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

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

Written by Masayuki.Ozawa

11月 29th, 2009 at 1:34 pm

Posted in SQL Server

TOP と ROWCOUNT を使用したデータ削除 その 1

leave a comment

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 秒

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

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

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

Written by Masayuki.Ozawa

11月 25th, 2009 at 3:41 pm

Posted in SQL Server

SQL Server の初回データベースバックアップとログの切り捨てについて

one comment

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.

Written by Masayuki.Ozawa

11月 24th, 2009 at 1:48 pm

Posted in SQL Server

DBCC PAGE で確認するページ情報

leave a comment

ページの情報を確認するための 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 についても
記載していきたいと思っています。

Written by Masayuki.Ozawa

11月 22nd, 2009 at 7:32 am

Posted in SQL Server

SQL Server の データページの領域管理に使用されているページ

leave a comment

基本的な情報は以下の 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 コマンドを使用することでページの情報を取得することもできますので、
次はこのコマンドを紹介してみたいと思います。

Written by Masayuki.Ozawa

11月 21st, 2009 at 8:24 am

Posted in SQL Server

Exchange 2010 RC を RTM にアップグレードできるか試してみました

leave a comment

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

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

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

image

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

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

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

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

Written by Masayuki.Ozawa

11月 17th, 2009 at 3:02 pm

Posted in Exchange

Exchange 2000 環境に Exchange 2010 を導入する際にエラーとなるタイミング

leave a comment

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

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

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

image

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

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

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

?

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

image

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

image?

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

Written by Masayuki.Ozawa

11月 17th, 2009 at 2:26 pm

Posted in Exchange

インデックスの再構築時のロックの状態

leave a comment

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

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

— ロックの取得状態をわかるようにトランザクションを開始
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] の違いはあるのですが。

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

Written by Masayuki.Ozawa

11月 17th, 2009 at 1:12 pm

Posted in SQL Server

インデックスの再構成時のロックの状態

leave a comment

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

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

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

Written by Masayuki.Ozawa

11月 14th, 2009 at 2:46 am

Posted in SQL Server