[SQL Azure] データベースの分離レベル（Isolation Levels）について

こんにちは、Windows Azure デベロッパー サポート チームです。本日は、SQL Azure の分離レベルについてご紹介します。

Read-Committed-Snapshot（RCSI）が既定

SQL Azureの ガイドラインと制限事項 に紹介されているように、SQL Azure のデータベースの分離レベルはSQL Server 2005から追加された機能の一つである、行レベルのバージョン管理に基づく分離レベルが採用されており、データベース作成時にREAD_COMMITTED_SNAPSHOT データベースオプションと、ALLOW_SNAPSHOT_ISOLATION データベースオプションは両方とも ON に設定されます。また、SQL Azure では ALTER DATABASE 句を使用し、SET <snapshot_option> を実行することがサポートされないため、ユーザーはこの設定を変更することができません。

行レベルのバージョン管理に基づく分離レベルを採用することのメリット

トランザクションによりデータが読み取られるとき、読み取り操作では、読み取るデータに対して共有 (S) ロックが獲得されないので、データを変更しているトランザクションはブロックされません。そのため、読み取りの同時実行性が向上します。また、行のバージョン管理を使用によりデータの読み取りの一貫性をステートメントレベルまたはトランザクションレベルで保証されます。つまり、データ読み取りの一貫性を保ちつつ、変更中のデータに対して読み取りの同時アクセスが可能になります。詳細は 行のバージョン管理に基づく分離レベルの利点 をご覧ください。

行レベルのバージョン管理に基づく分離レベルが採用される際のコスト

行レベルのバージョン管理に基づく分離を使用する場合は、行バージョンの管理と読み取りに必要なリソース使用量が増加というオーバーヘッドがあります。このオーバーヘッドの１つに、データベース行に行のバージョン管理情報を格納するための格納領域の確保（行のサイズの増加）があります。その他の詳細については、行のバージョン管理に基づく分離レベルのコストをご覧ください。

SQL Azureでは、このオーバーヘッドに関するコストよりも、一貫性を保ちつつロックを最小限に抑えるという同時実行の利点の方が上回っていると考えられ、行レベルのバージョン管理が採用されています。

なお、SQL Azureでは、ユーザー データベース のみ課金対象となるので、バージョン ストア 用に利用されるTempDBのデータベースの使用量は課金されません。しかし、一つのセッションによるTempDBの使用量が‘５GBを超える場合、そのセッションはサーバーにより切断されるので、アプリケーションの設計には留意が必要です。（詳細は SQL Azureの高可用性を保持するための仕組み をご覧ください。）

行のサイズの増加はどれくらい見積もったらいい？

SQL Azureではデータベース量に基づくエディションやデータベース最大容量の選択を行い、使用量に応じて課金されます。したがってデータベースのサイズは事前にある程度見積もっておく必要がありますが、行レベルのバージョン管理による行のサイズの増加をどのように見積もったらいいでしょうか。

SQL Server 2008 R2 を使って、実際に検証してみましょう。まず、データベース SnapShotisolationTest を作成します。

CREATE DATABASE SnapShotIsolationTest

SQL Server 2008 R2でデータベースを作成すると、既定でREAD_COMMITTED_SNAPSHOT データベースオプションと、ALLOW_SNAPSHOT_ISOLATION データベースオプションは両方とも OFF（RCSI OFF）に設定されます。

select is_read_committed_snapshot_on, snapshot_isolation_state_desc,
snapshot_isolation_state from sys.databases where name='SnapShotIsolationTest'

テーブル Tbl1 を作成し、データを1行追加してみます。

use SnapShotIsolationTest
go

create table tbl1 (c1 int)
insert into tbl1 values (1)
go

このとき 最大レコードサイズ は11Bytesであることがわかります。

select max_record_size_in_bytes
  from sys.dm_db_index_physical_stats
  (db_id('SnapShotIsolationTest'), object_id('tbl1'),null, null, 'DETAILED')

次に、追加した行の値を更新してみます。

update tbl1 set c1 = 2

最大レコードサイズ は 11Bytesのままです。

select max_record_size_in_bytes
  from sys.dm_db_index_physical_stats
  (db_id('SnapShotIsolationTest'), object_id('tbl1'),null, null, 'DETAILED')

