در Clustered Columnstore، Insertهای بزرگ مستقیماً به Rowgroup فشرده تبدیل می‌شوند و Insertهای کوچک در Deltastore قرار می‌گیرند. Delete در Rowgroup معمولاً ابتدا Logical است و در Delete Bitmap ثبت می‌شود؛ Update نیز ترکیبی از حذف منطقی رکورد قبلی و درج نسخه جدید است. این ساختار اجازه می‌دهد Queryهای تحلیلی با فشرده‌سازی بالا اجرا شوند.

SELECT * INTO dbo.OrdersColumnstore
FROM dbo.OrdersDisk;
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrdersColumnstore
ON dbo.OrdersColumnstore;

برای Columnstore برخی انواع داده پشتیبانی نمی‌شوند. Nonclustered Columnstore روی Rowstore امکان ایجاد نسخه ستونی برای Queryهای تحلیلی را فراهم می‌کند؛ محدودیت‌های DML و قابلیت Update در نسخه‌های SQL Server باید با نسخه مورد استفاده تطبیق داده شود.

Nonclustered Columnstore و In-Memory Hash Index

Nonclustered Columnstore می‌تواند مجموعه‌ای از ستون‌ها را برای تحلیل ستونی پوشش دهد. در Memory-Optimized Table دو خانواده اصلی Index وجود دارد: Hash و Nonclustered. Hash برای Equality Lookup با Key کامل بسیار سریع است و Bucket Count آن باید بر اساس Cardinality و رشد آینده انتخاب شود.

Hash Index مقدار Hash کلید را به Bucket نگاشت می‌کند. Collision باعث می‌شود چند Entry در یک Bucket زنجیره شوند؛ Bucket Count خیلی کم زنجیره‌های طولانی و افت کارایی ایجاد می‌کند، در حالی که مقدار بسیار بزرگ حافظه بیشتری مصرف می‌کند. Unique بودن Index می‌تواند رفتار و طراحی را تغییر دهد.

حافظه Hash Index پس از ساخت تا حد زیادی به تعداد Bucketها وابسته است، نه فقط تعداد Rowها. بنابراین Over-sizing می‌تواند Memory را هدر دهد. DMVهای XTP برای مشاهده Empty Bucket Percentage و Chain Length ابزار عملی ارزیابی طراحی Hash هستند.

ایجاد Memory-Optimized Hash Index

مثال فصل جدولی Memory-Optimized با Hash Index می‌سازد و داده Orders را در آن قرار می‌دهد. هدف مقایسه رفتار Hash و Nonclustered Index روی Predicateهای مختلف است. Equality روی کلید Hash سناریوی ایده‌آل است، در حالی که Range یا Predicate ناقص ممکن است Scan بیشتری ایجاد کند.

برای جست‌وجوی Range یا ستون‌هایی که Hash مناسب آن‌ها نیست، Nonclustered Index روی Memory-Optimized Table اضافه می‌شود. SQL Server امکان ALTER/بازسازی طراحی Indexهای حافظه‌محور را با روش‌های مخصوص خود فراهم می‌کند.

sys.dm_db_xtp_hash_index_stats

SELECT
  OBJECT_NAME(hs.object_id) AS TableName,
  i.name AS IndexName,
  hs.total_bucket_count,
  hs.empty_bucket_count,
  hs.avg_chain_length,
  hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs
JOIN sys.indexes AS i
  ON hs.object_id = i.object_id AND hs.index_id = i.index_id;

نسبت Bucket خالی و طول Chain شاخص‌های اصلی تشخیص Bucket Count نامناسب‌اند.

نگهداری Indexها

Indexها با گذر زمان تحت تأثیر DML، Page Split و تغییر توزیع داده قرار می‌گیرند. نگهداری باید مبتنی بر شواهد باشد: Missing Index Recommendation، Fragmentation، Statistics و هزینه واقعی Query. بازسازی بی‌قیدوشرط همه Indexها می‌تواند I/O، Log و Blocking زیادی ایجاد کند.

Missing Indexes

Optimizer هنگام Compile ممکن است تشخیص دهد Index مفیدی وجود ندارد و پیشنهاد Missing Index تولید کند. این پیشنهاد فقط از دید همان Query است؛ بنابراین نباید بدون بررسی workload کلی اجرا شود. ممکن است چند پیشنهاد هم‌پوشان را بتوان در یک Index بهتر ادغام کرد یا هزینه DML از منفعت Read بیشتر باشد.

DMVهای Missing Index اطلاعات Group، Equality/Inequality Columnها، Included Columnها و Usage را ارائه می‌کنند. داده‌های این DMV پس از Restart یا برخی رویدادها Reset می‌شوند؛ بنابراین برای تصمیم‌گیری بلندمدت باید Capture و تحلیل شوند.

Index Fragmentation

Fragmentation منطقی زمانی رخ می‌دهد که ترتیب منطقی Pageها با ترتیب فیزیکی/زنجیره Leaf هماهنگ نباشد یا Page Density کاهش یابد. این مفهوم با Fragmentation فایل‌سیستم متفاوت است. اثر آن به Storage، Scan Pattern و اندازه Index وابسته است.

تشخیص Fragmentation

