فشرده سازی داده در SQL Server

فشرده سازی داده در SQL Server

فشرده سازی یک جدول علاوه بر کاهش حجم دیسک مصرفی، به ما کمک می کند که IO کمتری اتفاق بیفتد. به عبارتی Physical Read و Logical Read کمتری اتفاق می افتد و تعداد صفحات کمتری از روی Disk و حافظه خوانده می شود و همچنین باعث کاهش ترافیک شبکه و نهایتا کوئری شما با سرعت بالاتری اجرا خواهد شد. پس با فشرده سازی ، داده ها در صفحات کمتری ذخیره می شوند و دستورات Select ما نیاز به خواندن صفحات کمتری از دیسک دارند.

از آنجایی که Cost مربوط به Disk و Memory و CPU و Network می باشد، لذا ما با فشرده سازی می توانیم Cost مربوط به Disk و Memory را در هنگام Select به شدت کاهش دهیم.

ولی اگر روی جداول فشرده بخواهیم عملیات Update انجام دهیم، CPU به شدت درگیر خواهد شد و Cost ما بالا خواهد رفت.

همینطور در جداول فشرده، هنگام عملیات Insert ، خصوصا عملیات Bulk، مدت زمان بیشتری صرف خواهد شد.

برای اطلاعات بیشتر به لینک زیر مراجعه کنید:

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver15

فشرده سازی را می توانیم در موارد زیر انجام دهیم:

• A whole table that is stored as a heap.
• A whole table that is stored as a clustered index.
• A whole nonclustered index.
• 4A whole indexed view.
• For partitioned tables and indexes, you can configure the compression option for each partition, and the various partitions of an object do not have to have the same compression setting.

در این قسمت به یک مثال ساده می پردازیم. دیتابیس Adventureworks2019 را انتخاب نموده وسپس در محیط new Query کلیک راست نموده و بر روی گزینه Design Query in Editor مطابق شکل زیر کلیک کنید.

در پنجره ظاهر شده مطابق شکل زیر جداول SalesorderHeader و SalesOrderDetail را انتخاب کنید.

حال مطابق شکل زیر تعدادی از فیلدهای هر دو جدول را به دلخواه انتخاب کنید. در نهایت بر روی دکمه OK کلیک کنید تا کوئری مربوط به این انتخابها برای شما ایجاد شود.

تذکر: اگر بر روی سرور خود Redgate انتخاب نموده اید کافیست کل کوئری را انتخاب و بر روی آن کلیک راست و گزینه Format SQL و یا کلیدهای Ctrl+K,Ctrl+Y را بگیرید تا کدهای شما مطابق شکل زیر مرتبط شوند.

حال در این کوئری قبل از کلمه From از دستور INTO TestDB..tblSales استفاده کنید. (لازم به ذکر است که نام دیتابیس تستی ما TestDB و اسکیمای ما dbo و نام جدولی که بناست ایجاد شود tblSales نامیده می شود).

همانطور که در شکل فوق دیده می شود تعداد ۱۲۱۳۱۷ رکورد به جدول tblSales واقع در دیتابیس TestDB منتقل شد.

لازم به ذکر است که شما می توانید با استفاده از دستور SP_Spaceused تعداد رکوردهای جدول tblSales را مشاهده نمایید.

تذکر: جدولی که به این روش ساخته می شود یک جدول Heap می باشد. شما می توانید با کوئری زیر جداول Heap را شناسایی کنید.

اگر بخواهیم جدولی را فشرده کنیم، هم می توانیم با استفاده از کوئری این کار را انجام دهیم و هم می توانیم به صورت Wizard ای این کار را انجام دهیم.

اگر بخواهیم به صورت Wizard ای یک جدول را فشرده کنیم،کافیست روی جدول مورد نظرمان کلیک راست نموده و گزینه Storage و سپس گزینه Manage Compression را کلیک می نماییم.

در این صورت پنجره ایی مطابق شکل زیر نمایان می شود(پنجره خوش آمد گویی). بر روی دکمه Next کلیک کنید تا به مرحله بعد بروید.

شما می توانید یک جدول را مطابق شکل زیر به دو حالت فشرده کنید. حالت Row Compression و حالت Data Compression (لازم به ذکر است که آیتم دیگر برای فشرده سازی به نام ColumnStore وجود دارد که در اینجا دیده نمی شود).

