Columnstore Archive چیست و نحوه استفاده از آن چگونه است؟

مقدمه

همان طور که می‌دانید داده‌ها در Columnstore ایندکس‌ها به شدت فشرده می‌شوند و بنابراین میزان قابل توجه ایی از فضای دیسک را حتی در مقایسه با Page Compression صرفه جویی می‌نمایند.
SQL Server 2014 نوع دیگری از مکانیسم فشرده سازی با نام Archival Compression را معرفی نمود که می¬توان آن را روی یک Partition خاص، یا یک ایندکس کامل، با مشخص نمودن ویژگی Data_Compression = Columnstore_Archive به ازای ایندکس‌های Columnstore به کار گرفت.
این ویژگی برای فشرده سازی از کتابخانه Xpress 8 و الگوریتم LZ77 استفاده می‌نماید.
این نوع از فشرده سازی به طور مستقیم با داده‌هایی binary کار می‌کند و به نوع داده وابسته نیست.
همان طور که گفته شد میزان فشرده سازی در Columnstore_Archive از Page Compression و همچنین Columnstore عادی بیشتر می‌باشد اما باید به این نکته توجه داشت که سرعت بازیابی داده در هنگام استفاده از Columnstore_Archive نسبت به Columnstorعادی پایین تر است زیرا این روش سربار مربوط به Decompression را شامل می شود.

در Data Warehouse‌ها مرسوم است که داده‌ها برای مدت زمان بسیار طولانی نگه داشته می‌شوند، حتی اگر داده‌های Historical باشند و به ندرت به آن‌ها مراجعه شود.
Archival Compression انتخاب بسیار مناسبی است برای داده‌های Static و داده‌هایی که به ندرت مورد بازیابی قرار می‌گیرند و بنابراین این نوع فشرده سازی ایده آل است که به ازای Partition اعمال گردد. یعنی داده‌های مربوط به پارتیشن‌های گذشته که مراجعه به آن‌ها کم است از این نوع فشرده سازی استفاده نمایند و داده‌های مربوط به پارتیشن‌های جاری از Columnstore ایندکس، زیرا همان طور که گفته شد سرعت بازیابی اطلاعات در Columnstore ایندکس عادی نسبت به Columnstore_Archive بیشتر می‌باشد.
در ادامه خواهیم آموخت که چگونه Data Partitioning و Columnstore ایندکس را به صورت هم زمان بر روی یک جدول پیاده سازی نماییم.
ابتدا یک Database با نام ColumnstoreDB ایجاد می‌نماییم.

Use master Use master
 GO
 IF DB_ID (N'ColumnstoreDB') Is Not Null
 Begin
  Alter DataBase ColumnstoreDB Set Single_User With Rollback Immediate
  Drop DataBase ColumnstoreDB
 End
 GO
 Create DataBase ColumnstoreDB On Primary
 ( Name = ColumnstoreDB, FileName = N'D:\DataBases\Data\ColumnstoreDB.mdf' )
 Log On
 ( Name = ColumnstoreDBLog, FileName = N'D:\DataBases\Data\ColumnstoreDB.ldf' )
GO

سپس یک جدول ایجاد می‌نماییم که در واقع یک Clustered Table می‌باشد زیرا دارای یک Clustered Index است.

Use ColumnstoreDB
 GO
 Drop Table If Exists JointHistory
 Create Table JointHistory
 (
RegDate DateTime Not Null,
ID Int Not Null, OtherValues NVarchar (30) Not Null,
Index IXCRegDateID Unique Clustered (RegDate, ID)
With (Data_Compression = Page, FillFactor = 90)
)

 سپس جدول ایجاد شده را با استفاده از داده‌های فرضی پر می کنیم.

Declare @Date DateTime = '2017-01-01 00:00:00.000' ;
With Tbl (r) as (Select 1 Union All Select r + 1 From Tbl Where r <= 525598)
 Insert Into JointHistory
 Select DATEADD (MINUTE, r, @Date) as ClDate,  r, CONCAT('OtherValues' , 2017, r % 2) as OtherValues from Tbl Option (Maxrecursion 0)
 Insert Into JointHistory
 Select DATEADD (YEAR, 1, RegDate), ID, REPLACE (OtherValues, N'2017', N'2018') From JointHistory
 Insert Into JointHistory
 Select DATEADD (YEAR, 1, RegDate), ID, REPLACE (OtherValues, N'2018', N'2019') From JointHistory Where RegDate >= '2018-01-01 00:00:00.000'
 GO

