none
rebuild index RRS feed

  • שאלה

  • אני לא מצליח להבין את פעולת rebuild לאינדקסים.

    קיים אצלי db advanturework 2012 

    כשאני מריץ את השאילתה הבאה אני רואה כמה אינדקסים שמעל 30 אחוד ואף הרבה מעבר לכך:

    declare @ThresholdInPercents decimal(5,2)
    set @ThresholdInPercents = 30;
    create table #FragmentedIndexes (DatabaseName sysname, TableName sysname, IndexName sysname, FragmentationInPercents decimal(5,2))
    insert into #FragmentedIndexes
    exec sp_MSforeachdb
    '
    use [?]
    SELECT    ''?''
    		,object_name(a.object_id)
            ,case a.index_id when 0 then ''HEAP'' else (select name from [?].sys.indexes where object_id = a.object_id and index_id = a.index_id) end as IndName
    		,a.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (db_id(''?''), NULL, NULL, NULL, NULL) AS a
    '
    select DatabaseName, TableName, IndexName, FragmentationInPercents
    from #FragmentedIndexes 
    where FragmentationInPercents>@ThresholdInPercents order by FragmentationInPercents desc
    drop table #FragmentedIndexes
    

      מה ההיגיון שלאחר פעולת rebuild האינדקס חוזר לאותה רמת פרגמנטציה?

    יכול להיות שאני מפספס משהו תוכלו להאיר את עיני

    לפי מה שאני מבין פרגמנטציה נוצרת עקב הכנסה לא רציפה של נתונים האם זה נכון?

    יום ראשון 08 פברואר 2015 13:22

תשובות

  • אהלן tetitu, שלום נתן

    הרשו לי בקצרה לעשות סדר קצת בדברים.

    1. tetitu אם תשים לב שואל השאל הציג בדיוק מה הוא עושה על מנת לבנות את האינדקס מחדש, אבל בהסבר מקוצר ואולי לא ברור :-) 
    אני לוחץ על העץ ועושה לטבלה rebuild all

    הוא אומנם לא מריץ שאילתה שהוא כתב ידנית, אבל הוא מריץ שאילתה טובה ונכונה דרך ממשק ה GUI

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

    3. לגבי השאלה עצמה (ואני לא כותב לגבי "הבעיה" מכיוון שאין כאן שום בעיה) בעקרון כל מה שצריך זה להבין מה זה אינדקס וכיצד נשמרים הנתונים של האינדקס מאחורי הקלעים, וכמובן את המושג Out-of-Order pages. למעשה זו שאלה מאוד נפוצה בפורומים, שכל איש מקצוע אמור להכיר :-)

    ההסבר: (1 )אם אינדקס הוא מאוד קטן (אם יש פחות מ 8 דפים, זה בדרך כלל יקרה) אז הוא עושה שימוש ב EXTENT שיתופי. לכן אתה יכול לנסות לבנות את האינדקס כמה פעמים שאתה רוצה וזה בדרך כלל לא יעזור. למעשה אין סיבה בדרך כלל לבנות אינדקסים מחדש אם יש פחות מכמה מאות דפים (נניח סתם ככלל אצבע 1000 דפים).

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

    (2) ישנה סיבה נוספת, שאינה קשורה למקרה הנוכחי כפי הנראה (אינו מתאים למסד הנתונים הזה אלא אם ביצעת שינויים), אשר בה אינדקס אינו מבצע פרגמנטציה טוב, וזה כאשר אין מקום בקובץ לבצע את הפעולה. אחד הפתרונות למצב זה הוא הוספת הרמז SORT_IN_TEMPDB יכול לעזור (ברירת המחדל של מצב OFF ולכן הפעולה מבצועת על הקובץ של מסד הנתונים הנוכחי).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]




    • נערך על-ידי pituachMVP, Moderator יום רביעי 11 פברואר 2015 05:50
    • סומן כתשובה על-ידי Netanelf יום חמישי 12 פברואר 2015 07:46
    יום שלישי 10 פברואר 2015 14:01
    מנחה דיון

כל התגובות

  • היי,

    תוכל בבקשה לצרף את הפקודה של ה- rebuild  עצמה שאתה מריץ?


    Best Regards,

    Itai Binyamin, MVP

    www.ItaiBinyamin.Blogspot.com


    • נערך על-ידי Itai Binyamin יום שני 09 פברואר 2015 08:44 /
    יום שני 09 פברואר 2015 08:43
  • אין פקודה מיוחדת שאני מריץ 
    אני לוחץ על העץ ועושה לטבלה rebuild all 
    אך אחוז הפרגמנטציה לא משתנה
    יום שני 09 פברואר 2015 12:37
  • כפי שאיתי מציין הפקודה ששלחת רק מציגה את ה דיפרגמנטציה ולא מבצעת אותו בפועל.

    הפקודה ל DEFRAG של האינדקסים ולא רק סידור שלהם תחת אותם PAGES שהם נמצאים.

     DBCC INDEXDEFRAG

    מכיוון שאני לא DBA אבל יש לי כמה שרתי SQL שאני מתחזק בכל זאת ולצרכי תחזוקה כללים.

    אני משתמש ב

    http://sqlfool.com/2011/06/index-defrag-script-v4-1/

    בד"כ זה עושה את העבודה.

    יום שלישי 10 פברואר 2015 10:58
  • אהלן tetitu, שלום נתן

    הרשו לי בקצרה לעשות סדר קצת בדברים.

    1. tetitu אם תשים לב שואל השאל הציג בדיוק מה הוא עושה על מנת לבנות את האינדקס מחדש, אבל בהסבר מקוצר ואולי לא ברור :-) 
    אני לוחץ על העץ ועושה לטבלה rebuild all

    הוא אומנם לא מריץ שאילתה שהוא כתב ידנית, אבל הוא מריץ שאילתה טובה ונכונה דרך ממשק ה GUI

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

    3. לגבי השאלה עצמה (ואני לא כותב לגבי "הבעיה" מכיוון שאין כאן שום בעיה) בעקרון כל מה שצריך זה להבין מה זה אינדקס וכיצד נשמרים הנתונים של האינדקס מאחורי הקלעים, וכמובן את המושג Out-of-Order pages. למעשה זו שאלה מאוד נפוצה בפורומים, שכל איש מקצוע אמור להכיר :-)

    ההסבר: (1 )אם אינדקס הוא מאוד קטן (אם יש פחות מ 8 דפים, זה בדרך כלל יקרה) אז הוא עושה שימוש ב EXTENT שיתופי. לכן אתה יכול לנסות לבנות את האינדקס כמה פעמים שאתה רוצה וזה בדרך כלל לא יעזור. למעשה אין סיבה בדרך כלל לבנות אינדקסים מחדש אם יש פחות מכמה מאות דפים (נניח סתם ככלל אצבע 1000 דפים).

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

    (2) ישנה סיבה נוספת, שאינה קשורה למקרה הנוכחי כפי הנראה (אינו מתאים למסד הנתונים הזה אלא אם ביצעת שינויים), אשר בה אינדקס אינו מבצע פרגמנטציה טוב, וזה כאשר אין מקום בקובץ לבצע את הפעולה. אחד הפתרונות למצב זה הוא הוספת הרמז SORT_IN_TEMPDB יכול לעזור (ברירת המחדל של מצב OFF ולכן הפעולה מבצועת על הקובץ של מסד הנתונים הנוכחי).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]




    • נערך על-ידי pituachMVP, Moderator יום רביעי 11 פברואר 2015 05:50
    • סומן כתשובה על-ידי Netanelf יום חמישי 12 פברואר 2015 07:46
    יום שלישי 10 פברואר 2015 14:01
    מנחה דיון
  • הנה שחזור מהיר של הנושא למי שמעוניין לחקור אותו

    אני מבצע את הפעולות הבאות:

    1. יוצר מסד נתונים חדש

    2. יותר טבלה חדשה + יוצר אינדקס ראשי

    3. מכניס לטבלה 10 רשומות בשאילתה אחת

    4. בודק פרגמנטציה: הכל טוב 0

    5. מכניס עוד 100*10 רשומות ב 100 פעולות נפרדות. הנתונים נכנסים בצורה לא מסודרת במכוון ויוצרים "בלאגן" באינדקס הכולל פעולות כמו SPLIT

    6. בודקים פרגמנטציה: כמצופה הערך גדול מאוד

    7. מבצעים בניית אינדקס מחדש: הערך אולי יורד מעט אבל לא בהרבה ונשאר מאוד גבוהה.

    8. תחזרו על פעולה 7 ותגבלו שלפעמים הערך גדל במקום לרדת :-)

    use master
    GO
    
    create database TestDB
    GO
    
    use TestDB
    GO
    
    CREATE TABLE dbo.TestTbl (
    	TestCol1 int NOT NULL,
    	TestCol2 nchar(10) NULL,
    	TestCol3 nvarchar(50) NULL
    )
    GO
    CREATE CLUSTERED INDEX IX_TestTbl_TestCol1 
        ON dbo.TestTbl (TestCol1); 
    GO
    
    insert TestTbl (TestCol1,TestCol2, TestCol3)
    values 
    	(1,'a','a'),(2,'b','b'),(3,'c','c'),(11,'aa','aa'),(12,'bb','bb'),(13,'cc','cc'),(22,'aaa','aaa'),(23,'bbb','bbb'),
    	(24,'asdf','fsg'),(5,'fj','jgk')
    GO
    
    DBCC SHOWCONTIG ('TestTbl')
    GO
    -- Resilt: no "Extent Switches" and no "Logical Scan Fragmentation"
    -- Extent Switches..............................: 0
    -- Logical Scan Fragmentation ..................: 0.00%
    -- Extent Scan Fragmentation ...................: 0.00%
    
    insert TestTbl (TestCol1,TestCol2, TestCol3)
    values 
    	(1,'a','a'),(2,'b','b'),(3,'c','c'),(11,'aa','aa'),(12,'bb','bb'),(13,'cc','cc'),(22,'aaa','aaa'),(23,'bbb','bbb'),
    	(24,'asdf','fsg'),(5,'fj','jgk')
    GO 100
    
    DBCC SHOWCONTIG ('TestTbl')
    GO
    -- Resilt: we have "Extent Switches" and "Logical Scan Fragmentation"
    -- Extent Switches..............................: 4
    -- Logical Scan Fragmentation ..................: 87.50%
    -- Extent Scan Fragmentation ...................: 33.33%
    
    
    ALTER INDEX [IX_TestTbl_TestCol1] 
    ON [dbo].[TestTbl] 
    REORGANIZE
    GO
    
    DBCC SHOWCONTIG ('TestTbl')
    GO
    -- NO HELP :-)
    -- the index is too small!
    
    -- Extent Switches..............................: 4
    -- Logical Scan Fragmentation ..................: 83.33%
    -- Extent Scan Fragmentation ...................: 33.33% ALTER INDEX [IX_TestTbl_TestCol1] ON [dbo].[TestTbl] REBUILD PARTITION = ALL GO DBCC SHOWCONTIG ('TestTbl') GO -- NO FULL HELP :-) -- the index is too small!

    -- Extent Switches..............................: 1
    -- Logical Scan Fragmentation ..................: 33.33%
    -- Extent Scan Fragmentation ...................: 0.00%

    -- Now try to rebuild the index several time more :-) -- It might even reduce first time and then it might even enlarge the value on another try :-)

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


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]



    יום שלישי 10 פברואר 2015 14:23
    מנחה דיון
  • תודה רבה על התשובה המפורטת רונן (וגם לשאר האנשים שעזרו).

    אם כך לפי מה שאני מבין הפעולה rebuild index לא תעזור לי מכיוון שהנתונים שלי על אותו Exctent משותף מכיוון שכמות הנתונים שלי יחסית קטנה.
    אם הטבלה שלי היתה ענקית וכמות המידע היתה מפוצלת לכמה extents הייתי רואה פחות פרגמנטציה לאחר פעולת rebuild index
    האם נכון מה שכתבתי עד לכאן?
    ובנוסף יש לי שאלה האם פרגמנטציה נוצרת עקב הכנסה לא רציפה של נתוניםן?
     
    יום רביעי 11 פברואר 2015 15:53
  • בעקרון החלק הראשון מספיק קרוב, מבחינת ההתנהגות ולשם הבנה בסיסית.
    * אין כאן עניין יחסי, אלא ערכים מדוייקים. כאשר אני מגיע למסד נתונים וחוקר אותו אני יכול לדעת תיאורטית מראש כיצד הדברים יתנהגו, זו מתמטיקה קלה (ארוכה ומורכבת אבל קלה, שכוללת פעולות חיבור וכפל פשוטות) וכמובן הבנה של כיצד הדברים פועלים מאחורי הקלעים. למשל אנחנו יודעים מה אורך הטור לפי הסוג שלו, אנחנו יודעים מה נשמר באינדקס בכל מצב, אנחנו יודעים מה גודל ה PAGE וכמה מתוך זה נשמר לכותרים (HEADERS) וכו'...

    נכון, כאשר הטבלה מספיק גדולה אז לא רק שהיית רואה ערך קטן יותר אחרי בניית האנדקס מחדש, אלא שהוא היה אמור להתקרב לאפס. בדוגמה שאני שמתי למשל אם תוסיף 1000 הרצות של הסקריפט שמכניס 10 נתונים, ואחר כך תבצע בניית האינדקס מחדש אז אתה אמור לקבל ערך של בסביבות 0.05%.

    >> ובנוסף יש לי שאלה האם פרגמנטציה נוצרת עקב הכנסה לא רציפה של נתוניםן?

    התשובה הקצרה היא לא רק.
    זה לא הסבר לכמה שניות בפורום אלא לפחות שעתיים הרצאה מאוד מקוצרת.

    תחילה צריך להבין כיצד נשמרים הנתונים במסד הנתונים וכיצד פועל האינדקס מבחנת שמירת הנתונים במצבים שונים. בעקרון השלב הבא הוא להבין את המושג המרכזי שמססתר מאחורי הערךך שאתה בודק אותו Out-of-Order pages, בלי להבין את המושג אי אפשר לדון ברצינות לעומק במה גורר מצב זה. יש פעולות שונות שיכולות לגרור מצב כזה אבל אני חושב שהנפוצה ביותר היא SPLIT על עמודים.

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

    * אל תשכח לסגור את השרשור בסיום על ידי סימון התשובות, וכמובן אתה יכול להצביע על הודעות מועלות גם כן.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    יום רביעי 11 פברואר 2015 21:10
    מנחה דיון