SQL Server 2011 - תכונות חדשות - COLUMNSTORE INDEX - ilDBA Portal

SQL Server 2011 – תכונות חדשות – COLUMNSTORE INDEX

04/01/2011 | פורסם על ידי

אמרו שאי אפשר לעשות את זה,אז אמרו!
בגירסה הבאה של SQL SERVER ,בסיס נתונים יחסי משולב עם בסיס נתונים מבוסס עמודות

הקדמה
מיקרוסופט מספקת הצצה לפיתוח חדש שלה בגירסה הבאה של SQL SERVER (שם קוד  "DENALI", או SQL Server 2011),ועוד איזה חידוש !

לראשונה חברה Microsoft הצליחה לשלב ארכטקטורה של בסיס נתונים מבוסס עמודות (COLUMN Based Database) יחד עם בסיס נתונים יחסי מבוסס שורות (ROW Based RDBMS ).

כידוע קיימים מספר מוצרים בשוק כמו VERTICA,SYBASE IQ ואחרים המספקים פתרונות של בסיסי נתונים מבוססי עמודה. רובם ככולם ממשים את אותו רעיון של שימוש בטבלה ועמודה כאובייקט פיסי נפרד, על שלל יתרונותיהם (לא נפרט כאן את כולם,המעונינים ימצאו חומר רב ברשת) וחסרונותיהם, לעומת המודל היחסי המוכר השומר ברמה הפיסית שורות בלבד.

כאשר משתמשים בבסיס נתונים מבוסס עמודות המאפשר שימוש בדחיסה אגרסיבית (Compression) ,יש יתרון גדול מאוד בשאילתות אגרגטיביות במחסני נתונים גדולים עד עצומים ,לעומת בסיסי נתונים יחסיים.

לעומת זאת, במערכותOLTP  לבסיסי נתונים יחסיים יש ביצועים טובים בהרבה מאשר בסיסי נתונים מבוססי עמודות – הן באיחזורים (SELECT) והן בעידכונים (DML) .

למעשה Microsoft פיתחה מנוע איחזור מבוסס על התפיסה העומדת מאחורי בסיסי נתונים עמודתיים כבר בExcel 2010 ע"י יכולת ה-PowerPivot,המנוע מאחוריו נקרא Vertipaq™ והוא למעשה מנוע בסיס נתונים עמודתי שפותח ע"י Microsoft, ומכאן היתה רק קפיצת דרך "קטנה" לשילובו  בתוך המנוע של  SQL Server.

המימוש של ה-VertiPaq™  יתבצע ע"י יצירת אינדקס מסוג ColumnStore ע"ג טבלה שהיא HEAP או B-Tree ,הארכיטקטורה הפיסית תראה כך :

