עבודה עם Clustered ColumnStore Index

במאמר זה נדבר על העבודה המעשית עם ה-Clustered ColumnStore Index (CCI). נסביר איך יוצרים אותו על חלק מהעמודות, נדבר על Catalog Views ואיך מתחזקים את ה-index.

יצירה של CCI על חלק מהעמודות

כל העמודות חיבות להיות מוכלות ב CCI ובעת היצירה אין צורך לציין את העמודות. אם ננסה לעשות זאת:

--creating table for demo
CREATE TABLE dbo.SalesOrders(SalesOrdersID INT IDENTITY, OrderDate DATETIME,
SubTotal MONEY, SalesOrderNumber INT)
GO
 
--creating columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX
idx_columnstore_SalesOrderNumber
ON dbo.SalesOrders(SalesOrdersID, OrderDate )
GO

נקבל את הודעת השגיאה הבאה:

Msg 35335, Level 15, State 1, Line 204
CREATE INDEX statement failed because specifying a key list is not allowed when creating a clustered columnstore index. Create the clustered columnstore index without specifying a key list.

מכיוון ש CCI משנה את המבנה הפיסי של אחסון המידע לאחסון מבוסס עמודה, כל העמודות חיבות להיות מוכלות באינדקס כבררת מחדל. זו הסיבה מדוע לא ניתן להוסיף עוד אינדקסים על הטבלה מלבד CCI.

דוגמא ליצירה של CCI וטעינת נתונים

בקטע הקוד הבא ניצור טבלה בשם SalesOrders, ניצור עליה CCI ובנבצע הכנסה של 2400000 שורות.

use [AdventureWorks2012]
--If the orders table is already present, you can delete it, and then create new one with the name Orders
IF OBJECT_ID('SalesOrders', 'U') IS NOT NULL BEGIN
DROP TABLE SalesOrders
END
GO
--creating table for demo
CREATE TABLE dbo.SalesOrders(SalesOrdersID INT IDENTITY, OrderDate DATETIME,
SubTotal MONEY, SalesOrderNumber INT)
GO
 
--creating columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX
idx_columnstore_SalesOrderNumber
ON dbo.SalesOrders
GO
 
--inserting 2400000 rows into table
INSERT INTO SalesOrders(OrderDate, SubTotal, SalesOrderNumber)
SELECT TOP 2100000
DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2011-11-04'
AS DATETIME)),
ABS(a.object_id % 10),
CAST(ABS(a.object_id % 13) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

Catalog Views של CCI

sys.column_store_row_groups הוא DMV שמאפשר לראות את כל ה- Row Groups שב- Delta Store וב- Column Stores. לכל partition בטבלה יש Delta Store אחד לפחות. נשתמש ב DMV זה בשביל להחליט מה מספר ה- Delta stores. אנו משתמשים ב- DMV זה בשביל לבדוק את איכות ה CCI. אם יש Row Group עם פחות 1M שורות, יש לבדוק למה, כפי שאדגים להלן. יש עוד 2DMVs: sys.column_store_segments שמאפשר למצוא את טווח הערכים של העמודות עבור הדחיסה שמשתמש באלימינציה של סגמנטים ומידע על גודלו של כל segement ו sys.column_store_segments. אני לא אעסוק בהם. נריץ את השאילתה הבאה:

select * from sys.column_store_row_groups;

נקבל את הפלט הבא:

הסבר: יש לנו 2 Row Group שהם דחוסים (state=3,state_description=COMPRESSED) ו Delta Dtore אחד (state=1, state_description=OPEN). שימו לב שסכום העמודה row_count שווה ל 1048576+1048576+2848 סה"כ 2,400,000, שזה בדיוק מספר השורות שהכנסנו. ההסבר: כמו שצינתי מספר השורות המינימאלי עבורו יתבצע דחיסה ומעבר לפורמט של Column Store הוא 1048076.

אני כעת אעורר את תהליך ה- tuple mover, באופן א- סינכרוני, על ידי שימוש בפקודה הבאה כדי לאלץ דחיסה של כל ה- Row group לפורמט של Column Store.

ALTER INDEX … REORGANIZE // compresses closed row groups
… REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

קטע הקוד יראה כך:

ALTER INDEX [idx_columnstore_SalesOrderNumber] ON [dbo].[SalesOrders] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
select * from sys.column_store_row_groups;

נריץ את השאילתה הבאה:

select * from sys.column_store_row_groups;

קבל הפעם את הפלט הבא:

הפעם יש לנו 3 Row Group שהם דחוסים (state=3,state_description=COMPRESSED).

בניה מחדש של CCI

בניה מחדש של האינדקס יכולה להתבצע על ידי האופציה WITH (DROP_EXISTING = ON), שזורקת את האינדקס הקיים ובונה אותו מחדש. שימו לב שאם האינדקס לא קיים תתקבל הודעת שגיאה.

--Recreating columnstore index WITH (DROP_EXISTING = ON) option 

CREATE CLUSTERED COLUMNSTORE INDEX 

idx_columnstore_SalesOrderNumber

ON dbo.SalesOrders WITH (DROP_EXISTING = ON) 

GO

select * from sys.column_store_row_groups; 

batch mode

ל columnstore index יש 2 סוגי אופרטורים: batch mode ו- row mode. Batch mode הוצג יחד columnstore index ב- SQL 2012. מכוון שcolumnstore index- הוא מבוסס עמודות, על ידיי שימוש ב- batch mode נוכל לעבד שורות רבות במקביל ב- batch אחד ובצורה יעילה יותר. ל-batch mode היתרונות הבאים:

• מעבד בין 64 עד 960 שורות בו זמנית ולכן מאפשר משיכת מידע בכמות גדולה יותר, ללא צורך בביצוע של decompression.
• מספר קריאות מערכת קטן יותר.
• זמין רק ב- parallel plan ב- columsstore.
• אין העתקה מיותרת של מידע.

בשביל לראות אם columnstore רץ ב- batch mode או row mode, עליך לבדוק את ה- properties של columnstore operation ב- execution plan עבור הטבלה SalesOrders_CCIכפי שמופיע בשרטוט הבא:

ב- SQL 2012 היו מגבלות רבות על batch mode ואופרטורים מסוג joins,, UNION ALLו- NOT IN, מה שגרם ל- execution plan מסוג row mode או עירוב של batch mode ו- row mode באופן לא עקבי. ב- SQL 2014 הוכנסו השיפורים הבאים:

• ה- batch mode הורחב עבור hash joins
• שימוש ב- אופרטורים מסוג rows לא מונע את השימוש ב- batch mode
• השימוש ב- batch mode הורחב עבור inner ו- outer joins, union all ועבור פקודות סיכום.

אכסון Clustered ColumnStore Index לארכיון

ב- SQL 2014 ל CCI יש אלגוריתם דחיסה נוסף, מלבד האלגוריתם שמגיע כבררת מחדל, בשם Column Store Archiving, עליו דיברתי. המחיר כאמור הוא תקורה של CPU גבוהה יותר וזמן גישה איטי יותר. הדחיסה כאמור יכולה להיעשות פר partition בטבלה. נוכל לבדוק מה גודל הטבלה שבנינו על ידי הפרוצדורה sp_spaceused באופן הבא:

EXEC sp_spaceusedSalesOrders

בפלט נקבל תחת העמודה reserved את הערך 7184 KB.

זה שטח האחסון שהטבלה תופסת בפועל. נריץ את הפקודה הבאה שמשנה את שיטת הדחיסה ל COLUMNSTORE_ARCHIVE

ALTER INDEX idx_columnstore_SalesOrderNumber ON SalesOrders
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

אם נבדוק מה גודל הטבלה כעת על ידי sp_spaceused נקבל תחת העמודה reserved את הערך 4496K. כלומר השיפור הדחיסה הוא כעת 4496/7184 והשווה ל 0.6. לא רע בכלל !

נוכל להחזיר את הטבלה לאלגוריתם דחיסה בררת מחדל COLUMNSTORE)) על ידי הפקודה הבא:

