הבלוג של גרי רשף

12/05/2010

Index Fragmentation

Filed under: Uncategorized — תגיות: , , , , , — גרי רשף @ 14:31

כשיוצרים Clustered Index מוגדר לו Fill Factor שקובע איזה אחוז מהדפים (Pages) יהיה מלא.
הסיבה לכך היא שכשמוסיפים ערכים חדשים באמצע האינדקס ואין שם מקום פנוי (מה שקורה כשה-Fill Factor הוא 100%)- המערכת מוסיפה את הערך בדף חדש, ובדף המלא יוצרת הפניה אליו:
יהיה מאוד לא יעיל "להזיז" את כל האינדקס מנקודת ההוספה ואילך מקום אחד קדימה, ולכן אין ברירה אלא לפצל אותו ובכך ליצור פרגמנטציה.
כמובן שכשיש מקום פנוי- מכניסים לשם את הערך החדש.

ניצור טבלה חדשה על בסיס טבלה קיימת ב-AdventureWorks,
נכניס לתוכה 200 רשומות אקראיות מתוך 290 קיימות,
וניצור Cluster Index על שדה המפתח עם FillFactor=100%:

Use tempdb;

Go

 

If Object_Id('dbo.Employee') Is Not Null Drop TABLE dbo.Employee

Go

 

Select    *

Into    dbo.Employee

From    (Select    Top 200 Cast(EmployeeID As Int) EmployeeID,

              NationalIDNumber,

              ContactID,

              LoginID,

              ManagerID,

              Title,

              BirthDate,

              MaritalStatus,

              Gender,

              HireDate,

              SalariedFlag,

              VacationHours,

              SickLeaveHours,

              CurrentFlag,

              rowguid,

              ModifiedDate

        From    Adventureworks.HumanResources.Employee

        Order By NewID()) T;

Go

 

CREATE Unique CLUSTERED INDEX Ix_Employee ON dbo.Employee(EmployeeID) WITH (FILLFACTOR = 100)

Go

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

מסיבה לא ברורה- לעיתים ה-Rebuild מאפס את הפרגמנטציה רק לאחר 2-3 נסיונות, ולכן "אעטוף" אותו בלולאה מתאימה:

While (SELECT avg_fragmentation_in_percent FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.Employee'),NULL, NULL , Null))>0

    Alter Index Ix_Employee On tempdb.dbo.Employee Rebuild With (Online=On);

Go 

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

SELECT    avg_fragmentation_in_percent,

        fragment_count,

        avg_fragment_size_in_pages,

        page_count

FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.Employee'),NULL, NULL , Null)

Go

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

Declare    @N Int;

Set        @N=1

While    @N<=20

    Begin

    Insert Into dbo.Employee

    Select Top 1 * 

    From    AdventureWorks.HumanResources.Employee

    Where    EmployeeID Not In (Select EmployeeID From tempdb.dbo.Employee)

    Order By NewID();

    Set        @N=@N+1;

    End;

Go

כעת נריץ שוב את השליפה לבדיקת מצב הפרגמנטציה, ואלו התוצאות עבור Fill Factor = 100%:

page_count avg_fragment_size_in_pages fragment_count avg_fragmentation_in_percent  
5 2.5 2 0 לפני ההוספה
9 1 9 89 לאחר ההוספה

ניתן לראות בעמודת avg_fragmentation_in_percent שהפרגמנטציה גדלה מ-0% לכמעט 90%, וכל זאת בעקבות הגדלת מספר הרשומות ב-10% (מ-200 ל-220).

מספר הפרגמנטים גדל מ-2 ל-9,

גודלו של כל אחד ירד מ-2.5 ל-1 (יש הרבה פחות רשומות לכל פרגמנט),

ומספר הדפים (Pages, נפח האיחסון שהאינדקס תופס) כמעט הוכפל מ-5 ל-9.

נחזור כעת על כל התהליך, אבל עם Fill Factor = 80% (בהנחה שכמות הנתונים צפויה לגדול בכ-10% – נהיה מעט "נדיבים" במקום הפנוי שנשאיר):

If Object_Id('dbo.Employee') Is Not Null Drop TABLE dbo.Employee

Go

 

Select    *

Into    dbo.Employee

From    (Select    Top 200 Cast(EmployeeID As Int) EmployeeID,

              NationalIDNumber,

              ContactID,

              LoginID,

              ManagerID,

              Title,

              BirthDate,

              MaritalStatus,

              Gender,

              HireDate,

              SalariedFlag,

              VacationHours,

              SickLeaveHours,

              CurrentFlag,

              rowguid,

              ModifiedDate

        From    Adventureworks.HumanResources.Employee

        Order By NewID()) T;

Go

 

CREATE Unique CLUSTERED INDEX Ix_Employee ON dbo.Employee(EmployeeID) WITH (FILLFACTOR = 80)

Go

 

While (SELECT avg_fragmentation_in_percent FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.Employee'),NULL, NULL , Null))>0

    Alter Index Ix_Employee On tempdb.dbo.Employee Rebuild With (Online=On);

Go 

 

SELECT    avg_fragmentation_in_percent,

        fragment_count,

        avg_fragment_size_in_pages,

        page_count

FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.Employee'),NULL, NULL , Null)

Go

 

Declare    @N Int;

Set        @N=1

While    @N<=20

    Begin

    Insert Into dbo.Employee

    Select Top 1 * 

    From    AdventureWorks.HumanResources.Employee

    Where    EmployeeID Not In (Select EmployeeID From tempdb.dbo.Employee)

    Order By NewID();

    Set        @N=@N+1;

    End;

Go

 

SELECT    avg_fragmentation_in_percent,

        fragment_count,

        avg_fragment_size_in_pages,

        page_count

FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.Employee'),NULL, NULL , Null)

Go

והנה התוצאות:

page_count avg_fragment_size_in_pages fragment_count avg_fragmentation_in_percent  
6 3 2 0 לפני ההוספה
6 3 2 0 אחרי ההוספה

נפח האחסון (Page Count) גדול יותר מההתחלתי במצב של Fill Factor = 100%,

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

דוגמה נוספת:

ניצור שליפה של המספרים 1-100,000,

נשלוף משם אקראית 10,000 מספרים לתוך טבלה,

ניצור Clustered Index ונבצע לו Rebuild,

נבדוק את מצב הפרגמנטציה בנקודה זו,

נוסיף 100 מספרים אקראיים חדשים לטבלה,

ונבדוק שוב את הפרגמנטציה;

כל זה- פעם ב- Fill Factor = 100% ופעם ב- Fill Factor = 95%.

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

Use tempdb;

Go

 

IF OBJECT_ID('dbo.GetNums') IS NOT NULL DROP FUNCTION dbo.GetNums;

GO

CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE

AS

RETURN

  WITH

  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),

  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)

  SELECT n FROM Nums WHERE n <= @n;