次に、SnapShotIsolationTestデータベースの データベース プロパティ を SQL Azureと同じ設定、つまりREAD_COMMITTED_SNAPSHOT データベースオプションと、ALLOW_SNAPSHOT_ISOLATION データベースオプション を両方とも ON（RCSI ON）にします。

ALTER DATABASE SnapShotIsolationTest SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE SnapShotIsolationTest SET READ_COMMITTED_SNAPSHOT ON
select is_read_committed_snapshot_on, snapshot_isolation_state_desc,
  snapshot_isolation_state from sys.databases where name='SnapShotIsolationTest'

テーブルの行の値を更新してみます。

update tbl1 set c1 = 1
select max_record_size_in_bytes
  from sys.dm_db_index_physical_stats
  (db_id('SnapShotIsolationTest'), object_id('tbl1'),null, null, 'DETAILED')

最大レコードサイズは 25Bytes になります。つまり、14Bytes ほど行のバージョン管理のための格納領域確保による行のサイズの増加があることがわかります。この14Bytes には、行のバージョン管理を行うために必要な情報であるトランザクション シーケンス番号 と 行のIdentifierが含まれます。

ここで、READ_COMMITTED_SNAPSHOT データベース オプション と、ALLOW_SNAPSHOT_ISOLATION データベースオプションを両方とも OFFに戻し、テーブルの行の値の更新を行ってみると、14Bytesの増分は発生しなくなることが確認できます。（ただし、更新対象となった行の行のバージョン管理がされなくなる場合に14Bytesの増分がなくなるのであって、RCSI=ONの際に更新され行のバージョン管理のための格納領域を確保している行は、RCSI=OFFにした後も、新たにその行が更新されない限り、行のバージョン管理を行うための領域はそのまま残ります。）

例として、以下のようなスクリプトをそれぞれSQL Azure 上、SQL Server上（RCSI ON/OFF）で実行し、実行直後のテーブルのサイズを比較すると次のような結果になります。（テーブルのサイズを確認する方法は SQL Azure データベースの使用サイズを確認するには をご覧ください。）

create table tbl1 (c1 bigint not null, c2 char(25), c3 char(25), c4 datetime)
create clustered index tbl1_cluidx on tbl1 (c1)
create index tbl1_idx on tbl1(c3)
go

DECLARE @count bigint
DECLARE @Random INT
DECLARE @RowString VARCHAR(10)
set @count = 0
WHILE (@count < 300000)
BEGIN
  select @Random = ROUND(((@count - 1) * RAND()),0)
  insert into tbl1
    values(@count, 'today is sunny', @Random, getdate())
    set @count = @count + 1
END

-- ここで一度テーブル tbl1のサイズの確認を実施します

alter index tbl1_cluidx on dbo.tbl1 rebuild with (ONLINE =ON)
alter index tbl1_idx on dbo.tbl1 rebuild with (ONLINE =ON)
go

上記の例のように、SQL Serverの既定の分離レベルのデータベース上で作成されたサイズが 約 38MBの表は、SQL Azure上では約49MBのサイズが必要となります。SQL Azureへ移行する前に、SQL Azureと同じ分離レベルのデータベースを SQL Server上に作成し、テーブルを作成してみることで、行のバージョン管理に基づく行のサイズの増分を加味した必要なサイズを算出することが可能です。

SQL Azureデータベースの使用量を節約するには？

SQL Azureでは、各ユーザーデータベースに対して、データベース使用量が課金されます。上記の例でわかるように、データの変更が多く発生したテーブルのインデックスを再構築することで、ある程度領域を節約することができます。SQL Server上にもともと存在するマスターテーブルなどをSQL Azureに移行することがありますが、移行時に多くの行の追加（INSERT）が発生するような場合も、テーブルの作成後（または移行後）にインデックスの再構築を実施することで、およそ5%から10%ほどの領域を節約することができる場合があります。

さらに詳しい情報は、以下のサイト(一部英語のサイトになります)をご覧ください。

• SQL Azure FAQ
• データベース エンジン での行のバージョン管理に基づく分離レベル
• 行のバージョン管理に基づく分離レベルの選択
• Overhead of row Versioning

また、Windows Azure / SQL Azure のサポートについては、以下を参照ください。

• Windows Azure のサポート

--
Senior Support Escalation Engineer
Kayoko Gray