none
שאילתה מגניבה על אינדקסים. RRS feed

  • דיון כללי

  • רציתי שאילתה אחת מרוכזת שתכיל מידע על אינדקסים.
    רציתי לראות למשל את ה-included columns, האם האינדקס מכווץ, מה תדירות השימוש בו, האם האינדקס הוא partitioned index ואם כן, על איזה scheme הוא נמצא ועוד.

    לקחתי את הבסיס לשאילתה מהמקורות הבאים:
    http://www.karaszi.com/sqlserver/util_sp_indexinfo.asp
    http://www.sqlskills.com/blogs/kimberly/post/sp_helpindex-rewrites.aspx
    אלה מקורות ממש מצוינים להבנה על תישאול מידע על אינדקסים

    אני משתמשת ב-www.ssmstoolspack.com, ויש שם קיצורי דרך ממש נוחים, אז רציתי להכניס קיצור דרך שייתן לי את השאילתה האימתנית הרצוייה על האינדקסים.

    אחרי הרבה ניסיונות, הגעתי לשאילתה הבא (ניסיתי לפרוס אותה כדי שיהיה קל לקרוא, אבל זה לא עבד :),
    ניסיתי אפילו לפרסס אותה עם האתר http://www.dpriver.com/pp/sqlformat.htm,
    אבל עדיין השאילתה נראית מסובכת).

    מבחינתי יש בה מספר באגים ואולי גם פונקציונליות חסרה -
    1.  אני מתעלמת מ-Filtering definition, סתם כי איננו משתמשים בזה, אז לא טרחתי, אבל זה יכול להיות תרגיל נחמד לנסות להכניס את המידע הזה.
    2.  אני משתמשת כרגע עם DISTINCT, כי אחרת הוא נותן לי מידע על כל אחד מה-Partitioned indexes ואני מרגישה שזה מיותר.
    3.  אני מניחה שאם האינדקס לא מחולק למחיצות, הוא יושב על PRIMARY FileGroup.

    זו לא שאלה בעצם, זה רק עניין של זמן וניסיונות עד שאתקן את הבאגים 2,3, אז הפוסט הזה הוא יותר תרומה לקהילה, כי אני מוצאת את השאילתה הזו כמאוד מאוד שימושית בעבודה.
    תהנו.

     

    רוני.

      SELECT DISTINCT OBJS.[object_id], 
                    OBJS.[name]           AS object_name, 
                    IDX.index_id, 
                    CASE IDX.is_disabled 
                      WHEN 1 THEN 'YES' 
                      ELSE 'NO' 
                    END             AS is_disabled, 
                    CASE WHEN is_unique = 1 THEN CASE WHEN IDX.is_primary_key=1 THEN 'PK UNIQUE ' WHEN IDX.is_primary_key=0 THEN 'UNIQUE ' ELSE '' END ELSE '' END + IDX.type_desc 
                     AS Index_Description, 
                    Isnull(IDX.[name], '---')       AS index_name, 
                    Isnull(PS.name, 'PRIMARY')       AS Located_On, 
                    Isnull(Index_Columns.index_columns_key, '---')  AS index_columns_key, 
                    Isnull(Index_Columns.index_columns_include, '---') AS index_columns_include, 
                    ddius.user_seeks, 
                    ddius.last_user_seek, 
                    ddius.user_scans, 
                    ddius.last_user_scan, 
                    ddius.user_lookups, 
                    ddius.last_user_lookup, 
                    ddius.user_updates, 
                    ddius.last_user_update, 
                    partitions.rows, 
                    partitions.sizemb, 
                    p.data_compression_desc 
    FROM   sys.objects OBJS 
           INNER JOIN sys.schemas SCH 
                   ON OBJS.schema_id = SCH.schema_id 
           INNER JOIN sys.indexes IDX 
                   ON OBJS.[object_id] = IDX.[object_id] 
           INNER JOIN sys.partitions P 
                   ON IDX.object_id = P.object_id 
                      AND IDX.index_id = P.index_id 
           LEFT JOIN sys.partition_schemes PS 
                  ON IDX.data_space_id = PS.data_space_id 
           INNER JOIN (SELECT [object_id], index_id, Sum(row_count) AS Rows , CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), 
           Sum( in_row_reserved_page_count + lob_reserved_page_count + row_overflow_reserved_page_count)) / CONVERT(NUMERIC(19, 3), 128)) AS SizeMB 
           FROM   sys.dm_db_partition_stats STATS 
           GROUP  BY [object_id], index_id) AS partitions 
                   ON IDX.[object_id] = partitions.[object_id] AND IDX.index_id = partitions.index_id 
           LEFT OUTER JOIN sys.dm_db_index_usage_stats ddius 
                   ON IDX.object_id = ddius.object_id AND IDX.index_id = ddius.index_id AND ddius.database_id = Db_id() 
           CROSS apply (SELECT LEFT(index_columns_key, Len(index_columns_key) - 1) AS index_columns_key, 
           LEFT(index_columns_include, Len(index_columns_include) - 1) AS index_columns_include
         FROM   (SELECT (SELECT COLS.[name] + ',' + ' ' 
                                        FROM   sys.index_columns IXCOLS 
                                               INNER JOIN sys.columns COLS 
                                                       ON IXCOLS.column_id = 
                                                          COLS.column_id 
                                                          AND IXCOLS.[object_id] = 
                                                              COLS.[object_id] 
                                        WHERE  IXCOLS.is_included_column = 0 
                                               AND IDX.[object_id] = 
                                                   IXCOLS.[object_id] 
                                               AND IDX.index_id = IXCOLS.index_id 
                                        ORDER  BY key_ordinal 
                                        FOR xml path('')) AS index_columns_key, 
                                       (SELECT COLS.[name] + ',' + ' ' 
                                        FROM   sys.index_columns IXCOLS 
                                               INNER JOIN sys.columns COLS 
                                                       ON IXCOLS.column_id = 
                                                          COLS.column_id 
                                                          AND IXCOLS.[object_id] = 
                                                              COLS.[object_id] 
                                        WHERE  IXCOLS.is_included_column = 1 
                                               AND IDX.[object_id] = 
                                                   IXCOLS.[object_id] 
                                               AND IDX.index_id = IXCOLS.index_id 
                                        ORDER  BY index_column_id 
                                        FOR xml path('')) AS index_columns_include) 
                               AS 
                               Index_Columns) AS Index_Columns 
    WHERE  OBJS.[name] = 'TableName' 

    יום שלישי 13 נובמבר 2012 16:24

