הבלוג של גרי רשף

18/11/2011

ColumnStore Index – הכרות ראשונה עם האינדקס החדש ב-SQL Server 2012

Filed under: Uncategorized — גרי רשף @ 16:44

גרסת SQL Server 2012 מפנקת אותנו עם שלל הפתעות וחידושים, ובינהם אינדקס מסוג חדש – השומר לפי עמודת.
במה דברים אמורים? הטבלה עצמה נשמרת ללא שינוי כ-Heap או כ-Clustered Index – שורה אחר שורה. על טבלה זו ניתן ליצור אינדקס בו הנתונים ישמרו לפי עמודות – בהתחלה נתוני עמודה אחת, לאחר מכן נתוני עמודה שתיים וכו'. אופציה זו אמורה להיות נוחה בעת שליפה של כמות גדולה של נתונים.
מדובר אם כך בארגון פיזי שונה של האינדקס, אך מבחינתנו בלא יותר מאשר שינוי קל בהגדרת האינדקס.
האופציות בהגדרת ColumnStore Index אינן רבות וכוללות בדרך כלל רק את רשימת העמודות שיש לכלול בו:
* הוא אינו יכול להיות Clustered Index (די הגיוני אם חושבים על זה).
* אין בו אופציית Unique (כנ"ל).
* אין אופציה ל-Filtered Index (זה דווקא לא מחוייב ההגיון).
* אין אופציה לבחירת סדר המיון Asc/Desc (כנ"ל).
* אין אופציה ל-Fill Factor (כאן יש להרהר מה יכולה להיות משמעות ה-Fill Factor באינדקס זה).
בקישור הנ"ל ניתן למצוא עוד מגבלות רבות, אבל יש שני יתרונות חשובים:
* אין מגבלת גודל שורה כמו באינדקס רגיל (די הגיוני).
* ביצועים משופרים – ובזה יעסוק המשך הפוסט.
ניצור שתי טבלאות המבוססות על טבלת המערכת sys.messages עם הודעות השגיאה (כ-225,000 שורות בגרסת 2012) – אחת עם אינדקס רגיל (להלן- RowStore) ואחת עם אינדקס ColumnStore:

Select *
Into   T_RowStore
From   sys.messages;
Go
 
Create Unique Clustered Index Idx_T_RowStore On T_RowStore(message_id, language_id);
Go
 
Select *
Into   T_ColumnStore
From   sys.messages;
Go
 
Create ColumnStore Index Idx_T_ColumnStore On T_ColumnStore(message_id, language_id, severity, is_event_logged, [text]);
Go

clip_image001

הרצתי שליפות שונות מהטבלאות, השוותי בין ה-Execution Plans, וליד כל שליפה ציינתי מה היה האחוז שלה:

--1
Select * From T_RowStore;   --69%
Select * From T_ColumnStore;--31%
 
--2
Select message_id From T_RowStore;    --95%
Select message_id From T_ColumnStore; --5%
 
--3
Select * From T_RowStore Where message_id=4919 And language_id=2070    --0%
Select * From T_ColumnStore Where message_id=4919 And language_id=270; --100%
 
--4
Select * From T_RowStore Where message_id=4919;    --0%
Select * From T_ColumnStore Where message_id=4919; --100%
 
--5
Select * From T_RowStore Where language_id=2070;   --69%
Select * From T_ColumnStore Where language_id=2070;--31%
 
--6
Select message_id, language_id, Count(*) Cnt From T_RowStore Group y message_id, language_id;    --56%
Select message_id, language_id, Count(*) Cnt From T_ColumnStore Grop By message_id, language_id; --44%
 
--7
Select message_id, language_id, Count(*) Cnt From T_RowStore Group By message_id, language_id Having Count(*)>1 Order By Count(*);    --41%
Select message_id, language_id, Count(*) Cnt From T_ColumnStore Group By message_id, language_id Having Count(*)>1 Order By Count(*); --59%
 
--8
Select * From T_RowStore Where [text]='The log in this backup set contains bulk-logged changes. Point-in-time recovery was inhibited. The database has been rolled forward to the end of the log.';    --69%
Select * From T_ColumnStore Where [text]='The log in this backup set contains bulk-logged changes. Point-in-time recovery was inhibited. The database has been rolled forward to the end of the log.'; --31%
 
--9
Select * From T_RowStore Order By message_id;   --1%
Select * From T_ColumnStore Order By message_id;--99%
 
--10
Select * From T_RowStore Order By severity;    --50%
Select * From T_ColumnStore Order By severity; --50%

התצוגה בשאילתה 8 משובשבת- מדובר בשליפת * Select עם תנאי על עמודת text.
באופן כללי אפשר להתרשם שבשליפות בהן יש יתרון גלוי לַ-Clustered Index (למשל- 3 & 4 בהן הפילטור לפי האינדקס, 7 בה הקיבוץ לפי האינדקס, 9 בה המיון לפי האינדקס) הוא מציג ביצועים טובים יותר, אבל בכל השאר- ה-ColumnStore Index עדיף.

אינני מצרף כאן את קבצי ה-Execution Plan אבל כדאי להוסיף שבכל השליפות מ-T_RowStore התבצע Clustred Index Scan/Seek (די הגיוני בהתחשב בכך שכך הטבלה שמורה), ובכל השליפות מ-T_ColumnStore התבצע ColumnStore Index Scan וזה לא מחוייב המציאות שכן ניתן היה לבצע גם Table Scan אבל ה-ColumnStore Index מכסה את כל העמודות (Covered Index) ולכן הרבה יותר קל למערכת לבצע עליו Scan ולשלוף כל עמודה בנפרד.

כדאי גם להוסיף שלא ניתן ליצור Covered Index רגיל על טבלת T_RowStore מכיוון שעמודת [text] היא (NVarchar(2048 וחורגת מגבולות האפשר.

ולבסוף- אין מניעה ליצור לטבלה אינדקסים משני הסוגים.

כלים כמו ColumnStore Index מותירים בי תחושה שעוד נכונו להם עלילות, יש הרבה מה לחקור ולנסות וכל הנ"ל אינו אלא טעימה ראשונה. למשל- לא בדקתי מה קורה בפעולות Join: מצד אחד אינדקס זה אינו תומך ב-Foreign Key, אך מי יודע- אולי הוא מפצה על כך בפעולות Scan יעילות יותר.

לאחר שפרסמתי את הפוסט התברר לי שלא קראתי את האותיות הקטנות עד הסוף: לאינדקס יש מגבלה "קטנה"- לא ניתן לעדכן אותו והוא הופך את הטבלה ל-Read Only. זה כמובן משנה את התמונה כולה..

יצירה של אתר חינמי או בלוג ב־WordPress.com.