none
sys.dm_db_index_physical_stats RRS feed

  • שאלה

  • אני משתמש ב DMV sys.dm_db_index_physical_stats ב SQL Server 2005 על מנת לבדוק פרגמנטציה של טבלה מסויימת, בת כ 200 מליון רשומות . להפי מה שאני רואה יש פרגמנטציה של 89%.

    SELECT

    * FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'TableName'),1,NULL,N'DETAILED'

    )

    קודם כל רציתי לוודא שאני לא טועה ומסתכל במקום הנכון. בנוסף רציתי להיות בטוח שהמידע אמין מאחר והאינקס נבנה מחדש  (drop - create)יומיים לפני ולא הגיוני שהפרגמנטציה גבוהה כל כך. בתודה משאש על עזרתכם.

    אלון.

     

     

    יום חמישי 26 ינואר 2012 09:58

תשובות

  • היי אלון,

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

    "פרגמנטציה פנימית" - כל Page בכל רמה באינדקס יכול להכיל מספר מסויים של רשומות (תלוי בגודלן כמובן) אבל אין זה מחייב שתמיד המספר הזה ימלא
    את כל אותו הPage ממספר אפשריות. למשל הגדרת הFILLFACTOR שהגדרת מאפשר להכניס לאותו הPage עד 90 אחוז וזה בכדי לנסות למנוע קצת את הביצוע היקר של Page Splits.
    באופן כללי נעדיף תמיד שכמות המידע שישב בתוך הPage יהיה כמה שיותר גבוהה וזה בכדיי שהSQL יקרא פחות Pages בשעת הצורך.

    כמו שהצגת אז שליפה בכדי לראות את כמות הפרגמנטציה הפנימית -

    SELECT IX.name AS 'Name'
         , PS.index_level AS 'Level'
         , PS.page_count AS 'Pages'
         , PS.avg_page_space_used_in_percent AS 'Page Fullness (%)'
      FROM sys.dm_db_index_physical_stats( 
               DB_ID(), 
               OBJECT_ID('XX'), 
               DEFAULT, DEFAULT, 'DETAILED') PS
      JOIN sys.indexes IX
        ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id 
      WHERE IX.name = 'PK_XX';
    GO

    "פרגמנטציה חיצונית" - מדברת על הסדר של הPages באינדקס. בקצרה למה הכוונה עם הפאנצ' החשוב באמת ולמה זה פוגע בביצועים -
    אז ככה מדובר בעצם בקשר בין הסידור הלוגי של הPages לסידור הפיזי, הוא קיים בצורה של אחוזים והפעם הפוך מפרגמנטציה פנימית, ככל שהאחוז גבוהה יותר ככה הביצועיים יפגעו יותר.
    הביצועים יפגעו בעיקר שנרצה לשלוף בעזרת האינדקס מספר רשומות ממספר Pages למשל שליפה על Range מסויים, וכאשר הרשומות לא יהיו מסודרות באותו הExtent כPage אחר Page מה שהSQL SERVER יצטרך לבצע זה בעצם לא לקרוא רק Extent אחד או X לא משנה אלא הרבה יותר וזה ביחס לכמות הפרגמנטציה שקיימת על אותו האיזור שהשליפה מתמקדת בו.

    יותר קריאות = יותר IO = השליפה מתעכבת.

    שליפה שמציגה את כמות הפרגמנטציה החיצונית באינדקס -

    SELECT IX.name AS 'Name'
    , PS.index_level AS 'Level'
    , PS.page_count AS 'Pages'
    , PS.avg_fragmentation_in_percent AS 'External Fragmentation (%)'
    , PS.fragment_count AS 'Fragments'
    , PS.avg_fragment_size_in_pages AS 'Avg Fragment Size'
    FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('XX'),
    DEFAULT, DEFAULT, 'LIMITED') PS
    JOIN sys.indexes IX
    ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
    WHERE IX.name = 'PK_XX';
    GO

    אחריי כל ההקדמה הארוכה הזאת אבל לדעתי היא הייתה הכרחית אז לגבי המקרה הספציפי שלך, אז כן זה בהחלט יכול לקרות.
    הכל תלוי בקצב ההכנסות שלך ובסדר ההכנסה לגבי העמודות ID1, ID2, TIME  בדגש על ID1 כי הוא המוביל באינדקס.

    וכנראה שבוודאות סדר ההכנסה של העמודות אינו נמצא בסדר עולה מה שגורם לכך שיווצרו Page splits באופן ניקר ממש (כנראה גם כמות ההכנסות שלך מסיבית). או שסדר ההכנסה הוא באמת בסדר עולה עבור העמודות הנ"ל אבל! הייתה ביומיים הזאת הכנסה מאסיבית לטבלה הזאת למשל טעינת חומר סדירה/חד פעמית של חומרים שנכנסו לא בסדר התואם את האינדס וזה מה שהביא לפרגמנטציה הרצינית הזאת.

    ולכן נוצר מצב שאחרי יומיים האינדקס שלך כבר ניראה כמו סוסיטה ולא פרארי :).


    טיפים קטנים על הדרך...


    במיוחד שמדובר על הClustered index שמחזיר איתו בLeaf level גם את כל שאר העמודות, מחישוב קטן יוצר שיש באיזור ה90-110 רשומות בכל Page וכל רשומה היא באיזור
    ה60-80 בתים, ככה שזה ניראה לא כזה נורא אבל עדיין זה אוליי יהיה עדיף לשקול לשים את הPK כNON CLUSTERED ובכך להקטין את גודל כל רשומה באינדקס ל-INT+SMALLINT+DATETIE=14
    בתים, מה שיביא לכך שיהיו פי 4-6 יותר רשומות בכל Page והסיכוי לPage splits יקטן.

    לפני שעושים צעד כזה צריך להבין למה הוגדר מלחתחילה הClustered Index על 3 העמודות אם יש על הטבלה הנ"ל שליפות שהינן שליפות על Range כל העמודות ID1, ID2, TIME אז אוליי זה נכון והכרחי אבל מהנסיון שלי ברוב המקרים זה לא ככה בעבודה עם הPK...
    צריך לזכור להתחשב גם בJOINS שמבצעים עם הטבלה על המפתח ולראות האם הם מבצעים סריקה של האינדקס או לא.

    בלי קשר למה שדיברנו עד כה, הייתי ממליץ לך לשנות את אחת ההגדרות של האינדקס מ- PAD_INDEX = OFF ל- ON, שזה בעצם גורם להגדרה שלך של הFILLFACTOR להתממש גם ברמות הבייניים באינדקס, וזה משום ששים לב שגם ברמות הגבהות יותר באינדקס שלך ישנה פרגמנטציה חיצונית גבוהה. וזה יקטין אותה.

    דבר אחרון זה לנסות לשחק קצת יותר עם הFILLFACTOR כלומר לעלות להוריד אותו מ90 לאיזור ה80 במידה ותחליט להשאיר את האינדקס במצב הנוכחי, כי גם ככה הPages שלך לא מלאים והסדר
    הכנסה של הרשומות אינו בסדר עולה ככה שגם ככה הPages האלו יתמלאו אך עם פחות Page split שזואי פעולה יקרה מאוד, וגם הפרגמנטציה כנראה לא תפחת אבל היא תתעכב קצת יותר ותגיע לאחר זמן ארוך יותר.


    מקווה שעזרתי,
    חיים פישנר.



    • נערך על-ידי haim fishner יום רביעי 11 אפריל 2012 09:32 סידור התגובה
    • הוצע כתשובה על-ידי haim fishner יום רביעי 11 אפריל 2012 15:26
    • סומן כתשובה על-ידי Alon2012 יום ראשון 29 אפריל 2012 13:54
    יום רביעי 11 אפריל 2012 09:30