העמודות C1 עד C6 מאוחסנות בקבוצות דפים נפרדות בתוך האינדקס מה שנותן את היתרונות הבאים :

  • רק העמודות הדרושות לשאילתא נקראות מהדיסק (בד"כ 15% או פחות מטבלת FACT טיפוסית)
  • קל מאוד לבצע דחיסה אגרסיבית כאשר שומרים עמודות. למשל, עמודת "מיגדר" תכיל רק שני ערכים "זכר"/"נקבה" לא משנה מה גודל ה-FACT.
  • בגלל הדחיסה רוב הדפים הדרושים לשאילתות חוזרות כבר יהיו ב-BUFFER CACHE  ,ודפים שאינם דרושים יבצעו PAGE OUT,זה יקרה גם במחסני נתונים עצומים.
  • למעשה הביצועים של ארכיטקטורה זו מושגים מיתרון אחד בלבד ,קריאה של הרבה הרבה פחות DATA או זיכרון מאשר בארכיטקטורה יחסית רגילה.

המימוש של ה-ColumStore Index  ב-SQL SERVER הוא מימוש "טהור". הדפים שמחזיקים את האינדקס הנ"ל נפרדים לחלוטין מדפי ה-HOBT (=קיצור של Heap Or B-Tree),זהו בסיס הנתונים  היחסי הגדול הראשון שמבצע סוג מימוש כזה של אינדקסים עמודתיים.

שימוש ב-COLUMNSTORE INDEX

אז איך בונים ומשתמשים באינדקס מהסוג הזה ?

כרגיל מיקרוסופט מנסה לפשט את העניינים ולכן  ליצור אינדקס מסוג COLUMNSTORE יהיה פשוט יחסית ויראה כך :

CREATE COLUMNSTORE INDEX cstore on [dbo].[Huge_Fact]
(Column1, Column2, Column3……. )

יצירת האינדקס תבנה את מבנה את הנתונים  שראינו קודם בתרשים .

בדיקת ביצועים

אז מה זה נותן לנו? מכיוון שקשה מאוד לייצר בסביבת בדיקות בסיס נתונים גדול מספיק ,אצטט מספרים שנבדקו במעבדות של מיקרוסופט ,הטבלה שעליה הורצה השאילתה שנראה  היא בת 1טרה-בייט (TB)  ומכילה 1.44 מיליארד שורות. המכונה שעליה בוצעה הבדיקה מכילה 32 ליבות עם 256 ג'יגה בייט זיכרון.

השאילתה הבאה הורצה פעמיים בשני מצבים:

  • ללא COLUMNSTORE Index
  • יחד עם COLUMNSTORE Index על כל העמודות.
select w_city, w_state, d_year,SUM(cs_sales_price)as cs_sales_price
from warehouse, catalog_sales, date_dim
where w_warehouse_sk = cs_warehouse_sk
and cs_sold_date_sk = d_date_sk
and w_state in('SD','OH')
and d_year in(2001,2002,2003)
groupby w_city, w_state, d_year
orderby d_year, w_state, w_city;

רק התוצאות של ההרצות השנייות נילקחות בחשבון (על מנת שהמידע יגיע מתוך ה-cache),להלן התוצאות :

Elapsed time (seconds)

Total CPU time (seconds)

Columnstore

1.10

31.0

No columnstore

501

502

Speedup

455x

16x

התוצאות נראות מדהימות בכל קנה מידה : שניה וקצת לבצע שאילתת Star-Join  על 1 טרה-בייט ו-1.44 מיליארד שורות, לעומת  8.5 דקות (לא רע כשלעצמו) לערך בלי ה-COLUMNSTORE. ההבדלים בביצועים יושפעו מכמות הזיכרון במכונה – ככל שיש פחות זיכרון במכונה, ההבלים לטובת ה-COLUMNSTORE  יהיו גדולים יותר.

אם הזיכרון גדול עד כדי כך שבשני המיקרים לא יהיה Page Out מהזיכרון, ההבדלים יהיו קטנים יותר, אך עדיין פי כמה וכמה מהזמן הרגיל.

אם הזיכרון גדול עד כדי כך שבשני המיקרים לא יהיה Page Out מהזיכרון, ההבדלים יהיו קטנים יותר, אך עדיין פי כמה וכמה מהזמן הרגיל.

חסרונות COLUMNSTORE Indexes

טוב,אז אתם בטח כבר שואלים עצמכם ,איפה מסתתרות  ה"עיזים".

  • הנפח שתופס ה-COLUMNSTORE Index  נע בין  1/4 ל- 1/15 מגודל ה-DATA  (תלוי כמובן בכמות העמודות שבחרתם) וזאת בגלל הדחיסה האגרסיבית המאפיינת טכנולוגיה זו.
  • הזמן שלוקח לבנות COLUMNSTORE Index  אינדקס הוא בין  פי 2 לפי 3 מהזמן שלוקח לבנות אינדקס זהה רגיל.
  • לא ניתן לבנות טבלה כ-COLUMNSTORE בלבד כלומר כ-CLUSTERED INDEX  הטבלה חייבת להיות HEAP  או B-Tree  לפני.
  • המגבלה הגדולה ביותר של COLUMNSTORE Index לפחות בגירסת "DENALI" היא שלא ניתן לבצע פעולות DML  או BULK LOAD  לטבלה שעליה הוא מוגדר.
    עם זאת, מייד נראה כיצד לעקוף חלקית מגבלה זו.

טעינת נתונים

אז איך טוענים נתונים ?

כמובן שניתן להסיר את האינדקס להכניס נתונים ולבנותו מחדש ,מה שלא תמיד ישמח את ה-DBA הממוצע  .מה שיותר מעשי לבצע זה SWITCH IN לטבלה שיש לה COLUMNSTORE .
כמובן שהאינדקס במיקרה זה חייב להיות ALIGNED.

עוד פיתרון יצירתי הוא הגדרת VIEW  שמכיל UNION ALL בין טבלה שיש עליה COLUMNSTORE לבין טבלה שאין לה אינדקס כזה ,מה שיאפשר עידכון נתונים לטבלה השניה דרך ה-VIEW ושאילתות עדיין יוכלו להרוויח מהאינדקס על הטבלה הראשונה.

ומה הכי יפה? אם יש לי שאילתה שאינה מרוויחה משימוש ב-COLUMNSTORE ,האופטימייזר יבחר בעצמו באינדקס הטוב ביותר למיקרה זה ,מכיוון  שמבנה אינדקסים רגיל עדיין יכול להיות קיים על ה-HOBT.

סיכום

ברשומה זו סקרנו לכם על COLUMNSTORE Index.
COLUMNSTORE Index הוא פיתוח חדשני שיצא בגרסה הבאה של SQL Server ("DENALI").
זוה פריצת דרך טכנולוגית המאפשרת שיפור ביצועים מרשים על ידי שימוש בארכיטקטורה של בסיס נתונים מבוסס עמודות, ומתאימה במיוחד לסביבות של מחסני נתונים (DWH).
בהחלט יש למה לחכות לגרסה הבאה של MSSQL.

לנוחיותכם ולמי שרוצה לקרוא עוד על התכונה החדשה, מצורף pdf רשמי של Microsoft המתאר אותה:
Columnstore Index in SQL Server 2011

אנחנו נמשיך לכתוב לכם ברשומות הבאות על עוד New features  מעניינים של Denali ועוד.
נתראה ברשומה הבאה !

4 תגובות ל- “SQL Server 2011 – תכונות חדשות – COLUMNSTORE INDEX”

[…] הוספת תכונה זו היא חלק מאסטרטגיה ברורה של מיקרוסופט להפוך את SQL Server לבסיס נתונים שאינו מכיל רק נתונים במסגרת המודל הרלציוני בלבד (דוגמא נפלאה לכך היא תכונה חדשה שתתממש בגרסת 11 – Columnbased Index – מימוש מבנה נתונים אנכי בתוך בסיס הנתונים הרלציוני. סיפרנו על כך כאן: לינק). […]

דוד יצחק | 27/01/2011 בשעה 02:33
commenter

אין כמו לרוץ לגרסאות החדשות אבל לפעמים מרוב ריצה לגרסאות החדשות לא ממקסמים את ה- new feature של הגרסה הקודמת.
רוצים דוגמא :
בה אלי לקוח וביקש לעבוד עם טבלה עם יותר מ- 1024 עמודות כי הוא טוען תוצאות של metadata ממקור חיצוני .
אתם זוכרים איך פותרים את הבעיה ?
הפתרון בעוד מספר ימים .
רמז : בכול מקרה זה אחד מה- new features של SQL 2008 R1/R2

דוד יצחק | 20/02/2011 בשעה 07:28
commenter

טוב אז התשובה היא sparse column
סיכום קצר :
צורך ב- Sparse Column
• לא צורך שטח אם העמודה היא Null .
• ניתן להגדיר עד 30000 עמודות מסוג sparse (עמודות רגילות עד 1024)
תנאים להגדרת Sparse column
• עמודות המוגדרות is null
• עמודה לא מסוג : ROWGUIDCOL , IDENTITY, text, ntext,, image, timestamp או FILESTREAM .

Column Sets מהי
עמודה מסוג Column sets מאפשרת עבודה עם כל ה- sparse column בטבלה דרך untyped XML (שלא קשור ל- XML סכימה) והוא בר עדכון .
מגבלות עמודה מסוג Column sets
• עמודה אחד פר טבלה .
• לא ניתן על להגדיר default values/Constrains
• לא ניתן להגדיר Computed columns שפונות ל- Column sets
• לא ניתן למחיקה : יש למחוק וליצור מחדש
ניתן ליצור Column sets אחרי שיצרתי Sparse Column .
מתי להשתמש ב- Sparse Column ?
יתרונות :
טוב בפתרון בעיות ביצועים ואחסון ב- DBs שתוכננו רע .
שימוש ביותר מ- 1024 עמודות פר טבלה .
חסרונות :
לא תמיד מתאים , כי הוא בניגוד לחוקי הנרמול .

דוד יצחק | 20/02/2011 בשעה 07:29
commenter

דוגמא של sparse column

/**********************************************************************************
NAME
Sparse_Columns_Example

DESCRIPTION
Sparse Columns Examples including :
Column Sets
Working with Sparse Columns.

MODIFIED (MM/DD/YY)
David Itshak 02/20/11 – Created

sources :Microsoft SQL Server 2008 R2 UNLEASHED , SAMS Press

***********************************************************************************/

USE AdventureWorks2008R2
GO
USE [AdventureWorks2008R2]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product_sparse]') AND type in (N'U'))
DROP TABLE [dbo].[Product_sparse]
GO

