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

یکی از قابلیت‌های بسیار کاربردی در SQL Server، فشرده سازی است در این مقاله قصد داریم این قابلیت جذاب را در سطح بکاپ و جدول توضیح دهیم. در ادامه به بررسی هر دو سطح می‌پردازیم:

فشرده‌سازی بکاپ

این قابلیت فوق‌العاده که از ۲۰۰۸ SQL Server  معرفی شده است، باعث می‌شود حجم بکاپ‌ها بشدت کاهش پیدا کند. این کاهش حجم به دلیل کم‌تر‌ شدن I/O ، معمولا منجر به سریع‌تر اجرا شدن عملیات بکاپ‌گیری می‌شود ولی از طرفی با توجه به درگیر کردن CPU جهت فشرده‌سازی اطلاعات، میزان مصرف CPU نسبت به حالت معمول بالاتر می‌رود. نمودار زیر نسبت سایز بکاپ، میزان مصرف CPU و مدت زمان اجرای بکاپ گیری در دو حالت فشرده و معمولی را نشان می‌دهد.

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

نحوه اجرای بکاپ همراه با فشرده‌سازی از طریق دستور:

BACKUP DATABASE TestDb TO DISK = N'D:\Backup\TestDb.bak' WITH COMPRESSION

با توجه به کاهش حجم شدید بکاپ‌ها و هم‌ چنین سریع‌تر‌ شدن زمان اجرای آن، پیشنهاد می‌شود بکاپ دیتابیس‌ها همیشه به صورت فشرده گرفته شود. برای این کار بهتر است گزینه Compress Backup را در تنظیمات سرور فعال کنید تا عملیات بکاپ‌گیری به صورت پیش فرض، فشرده انجام شود.

برای فعال کردن این تنظیم از طریق SSMS باید به پنجره Server Properties و تب Database Settings رفته و گزینه Compress backup را انتخاب کنید:

نحوه فعال کردن با دستور هم به شکل زیر است:

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

فشرده‌سازی جدول در SQL Server

فشرده‌سازی بر روی جدول قابلیت جذاب دیگری است که از ۲۰۰۸ SQL Server معرفی شده است که می‌توان بر آن را روی جداول، ایندکس‌ها و حتی برای یک یا چند پارتیشن خاص فعال کرد و به دو حالت Row و Page انجام می‌شود.

Row Level

در این حالت رکوردهایی که ستون‌های fixed-length دارند به variable length تبدیل می‌شوند.  به عنوان مثال اگر مقدار ‘Hi’ در char(10) ذخیره شده باشد با فعال کردن فشرده‌سازی Row، فضاهای اضافه حذف می‌شوند و این عملیات به ازای تمام رکوردها رخ می‌دهد.

Page Level

در این حالت در ابتدا الگوریتم فشرده‌سازی Row Level اعمال می‌شود و سپس از طریق فشرده‌سازی prefix و dictionary به دنبال الگوهایی تکراری می‌گردد و آن‌ها را با مقادیر کوچکتری جایگزین می‌کند.

در واقع زمان فعال‌سازی فشرده‌سازی Page، عملیات زیر انجام می‌شود:

• Row Compression
• Prefix Compression
• Dictionary Compression

در  ادامه به بررسی چند مثال می‌پردازیم:

• فعال کردن فشرده‌سازی از نوع Row بر روی جدول :

ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (DATA_COMPRESSION = ROW)

ایجاد یک ایندکس با فشرده‌سازی از نوع Page :

CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_CustomerID] ON [Sales].[SalesOrderHeader] ([CustomerID]) WITH (DATA_COMPRESSION = PAGE)

• ایجاد یک ایندکس بر روی یک جدول با چهار پارتیشن و فعال کردن فشرده‌سازی بر روی پارتیشن‌های ۱ ، ۲ و ۳ و غیرفعال بودن فشرده‌سازی بر روی پارتیشن ۴ :

CREATE NONCLUSTERED INDEX [IX_TableName(ColumnName)]
ON TableName(ColumnName)
WITH (DATA_COMPRESSION = NONE ON PARTITIONS(4), DATA_COMPRESSION = PAGE ON PARTITIONS(1 , 2 , 3))
	ON [PS_TableName]([Year]);
GO

قبل از فعال‌کردن فشرده‌سازی Page یا Row بر روی جدول و یا ایندکس می‌توان از پروسیجر سیستمی زیر، برای بدست آوردن حجم تخمینی جدول یا ایندکس در صورت فعال‌کردن فشرده‌سازی استفاده کرد:

EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderHeader', NULL, NULL, 'Page'
EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderHeader', NULL, NULL, 'Row'

چند نکته در مورد Data Compression در SQL Server

• فشرده‌سازی به صورت Row قسمتی از فشرده‌سازی Page است و در نتیجه، کاهش حجم جدول یا ایندکس در حالت فشرده‌سازی Page بیش‌تر است.
• با توجه به سرباری که عملیات فشرده‌سازی بر روی سرور دارد (مصرف CPU بیشتر می‌شود)، این نوع عملیات، برای جداولی که دائما درج و بروزرسانی داده دارند، مناسب نیست.
• بعد از فعال کردن فشرده‌سازی بر روی یک جدول، ممکن است پرفورمنس بعضی از کوئری‌های مرتبط با آن جدول، با توجه به کم‌تر شدن تعداد page ها بالاتر رود.
• این نوع فشرده‌سازی تاثیری بر روی رکوردها و داده‌هایی که از نوع LOB هستند، ندارد و بهتر است برای این نوع داده‌ها از فانکشن Compress که از ۲۰۱۶ SQL Server معرفی شده است، استفاده شود.
• موارد مطرح شده برای ایندکس‌های معمولی هستند و این بحث در ایندکس‌های Columnstore متفاوت است.
• این نوع فشرده‌سازی برای جداول بزرگ آرشیوی خیلی مناسب هستند. (جداولی که معمولا به صورت شبانه و در زمانی که لود سرور بسیار کم است، درج داده دارند.)
• این نوع فشرده‌سازی برای پارتیشن‌های قدیمی یک جدول بزرگ هم خیلی مناسب هستند.(در واقع می‌توان فشرده‌سازی را روی تعدادی از پارتیشن‌های یک جدول فعال کرد.)

جمع بندی:

در این مقاله توضیحات مختصر و کاربردی در مورد موضوع بسیار مهم فشرده‌سازی در SQL Server، در دو سطح بکاپ‌گیری و جدول مطرح شد که با توجه به موارد گفته شده و نیاز، می‌توان از این قابلیت جذاب در دیتابیس‌ها استفاده کرد و حجم داده‌ها و بکاپ‌های خود را به طرز چشمگیری کاهش داد و باعث استفاده بهینه از فضای دیسک و حتی بالا رفتن سرعت اجرای کوئری‌ها شد.