Префиксно-словарное и var-сжатие

Ранее этот пост находился на форуме сообщества Russian SQL Server User Group по адресу https://sqlclub.ru/forum/viewtopic.php?f=6&t=1064, что позволяло во время демонстрации ссылаться на скрипт, чтобы слушатели могли не только посмотреть, но и впоследствии самостоятельно воспроизвести демку для закрепления материала. К сожалению, безответственный администратор по имени Сергей Заворуев положил сайт sqlclub.ru и ударился в бега, став недоступным ни по e-mail, ни по телефону, чем немало подставил сообщество. Хотелось бы предостеречь будущих возможных деловых партнеров Сергея Заворуева (https://1stat.ru/?show=whois\&person=Sergey V Zavoruev), разработка, продвижение и сопровождение сайтов, сетевые работы любой сложности, о риске, который они на себя берут, затевая с ним совместные проекты. Может внезапно кидануть и смыться.

----------------------------------------------------------------------------------------------------------------------

/* Компрессия страниц и записей в SQL Server 2008. Эта дема будет показываться на лонче 18.03.2008 */

--Компрессия может использоваться для таблиц и индексов. Что такое компрессия

--для страниц и записей, рассказывается на слайдах.

--При партиционировании компрессию можно применять к каждой партиции в отдельности.

--Создаем БД о трех файл-группах.

use tempdb

if exists (select 1 from sys.databases where name = 'TestCompression') begin

alter database TestCompression set single_user with rollback immediate

drop database TestCompression

end

create database TestCompression

on primary

(name = TestCompression,

filename = 'C:\Temp\TestCompression.mdf'),

filegroup TestCompression_FG1

(name = TestCompression1,

filename = 'C:\Temp\TestCompression_FG1_File1.mdf'),

filegroup TestCompression_FG2

(name = TestCompression2,

filename = 'C:\Temp\TestCompression_FG2_File1.mdf')

go

use TestCompression

--Создаем функцию и схему для автоматического разложения по партициям

create partition function myRangePF (int)

as range left for values (0, 1, 2);

--left означает, что междиапазонная граница относится к левому диапазону:

--(-бесконечность, 0], (0, 1], (1, 2], (2, +бесконечность)

create partition scheme myRangePS

as partition myRangePF

to ([primary], TestCompression_FG1, TestCompression_FG2, [primary]);

--сделали привязку интервалов к файл-группам

--Создаем таблицу для экспериментов

create table Customers(

CustomerKey int,

Title nvarchar(10),

FirstName nvarchar(50),

LastName nvarchar(50),

Gender nvarchar(1),

YearlyIncome money,

TotalChildren tinyint,

Education nvarchar(40),

Occupation nvarchar(100),

AddressLine nvarchar(255),

Phone nvarchar(20),

PartitionID int

)

on MyRangePS(PartitionID)

--Наполняем ее так, что по 1000 одинаковых записей попадает в primary, FG1, FG2

insert Customers

(CustomerKey, Title, FirstName, LastName, Gender, YearlyIncome, TotalChildren,

Education, Occupation, AddressLine, Phone, PartitionID)

select top 1000 CustomerKey, Title, FirstName, LastName, Gender, YearlyIncome, TotalChildren,

EnglishEducation, EnglishOccupation, AddressLine1 + AddressLine2, Phone, 0

from AdventureWorksDW.dbo.DimCustomer

insert Customers

(CustomerKey, Title, FirstName, LastName, Gender, YearlyIncome, TotalChildren,

Education, Occupation, AddressLine, Phone, PartitionID)

select top 1000 CustomerKey, Title, FirstName, LastName, Gender, YearlyIncome, TotalChildren,

EnglishEducation, EnglishOccupation, AddressLine1 + AddressLine2, Phone, 1

from AdventureWorksDW.dbo.DimCustomer

insert Customers

(CustomerKey, Title, FirstName, LastName, Gender, YearlyIncome, TotalChildren,

Education, Occupation, AddressLine, Phone, PartitionID)

select top 1000 CustomerKey, Title, FirstName, LastName, Gender, YearlyIncome, TotalChildren,

EnglishEducation, EnglishOccupation, AddressLine1 + AddressLine2, Phone, 2

from AdventureWorksDW.dbo.DimCustomer

--Вот информация по партициям, сколько в каждой записей с какими диапазонами

select $partition. myRangePF(PartitionID) as PartNo, min(PartitionID), max(PartitionID), count(*)

from Customers group by $partition. myRangePF(PartitionID) order by PartNo

--Вот сколько места они занимают

select au.data_space_id, p.partition_number, au.total_pages, au.used_pages, au.data_pages, p.rows, p.data_compression_desc, ds.name

from sys.allocation_units au join sys.partitions p on au.container_id = p.partition_id

join sys.data_spaces ds on au.data_space_id = ds.data_space_id

where p.object_id = object_id('Customers')

