במאמר זה נדבר על העבודה המעשית עם ה-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>
דוד יצחק
Latest posts by דוד יצחק (see all)
- MongoDB ל DBA ומפתחים הלכה למעשה – חלק ב - 16/02/2016
- MongoDB ל DBA ומפתחים הלכה למעשה - 07/02/2016
- בדיקת ביצועים של Clustered ColumnStore Index - 06/11/2014
השאר תגובה: