SQL Server 2008R2 データ圧縮 その①

最近のデータベースはデータ圧縮が流行っているので SQL Server 2008R2 で少しさわってみました。
圧縮は SQL Server 2008/2008R2 の Datacenter と Enterprise と Developer のエディションで使用できるそうです。
SQL Server のデータ圧縮は SQL Server Management Studio から データベース内のテーブルを選んで、右クリックのメニュー - Storage - Manage Compression で 圧縮ウィザードを起動して設定します。


SQL Server 2008 でのデータ圧縮には「行圧縮(ROW Compression)」と「ページ圧縮(PAGE Compression)」があります。
行圧縮は固定長データ型を可変長にして管理する方法だそうで、ページ圧縮は行圧縮に加えてページ内の共通パターンを集約するそうです。
圧縮といってもデータベースやログの空き領域を切り詰めるシュリンクとは別物です。
NTFSのファイル圧縮や圧縮ドライブとも違います。これらにデータベースを構築する事は非推奨になってます。

圧縮はテーブル単位で設定しますが、CREATE TABLE でテーブルを作成する時に圧縮を指定する方法と、圧縮なしで作成したテーブルを ALTER TABLE で変換する方法があります。
後者は冒頭の圧縮ウィザードになる訳ですが、何千個もテーブルがあるといちいちやってられません。

ALTER TABLE テーブル名 REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = ROW又はPAGE ) で出来るので、全テーブルを変換する SQL文を作ってみました。
また、圧縮はインデックスでも同じ様に作れるのでインデックスも圧縮してみます。
ALTER INDEX インデックス名又はALL ON テーブル名 REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = ROW又はPAGE ) でINDEXの圧縮が出来る様です。
以前にインデックス再構築で作ったスクリプトを流用しています。
文の先頭が解かり辛いので > を先頭に付けてます。
とりあえず全部行圧縮にします。
----------------------------------------------------------------------------
> sqlcmd -E -d SMS -S atlas -Q "exec sp_tables" -o tables.txt -h -1
> if exist tbcompress.sql del tbcompress.sql
> for /F "eol=( tokens=2,3,4" %%i in (tables.txt) do @if %%k == TABLE @if not %%i == sys @if not %%i == INFORMATION_SCHEMA @echo ALTER TABLE [%%i].[%%j] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = ROW ) >> tbcompress.sql && @echo go >> tbcompress.sql && @echo ALTER INDEX all ON [%%i].[%%j] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = ROW ) >> tbcompress.sql && @echo go >> tbcompress.sql
> sqlcmd -E -d SMS -S atlas -i tbcompress.sql -e
----------------------------------------------------------------------------
SMS はデータベース名, atlas はサーバ名です。
一行目でテーブルのリストを tables.txt に出力します。
二行目で tbcompress.sql を前回実行したものがあれば削除します。
三行目で ALTER TABLE と ALTER INDEX でテーブルやインデックスを圧縮するSQL文 tbcompress.sql を作成します。
四行目で圧縮するSQL文 tbcompress.sql を実行します。
圧縮の種類は DATA_COMPRESSION = のところで指定します。
行圧縮(ROW) , ページ圧縮(PAGE) , 圧縮解除(NONE) が指定出来るので状況に応じて変更して下さい。

tbcompress.sql の中身は下のSQLがテーブル毎に繰り返し記述されます。
文の先頭が解かり辛いので > を先頭に付けてます。
----------------------------------------------------------------------------
> ALTER TABLE [sms].[DSVASSESSADMIN] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = ROW )
> go
> ALTER INDEX all ON [sms].[DSVASSESSADMIN] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = ROW )
> go
----------------------------------------------------------------------------

コメント 1

さんくすあろと
by びへいびあ (2016-03-03 17:26)