Updateable Clustered ColumnStore Index ב- SQL 2014

בגרסת SQL Server 2014 הציגו מיקרוסופט מספר טכנולוגיות מסוג in memory:

• In memory OLTP– שימוש ב- in memory Table עבור סביבת OLTP. על פי מיקרוסופט שיפור בביצועים פי 5 עד 20 אך הדבר דורש שינוי קוד ושינויי אפליקציה.
• Buffer Pool Extension – הרחבה של ה-RAMעל ידי דיסק מהיר חיצוני, על פי מיקרוסופט פי 4 עד 10 מגודל ה- RAM. הדבר יכול להביא שיפור של עד פי 3 בביצועים באופן שהוא שקוף לאפליקציה.
• In memory DW– על פי מיקרוסופט שיפור פי 5 עד 25 בביצועים ויחס דחיסה גבוה של הנתונים על ידי שימוש ב Clustered ColumnStore Indexes (CCI), שהוצג ב- SQL 2014, ועל כך במאמר זה.

Column Store Index יעילים עבור שאילתות מאשר מידע הנמצא על הדיסק על פי הבא:

• שאילתות מול הרבה שורות שדורשים ביצוע סריקה עם פילטר כמו =, >, < ו – IN.
• שאילת על חלק מהעמודות בטבלה, למשל 5 מתוך 100 עמודות.
• האצה של ביצוע joins, סיכומים, וסינון מתוך כמות מידע גדולה, ביצוע שאילתות מול סכימת כוכב וטבלאות fact (בד"כ אחרי טעינות מסיביות של נתונים).

ה-Column Store Index יכול לשפר ביצועים של אפליקציות מול מחסני נתונים ושאילתות אנליטיות. לאפליקציות OLTP שמשתמשות בתנועות (טרנזאקציות) קצרות האינדקס הזה פחות מתאים.

השימוש באינדקס זה שקוף לאפליקציה, עבור גיבוי שחזור ולפתרונות השרידות והזמינות: mirror, High Group Availability ו- log shipping. השימוש ב- CI מצמצם את השימוש באינדקסים רגילים, indexed Views, סיכומים ולפעמים את השימוש בקוביות OLAP.

Non-Clustered ColumnStore Index (NCCI) ב- SQL 2012

בחלק הקודם נתתי רקע על Column Store Index, סקרתי בהרחבה Non-Clustered ColumnStore (NCCI),שהוצג ב- SQL 2012, והצגתי את היתרונות והחסרונות. בחלק הקודם ראינו שאינדקסים סטנדרטיים מסוג B tree ו- heaps מבוססים על שורות ולעומת זאת אינדקסים מסוג CI:הם מבוססי עמודה. הרעיון הוא שכל דף מכיל מידע מעמודה בודדת ורק העמודות הנדרשות מגיעות לזיכרון. זוהי הסיבה שניתן להוריד את התקורה של ה- I/O בצורה דרסטית – השיפור בביצועים מגיע כי ניתן לדחוס יותר דפים לזיכרון ויש התאמה באופן אוטומטי למשאבי ה- CPU.

ההתאמה הזו מבוצעת על ידי הפרמטר DOP) Degree of Parallelism) אותו סקרתי בהרחבה במאמר הקודם אך נזכיר בקצרה:

• האינדקס יכול להיות על חלק מהעמודות.
• האינדקס נבנה על גבי אחסון מסוג שורה (rowstore) ולא אחסון מסוג עמודה (column store).
• טבלה עם NCCI היא לקריאה בלבד. במקרה של עדכון נדרש לבצע Drop לאינדקס, טעינת נתונים ויצירת אינדקס. לחילופין ALTER INDEX… DISABLE, טעינת נתונים ובסיום ALTER INDEX… ENABLE.
• אלגוריתם הדחיסה הוא של B tree או Heap ולא יכול לנצל את היתרונות של אחסון מבוסס עמודה.
• ניתן ליצור NCCI אחד בלבד על טבלה.
• שאילתות מסוג שוויון (equality) ושאילתות על טווח קצר (rang queries) יעילות יותר כי הם משתמשים ב- B tree.

Clustered ColumnStore Index (CCI) ב- SQL 2014

מכוון שבגרסה הקודמת היו לא מעט חסרונות, בגרסה של SQL 2014 בוצע שיפור ב-NCI על ידי שימוש ב Clustered ColumnStore Index (CCI) שלו התכונות הבאות:

• ב – CCI המידע מאוחסן בצורה דחוסה ועל פי חלוקה לעמודות בצורה כזו שניתן לבצע דחיסה בצורה יעילה, לחסוך מקום במערכת האחסון ולשפר ביצועים על ידי צריכת זיכרון נמוכה, כי רק העמודות הנדרשות מגיעות לזיכרון וגם אז, הם מגיעות בצורה דחוסה. ה-CCI לא מאוחסן בצורה ממוינת כמו Clustered Index רגיל, כפי שהוסבר בחלק ראשון של המאמר, למרות שמונח Clustered מופיע בשם האינדקס.
• כל העמודות נדרשות עבור CCI ולכן לא ניתן ליצור אינדקסים נוספים.
• טבלה עם CCI ניתנות לעדכון: INSERT, UPDATE, DELETE ו- bulk load.
• ניתן לבצע שימוש ב-Archival Compressionשמשפר את יחס הדחיסה עד X10. זהו אלגוריתם דחיסה משופר החל מ – SQL 2014. בפקודה ALTER INDEX … REBUILD התווספה האופציה COLUMNSTORE_ARCHIVE. Archival Compression מיועד עבור כמויות מידע גדולות למטרות ארכיון, כאשר נדרש מצמום בשטח דיסק כשהתקורה הנוספת היא בזמן נוסף לצורך אחסון ואחזור לעומת אלגוריתם הדחיסה הרגיל של Column Store Index.
• שאילתות מסוג שוויון (equality) ושאילתות על טווח קצר (rang queries) פחות יעילות ויתכן וידרשו סריקה מלאה של הטבלה.
• ב-  SQL 2012 היו Data types רבים שלא נתמכו על ידי NCI כמו binary, unique identifier ו- decimals עם precision > 18. ב- SQL 2014 כל ה- Data types שהם לא מסוג blob נתמכים על ידי columnstore index מלבד: n)text), image, rowversion, timestamp, sql_variant, hierarchyid, geography, geometry, xml ו- Varchar(max)(n).
• CCIזמין בגרסאות Enterprise, Developer ו- Evaluation Edition בלבד כלומר יש לו משמעות כספית מבחינת הרישוי.

המבנה של Clustered ColumnStore Index

השרטוט הבא מתאר את המבנה של CCI:

• Row Group היא קבוצה של שורות בטבלה, שמכווצים יחד לפורמט מבוסס עמודות (Colum Store) באותו זמן. מספר השורות חייב להיות מספיק גדול כדי שיחס הדחיסה יהיה מספיק יעיל, וקטן מספיק בשביל להיות יעיל לבצע פעולות בזיכרון. מספר השורות ב- Row Group הוא קבוע ועומד כרגע על 1048076.
• Column Segment מכיל מידע של עמודה אחת בטבלה ב- Row Group.
• כל Row Group מכיל Column Segment אחד עבור כל עמודה בטבלה (בשרטוט C1,C2..).
• כל Column Segment נדחס בפני עצמו ומאוחסן ע"ג המדיה הפיסית כ- LOB.
• Segment הוא יחידת העברה בן הדיסק לזיכרון.

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