Non-Clustered ColumnStore Indexes ב-SQL 2012 ו-SQL 2014

ColumnStore Indexes היא טכנולוגיה לאחזור ועדכון נתונים מבוססי עמודה. 2 סוגי אינדקסים:

• Non-Clustered Columnstore index (החל מ-SQL 2012)
• Updateable Clustered ColumnStore Index ( החל מ SQL 2014)

ב-ColumnStore Indexes הם אינדקסים שמבוססים על מיון המידע וקיבוצו על פי עמודות ולא שורות כמו האינדקסים הותיקים מסוג RowStore index. ColumnStore Indexes מטרתו לשפר ביצועים שאילתות עם כמויות נתונים גדולות, בדרך כלל מול טבלת fact במחסני נתונים. נניח ונתונה הטבלה הבאה:

אם נבנה אינדקס על כל 4 העמודות בטבלה ( FirstName, LastName, HireDate ו-Gender) באינדקס מבוסס שורה, המידע על טבלת העובד יאוחסן בדף אחד או יותר ויראה כך:

באינדקס מבוסס עמודה, המידע בטבלה Employee, מאוחסן בדף נפרד עבור כל עמודה כפי שמופיע בשרטוט הבא:

Data page הוא דף בגודל 8-KB שמכיל מידע. אם יש לך 20 שורות, והגודל של ה-20 שורות הוא 32KB, אזי סה"כ ידרשו 4 דפים במקרה של rowstore index. ה-columnstore index אינו מכיל את כול המידע, אלא מידע עבור עמודה אחת בלבד. דוגמא אם לעמודה FirstName יש 100 ערכים והגודל של ה 100 ערכים הוא 60KB. לכן הוא יכיל 8 דפים מסוג columnstore. באותו אופן עבור שאר 3 העמודות.

יתרונות של אחסון מסוג Columnstore ואינדקס מבוסס עמודות

1. לעמודות יש בדרך כלל מידע דומה וחוזר בטבלה, כך שניתן להגיע ליחס דחיסה גבוה. columnstore indexמאוחסן בצורה מכווצת על הדיסק, כך שהוא דורש פחות מקום על הדיסק ומאפשר חיפוש מהיר יותר.

2. Columnstore indexes משתמש ב-X-Velocity in-Memory Compression Engine שכבר בשימוש ע"י מיקרוסופט במספר מוצרים כמו: PowerPivot, Tabular Mode for Analysis Servicesו-PowerView. הרעיון הבסיסי של מנוע זה הוא חלוקה של המידע ל row groups, שנקראים גם segments. כל segment מכיל כ-1.000.000 שורות (כרגע הפרמטר לא ניתן לשליטה), והשורות מכווצות ע"י אלגוריתם דחיסה פנימי של מיקרוסופט. לשורות שדורשות מידע נוסף (למשל מידע מסוג character), מתווסף מידע נוסף שנקרא Dictionaries. ה-segments וה-dictionaries מומרים ל-Blobs ומאוחסנים ב-SQL Server, כדפים הסטנדרטיים של SQL Server בגודל 8K. במידה ו-Blob גדול מ-8K, אזי המנגנון הרגיל של אחסון Blobs מופעל. יחס דחיסה גבוה משפר את ביצועי השאילתה ע"י צריכת זיכרון נמוכה יותר ולנצל את הזיכרון ביעילות ולהעלות או להוציא מהזיכרון עמודות על פי דרישה. ב-SQL 2014 מלבד השימוש בפרמטר DOP (Degree Of Parallelism), צריכת הזיכרון תשתנה בהתאם למשאבים הקיימים, לפחות לפי הפרסומים הרשמיים.

3. בדרך כלל שאילתות צריכות חלק מהעמודות של הטבלה. columnstore index לא יחפש את כל הדפים של כל העמודות, הוא יחפש רק את העמודות הנדרשות לשאילתה. רק הדפים מבוססי עמודות שמתאימות לשאילתה יעלו לזיכרון, מה שמאוד יעיל עבור טבלאות גדולות. לכן הביצוע של השאילתות יהיה עם פחות I/O ו-CPU לעומת אינדקסי מבוססי שורה. דוגמא. נניח ונתונה השאילתה הבאה בהתייחס לטבלה הקודמת:

SELECT
FirstName,
LastName,
FROM Employee
WHERE HireDate >= '1/1/2010'

אינדקס מבוסס שורות לא יעיל פה כי הוא מעלה לזיכרון דפים עם כל השורה, כולל עמודות שאין בהן צורך ואינם נדרשים בשאילתה מה שגורם לצריכת זיכרון גבוה, ו-I\O מיותר. תארו לכם מה קורה אם לטבלה יש 40 עמודות, ונדרשים רק 3 עמודות.

דברים שכדאי להתחשב בהם

לפני שיוצרים Non-Clustered Columnstore index יש להתחשב בתנאים הבאים:

1. כאשר יצרנו Non-Clustered ColumnStore Indexes על טבלה לאחר טעינת הנתונים, הטבלה הופכת להיות לקריאה בלבד ללא אפשרות לעדכן את הנתונים. בשביל לטעון נתונים ניתן לבצע אחד מהאפשרויות הבאות:

• יש לבצע disable לאינדקס לפני ביצוע פקודות DML\ טעינת נתונים ו-enable חזרה סיום כדי להשתמש ביתרונותיך האינדקס בשאילתות Select.דוגמא:

ALTER INDEX idx_columnstore_SalesOrderNumber ON SalesOrders DISABLE;
--update mytable --
ALTER INDEX idx_columnstore_SalesOrderNumber on SalesOrders REBUILD;