כל התגובות

  • הי,

    אתה מסתכל במקום הנכון, אפשר גם לראות שה- fragment_count גדול מאוד.

    אתה יכול לצרף את ההגדרה של ה- Index וסוגי השדות?

    במאמר המצורף תוכל למצוא הסבר על ה- DMV

    http://sqlserverpedia.com/wiki/DM_Objects_-_Sys.dm_db_index_physical_stats

    בהצלחה


    אסף שלם
    • הוצע כתשובה על-ידי pituachMVP, Editor יום רביעי 11 אפריל 2012 13:57
    יום חמישי 26 ינואר 2012 15:09
  • אסף תודה! להלן מידע על האינדקס:

    ALTER

    TABLE [dbo].[XX] ADD CONSTRAINT [PK_XX] PRIMARY KEY CLUSTERED

    [ID1] ASC, -- INT[ID2] ASC,

    -- SMALLINT

    [Time]

    ASC -- DATETIME

    )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TABLES]


    אלון.
    יום חמישי 26 ינואר 2012 15:20
  • היי אלון,

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

    "פרגמנטציה פנימית" - כל Page בכל רמה באינדקס יכול להכיל מספר מסויים של רשומות (תלוי בגודלן כמובן) אבל אין זה מחייב שתמיד המספר הזה ימלא
    את כל אותו הPage ממספר אפשריות. למשל הגדרת הFILLFACTOR שהגדרת מאפשר להכניס לאותו הPage עד 90 אחוז וזה בכדי לנסות למנוע קצת את הביצוע היקר של Page Splits.
    באופן כללי נעדיף תמיד שכמות המידע שישב בתוך הPage יהיה כמה שיותר גבוהה וזה בכדיי שהSQL יקרא פחות Pages בשעת הצורך.

    כמו שהצגת אז שליפה בכדי לראות את כמות הפרגמנטציה הפנימית -

    SELECT IX.name AS 'Name'
         , PS.index_level AS 'Level'
         , PS.page_count AS 'Pages'
         , PS.avg_page_space_used_in_percent AS 'Page Fullness (%)'
      FROM sys.dm_db_index_physical_stats( 
               DB_ID(), 
               OBJECT_ID('XX'), 
               DEFAULT, DEFAULT, 'DETAILED') PS
      JOIN sys.indexes IX
        ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id 
      WHERE IX.name = 'PK_XX';
    GO

    "פרגמנטציה חיצונית" - מדברת על הסדר של הPages באינדקס. בקצרה למה הכוונה עם הפאנצ' החשוב באמת ולמה זה פוגע בביצועים -
    אז ככה מדובר בעצם בקשר בין הסידור הלוגי של הPages לסידור הפיזי, הוא קיים בצורה של אחוזים והפעם הפוך מפרגמנטציה פנימית, ככל שהאחוז גבוהה יותר ככה הביצועיים יפגעו יותר.
    הביצועים יפגעו בעיקר שנרצה לשלוף בעזרת האינדקס מספר רשומות ממספר Pages למשל שליפה על Range מסויים, וכאשר הרשומות לא יהיו מסודרות באותו הExtent כPage אחר Page מה שהSQL SERVER יצטרך לבצע זה בעצם לא לקרוא רק Extent אחד או X לא משנה אלא הרבה יותר וזה ביחס לכמות הפרגמנטציה שקיימת על אותו האיזור שהשליפה מתמקדת בו.

    יותר קריאות = יותר IO = השליפה מתעכבת.

    שליפה שמציגה את כמות הפרגמנטציה החיצונית באינדקס -

    SELECT IX.name AS 'Name'
    , PS.index_level AS 'Level'
    , PS.page_count AS 'Pages'
    , PS.avg_fragmentation_in_percent AS 'External Fragmentation (%)'
    , PS.fragment_count AS 'Fragments'
    , PS.avg_fragment_size_in_pages AS 'Avg Fragment Size'
    FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('XX'),
    DEFAULT, DEFAULT, 'LIMITED') PS
    JOIN sys.indexes IX
    ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
    WHERE IX.name = 'PK_XX';
    GO

    אחריי כל ההקדמה הארוכה הזאת אבל לדעתי היא הייתה הכרחית אז לגבי המקרה הספציפי שלך, אז כן זה בהחלט יכול לקרות.
    הכל תלוי בקצב ההכנסות שלך ובסדר ההכנסה לגבי העמודות ID1, ID2, TIME  בדגש על ID1 כי הוא המוביל באינדקס.

    וכנראה שבוודאות סדר ההכנסה של העמודות אינו נמצא בסדר עולה מה שגורם לכך שיווצרו Page splits באופן ניקר ממש (כנראה גם כמות ההכנסות שלך מסיבית). או שסדר ההכנסה הוא באמת בסדר עולה עבור העמודות הנ"ל אבל! הייתה ביומיים הזאת הכנסה מאסיבית לטבלה הזאת למשל טעינת חומר סדירה/חד פעמית של חומרים שנכנסו לא בסדר התואם את האינדס וזה מה שהביא לפרגמנטציה הרצינית הזאת.

    ולכן נוצר מצב שאחרי יומיים האינדקס שלך כבר ניראה כמו סוסיטה ולא פרארי :).


    טיפים קטנים על הדרך...


    במיוחד שמדובר על הClustered index שמחזיר איתו בLeaf level גם את כל שאר העמודות, מחישוב קטן יוצר שיש באיזור ה90-110 רשומות בכל Page וכל רשומה היא באיזור
    ה60-80 בתים, ככה שזה ניראה לא כזה נורא אבל עדיין זה אוליי יהיה עדיף לשקול לשים את הPK כNON CLUSTERED ובכך להקטין את גודל כל רשומה באינדקס ל-INT+SMALLINT+DATETIE=14
    בתים, מה שיביא לכך שיהיו פי 4-6 יותר רשומות בכל Page והסיכוי לPage splits יקטן.

    לפני שעושים צעד כזה צריך להבין למה הוגדר מלחתחילה הClustered Index על 3 העמודות אם יש על הטבלה הנ"ל שליפות שהינן שליפות על Range כל העמודות ID1, ID2, TIME אז אוליי זה נכון והכרחי אבל מהנסיון שלי ברוב המקרים זה לא ככה בעבודה עם הPK...
    צריך לזכור להתחשב גם בJOINS שמבצעים עם הטבלה על המפתח ולראות האם הם מבצעים סריקה של האינדקס או לא.

    בלי קשר למה שדיברנו עד כה, הייתי ממליץ לך לשנות את אחת ההגדרות של האינדקס מ- PAD_INDEX = OFF ל- ON, שזה בעצם גורם להגדרה שלך של הFILLFACTOR להתממש גם ברמות הבייניים באינדקס, וזה משום ששים לב שגם ברמות הגבהות יותר באינדקס שלך ישנה פרגמנטציה חיצונית גבוהה. וזה יקטין אותה.

    דבר אחרון זה לנסות לשחק קצת יותר עם הFILLFACTOR כלומר לעלות להוריד אותו מ90 לאיזור ה80 במידה ותחליט להשאיר את האינדקס במצב הנוכחי, כי גם ככה הPages שלך לא מלאים והסדר
    הכנסה של הרשומות אינו בסדר עולה ככה שגם ככה הPages האלו יתמלאו אך עם פחות Page split שזואי פעולה יקרה מאוד, וגם הפרגמנטציה כנראה לא תפחת אבל היא תתעכב קצת יותר ותגיע לאחר זמן ארוך יותר.


    מקווה שעזרתי,
    חיים פישנר.



    • נערך על-ידי haim fishner יום רביעי 11 אפריל 2012 09:32 סידור התגובה
    • הוצע כתשובה על-ידי haim fishner יום רביעי 11 אפריל 2012 15:26
    • סומן כתשובה על-ידי Alon2012 יום ראשון 29 אפריל 2012 13:54
    יום רביעי 11 אפריל 2012 09:30
  • הסבר יפה מאוד חיים
    יום רביעי 11 אפריל 2012 13:11
    מנחה דיון