MSSQL: сравниваем data compression и backup compression

MSSQL поддерживает компрессию бэкапов на лету - легковесную и быструю. Также данные можно упаковать внутри базы с помощью DATA_COMPRESSION = PAGE или ROW. Как мы помним, упакованные данные плохо пакуются. Как упаковка данных повлияет на размер бэкапа?

Тестовая база

Создаем большую табличку test в чистой базе (не забываем перевести ее в SIMPLE):

create table seed (n int)
GO
set nocount on
declare @n int=9999 while @n>=0 begin
  insert into seed select @n
  set @n=@n-1
  end
GO
create table test (n int identity, a int, b int, str varchar(128))
GO
create clustered index PK_n on test (n)
GO
insert into test (a,b,str)
  select L.n,R.n,'seed '+convert(varchar,L.n)+' and '+convert(varchar,R.n)
    from seed L, seed R
GO
create index IXa on test (a)
GO
create index IXb on test (b)
GO
create index IXstr on test (str)
GO

Запишем размеры индексов и бэкапов - упакованного и обычного. Затем применим ко всем индексам DATA_COMPRESSION = PAGE

alter index PK_n on test REBUILD with (data_compression=PAGE)
alter index IXa on test REBUILD with (data_compression=PAGE)
alter index IXb on test REBUILD with (data_compression=PAGE)
alter index IXstr on test REBUILD with (data_compression=PAGE)

и снова все запишем.

Наконец, применим DATA_COMPRESSION = ROW

alter index PK_n on test REBUILD with (data_compression=ROW)
alter index IXa on test REBUILD with (data_compression=ROW)
alter index IXb on test REBUILD with (data_compression=ROW)
alter index IXstr on test REBUILD with (data_compression=ROW)

Результаты

Все размеры приведены в Mb

Index	no compression	compression PAGE	compression ROW
PK_n	4355	1992	3646
IXa	1355	854	1152
IXb	1355	963	1152
IXstr	3091	1239	3174

Теперь рассмотрим размер full backups, тоже в мегабайтах:

Backup	no compression	compression PAGE	compression ROW
no compression	10417	5189	9364
compression	1934	2331	2285

Выводы

Америку я не открыл, результаты довольно ожидаемые, но хотелось убедиться еще раз

• Упаковка бэкапа всегда уменьшает его размер, вне зависимости от типа упаковки данных внутри, так что это полезно.

• Как и ожидалось, наиболее сильно бэкап уменьшается, если данные внутри базы не сжаты (до 5 раз). В реальности этот коэффициент обычно 3-4

• compression=PAGE очень сильно уменьшает объем данных и увеличивает cache hits ratio. Очевидно, ценой повышенного CPU

• compression=ROW работает хуже и иногда даже увеличивает место, занимаемое данными.

• И все же data compression может ненамного увеличить объем упакованного бэкапа