twitter 経由で Azure SQL DB to support page and row compression capabilities を知ったのですが、SQL Database で行 / ページ圧縮が使用できるようになったようです。
# 東アジア / 日本 (東) で試したところ両方使えました。
これにより以下のようなクエリ実行が可能になりました。
IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULL
DROP TABLE dbo.sample_table
GO
CREATE TABLE dbo.sample_table
(
c1 int NOT NULL,
c2 char(10) NULL,
c3 datetime NULL,
CONSTRAINT PK_sample_table PRIMARY KEY (c1) WITH (DATA_COMPRESSION=PAGE)
)
GO
以下のようなクエリを実行して、3 種類のテーブル (通常 / 行圧縮 / ページ圧縮) を作成してみます。
IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULL
DROP TABLE dbo.sample_table
GO
CREATE TABLE dbo.sample_table
(
c1 int NOT NULL,
c2 char(10) NULL,
c3 datetime NULL,
CONSTRAINT PK_sample_table PRIMARY KEY (c1)
)
GO
IF OBJECT_ID('dbo.sample_table_row', 'U') IS NOT NULL
DROP TABLE dbo.sample_table_row
GO
CREATE TABLE dbo.sample_table_row
(
c1 int NOT NULL,
c2 char(10) NULL,
c3 datetime NULL,
CONSTRAINT PK_sample_table_row PRIMARY KEY (c1) WITH (DATA_COMPRESSION=ROW)
)
GO
IF OBJECT_ID('dbo.sample_table_page', 'U') IS NOT NULL
DROP TABLE dbo.sample_table_page
GO
CREATE TABLE dbo.sample_table_page
(
c1 int NOT NULL,
c2 char(10) NULL,
c3 datetime NULL,
CONSTRAINT PK_sample_table_page PRIMARY KEY (c1) WITH (DATA_COMPRESSION=PAGE)
)
GO
このテーブルに対して以下のクエリでデータを挿入します。
SET NOCOUNT ON GO DECLARE @i int = 1 WHILE (@i <= 10000) BEGIN INSERT INTO sample_table VALUES(@i, @i, GETDATE()) INSERT INTO sample_table_row VALUES(@i, @i, GETDATE()) INSERT INTO sample_table_page VALUES(@i, @i, GETDATE()) SET @i += 1 END
データ挿入後に以下のクエリでページの使用状況を確認してみます。
SET NOCOUNT ON
GO
SELECT
OBJECT_NAME(object_id),*
FROM sys.dm_db_partition_stats
WHERE object_id IN
(OBJECT_ID('dbo.sample_table'),
OBJECT_ID('dbo.sample_table_row'),
OBJECT_ID('dbo.sample_table_page'))
結果がこちらになります。
使用しているページ数 (in_row_used_page_count) のサイズを見ると
通常 > 行圧縮 > ページ圧縮
の順となっています。
圧縮をすることでページ内の密度が増え、CPU の負荷増とのトレードオフで以下のようなメリットがあります。
- 使用するページ数が減少することで、データベースの使用領域を減らす
- ページ密度が上がることで、一度に読み取る行数が増える
- ページ密度が上がることで、メモリ上に保持できるデータが増える
- ダーティーページの書き込み時のディスク負荷が減る
ボックスタイプ (通常の SQL Server) では行 / ページ圧縮の機能は Enterprise Edition の機能となりますが、SQL Database のエディションは DB の上限サイズのみが異なり機能差がありませんのでどのエディションでも使用できます。
SQL Database では以下のようなオンラインのインデックス操作もできますので、以下のようなインデックス再構築による既存インデックスへの圧縮の適用が可能です。
ALTER INDEX [PK_sample_table_page] ON [dbo].[sample_table_page] REBUILD WITH(ONLINE=ON, DATA_COMPRESSION=PAGE)
オンラインインデックス操作もボックスタイプの場合は Enterprise Edition の機能となりますので、SQL Database でこの辺の機能がのエディションでも使えるのはメリットなのかなと思います。