SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server のデータ圧縮のサイズ見積もりを複数テーブルを対象に実施

leave a comment

SQL Server 2008 移行で実装されたデータ圧縮ですが、圧縮後のサイズ見積もりを SSMS で調べることができます。

データ圧縮対象のテーブル / インデックスを選択し、ウィザードを使用して、サイズの見積もりをすることができるのですが、見積もりはテーブルまたはインデックスを選択して実行する必要があるため、一つのオブジェクトに対してしか見積もることができません。
image

この圧縮時の見積もりを複数のオブジェクトに対して実行してみるというのが今回の内容になります。

■複数のオブジェクトに対してデータ圧縮の見積もりを実施


データ圧縮の見積もりですが、[sp_estimate_data_compression_savings] を使用して実行されています。
そのため、このストアドプロシージャをカーソル等を使用して、複数のオブジェクトに対して実行することで複数のデータ圧縮の見積もりを取得することがかのうです。

以下のようなクエリで取得ができるかと思います。
# このクエリだとインデックス付きビューは取得できないと思いますが…。

DECLARE @schema sysname , @name sysname
DECLARE @compmode sysname = ‘PAGE’
DECLARE @table TABLE(
[object_name] sysname
, [schema_name] sysname
, [index_id] int
, [partition_number] int
, [size_with_current_compression_setting (KB)] bigint
, [size_with_requested_compression_setting (KB)] bigint
, [sample_size_with_current_compression_setting (KB)] bigint
, [sample_size_with_requested_compression_setting (KB)] bigint
)

DECLARE userobject_cursor CURSOR FAST_FORWARD
FOR
SELECT OBJECT_SCHEMA_NAME(object_id), name
FROM sys.objects
WHERE type = ‘U’

OPEN userobject_cursor

FETCH NEXT FROM userobject_cursor
INTO @schema, @name

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @table EXEC(‘ sp_estimate_data_compression_savings ”’ + @schema + ”’, ”’ + @name + ”’, NULL, NULL, ”’ + @compmode + ””)
FETCH NEXT FROM userobject_cursor
INTO @schema, @name
END

SELECT
SUM([size_with_current_compression_setting (KB)]) AS [size_with_current_compression_setting (KB))(Total)]
, SUM([size_with_requested_compression_setting (KB)]) AS [size_with_requested_compression_setting (KB)(Total)]
FROM @table

SELECT * FROM @table

CLOSE userobject_cursor
DEALLOCATE userobject_cursor

image

size_with_current_compression_setting (KB) が現在のサイズ、size_with_requested_compression_setting (KB) が圧縮後の想定される見積もりサイズになるはずです。

データベースのバックアップ圧縮はページ圧縮に近い圧縮方式になるようなので、バックアップ圧縮のサイズを見積もるときの参考に使えるかもしれませんね。
# ページ圧縮をしたデータベースのバックアップをバックアップ圧縮で取得した場合、圧縮の効果がみられることもあり、ページ圧縮されたデータベースのサイズより小さくなることもありますので、あくまでも参考値で実機確認は必要です。

Written by masayuki.ozawa

11月 11th, 2011 at 8:30 am

Posted in SQL Server

Tagged with

Leave a Reply

*