اکنون با استفاده از دستورات زیر جدول فوق را پارتیشن می نماییم. در اینجا قصد ما این نیست که Data Partitioning را توضیح دهیم زیرا این موضوع نیاز به یک مقاله جداگانه دارد.

Use master
 GO
 Alter DataBase ColumnstoreDB Add FileGroup FG_2017
 Alter DataBase ColumnstoreDB Add FileGroup FG_2018
 Alter DataBase ColumnstoreDB Add FileGroup FG_2019
 Alter DataBase ColumnstoreDB Add FileGroup FG_Next
 GO
Alter DataBase ColumnstoreDB Add File (Name = F_2017, FileName = N'D:\DataBases\Data\F_2017.ndf') To FileGroup FG_2017
Alter DataBase ColumnstoreDB Add File (Name = F_2018, FileName = N'D:\DataBases\Data\F_2018.ndf') To FileGroup FG_2018
Alter DataBase ColumnstoreDB Add File (Name = F_2019, FileName = N'D:\DataBases\Data\F_2019.ndf') To FileGroup FG_2019
Alter DataBase ColumnstoreDB Add File (Name = F_Next, FileName = N'D:\DataBases\Data\F_Next.ndf') To FileGroup FG_Next
 GO
Use ColumnstoreDB
GO
Create Partition Function PF_Main (DateTime)
As Range Right For Values ('2018-01-01 00:00:00.000', '2019-01-01 00:00:00.000', '2020-01-01 00:00:00.000')
Create Partition Scheme PS_Main As Partition PF_Main To (FG_2017, FG_2018, FG_2019, FG_Next)

ابتدا می‌خواهیم ایندکس کلاستر جدول را حذف نماییم و یک Clustered Columnstore Index را به صورت پارتیشن شده روی جدول ایجاد نماییم:

Drop Index IXCRegDateID  On JointHistory
 Create Clustered Columnstore Index IXCRegDateID ON JointHistory On PS_Main (RegDate)

 خطایی که بعد از اجرا دو دستور بالا ایجاد می‌شود را مشاهده نمایید ( این Error بسیار جالب است.)

The statement failed because a columnstore index must be partition-aligned with the base table.
Create the columnstore index using the same partition function and same (or equivalent) partition scheme as the base table. If the base table is not partitioned, create a nonpartitioned columnstore index.

 مفهوم Error این است که ابتدا باید جدول را با استفاده از یک Clustered Index عادی پارتیشن نماییم:

Create Unique Clustered Index IXCRegDateID ON JointHistory (RegDate, ID) With (Data_Compression = Page) On PS_Main (RegDate)

 اکنون با استفاده از دستور زیر می‌توان یک جدول داشت که از هر دو قابلیت Data Partitioning و Clustered Columnstore Index به صورت هم زمان استفاده می‌نماید.

Create Clustered Columnstore Index IXCRegDateID ON JointHistory With (Drop_Existing = ON) On PS_Main (RegDate)

اکنون می‌خواهیم پارتیشن‌هایی که داده‌های آن‌ها به ندرت بازیابی می‌شوند را به صورت Columnstore_Archive فشرده نماییم.
برای این کار از دستور زیر استفاده می‌شود:

Alter Table JointHistory Rebuild Partition = All With (Data_Compression = ColumnStore_Archive ON Partitions(1,2))

با استفاده از View سیستمی زیر می‌توان وضعیت فشرده سازی را به ازای پارتیشن‌های یک جدول مشاهده نمود.

Select Partition_number, rows, data_compression, data_compression_desc from sys.partitions Where object_id = OBJECT_ID(N'JointHistory')

همان طور که در تصویر زیر مشاهده می‌نمایید:
پارتیشن‌های شماره یک و دو به حالت Columnstore_Archive و پارتیشن‌های شماره سه و چهار به صورت Columnstore عادی فشرده شده‌اند. مجددا یادآوری می‌کنم که یکی از ویژگی‌های مهم ایندکس‌های Columnar فشرده سازی داده‌ها می‌باشد.
اگر به هر دلیلی بخواهیم داده‌ها را از حالت فشرده سازی Columnstore_Archive خارج نماییم می‌توانیم با استفاده از دستور زیر این کار را انجام دهیم

Alter Table JointHistory Rebuild Partition = All With (Data_Compression = ColumnStore)