نوع فشرده سازی Row را انتخاب نموده و بر روی دکمه Calculate کلیک می نماییم. شما در پنجره زیرمیزان فشرده سازی بر اساس تکنیک Row Compression را مشاهده می کنید. در اثر این نوع فشرده سازی تا میزان تقریبا هشت مگابایت جدول شما فشرده خواهد شد. این میزان فشرده سازی همیشه ثابت نیست و به داده های شما بستگی دارد.

حالت بعدی فشرده سازی بر اساس Page Compression می باشد. مطابق شکل زیر در اثر این نوع فشرده سازی ، حجم جدول شما از تقریبا هجده مگابایت به چهار مگابایت خواهد رسید(تقریبا یک چهارم).

همانطور که در دو شکل فوق می بینید ، فشرده سازی در سطح Page به مراتب کلانتر از فشرده سازی در سطح Row می باشد. در شکل فوق بر روی دکمه Next کلیک کرده تا داده های خود را فشرده کنیم.

در اثر فشرده سازی دیتا چقدر فضا صرفه جویی خواهیم کرد؟

شما می توانید به کمک SP سیستمی به نام SP_estimate_data_compression_saving مطابق شکل زیر حجم مربوط به جدول را قبل و بعد از فشرده سازی را مشاهده نمایید. این SP مطابق شکل زیر دارای پنج پارامتر ورودی می باشد.

حال اگر بخواهیم فشرده سازی به روش Row را با فشرده سازی به روش Page و فشرده سازی به روش ColumnStore مقایسه کنیم، مطابق شکل زیر خواهیم دید که در اثر فشرده سازی به روش Row حجم جدول ما از 18MB به 10MB تقلیل خواهد یافت و در روش Page حجم جدول از 18MB به 4MB و در روش ColumnStore حجم جدول از 18MB به 3MB تقلیل خواهد یافت.

تذکر: ما با دستور ColumnStore مطابق کد زیر نمی توانیم داده ها را فشرده کنیم ولی مطابق شکل فوق می توانیم تخمین بزنیم که اگر روی جدول Column Store Index قرار دهیم به طور اتوماتیک داده های ما به چه میزان فشرده خواهد شد (کد زیر غلط است):

ALTER TABLE [Sales].[SalesOrderDetail] Rebuild
WITH(DATA_COMPRESSION = ColumnStore)

سوالی که اینجا به ذهن می رسد این است که آیا بهتر نیست که همه جداول خود را فشرده کنیم؟

نکته ایی که باید به آن توجه کنیم این است که فشرده سازی جداول باعث کاهش سرعت دستورات Insert و Delete و Update می شود، ولی به شدت باعث افزایش سرعت Select می شود. مخصوصا در جداول Fact مربوط به دیتابیس های Data Warehouse و پارتیشن های آرشیو جداول مربوط به جداول پارتیشن بندی شده (نه پارتیشن مثلا ماه جاری) و دیتابیس های آرشیو و سامانه هایی که از جنس Report هستند و به طور کلی جداولی که دارای Scan بالایی هستند و تقریبا Update ایی روی آنها صورت نمی گیرد ، گزینه مناسبی برای فشرده سازی هستند.

سوال: چه عملیاتی در SQL Server وجود دارد که کل جدول را Scan می کنند؟ به بعضی از آنها اشاره می کنیم.

• Cursor
• Order By
• Aggregate Function (چون Group By انجام می دهد)

لذا تا جایی که می توانید از موارد بالا که باعث کاهش Performance می شوند، استفاده نکنید.

تذکر مهم: وقتی جدولی را فشرده می کنید، این جدول Rebuild می شود و تمامی Dirty Page های مربوط به این جدول، روی دیسک، Persist می شوند. پس خود عملیات فشرده سازی هزینه بر می باشد.

وقتی جدولی را بر اساس Row Level Compression فشرده می کنید چه اتفاقی می افتد؟

دوستان Data Type هایی که ما در SQL Server داریم می توانیم از یک نگاه آنها را به دو قسمت تقسیم بندی نماییم :

1. Fix Lengthها:

دیتا تایپ هایی که Fix Length هستند عبارتند از:

• عددی ها مثل: مانند Tinyint , Smallint , int , bigint
• رشته ایی ها مثل:مانند Char , NChar
• تاریخ مثل: مانند SmallDateTime , DateTime , Date , Time
• سایر دیتا تایپ ها: مانند Uniqueidentifier

2. Variable Lengthها

Row Level Compression

فرض کنید در جدول SalesOrderDetail شما فیلدی به نام OrderQty دارید که دیتا تایپ مربوط به آن را از نوع Int انتخاب کرده اید. حال فرض کنید در آن مقدار عدد ۱۲ را وارد کنیم در این صورت چهاربایت فضا اشغال خواهد شد. حال اگر عدد ۲۱۴۷۰۰۰۰۰۰ هم وارد کنید باز هم چهار بایت فضا اشغال خواهد شد. وقتی ما جدولی را به روش Row فشرده می کنیم، الگوریتم فشرده سازی آن به این صورت است که نگاه می کنه میبینه که عدد ۱۲ رو می تونه توی یک فضای یک بایت هم جای بده، لذا به جای اینکه فضای چهار بایت به آن اختصاص بده ، آن عدد ۱۲ را در فضای یک بایت جای می دهد. به عبارت بهتر در این الگوریتم Fix Length ها را به Variable Length ها تغییر می یابند. همچنین در الگوریتم Row برای Null ها فضا دیگر اشغال نمی شود و همچنین در این روش برای صفرها نیز فضا اشغال نمی شود. در نهایت این که الگوریتم فشرده سازی ROW بیشتر تمرکزش روی Data می باشد.

لازم به ذکر است که دیتا تایپ هایی همچون Tinyint و یا SmallDateTime و Uniqueidentifier و Time را نمی توان فشرده کرد.

نکته: اگر فضای مربوط به این عدد ۱۲ رو که از چهار بایت به یک بایت رسونده ، شما بیایید عدد ۱۲ را بنا به هر دلیلی به عدد ۱۲۰۰ تغییر دهید در این صورت Page Split رخ داده و باعث کاهش Performance می شود. به خاطر همین است که توصیه بر این است که سعی کنید روی دیتاهایی که Report ایی و یا آرشیو هستند عملیات فشرده سازی را انجام دهید.

Page Level Compression

در الگوریتم فشرده سازی به روش Page Level Compression ، به طور پیش فرض Row Level Compression اتفاق می افتد.

در ضمن دو تا Compression دیگر نیز در الگوریتم Page Level Compression اتفاق می افتد، یکی Prefix Column Compression و دیگری Dictionary Compression می باشد.

اگر در ستون ستون LastName من ۲ تا فامیلی اکبری داشته باشم و فرض کنید که طراح دیتابیس دیتا تایپ این فیلد را به اشتباه Char(30) گرفته باشد. قاعدتا باید ۶۰ بایت فضا اشغال می شد. ولی اگر شما عملیات فشرده سازی (به روش Page) را انجام دهید در این صورت در قدم اول شما ۵۰ بایت شما فشرده سازی خواهید داشت، یعنی اول الگوریتم Row را اعمال می کند(به ازاء هر نام اکبری ، ۲۵ بایت فضا Save خواهید داشت).

در قدم بعدی الگوریتم فشرده سازی به روش Page ، SQL Server می آد یک نام اکبری را نگه داشته و مابقی را بهش Reference می دهد(شبیه کاری که در Column Store Index انجام می شود).

نتیجه اینکه در جداولی که کاردیتالیتی دیتا در ستون ها پایین است (میزان تکرار داده ها بالاست (مثل ستون نام)) در این صورت نتیجه فشرده سازی بسیار متفاوت خواهد بود.

تذکر: فشرده سازی روی ستون های BLOB چندان تاثیرگذار نیست.

اسکریپت مربوط به فشرده سازی به روش Page Level Compressing

اگر بر روی جدول مورد نظرتان کلیک راست نموده و گزینه Properties را کلیک کنید و در پنجره ظاهر شده مطابق شکل زیر، بر روی Storage کلیک کنید در این صورت در قسمت Compression Type ، نوع فشرده سازی را خواهید دید، که در شکل زیر None می باشد.

حال چنانچه از اسکریپت زیر برای فشرده سازی به روش Page Level Compressing استفاده کنید(در کد زیر در ضمن می خواهیم دو تا Core های سرورمان درگیر شوند)

ALTER TABLE [Sales].[SalesOrderDetail] Rebuild
WITH(DATA_COMPRESSION = PAGE,MAXDOP=2)

در این صورت مطابق شکل زیر در قسمت Compression Type گزینه Page را خواهید دید.