GO

וכעת נבצע את כל הבדיקה עבור Fill Factor = 100% במכה אחת (הריצה עלולה להימשך במשך מספר דקות):

If Object_Id('dbo.T_Misparim') Is Not Null Drop TABLE dbo.T_Misparim

Go

 

Select    *

Into    dbo.T_Misparim

From    (Select    Top 10000 n Mispar

        From    dbo.GetNums(100000)

        Order By NewID()) T;

Go

 

CREATE Unique CLUSTERED INDEX Ix_Misparim ON dbo.T_Misparim(Mispar) WITH (FILLFACTOR = 100) --Change To 95

Go

 

While (SELECT avg_fragmentation_in_percent FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.T_Misparim'),NULL, NULL , Null))>0

    Alter Index Ix_Misparim On tempdb.dbo.T_Misparim Rebuild With (Online=On);

Go 

 

SELECT    avg_fragmentation_in_percent,

        fragment_count,

        avg_fragment_size_in_pages,

        page_count

FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.T_Misparim'),NULL, NULL , Null)

Go

 

Declare    @N Int;

Set        @N=1

While    @N<=100

    Begin

    Insert Into dbo.T_Misparim

    Select Top 1 * 

    From    dbo.GetNums(100000)

    Where    n Not In (Select Mispar From tempdb.dbo.T_Misparim)

    Order By NewID();

    Set        @N=@N+1;

    End;

Go

 

SELECT    avg_fragmentation_in_percent,

        fragment_count,

        avg_fragment_size_in_pages,

        page_count

FROM    sys.dm_db_index_physical_stats(db_id('tempdb'),OBJECT_ID('tempdb.dbo.T_Misparim'),NULL, NULL , Null)

Go

נשמור את התוצאות ונריץ שוב- הפעם עם Fill Factor = 95% (הדגשתי באדום את מיקום הפרמטר שיש לשנות בקוד),

וקיבלנו את התוצאות הבאות:

page_count avg_fragment_size_in_pages fragment_count avg_fragmentation_in_percent  
22 7.33 3 0 100% לפני ההוספה
42 1.02 41 95 100% אחרי ההוספה
23 7.67 3 0 95%  לפני ההוספה
23 7.67 3 0 95%  אחרי ההוספה

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

הערות:

פעולת Rebuild לאינדקס אמורה לאפס את הפרגמנטציה, אבל בטבלאות אמיתיות בסביבת Production זו פעולה כבדה שרצוי להימנע ממנה.

מודעות פרסומת

2 תגובות »

  1. הכל טוב ונכון ומאמר חשוב למי שרוצה להבין את משמעות הfill factor.

    אך תמיד מתעוררת השאלה מהו המספר האידיאלי לשים בfill factor.
    כיצד אני אדע מה כדאי לי?
    כי למערכת מסויימת יכול להיות ש80% זה מה שנדרש,
    ובמערכת אחרת מספיק אם אשים 95% .
    כיצד אדע לאזן ולהחליט על מספר המיטבי ?

    בנתיים בשיטה שלי – אם יש Insert אשר עלול לפצל את הדפים, אזי אני שם 90%, ואם לא אז 100% (כי במילא תמיד הוא ילך לסוף הpage).

    תגובה של איתן — 12/05/2010 @ 16:06

    • ברור שמדובר כאן בכללי אצבע כאלה או אחרים..
      בהנחה שבין פעולת תחזוקה אחת לשניה מספר הרשומות גדל ב-10% וה-ID החדשים מפוזרים – מן הסתם דרוש Fill Factor של 90% ומטה (כלומר- 10% לפחות מקום פנוי).
      בנוסף כדאי לזכור ששינוי של המפתח (ערך השדה עליו יש Clustered Index) כמוהו כהכנסת ערך חדש.

      תגובה של גרי רשף — 13/05/2010 @ 12:48


RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: