content top
Clustered ColumnStore Index ב- SQL 2016 ועבודה עם Query store הלכה למעשה

Clustered ColumnStore Index ב- SQL 2016 ועבודה עם Query store הלכה למעשה

במאמר זה אני אעסוק ב Clustered ColumnStore Index מול SQL 2016 CTP3 .

בתור רקע טוב תוכלו למצוא באינטרנט סדרת מאמרים בעברית שכתבתי על Clustered ColumnStore Index (CCI) ב SQL 2014 ו Non-Clustered ColumnStore (NCCI) ב SQL 2012 . בנוסף הרצאה שלי עמוסה בקטעי Demos מקוריים בשם SQL Server 2014
Lesson from the Fields
ב SQL Pass Hebrew Chapter בכתובת :

http://globalhebrew.sqlpass.org/en-us/home.aspx?EventID=2343

למאמר יש script בשם SQL_2016_ Clustered_Columnstore_index.sql מלא דוגמאות ומתועד היטב , תוכלו להיעזר בו לצורך תרגול .

בקצרה : אינדקסים סטנדרטיים מסוג B tree ו- heaps הם מבוססים על חלוקה לשורות (Row Based) . לעומת זאת אינדקסים מסוג ColumnStore Index (CI) הם מבוססי עמודה , כל דף מכיל מידע מעמודה בודדת , רק העמודות הנדרשות מגיעות לזיכרון . זו הסיבה שניתן להוריד את תקורת ה- I/O בצורה דרסטית , יש שיפור בביצועים כי ניתן לדחוס יותר דפים לזיכרון ויש התאמה באופן אוטומאטי למשאבי ה- CPU ע"י הפרמטר DOP) Degree of Parallelism) . CI יעילים מאוד עבור שאילתות אנליטיות מהסוג הבא:

· שאילתות מול הרבה שורות שדורשים ביצוע סריקה עם פילטר כמו = , > , < ו – IN .

· שאילת על חלק מהעמודות בטבלה , למשל 5 מתוך 100 עמודות .