CREATE TABLE Product_sparse
(
ProductID INT NOT NULL PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
Color NVARCHAR(15) SPARSE NULL,
Weight DECIMAL(8,2) SPARSE NULL,
SellEndDate DATETIME SPARSE NULL,
ProductInfo XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
GO
INSERT INTO Product_sparse
(ProductID, ProductName, Color, Weight, SellEndDate)
SELECT ProductID, Name, Color, Weight, SellEndDate
FROM Production.Product
GO

SELECT productID, productName, Color, Weight, SEllEndDate
FROM Product_sparse
where ProductID < 320
go
/*
productID productName Color Weight SEllEndDate
——— ——————— ———— ————- ———–
1 Adjustable Race NULL NULL NULL
2 Bearing Ball NULL NULL NULL
3 BB Ball Bearing NULL NULL NULL
4 Headset Ball Bearings NULL NULL NULL
316 Blade NULL NULL NULL
317 LL Crankarm Black NULL NULL
318 ML Crankarm Black NULL NULL
319 HL Crankarm Black NULL NULL
*/

/*
Note, however, that if you use SELECT * in a query and the table has a column set defined
for the sparse columns, the column set is returned as a single XML column instead of the
individual columns*/
SELECT *
FROM Product_sparse
where ProductID < 320
go
/*
ProductID ProductName ProductInfo
———– ———————- ———————————-
1 Adjustable Race NULL
2 Bearing Ball NULL
3 BB Ball Bearing NULL
4 Headset BallBearings NULL
316 Blade NULL
317 LL Crankarm Black
318 ML Crankarm Black
319 HL Crankarm Black
*/

/* You need to explicitly list the columns in the SELECT clause to have the result columns
returned as relational columns.
When the column set is defined, you can also operate on the column set by using XML
operations instead of relational operations. For example, the following code inserts a row
into the table by using the column set and specifying a value for Weight as XML:
*/

INSERT Product_sparse(ProductID, ProductName, ProductInfo)
VALUES(5, 'ValveStem', '.12')
go
SELECT productID, productName, Color, Weight, SEllEndDate
FROM Product_sparse
where productID = 5
go

/*
productID productName Color Weight SEllEndDate
———– ———– —– —— ———–
5 ValveStem NULL 0.12 NULL
*/

/*
Notice that NULL is assumed for any column omitted from the XML value, such as Color
and SellEndDate in this example.
When updating a column set using an XML value, you must include values for all the
columns in the column set you want to set, including any existing values. Any values not
specified in the XML string are set to NULL. For example, the following query sets both
Color and Weight where ProductID = 5:
*/

Update Product_sparse
set ProductInfo = 'black.20'
where productID = 5
SELECT productID, productName, Color, Weight, SEllEndDate
FROM Product_sparse
where productID = 5
go

/*
productID productName Color Weight SEllEndDate
———– ———– —– —— ———–
5 ValveStem black 0.20 NULL
*/

/*
Now, if you run another update but only specify a value for Weight in the XML string, the
Color column is set to NULL:
*/

Update Product_sparse
set ProductInfo = '.10'
where productID = 5
SELECT productID, productName, Color, Weight, SEllEndDate
FROM Product_sparse
where productID = 5
go

/*
productID productName Color Weight SEllEndDate
———– ———– —– —— ———–
5 ValveStem NULL 0.10 NULL
*/

/*
However, if you reference the sparse columns explicitly in an UPDATE statement, the other
values remain unchanged:
*/

Update Product_sparse
set Color = 'silver'
where ProductID = 5
SELECT productID, productName, Color, Weight, SEllEndDate
FROM Product_sparse
where productID = 5
go

/*
productID productName Color Weight SEllEndDate
———– ———– —— —— ———–
5 ValveStem silver 0.10 NULL

*/

/*
Column sets are most useful when you have many sparse columns in a table (for example,
hundreds) and operating on them individually is cumbersome. Your client applications
may more easily and efficiently generate the appropriate XML string to populate the
column set rather than your having to build an UPDATE statement dynamically to determine
which of the sparse columns need to be included in the SET clause. Applications
might actually see some performance improvement when they select, insert, or update
data by using column sets on tables that have lots of columns.
*/

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

קהילת

קהילת ה- BI וה- BIG DATA מתכנסת ב- 28-10 !

שלום רב, למי שלא ידע 🙂 קהילת ה- BI וה- BIG DATA בעולמות Microsoft מתכנסת מידי חודש על מנת להפגש, להכיר ולשמוע הרצאות במגוון נושאים טכנולוגיים מרתקים בתחום. במפגש הקרוב (מספר 63) שיתקיים ב- 28-10-2015, יום רביעי [...]
הזמנה

הזמנה ל-SQL Saturday #481 – Israel 2016

שלום רב, בקרוב יתקיים בישראל כנס טכנולוגי מרכזי קהילת ה-DB וה-BI בתחום ה-SQL Server – ה-SQL Saturday! הכנס אשר מאורגן בהתנדבות על ידי אנשי הקהילה יכלול במהלכו מספר מסלולי לימוד בתחומים טכנולוגיים שונים. [...]

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]

תיעוד בסיס הנתונים – למה זה טוב?

ישנם מספר נושאים חשובים שיש לתת עליהם את הדעת בפיתוח ותכנון בסיס נתונים. כמובן שצריך לשים לב לנירמול נכון של הטבלאות, וכמובן שצריך להגדיר סטנדרט לכתיבה נכונה של הקוד, וכמובן שאי אפשר בלי [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: