sys.dm_db_index_usage_stats: האם יש שימוש לאינדקסים

14/08/2015

הפוסט הקודם שלי עסק בפקודות Insert לטבלה מאונדקסת, והראה את המחיר שלהם: הרווח ב-Select ידוע, אך יש לזכור שאנחנו משלמים על כך בכל פקודת Insert/Update/Delete, ויש לבחון שאיננו מחזיקים אינדקסים מיותרים שעולים לנו בתחזוקתם, אך אינני משתמשים בהם ומפיקים מהם תועלת.
המקור למידע לגבי השימוש באינדקסים נמצא בטבלת המערכת המצויינת בכותרת. צריך כמובן לברר איזו טבלה עומדת מאחורי כל object_id, איזה אינדקס מאחורי כל index_id, מה המבנה של האינדקס וכו’; וכדי לעשות “לונג סטורי שורט”, להלן השליפה עם כל מה שצריך:

Declare @TableName Varchar(200)='dbo.MyTbl';

With Idx As

(Select    I.object_id, 

        I.index_id,

        User_Seeks,

        Format(Cast(User_Seeks As Float)/NullIf(Sum(User_Seeks) Over(Partition By S.OBJECT_ID),0),'0%') P_User_Seeks,

        User_Scans,

        Format(Cast(User_Scans As Float)/NullIf(Sum(User_Scans) Over(Partition By S.OBJECT_ID),0),'0%') P_User_Scans,

        User_Lookups,

        Format(Cast(User_Lookups As Float)/NullIf(Sum(User_Lookups) Over(Partition By S.OBJECT_ID),0),'0%') P_User_Lookups, 

        User_Updates

From     sys.dm_db_index_usage_stats S 

Inner Join sys.indexes I 

        ON I.object_id=S.object_id

        AND I.index_id=S.index_id

Inner Join sys.objects O

        On I.object_id=O.object_id

Where    S.database_id=db_id()

        And Object_ID(@TableName)=O.object_id),

T As

(Select T.object_id,

        I.index_id,

        Case When I.is_primary_key=0 Then

        Case When IC.key_ordinal=1 Then 

                'Create '+Case When I.is_unique=1 Then ' Unique ' Else '' End+' '+I.type_desc Collate Database_Default+' Index ['+I.name+'] On ['+Schema_Name(T.schema_id)+'].['+T.name+'] (' Else '' End

            Else

                Case When IC.key_ordinal=1 Then 'Alter Table ['+Schema_Name(T.schema_id)+'].['+T.name+'] Add Constraint ['+I.name+'] Primary Key '+I.type_desc Collate Database_Default+'(' Else '' End

            End [Command],

        '['+C.name+'] '+Case When IC.is_descending_key=0 Then 'Asc' Else 'Desc' End [Col],

        '['+C.name+']' [Coli],

        '['+Schema_Name(T.schema_id)+'].['+T.name+']' TableName,

        I.name IndexName,

        IC.is_included_column,

        IC.key_ordinal

From    sys.indexes I

Inner Join sys.index_columns IC

        On I.object_id=IC.object_id

        And I.index_id=IC.index_id

Inner Join sys.columns C

        On IC.object_id=C.object_id

        And IC.column_id=C.column_id

Inner Join sys.tables T

        On C.object_id=T.object_id

Where   I.index_id<>0),

Cmd As

(Select  Max(T0.object_id) object_id,

        T0.TableName,

        Max(T0.index_id) index_id,

        T0.IndexName,

        Max(Command) [Create],

        Stuff((Select ','+T1.Col As [text()]

                From T  T1

                Where   is_included_column=0

                        And T1.TableName=T0.TableName

                        And T1.IndexName=T0.IndexName

                Order By T1.key_ordinal

                For XML Path('')),1,1,'')+')' [Columns],

        ' Include ('+Stuff((Select ','+T1.Coli As [text()]

                From T  T1

                Where   is_included_column=1

                        And T1.TableName=T0.TableName

                        And T1.IndexName=T0.IndexName

                Order By T1.key_ordinal

                For XML Path('')),1,1,'')+')' [Include]

From    T T0

Group By T0.TableName,

        T0.IndexName)

Select    Cmd.TableName,

        Cmd.IndexName,

        Idx.*,

        Cmd.[Create],

        Cmd.[Columns],

        Cmd.[Include]

From    Cmd

Inner Join Idx

        On Cmd.object_id=Idx.object_id

        And Cmd.index_id=Idx.index_id

Order By User_Seeks Desc;

להלן דוגמה לפלט:

image

אפשר לראות את 11 האינדקסים על הטבלה בסדר יורד (פחות או יותר) מבחינת השימוש.
לגבי האינדקסים שבתחתית כדאי להרהר אם יש בהם צורך.
סימנתי באדם את ה-Lookups על הטבלה (Clustered Index & Primary Key), כ-18 מיליון;
וה-Seeks על האינדקסים האחרים, כ-19 מיליון:
לא במקרה המספרים הללו דומים, שכן בפעולות Seek רבות יש להשלים את העמודות החסרות באינדקס בעזרת Lookup לטבלה, וזה פוגע דרסטית בביצועים: במקרה הטוב – מתבצע Lookup יקר, ובמקרה הפחות טוב מתבצע Scan יקר עוד יותר על הטבלה כולה.
המספרים הם מהאיתחול האחרון של ה-Service, או מיצירת האינדקס (האחרון מהשניים).
שלוש העמודות הימניות מהוות ביחד את פקודת ה-Create של האינדקס.

שימוש מועיל!

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

כתיבת תגובה

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