追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その9

こんにちは、nagino です。

この不況ということで、仕事が無くて窓際族は暇です。

いやまあ、首切りに備えなければいけないのですが（笑）。

# 笑っている場合ではない。。。

それはさておき、今回は分離レベルについてです。

分離レベルとパフォーマンスの関係を理解するには、ロック待ちの挙動を考えると分かりやすいかと思います。

通常 SQL Server では分離レベルが Read Committed になっています。

これは、SELECT する際に共有ロックをかけます。

共有ロックがかかっていると、共有ロックは重ねがけできますが、排他ロックがかけられません。

SQL Server は通常行レベルロックを行いますので、通常共有ロック自体は問題になりません。

しかし、全行検索や、クラスタ化インデックスが存在しないテーブルの SELECT、大量のロックによるロックエスカレーションなどが発生した場合、テーブル単位で共有ロックがかかることがあります。

そうすると、そのテーブルのどの行も排他ロックがかけられず、更新処理が待たされます。

良くあるのは、初期表示で全件表示しているシステム、あるいはもっと困るのは全件 SELECT していてプログラム内でページングやフィルタリングしているシステムで、そのテーブルの更新処理がやたら遅い、あるいは誰か更新している時に表示が遅い、という形でのパフォーマンス劣化です。

本質的には、プログラムを適切に修正することですが、そのデータベース全体でダーティリードしても差し支えないようなデータ、ないしはそういう前提で運用可能なシステムの場合、データベースの分離レベルを Read Uncommitted にしてしまうことで、共有ロックをかけなくなり、パフォーマンスが改善することがあります。

通常はダーティリードしても構わない処理毎に、プログラム側でトランザクションレベルで分離レベルを設定するのが筋ですが、処理がストアドプロシージャになっている場合は、ストアドプロシージャ内で分離レベルを設定することができますので、その場合は DB 管理者でなんとかすることができます。

まあ、建前上そうであっても、実際はプログラムでどのようにストアドプロシージャを使用しているか等々を考えると、なかなか手を出せないと思いますので、通常は開発時に適切に考慮するのが筋ですけれども。。。

そもそもプロシージャ化されているのであれば、クエリ自体をチューニングするのが本筋ですし。。。。。。