ALTER INDEX idx_columnstore_SalesOrderNumber ON SalesOrders
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);

סיכום פקודות TSQL עבור Columnstore Index

פקודות TSQL עבור Columnstore Index להלן.

יצירה של אינדקס מ- HEAP:

CREATE CLUSTERED COLUMNSTORE INDEX … // from HEAP

יצירה של אינדקס אחרי זריקה של ה- CCI הקים:

CREATE CLUSTERED COLUMNSTORE INDEX … WITH (DROP_EXISTING = ON) // from CI

ביצוע rebuild לאינדקס בונה את כל האינדקס CCI מחדש לחלוטין ולמעשה כל הפקודות הבאות שקולות:

<strong>ALTER TABLE … REBUILD </strong>
<strong>ALTER INDEX … REBUILD </strong>
<strong>CREATE CLUSTERED COLUMNSTORE INDEX … WITH (DROP_EXISTING = ON)</strong>
<strong>

ר- אירגון של האינדקס מבצע compress לכל ה- row groups שהם בסטאטוס closed, כלומר הגיעו למספר השורות המכסימאלי

<pre><strong>ALTER INDEX … REORGANIZE // compresses closed row groups </strong>

רה-ארגון של האינדקס עם האופציה COMPRESS_ALL_ROW_GROUPS = ONמבצע compress לכל ה- row groups, ללא קשר למספר השורות בהם, כפי שנראה בהמשך.

<strong>ALTER INDEX REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) // compresses all row groups</strong>
<strong>

The following two tabs change content below.

• Bio
• Latest Posts

דוד יצחק

דוד יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle ,Sybase Anywhere מזה 17 שנה עם התמחות בשיפור ביצועים, שרידות, זמינות גבוהה, רפלקציה, תוספים גיאוגרפיים, אבטחת מידע ו-Business Intelligence. הוא מרכז את פורום DBA צפון של ilOUG ומרצה בפורום משתמשי SQL Server.

Latest posts by דוד יצחק (see all)

• MongoDB ל DBA ומפתחים הלכה למעשה – חלק ב - 16/02/2016
• MongoDB ל DBA ומפתחים הלכה למעשה - 07/02/2016
• בדיקת ביצועים של Clustered ColumnStore Index - 06/11/2014