כל התגובות

  • הי רוני,

    תודה רבה על התרומה לקהילה. זה אכן מאוד שימושי.

    הנה התרומה הקטנה שלי:

    1. כדאי להוסיף תנאי על שם הסכימה. כמו שהסקריפט כתוב כרגע, אם יש שתי טבלאות בעלות אותו שם בסכימות שונות, את תקבלי את כל האינדקסים בשתי הטבלאות.
    2. השאילתה עושה Join עם "sys.schemas", למרות שנראה לי שאת בכלל לא משתמשת ב-View הזה בשום מקום.
    3. ה-Filter Definition של האינדקס נמצא ב-"sys.indexes". מכיוון שאת כבר משתמשת ב-View הזה, כל מה שנשאר זה להציג את העמודה הרלוונטית.
    4. אם אינדקס מסוים מחולק ל-Partitions, אז כל Partition יכול להיות מכווץ בצורה שונה או בכלל לא. השאילתה שלך מחזירה את העמודה "data_compression_desc" מתוך "sys.partitions" ועושה DISTINCT על הכל. במצב כזה, תקבלי מספר שורות עבור אותו אינדקס עם אותם נתונים בדיוק, מלבד העמודה "data_compression_desc". ליתר דיוק, תקבלי שורה אחת עבור כל סוג כיווץ שקיים (לפחות פעם אחת) עבור האינדקס. במקום זה, נראה לי שכדאי להכניס את "sys.partitions" לתוך ה-Derived Table (זה שקראת לו "partitions"). שם את יכולה לעשות Join בינו לבין "sys.dm_db_partition_stats", ושם כבר יש לך GROUP BY. לשם הייתי מוסיף ארבע עמודות אגרגטיביות - כל עמודה מחזירה את מספר ה-Partitions שמשתמשים בסוג כיווץ מסוים ("NONE", "ROW", "PAGE", "COLUMNSTORE"). כך גם תוכלי להיפטר מה-DISTINCT.
    5. במקום להניח שהאינדקס נמצא ב-"PRIMARY", את יכולה פשוט לעשות עוד Join קטן ל-"sys.data_spaces" (לפי העמודה "data_space_id" ב-"sys.indexes").

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום חמישי 15 נובמבר 2012 13:25
    מנחה דיון