خانه SQL Server Columnstore Archive چیست و نحوه استفاده از آن چگونه است؟ SQL Server افزایش سرعت SQL Server نوشته شده توسط: مهدی قپانوری تاریخ انتشار: ۱۳ آبان ۱۳۹۸ آخرین بروزرسانی: ۲۲ شهریور ۱۴۰۲ زمان مطالعه: 12 دقیقه ۰ (۰) مقدمه همان طور که میدانید دادهها در 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) چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش دانلود مقاله Columnstore Archive چیست و نحوه استفاده از آن چگونه است؟ فرمت PDF 6 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 15 مقاله توسط این نویسنده محصولات 1 دوره توسط این نویسنده مهدی قپانوری مهدی قپانوری بیش از 6 سال است که در زمینههای نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزههایPerformance Tuning، Database Administration، Database Development و طراحی سیستمهای OLTP میباشد. مهدی علاقهمند به R&D در حوزههای نوین SQL Server است. معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ محمدرضا خلیل زاده ۲۷ / ۰۵ / ۰۰ - ۰۱:۳۰ سلام همکار عزیز بسیار عالی بود. تشکر فراوان. پاسخ به دیدگاه محمدرضا خلیل زاده ۲۷ / ۰۵ / ۰۰ - ۰۱:۳۰ سلام همکار عزیز بسیار عالی بود. تشکر فراوان. پاسخ به دیدگاه ارسلانی ۱۴ / ۰۸ / ۹۸ - ۰۷:۴۸ با عرض سلام بسیار مفید بود. سپاسگزارم پاسخ به دیدگاه ارسلانی ۱۴ / ۰۸ / ۹۸ - ۰۷:۴۸ با عرض سلام بسیار مفید بود. سپاسگزارم پاسخ به دیدگاه