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


Ранее этот пост находился на форуме сообщества Russian SQL Server User Group по адресу http://sqlclub.ru/forum/viewtopic.php?f=6&t=1064, что позволяло во время демонстрации ссылаться на скрипт, чтобы слушатели могли не только посмотреть, но и впоследствии самостоятельно воспроизвести демку для закрепления материала. К сожалению, безответственный администратор по имени Сергей Заворуев положил сайт sqlclub.ru и ударился в бега, став недоступным ни по e-mail, ни по телефону, чем немало подставил сообщество. Хотелось бы предостеречь будущих возможных деловых партнеров Сергея Заворуева (http://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) напрямую.


Skip to main content