連載
» 2006年05月30日 00時00分 公開

Dr. K's SQL Serverチューニング研修(4):排他制御メカニズムから“待ち”原因を究明する (2/3)

[熊澤幸生, 工藤淳(インタビュアー),CSK Winテクノロジ/オフィスローグ]

ロックをどこまでかけるか?
その範囲を示したのが「粒度」

ロックの粒度は6種類

 SQL Serverでは、粒度の違いによって6種類のロックが提供されています。表1にロックの粒度とその説明を示します。

ロックの粒度 説明
RID(行識別子) 行単位(RID)でロックする
キー範囲 インデックス内のキー行をロックする
ページ 8Kbytesのデータページ/インデックスページをロックする
エクステント 連続する8つのデータページ/インデックスページをロックする
テーブル データ/インデックスのテーブル全体をロックする
データベース データベース全体をロックする
表1 ロック粒度とリソースの対応(粒度の細かいものから昇順)

 表1を見ていただくと分かりますが、一番粒度が小さいのが「RID(行識別子)」、次は「キー範囲」になっています。ここでいうキーとはインデックス内のキー行で、単一のキーに対しても、範囲指定した場合にはその範囲でもロックがかけられます。これらが最も小さい単位でのロックになります。その上になると、今度は「ページ」、つまり8Kbytesのページ単位でのロックです。その上は「エクステント」「テーブル」「データベース」と、どんどん粒度が大きくなっていきます。

 ロックの粒度は、パフォーマンスと同時実行性のトレードオフの関係にあります。粒度を細かくすれば同時実行性能は向上しますが、ロック処理のオーバーヘッドは大きくなります。例えば、1つのテーブルに1億行のデータを持っているとしましょう。ここで1億件のテーブルスキャンを行った場合、行ロックしか持っていないと、1億行の共有ロックをかけなくてはなりません。しかし、8Kbytesの1ページに100行入るようテーブルを設計しておいてやれば、必要なロックはページ単位になりますので、1億÷100=100万個の共有ロックで済み、パフォーマンスの向上を期待できます。

ロックの粒度はヒント句で変更できる

 SQL Serverでは、ロックマネージャがロック粒度の大きさを自動的に選択する仕組みを持っています。しかし必要な場合は、アプリケーションから細かくロックの制御を行うことも可能です。その場合はヒント句で下記のように指定できるわけです(表2)。

ロックのヒント 説明
HOLDLOCK 共有ロックをトランザクションの完了時まで保持する
NOLOCK 共有ロックを実行せず、排他ロックも認めない
PAGLOCK ページレベルのロックを使用する
READPAST ロックされた行をスキップする
ROWLOCK 行レベルのロックを使用する
TABLOCKX テーブルに排他ロックを使用する(ステートメントまたはトランザクションの終了まで保持される)
UPDLOCK テーブルの読み出しの間、共有ロックの代わりに更新ロックを使用する(ステートメントまたはトランザクションの終了まで保持される)
XLOCK 排他ロックを使用する(ステートメントで処理されるすべてのデータについて、トランザクションの終了まで保持される)
表2 SQL文で指定できるロックの代表的なヒント句

 ロックに関係するパフォーマンスを監視するには、第3回「パフォーマンスを満たす物理メモリ量を算出する」の3ページ目で紹介した「sysperfinfo」システムビューのパフォーマンス・カウンタを調べればよいのです。図3を見ると、「instance_name」列に先ほど説明したロックの粒度が示されています。

図3 sysperfinfoのロック・オブジェクト一覧(画像をクリックすると拡大します) 図3 sysperfinfoのロック・オブジェクト一覧
画像をクリックすると拡大します

 こういったデータを手掛かりに、パフォーマンス低下の原因となっているアプリケーションのロック粒度を変更する、あるいはアプリケーション設計の変更を提案するなどが、パフォーマンス・チューニングの仕事です。パフォーマンス・カウンタから原因を探り当てるには「こうすればよい」と簡単に示せるものではありません。しかしSQL Serverの内部構造を詳しく調べていくことで、チューニングのポイントを理解していくことは可能です。そこで次ページではさらに深くSQL Serverの内部構造に分け入ってみましょう。(次ページへ続く)

Copyright © ITmedia, Inc. All Rights Reserved.

編集部からのお知らせ

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。