SE の雑記

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

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

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

Leave a Reply

*