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

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

תחום ה-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 שהבלוג שלו הוא מקור בלתי נדלה למידע, והוא עצמו ענה על כמה שאלות שלי..


כתיבת תגובה

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

one × four =