· האצה של ביצוע joins , סיכומים, וסינון מתוך כמות מידע גדולה, ביצוע שאילתות מול סכימת כוכב וטבלאות fact (בד"כ אחרי טעינות מסיביות של נתונים).

החידושים עבור CCI בגרסה SQL 2016. :

· לטבלה מבוססת עמודות (rowstore) יכול להיות updateable nonclustered columnstore index אחד . בגרסאות MSSQL 2014/2012 , שימוש באינדקס כזה היה הופך את הטבלה ל Read Only .

· הגדרה של nonclustered columnstore index שמאפשר ליצור filtered condition. בצורה כזו ניתן ליצור nonclustered columnstore index על המידע ה"קר" בטבלה, שהגישה אליו בתדירות נמוכה , ומשמש לצורכי ניתוח ו DW . לעומת זאת הגישה למידע ה"חם" בטבלה ,שמשמש עבור OLTP , לא תיפגע ע"י ה columnstore index . עלינו לזכור שפעולות DML על columnstore index של עדכון או חיפוש רשומה או רשומות ספציפיות הן איטיות מאוד לעומת אינדקס btree רגיל . יש פה שילוב של טבלה שמשמשת הן ל OLTP והן ל DW באותו זמן . מיקרוסופט קוראים לזה Operational DW . זהו למעשה מגמה עולמית שה OLTP DB ישמש גם כ DW DB . היתרונות הן חיסכון בהוצאות וקבלת נתוני הניתוח בזמן אמת .

· כעת ל- clustered columnstore index יכול להיות nonclustered rowstore indexes אחד או יותר . בגרסת SQL 2014 היה אפשר ליצור clustered columnstore index בלבד על הטבלה . אינדקס מסוג nonclustered rowstore indexes יעיל מאוד לעומת clustered columnstore index בחיפוש של חיפוש רשומה או רשומות ספציפיות ע"פ קריטריון כלשהו . אני אדגם בהמשך .

· תמיכה ב Primary Key או foreign keys ע"י שימוש ב btree index למימוש constraints על clustered columnstore index.

רק הערה להמשך : שאילתות עם CI לא נתמכות ע"י Live Query Statistics , כלומר יכולת לראות את ה execution plan בזמן אמת . אנחנו נשתמש ב query store לצורך השוואה בין ביצועים של שאילתות שונות .

יצירה של CCI על חלק מהעמודות

כל העמודות חיבות להיות מוכלות ב CCI ובעת היצירה אין צורך לציין את העמודות . אם ננסה לעשות זאת :

--creating table for demo

CREATE TABLE dbo.SalesOrders (SalesOrdersID INT IDENTITY, OrderDate

                            DATETIME,

                            SubTotal MONEY, SalesOrderNumber INT)

GO


CREATE CLUSTERED COLUMNSTORE INDEX idx_columnstore_SalesOrderNumber ON dbo.SalesOrders (SalesOrdersID, OrderDate )

GO

נקבל את הודעת השגיאה הבאה:

Msg 2714, Level 16, State 6, Line 2

There is already an object named 'SalesOrders' in the database.

Msg 35335, Level 15, State 1, Line 11

The 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 וטעינת נתונים

לצורך ניתוח הביצועים בשלב הראשון נפעיל את ה query store ,נבצע לו "ניקוי" למקרה והוא כבר היה פעיל ואגר מידע , ומיד לאחר מכן נבצע לו disable בזמן יצירת האינדקסים .

SET STATISTICS IO ON

GO

SET STATISTICS TIME ON

GO

ALTER DATABASE [AdventureWorks2014] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [AdventureWorks2014] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [AdventureWorks2014] SET RECOVERY SIMPLE

GO

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = ON

GO

-- Clear QUERY_STORE so we will get only the following query in query store GUI 

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE CLEAR ALL -- Disbale Query Store on the database while creating indexes 

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = OFF

GO

בקטע הקוד הבא ניצור טבלה בשם SalesOrders , ניצור עליה CCI ונבצע הכנסה של 2100000 שורות .

use [AdventureWorks2014]

--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


--inserting 2100000 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

--creating clustered columnstore index

CREATE CLUSTERED COLUMNSTORE INDEX idx_columnstore_SalesOrderNumber ON dbo.SalesOrders

GO

נפעיל מתוך SSMS את האופציה Query->Include actual Plan . נריץ את השאילתה הבאה מה- Execution Plan ונראה שהשאילתה מבצעת columnstore index scan .

-- The execution plan will show a columnstore index scan also for this query 

SELECT TOP(5) * FROM SalesOrders

clip_image002

נפעיל את ה query store . נבצע את השאילה הבאה ונראה שהשאילתה מבצעת גם כן columnstore index scan . "נכבה" את ה query store .

-- enable Query Store on the database

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = ON

GO

-- The execution plan will show a columnstore index scan also for this query 


SELECT * FROM SalesOrders where SalesOrdersID=1105662 -- Disbale Query Store on the database while creating indexes 

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = OFF

GO

clip_image004

חדש ב SQL 2016 . נוכל להגדיר nonclustered index לשפר את היעילות של table seeks מול מחסני נתונים . שאילתות שמחזירות סט נתונים קטן או מחפשות ע"פ ערך ספציפי יעבדו בתורה יעילה יותר מול b tree indexes . " נכבה" את ה query store .ניצור nonclustered index על עמודה החיפוש SalesOrdersID .נפעיל את ה query store . נבצע את השאילה הקודמת שוב ונראה שהשאילתה מבצעת Index Seek (nonCluster) שהוא יותר יעיל . "נכבה" את ה query store .

-- Disbale Query Store on the database while creating indexes 

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = OFF

GO


--Add a nonclustered index.

CREATE UNIQUE INDEX IDX_ ON dbo.SalesOrders (SalesOrdersID) where SalesOrdersID > 0;


-- enable Query Store on the database

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = ON

GO


-- This time execution plan will show now Index Seek (nonCluster) which is more eficient 

SELECT * FROM SalesOrders where SalesOrdersID=1105662


-- Disbale Query Store on the database while creating indexes 

ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = OFF

GO

clip_image006

נוכל להשוות בין שני ההרצות . ב SSMS תחת AdventureWorks2014 ניגש ל Query store-> Top Resource consuming Query . מקש ימני ונבחר ב view top Resource Consuming Query .

clip_image008

במסך שיפתח נבחר ב Avg עבור Statistics , Logical Reads עבור Metric בציר האפקי ו Exec count בציר האנכי. נוכל לראות את 2 השאילתות שלנו עם query id שווה ל 1 ו query id השווה ל 2 . אפשר לראות ש exec count שווה ל 1 עבור 2 השאילתות כי בצענו כל שאילתה פעם אחת . אם נעמוד עם העכבר על query id=1 , נוכל לראות את השאילתה , ה execution plan ו Avg Logical Reads .

clip_image010

נעמוד עם העכבר על query id=2 , נוכל לראות את השאילתה , ה execution plan ו Avg Logical Reads . ברור שהשאילתה בה השתמשנו ב nonclusterd index יעילה יותר כי Avg Logical Reads שלה שווה ל 45 לעומת 1752 ! .

clip_image012

Catalog Views של CCI

sys.column_store_row_groups הוא DMV שמאפשר לראות את כל ה- Row Groups שב- Delta Store שבוצע להם דחיסה וכל כל ה- Row Groups שב- Column Stores שלא בוצע להם דחיסה . נשתמש ב DMV זה בשביל להחליט מה מספר ה- Delta stores . אנו משתמשים ב- DMV זה בשביל לבדוק את איכות ה CCI . אם יש Row Group עם פחות 1M שורות והוא לא במצב COMPRESSED , יש לבדוק למה, כפי שאדגים להלן . יש עוד 2 DMVs : sys.column_store_segments שמאפשר למצוא את טווח הערכים של העמודות עבור הדחיסה

שמשתמש באלימינציה של סגמנטים ומידע על גודלו של כל segement ו sys.column_store_segments. אני לא אעסוק בהם . נריץ את השאילתה הבאה :

SELECT  i.object_id,

        object_name(i.object_id) AS TableName,

        i.name AS IndexName,

        i.index_id,

        i.type_desc,

        CSRowGroups.*,

        100*(total_rows – ISNULL(deleted_rows,0))/total_rows AS PercentFull

FROM    sys.indexes AS i

JOIN    sys.column_store_row_groups AS CSRowGroups

        ON i.object_id = CSRowGroups.object_id

        AND i.index_id = CSRowGroups.index_id

--WHERE object_name(i.object_id) = ‘<table_name>’

WHERE   object_name(i.object_id) = ‘SalesOrders’

ORDER BY object_name(i.object_id),

        i.name,

        row_group_id

נקבל את הפלט הבא :

clip_image014

הסבר : יש לנו 4 rowgroup ב columnstore שהם COMPRESSED rowgroup , כלומר state_description=COMPRESSED ו state=3 . מספר השורות שווה ל 1048576+329140+343980+378304 =2,100,000 . במקרה שלנו העמודה PercentFull מחשבת את יעילות ה row group . במקרה שלנו כולם שווים ל 100 אחוז וה CCI יעיל . אין לנו אף rowgroup שהוא Delta Store עבורו state=1 , state_description=OPEN , כלומר לא דחוס .

בניה מחדש של 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;

ר- אירגון של CCI יכולה להתבצע עם הפקודה הבאה .

ALTER INDEX [idx_columnstore_SalesOrderNumber] ON [dbo].[SalesOrders] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Columnstore Archiving

מ- SQL 2014 ל CCI יש אלגוריתם דחיסה נוסף , מלבד האלגוריתם שמגיע כבררת מחדל , בשם Column Store Archiving . המחיר הוא תקורה של CPU גבוהה יותר וזמן גישה איטי יותר . הדחיסה כאמור יכולה להיעשות פר partition בטבלה . נוכל לבדוק מה גודל הטבלה שבנינו ע"י הפרוצדורה sp_spaceused באופן הבא :

EXEC sp_spaceused SalesOrders

בפלט נקבל תחת העמודה reserved את הערך 53368 KB.

clip_image016

זה שטח האחסון שהטבלה תופשת . נריץ את הפקודה הבאה שמשנה את שיטת הדחיסה ל COLUMNSTORE_ARCHIVE

ALTER INDEX idx_columnstore_SalesOrderNumber ON SalesOrders REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

אם נבדוק מה גודל הטבלה כעת ע"י sp_spaceused נקבל תחת העמודה reserved את הערך 50808K. כלומר חסכנו כ 6% בשטח האחסון .

clip_image018

נוכל להחזיר את הטבלה לאלגוריתם דחיסה בררת מחדל COLUMNSTORE)) ע"י הפקודה הבא :

ALTER INDEX idx_columnstore_SalesOrderNumber ON SalesOrders REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE);


תגובה אחת על “Clustered ColumnStore Index ב- SQL 2016 ועבודה עם Query store הלכה למעשה”

  1. מאת אלכסנדר ברקוביץ׳:

    אחלה מאמר, עזרת לי מאוד, תודה

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

5 × 4 =