DMF ‏sys.dm_db_index_physical_stats با Modeهای LIMITED، SAMPLED و DETAILED وضعیت Pageها را گزارش می‌کند. پارامترهای Database ID، Object ID، Index ID و Partition Number اجازه می‌دهند دامنه بررسی محدود شود. DETAILED اطلاعات بیشتر ولی هزینه بالاتر دارد.

--- PDF PAGE 293 ---
276
page without page splits occurring. Many DBAs change the FILLFACTOR when they are 
not required to, however, which automatically causes internal fragmentation as soon as 
the index is built. PAD_INDEX can be applied only when FILLFACTOR is used, and it applies 
the same percentage of free space to the intermediate levels of the B-tree.
External fragmentation is also caused by page splits and refers to the logical order 
of pages, as ordered by the index key, being out of sequence when compared to the 
physical order of pages on disk. External fragmentation makes it so SQL Server is less 
able to perform scan operations using a sequential read, because the head needs to 
move backward and forward over the disk to locate the pages within the file.
Note  This is not the same as fragmentation at the file system level where a data 
file can be split over multiple, unordered disk sectors.
Detecting Fragmentation
You can identify fragmentation of indexes by using the sys.dm_db_index_physical_
stats DMF. This function accepts the parameters listed in Table 8-2.
Table 8-2.  sys.dm_db_index_physical_stats Parameters
Parameter
Description
Database_ID
The ID of the database that you want to run the function against. If you do 
not know it, you can pass in DB_ID('MyDatabase') where MyDatabase 
is the name of your database.
Object_ID
The Object ID of the table that you want to run the function against. If you 
do not know it, pass in OBJECT_ID('MyTable') where MyTable is the 
name of your table. Pass in NULL to run the function against all tables in 
the database.
Index_ID
The index ID of the index you want to run the function against. This is always 
1 for a clustered index. Pass in NULL to run the function against all indexes 
on the table.
(continued)
Chapter 8  Indexes and Statistics

--- PDF PAGE 294 ---
277
Listing 8-13 demonstrates how we can use sys.dm_db_index_physical_stats to 
check the fragmentation levels of our OrdersDisk table.
Listing 8-13.  sys.dm_db_index_physical_stats
USE Chapter8
GO
SELECT
i.name
,IPS.index_type_desc
,IPS.index_level
,IPS.avg_fragmentation_in_percent
,IPS.avg_page_space_used_in_percent
,i.fill_factor
,CASE
    WHEN i.fill_factor = 0
        THEN 100-IPS.avg_page_space_used_in_percent
    ELSE i.fill_factor-ips.avg_page_space_used_in_percent
END Internal_Frag_With_Fillfactor_Offset
,IPS.fragment_count
,IPS.avg_fragment_size_in_pages
Parameter
Description
Partition_Number
The ID of the partition that you want to run the function against. Pass in 
NULL if you want to run the function against all partitions or if the table is 
not partitioned.
Mode
Choose LIMITED, SAMPLED, or DETAILED. LIMITED only scans the non-
leaf levels of an index. SAMPLED scans 1% of pages in the table, unless the 
table has 10,000 pages or less, in which case DETAILED mode is used. 
DETAILED mode scans 100% of the pages in the table. For very large 
tables, SAMPLED is often preferred due to the length of time it can take to 
return data in DETAILED mode.
Table 8-2.  (continued)
Chapter 8  Indexes and Statistics

SELECT *
FROM sys.dm_db_index_physical_stats
(
  DB_ID('Chapter8'),
  OBJECT_ID('dbo.OrdersDisk'),
  NULL, NULL, 'DETAILED'
);

در خروجی، avg_fragmentation_in_percent، page_count و avg_page_space_used_in_percent از شاخص‌های مفیدند. درصد Fragmentation بدون توجه به Page Count نباید مبنای خودکار تصمیم باشد.

حذف Fragmentation

دو عملیات اصلی REORGANIZE و REBUILD هستند. REORGANIZE آنلاین‌تر و تدریجی است و Leaf Level را مرتب می‌کند؛ REBUILD ساختار Index را از نو می‌سازد، می‌تواند Statistics را نیز به‌روز کند و معمولاً Resource بیشتری مصرف می‌کند. آستانه ثابت برای همه سیستم‌ها وجود ندارد و باید با workload سازگار شود.

ALTER INDEX NCI_CustomerID
ON dbo.OrdersDisk
REBUILD WITH (MAXDOP = 1);

SSD مسئله Fragmentation منطقی را حذف نمی‌کند؛ فقط هزینه برخی I/Oها را تغییر می‌دهد. Rebuild می‌تواند با ONLINE و سایر Optionها تنظیم شود.

Resumable Index Operations

ALTER INDEX NCI_CustomerID ON dbo.OrdersDisk
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 1);

ALTER INDEX NCI_CustomerID ON dbo.OrdersDisk PAUSE;

Resumable Rebuild اجازه می‌دهد عملیات طولانی Pause و بعداً Resume شود و Window نگهداری بهتر مدیریت شود.

ALTER INDEX NCI_CustomerID ON dbo.OrdersDisk RESUME;
-- یا
ALTER INDEX NCI_CustomerID ON dbo.OrdersDisk ABORT;

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = WHEN_SUPPORTED;
ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;

Database Scoped Configuration می‌تواند رفتار پیش‌فرض Online/Resumable را در عملیات پشتیبانی‌شده ارتقا دهد.