فشرده سازي اطلاعات در SQL server 2008

علاوه بر فشرده سازي خودكار بك آپ‌ها كه پيشتر در مورد آن‌ها صحبت شد، اس كيوال سرور 2008 دو نوع فشرده سازي ديگر را نيز پشتيباني مي‌كند:

Row Compression :

حالت row compression نحوه‌ي ذخيره سازي فيزيكي داده‌ها را تغيير مي‌دهد. فعال سازي آن اثرات زير را خواهد داشت:
الف) متاديتاي هر ركورد را حداقل مي‌كند (منظور از متاديتا اطلاعاتي مانند اطلاعات ستون‌ها، طول و آفست و غيره است)
ب) داده‌هاي عددي و رشته‌هايي با طول ثابت، به صورت اطلاعاتي با طول متغير ذخيره خواهند شد، درست مانند varchar ها.

براي ايجاد جدولي كه row compression در آن به صورت پيش‌فرض فعال است، مي‌توان مانند مثال زير عمل كرد:

CREATE TABLE MyTable
(
      ID int identity Primary key,
      Name char(100),
      Email char(100)
)
WITH (DATA_COMPRESSION = Row);

GO

و اگر جدول موجودي را مي‌خواهيد تغيير داده و اين خاصيت را بر روي آن فعال نمائيد، روش زير را اعمال كنيد:

Alter TABLE MyTable REBUILD WITH (DATA_COMPRESSION=Row, MAXDOP=2);

در اينجا MAXDOP مشخص مي‌كند كه از چند CPU بايد براي فشرده سازي استفاده شود. (اگر جدولي حجيم داريد، به اين صورت مي‌توان عمليات فشرده سازي را سريعتر به پايان رساند)


Page Compression :

در روش دوم فشرده سازي اطلاعات در اس‌كيوال سرور 2008 ، كه مهم‌ترين حالت موجود نيز مي‌باشد، اطلاعات مشترك، بين سطرهاي يك صفحه به اشتراك گذاشته مي‌شوند. اين روش از فناوري‌هاي زير استفاده مي‌كند:
الف) روش row compression كه در مورد آن صحبت شد جزئي از اين روش است.
ب) Prefix Compression : به ازاي هر ستون در يك صفحه، Prefix هاي تكراري يافت شده و در هدر مخصوص فشرده سازي ذخيره مي‌شوند (محل اين هدر پس از هدر صفحه است). سپس هرجايي كه به اين Prefix ها اشاره شده‌باشد، عدد منحصربفرد شناسايي كننده آن‌ها نسبت داده مي‌شود.
ج) Dictionary Compression : در اين حالت مقادير تكراري يك صفحه جستجو شده و در هدر فشرده سازي صفحه ذخيره مي‌شوند. حالت Prefix Compression فقط به يك ستون منحصر مي‌شود اما Dictionary Compression به كل صفحه اعمال مي‌گردد.

براي فعال سازي آن در يك جدول جديد به روش زير مي‌توان عمل نمود:

CREATE TABLE MyTable
(
      ID int identity Primary key,
      Name char(100),
      Email char(100)
)
WITH (DATA_COMPRESSION = Page);

و براي اعمال آن به جدولي موجود از روش زير مي‌توان استفاده كرد:

Alter TABLE MyTable REBUILD WITH (DATA_COMPRESSION=Page, MAXDOP=2);

يك سري رويه‌هاي ذخيره شده سيستمي جديد نيز براي محاسبه حجم جداول، پيش و پس از فشرده سازي (بدون فشرده سازي واقعي) نيز در اين نگارش گنجانده شده‌اند كه به شرح زير هستند:

-- بررسي اينكه چه ميزان فضا با اعمال فشرده سازي صفحات قابل صرفه جويي خواهد بود
EXEC sp_estimate_data_compression_savings 'schemaname', 'TableName', NULL, NULL, 'PAGE';

-- بررسي اينكه چه ميزان فضا با اعمال فشرده سازي رديف‌ها قابل صرفه جويي خواهد بود
EXEC sp_estimate_data_compression_savings 'schemaname', 'TableName', NULL, NULL, 'ROW';

بنابراين قبل از اينكه فشرده سازي را فعال نمائيد، ابتدا بررسي كنيد آيا واقعا ميزان قابل توجهي اطلاعات فشرده خواهند شد و نتيجه حاصل رضايت بخش است يا خير. همچنين بايد درنظر داشت كه جداول و يا ايندكس‌هايي كه read و write بالايي دارند براي اين منظور مناسب نيستند. براي يافتن آن‌ها كوئري زير را اجرا كنيد:

USE dbName;
SELECT objectname = OBJECT_NAME(s.object_id),
      indexname = i.name,
      i.index_id,
      reads = range_scan_count + singleton_lookup_count,
      'leaf_writes' = leaf_insert_count + leaf_update_count + leaf_delete_count,
      'leaf_page_splits' = leaf_allocation_count,
      'nonleaf_writes' = nonleaf_insert_count + nonleaf_update_count +
      nonleaf_delete_count,
      'nonleaf_page_splits' = nonleaf_allocation_count
FROM   sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) AS s
      INNER JOIN sys.indexes AS i
           ON  i.object_id = s.object_id
WHERE  OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
      AND i.index_id = s.index_id
ORDER BY
      leaf_writes DESC,
      nonleaf_writes DESC

و جهت تكميل مبحث مي‌توان به مقاله بسيار جامع زير كه اخيرا توسط مايكروسافت منتشر شده است رجوع نمود:

Data Compression: Strategy, Capacity Planning and Best Practices