סקריפט לשדרוג טבלאות ל-Clusterd ColumnStore Index

01/02/2017

תחום ה-ColumnStore עבר כמה וכמה שינויים מאז שהוצג לראשונה בגרסת 2012.
כיום, בגרסת 2016, ניתן ליצור כבר Clusterd ColumnStore Index שזו טבלה הבנוייה כ-ColumnStore, ולא רק טבלה סטנדארטית שיש לה בנוסף אינדקס ColumnStore; והרבה מהמגבלות שצויינו בפוסט שלי מאז – הוסרו.
כדאי לציין שהמונח Clusterd ColumnStore Index קצת מטעה, כי בניגוד ל-Clustered Index “רגיל” (RowStore) – הוא אינו ממויין פיזית; ולכן ראוי היה לקרוא לו ColumnStore Heap, אלא ששם זה לא נשמע כל כך טוב..

אני מצרף בזאת סקריפט לשדרוג טבלאות ל-Clustered ColumnStore Index (מחולל את פקודות ה-TSQL הרלוונטיות):

  • טבלאות שיש להן כבר Clustered Index “רגיל” (RowStore) יכולות להשתדרג בשלב אחד תוך שימוש ב-DropExisting=On, שחוסך את הצורך לבטל את ה-Clustered Index הקיים (=לבנות את כל הטבלה מחדש) וליצור את ה-ColumnStore.
  • טבלאות שיש להן Clustered Index שהוא גם Primary Key – אין ברירה אלא לבטל את האינדקס ולהפוך אותן ל-Heap מכיוון שאינדקס ColumnStore אינו יכול להיות PK, ואז לבנות את ה-Clusterd ColumnStore Index, ובנפרד PK כאינדקס רגיל.
  • כאשר לטבלה ולאינדקסים יש אותה פונקציית Partition (מפורטשים באופן זהה) – אין בעייה להפוך ל-Clusterd ColumnStore Index, אבל אם לא (מפורטשים בצורה שונה או חלק כן  וחלק לא) – לא ניתן. במקרה זה תופיע הודעת שגיאה מתאימה וצריך להחליט מה לעשות ומה לשנות.
  • כרגע יש עדיין מספר data types שאינם נתמכים, כמו – (Varchar(Max וכיו”ב. לפיכך בשלב הזה הסקריפט שלי יחזיר הודעת שגיאה מתאימה, אבל בעתיד –נקווה שהמגבלה תוסר (בגרסת vNext וב-Azure כנראה שכבר ניתן). במקרה כזה יש לשנות את הקוד בהתאם..
  • הסקריפט רץ ב-On Premise וב-Azure.
  • שימו לב שבטבלה יכול להיות ColumnStore Index אחד לכל היותר: או שהוא Clustered ואזי כל האינדקסים הםRowStore, או שהוא אינו Clustered ואז הטבלה עצמה ושאר האינדקסים אינם ColumnStore.

חשוב מאוד: לכאורה אפשר לקחת את כל הפקודות שהוא מחולל ולהריץ בהצלחה בדטבייס, אלא שיש להפעיל שיקול דעת: כן לשנות או לא לשנות.. כלל האצבע הוא שמעל מיליון שורות כדאי לשנות, אבל כללי אצבע אינם מחליפים שיקול דעת: יתכן שזה לא כדאי, יתכן שהטבלה משמשת גם לשאילתות OLTP ואז יש לחשוב על איסטרטגיה משולבת של RowStore & ColumnStore והטבלה לא בהכרח תהיה Clusterd ColumnStore Index, יש לבדוק את קצב ויעילות העדכונים וכו’.
בקיצור- לא לומר “אבל בבלוג של גרי היה סקריפט ולא קראנו את האותיות הקטנות”..

With T As

(Select    Object_Schema_Name(id) SchemaName,

        Object_Name(id) ObjectName,

        I.is_primary_key IsPKCl,

        I.name IndexName,

        SI.rowcnt,

        SI.id object_id,

        I.index_id,

        IsNull(OA1.is_primary_key,0) is_primary_key,

        OA1.name PKName,

        OA2.IsPartitionAligned,

        OA3.TypeNotSupported,

        OA3.ColumnNotSupported,

        OA3.max_length,

        I.type_desc TableType,

        OA3.is_computed ComputedColumn

From    sys.tables T (NoLock)

Cross Apply (Select Top 1 *

            From    sys.sysindexes SI (NoLock)

            Where    SI.id=T.object_id

            Order By indid) SI

Cross Apply (Select Top 1 *

            From    sys.indexes I (NoLock)

            Where    T.object_id=I.object_id

            Order By I.index_id) I

Outer Apply (Select    Top 1 I2.is_primary_key,

                    I2.name

            From    sys.indexes I2 (NoLock)

            Where    I2.object_id=I.object_id

                    And I2.is_primary_key=1) OA1

Outer Apply (Select    Case When Min(PS.name)=Max(PS.name) And Count(I2.data_space_id)=Count(PS.data_space_id) Then 1

                        When IsNull(Min(PS.name),Max(PS.name)) Is Null Then 1

                        Else 0

                        End IsPartitionAligned

                    From    sys.indexes I2 (NoLock)

                    Left Join sys.partition_schemes PS (NoLock)

                            On I2.data_space_id=PS.data_space_id

                    Where    I2.object_id=I.object_id) OA2

Outer Apply (Select    Top 1 T1.name TypeNotSupported,

                    C.name ColumnNotSupported,

                    C.max_length,

                    C.is_computed

            From    sys.types T1 (NoLock)

            Inner Join sys.types T2 (NoLock)

                    On (T1.is_user_defined=0 And T1.system_type_id=T2.system_type_id And T1.user_type_id=T2.user_type_id)

                        Or (T1.is_user_defined=1 And T1.system_type_id=T2.user_type_id)

            Inner Join sys.columns C (NoLock)

                    On T1.user_type_id=C.user_type_id

            Where    C.object_id=SI.id

                    And (T2.name Not In ('sysname','datetimeoffset','datetime2','datetime','smalldatetime','date','time','float','real','decimal','numeric','money','smallmoney','bigint','int','smallint','tinyint','bit','nchar','char','binary','uniqueidentifier','varchar','nvarchar','varbinary')

                        Or (T2.name In ('varchar','nvarchar','varbinary') And C.max_length=-1)

                        Or C.is_computed=1)) OA3

Where    0=0

        --And SI.rowcnt>900000

        And I.type_desc In ('CLUSTERED','HEAP'))

--Select * From T

Select    Concat(SchemaName,'.',ObjectName) TableName,

        rowcnt [Rows],

        Case When IsPartitionAligned= 0 Then Concat('RaisError(''Table ',SchemaName,'.',ObjectName,' is not partition aligned: not all the indexes belong to the same partition function.'',16,1);')

            When ComputedColumn=1 Then Concat('RaisError(''Column ',ColumnNotSupported,' in table ',SchemaName,'.',ObjectName,' is computed.'',16,1);')

            When TypeNotSupported Is Not Null Then Concat('RaisError(''Type ',TypeNotSupported,Iif(max_length=-1,'(max)',''),' in table ',SchemaName,'.',ObjectName,' (column ',ColumnNotSupported,') is not supported.'',16,1);')

            When is_primary_key=0 Then

            Concat('Create    Clustered ColumnStore Index [',Iif(TableType='CLUSTERED',IndexName,Concat('Idx_',SchemaName,'_',ObjectName)),']',Char(13),

            '        On [',SchemaName,'].[',ObjectName,']',Char(13),

            '        With (',Iif(TableType='CLUSTERED','Drop_Existing=On, ',''),'Data_Compression=Columnstore);')

            Else

            Concat('Alter Table [',SchemaName,'].[',ObjectName,'] Drop Constraint [',PKName,'];',Char(13),

            'Create    Clustered ColumnStore Index [',IndexName,'_CCS]',Char(13),

            '        On [',SchemaName,'].[',ObjectName,']',Char(13),

            '        With (',Iif(IsPKCl=1,'','Drop_Existing=On, '),'Data_Compression=Columnstore);',Char(13),

            'Alter   Table [',SchemaName,'].[',ObjectName,']',Char(13),

            '        Add Constraint [',IndexName,'] Primary Key(',OA.PK,')',Char(13),

            '        With(Data_Compression=Page);')

            End SQL

From    T

Outer Apply    (Select    Stuff((Select    ','+C.name As [text()]

                        From    sys.index_columns IC (NoLock)

                        Inner Join sys.columns C (NoLock)

                                On IC.object_id=C.object_id

                                And IC.column_id=C.column_id

                        Where    IC.object_id=T.object_id

                                And IC.index_id=T.index_id

                        Order By IC.key_ordinal

                        For XML Path('')),1,1,'') PK) OA

Order By rowcnt Desc;

תודה מיוחדת מגיעה ל-Niko Neugebauer שהבלוג שלו הוא מקור בלתי נדלה למידע, והוא עצמו ענה על כמה שאלות שלי..

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

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