بعد از اجرای کد مربوط به فشرده سازی با الگوریتم Page Level Compression مطابق شکل زیر در قسمت Compression Type گزینه Page را خواهید دید.

اگر بنا به هر دلیلی از فشرده سازی جدول مورد نظرتان پشیمان شدید کافیست کد زیر را اجرا کنید :

ALTER TABLE [Sales].[SalesOrderDetail] Rebuild
WITH(DATA_COMPRESSION = NONE,MAXDOP=2)

تذکر: شما می توانید ایندکس های نان کلاستر را نیز فشرده کنید. حتی شما می توانید فقط داده های مربوط به پارتیشن های خاصی را فشرده کنیم.

تذکر: اگر خواستید یک نان کلاستر ایندکس را فشرده کنید در این صورت می توانید مطابق کد زیر عمل نمایید. به مثال زیر دقت کنید.

ALTER INDEX IX_tblSales_OrderDate 
ON tblSales REBUILD 
WITH (DATA_COMPRESSION = PAGE)

نحوه بدست آوردن آبجکت های فشرده شده:

برای بدست آوردن آبجکت های فشرده شده در یک دیتابیس می توانیم از کوئری زیر استفاده کنیم:

SELECT DISTINCT
    s.name,t.name,i.name,i.type,i.index_id,
    p.partition_number,p.rows
FROM sys.tables t
LEFT JOIN sys.indexes i
ON t.object_id = i.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id
LEFT JOIN sys.partitions p
ON i.index_id = p.index_id
    AND t.object_id = p.object_id

تذکر: در جداول دیتابیس های Data Warehouse هیچگاه جداول Fact Less Fact را فشرده نمی کنیم. این جدول که به آن Junction Table نیز گفته می شود حاصل ارتباط چند به چند بین جدول Fact و یک جدول Dimension است.

مثال: یک کپی از جدول tblSales با داده هایش با نام tblSales2 بسازید. سپس جدول اول یعنی tblSales را با الگوریتم Page فشرده کنید.

مشاهده حجم اشغالی توسط دو جدول tblSales و tblSales2 توسط گزارش های خود SQL Server

در این صورت پنجره ایی مطابق شکل زیر نمایان می شود.

در قدم بعدی می خواهیم Cost دو کوئری را به کمک Execution Plan دو کوئری زیر را با هم مقایسه کنیم :

حال Actual Execution Plan رو فعال کنید و یک Select از دو جدول بگیرید در این صورت خواهید دید که Cost حالت فشرده ۲۳ درصد و Cost جدول غیرفشرده ۷۷ درصد می باشد.

در قدم بعدی می خواهیم مطابق شکل زیر به کمک دستور Set Statistics IO ON آمار مربوط به تعداد Physical Read و Logical Read مربوط به دو جدول را بدست آورده و با هم مقایسه کنیم. همانطور که مشاهده می کنید آمار تعداد IO های مربوط به Logical Reads مربوط به جدول فشرده ۵۷۶ عدد می باشد(یعنی ۵۷۶ عدد Page از هارد دیسک خونده و اورده به حافظه) ، و آمار تعداد IO های مربوط به Logical Reads مربوط به جدول عادی ۲۳۵۷ عدد می باشد.

ولی به یک نکته توجه کنید و آن اینکه Cost ، CPU Usage مربوط به جدول فشرده مطابق شکل زیر بالاتر می رود. که این کاملا طبیعی است.

در مرحله بعدی این مثال یک جدول سومی درست کنید و داده های آن را مطابق کوئری زیر پر کنید و سپس روی آن یک Column Store Index درست کنید.

SELECT * 
INTO tblsales3
FROM tblsales2

کافیه در جدول جدید یعنی tblSales3 که یک جدول Heap است کلیک راست کرده و گزینه New Index و سپس گزینه Clustered Columnstore Index را مطابق شکل زیر کلیک می نماییم.

حال اگر بخواهیم حجم سه جدول را با هم مقایسه کنیم ، شکلی مطابق شکل زیر خواهیم داشت.

حال اگر Cost سه جدول را با استفاده از Execution Plan مشاهده کنیم ، مطابق شکل زیر خواهیم دید که روی جدول سوم که Column Store Index زدیم ، میزان Cost ، یک درصد می باشد و جدول اول که با الگوریتم Page فشرده شده است میزان Cost آن ۲۳ درصد شده است و جدول عادی یعنی tblSales میزان Cost اش ۷۶ درصد می باشد.