--Крайние левая и правая партиции отображены на файл-группу primary.

--В левой лежит 1000 строк с PartitionID = 0, в правой, понятно, ничего нет

--В двух средних лежат по 1000 строк с признаком PartitionID = 1 и 2 соответственно.

--Оценка потенциального выигрыша в месте от компрессии

--Для таблицы в целом

exec sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'Customers',

@index_id = null, @partition_number = null, @data_compression = 'page'

--По партициям и методам сжатия

exec sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'Customers',

@index_id = null, @partition_number = 2, @data_compression = 'page'

exec sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'Customers',

@index_id = null, @partition_number = 3, @data_compression = 'row'

--Сжимаем

alter table Customers rebuild partition = 2 with (data_compression = page)

alter table Customers rebuild partition = 3 with (data_compression = row)

--Вот сколько места они занимают теперь

select au.data_space_id, p.partition_number, au.total_pages, au.used_pages, au.data_pages, p.rows, p.data_compression_desc, ds.name

from sys.allocation_units au join sys.partitions p on au.container_id = p.partition_id

join sys.data_spaces ds on au.data_space_id = ds.data_space_id

where p.object_id = object_id('Customers')

--Расжатие

alter table Customers rebuild partition = 3 with (data_compression = none)

----------------------------------------------------------------------------------------------------------------------

Йа нопесал оч.хор.хр.пр., кот. сжимает/разжимает все пользовательские таблицы внутри БД вместе с их индексами. Возьмем какую-нибудь базу данных и посмотрим, как там чего разлеглось.

select o.name, au.total_pages, au.used_pages, au.data_pages, p.rows, p.data_compression_desc, p.partition_number, au.data_space_id, ds.name

from sys.allocation_units au join sys.partitions p on au.container_id = p.partition_id

join sys.data_spaces ds on au.data_space_id = ds.data_space_id

join sys.objects o on p.object_id = o.object_id

where o.is_ms_shipped = 0

Скрипт 1

Можно видеть, что она состоит из двух несжатых таблиц по столько-то записей каждая, занимающих столько-то страниц. Можно умножить на 8К и получить размер на диске.

name	total_pages	used_pages	data_pages	rows	data_compression_desc	partition_number	data_space_id	name
WebStat_Jan09	10418	10411	10410	262077	NONE	1	1	PRIMARY
WebStat_Mar09	22146	22141	22140	1002422	NONE	1	1	PRIMARY

Табл.1

Данная процедура их сжимает:

if exists(select 1 from sys.procedures where name = ' СжатьРазжатьВсюБазу' and schema_id = schema_id()) drop proc СжатьРазжатьВсюБазу

go

create proc СжатьРазжатьВсюБазу @dbname sysname = null, @compress bit = 1 as

begin

declare @precmd nvarchar(1000), @postcmd nvarchar(1000)

if @dbname is not null select @precmd = 'use ' + @dbname, @postcmd = 'use ' + db_name()

declare @compression varchar(4) = case @compress when 1 then 'page' else 'none' end

declare @cmd1 nvarchar(1000) = 'alter table ? rebuild partition = all with (data_compression = ' + @compression + ')'

declare @cmd2 nvarchar(1000) = 'alter index all on ? rebuild partition = all with (data_compression = ' + @compression + ')'

exec sp_MSforeachtable @command1 = @cmd1, @command2 = @cmd2, @replacechar = '?', @precommand = @precmd, @postcommand = @postcmd

end

Скрипт 2

Замечание: процедура sp_MSforeachtable перебирает только пользовательские таблицы, не обращая внимание на служебные, что в данном случае совпадает с желаниями рабочего подростка, поск. избавляет от необходимости фильтровать наподобие where o.is_ms_shipped = 0 в Скрипте 1.

Пример вызова.

use pubs

exec WebStat.. СжатьРазжатьВсюБазу @dbname = 'Webstat', @compress = 1

use WebStat

Смотрим результат. Повторяем запрос Скрипт 1:

name	total_pages	used_pages	data_pages	rows	data_compression_desc	partition_number	data_space_id	name
WebStat_Jan09	4434	4432	4431	262077	PAGE	1	1	PRIMARY
WebStat_Mar09	4970	4964	4963	1002422	PAGE	1	1	PRIMARY

Табл.2

Видим, что все сжалось. Прикидки по длительности процесса делайте сами, т.к. это сильно зависит от конкретного железа и распределения данных. Данный детский пример на вистовой виртуалке на ноутбуке длился 42 сек. Т.е. грубо 40 сек. на 30 тыс.страниц.

В процедуре СжатьРазжатьВсюБазу захардкодена page-компрессия, т.к. в большинстве задач, с которыми я сталкивался, она оказывалась предпочтительнее. Если есть желание использовать также row-компрессию, перепишите параметр @compress. Можете сделать его не булевым, а строковым и передавать в него тип компрессии (page, row, none) напрямую.