• זריקת האינדקס, ביצוע פקודות DML\ טעינת נתונים ויצירת האינדקס מחדש בסיום.
• שימוש ב – staging table לצורך טעינת נתונים, בנית Non-Clustered ColumnStore Index על הטבלה והכנסת הטבלה ל-Partitioned Table.

2. לא ניתן לשנות את מבנה ה -ColumnStore Indexe ע"י הפקודה ALTER INDEX. יש לזרוק וליצור את האינדקס מחדש.
3. האם הטבלה תכיל הרבה מידע ומיליוני רשומות?

סעיפים 1 ו 2 הנ"ל הם חסרון עצום שנפתרו חלקית ב Updateable Clustered ColumnStore Index של SQL 2014. נניח שנתונה טבלה בגודל 500GB שמאוחסנת כ-Row Store בצורה הרגילה. עם Non-Clustered ColumnStore ניתן לדחוס את הנתונים עד ל 100GB. עדין נאלצים לשמור את המידע פעמים: טבלה רגילה מאוחסנת כ-Row Store בצורה הרגילה ומאפשר קריאה ועדכון ופעם שנייה טבלה בפורמט של Non-Clustered ColumnStore לקריאה בלבד ובתקווה שהשאילתות ישתמשו ב Non-Clustered ColumnStore Index.

בקצרה קימות המגבלות הבאות עבור Non-Clustered ColumnStore Indexes. הרשימה המלאה ב-BOL:

• טבלה עם Non-Clustered ColumnStore Indexes יכולה להכיל unique constraints, primary key constraints, foreign key constraints אבל לא כחלק מ Non-Clustered ColumnStore Indexes.
• לא ניתן להשתמש ב ASC/DESC או ב INCLUDE.
• יצירה של ColumnStore index לוקחת יותר זמן מ-B-Tree Index (על פי התיעוד ומניסיון, לפחות פי 1.5) עבור אותו קבוצת עמודות בגלל שהמידע מכווץ.
• לא כל סוגי הנתונים נתמכים (ntext, text, image rowversion (and timestamp)varchar(max), nvarchar(max), sql_variantCLR types (hierarchyid, spatial types)).

סוגי דחיסה של ColumnStore Indexes

ColumnStore Compression – החל מ-SQL 2012.

Archival Compression– החל מ – SQL 2014.בסביבת מחסני נתונים בהם יש הרבה מידע היסטורי שהגישה אליהם מועטה אבל הם תופשים הרבה מקום. ב-זוהי שיטת הדחיסה החדשה עבור ColumnStore Indexes. זה נעשה ע"י שימוש באלגוריתם דחיסה נוסף מעל זו הקיים מסוג Xpress8 והמשתמש ב-data streams של 64KB. הוא מאפשר שיפור של עד כ 30% ביחס הדחיסה.

הערה: ניתן לשנות את צורת הדחיסה ברמה של partitioned ב-partitioned table ע"י ביצוע rebuild.

להן אבצע הדגמה עבור Non-Clustered Columnstore index.ה-script של ההדגמה שמו SQL_2014_ Non-Clustered_Columnstore_index.sql ניתן להוריד מהאתר. ניצור טבלה בשם SalesOrders ונאכלס אותה עם 2 מיליון רשומות. קטע הקוד המתאים:

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 SalesOrders(SalesOrdersID INT IDENTITY, OrderDate DATETIME,
SubTotal MONEY, SalesOrderNumber INT)
GO
--inserting 2000000 rows into table
INSERT INTO SalesOrders(OrderDate, SubTotal, SalesOrderNumber)
SELECT TOP 2000000
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

ניצור על הטבלה Cluster Index על העמודה SalesOrderNumber.

--creating clustered index on column SalesOrderNumber 
CREATE CLUSTERED INDEX idx_SalesOrderNumber ON SalesOrders(SalesOrderNumber)
GO

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

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
 
SELECT
SalesOrderNumber
,sum(SubTotal) as SumAmt
,avg(SubTotal) as AvgAmt
FROM
SalesOrders
WHERE
SalesOrderNumber>3
Group By
SalesOrderNumber
Order By
SalesOrderNumber

נסתכל על ה-execution plan יש אינדקס על הטבלה, SQL Server יודע מה הערכים בכול דף. הוא ניגש לדף הספציפי, שולף את כל הערכים ומציג אותם בסדר מה שנקרא Index seek. במקרה שלנו Clustered Index Seek.

כעת נזרוק את ה-Clustered Index. ניצור NONCLUSTERED COLUMNSTORE INDEX על 2 העמודות הללו.

--dropping clustered index idx_SalesOrderNumber
DROP INDEX idx_SalesOrderNumber ON SalesOrders
GO
--creating columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX
idx_columnstore_SalesOrderNumber
ON SalesOrders(SubTotal,SalesOrderNumber)
GO

נריץ את השאילתה הקודמת שוב. נביט שוב ב-execution plan. במקרה שלנו יש שימוש ב Columnsore Index Seek.

נשווה בין 2 ה-execution plan ונראה יש יתרון רב וניכר על פי הטבלה הבאה:

Operator                      Clustered Index      COLUMNSTORE Index
Estimated I/O Cost            5.30641              0.03125
Estimated Operator Cost       6.08003              0.113133
Estimated CPU cost            0.77542              0.110008
Estimated Subtree Cost        6.08003              0.113133

ב-SQL 2014 החסרונות של Non-Clustered ColumnStore Indexes שופרו ע"י שימוש ב Updateable Clustered ColumnStore Index החדש. על כך בהרחבה במאמר הבא.

למי שרוצה לנסות את כל מה שדיברנו כאן היום, מצורף הסקריפט SQL_2014_ Non-Clustered_Columnstore_index.sql.

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