Indexes and Performance

J Steven Jones
sjones@swynk.com

מבוא

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

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

מה קורה במרוצת הזמן?

שרת ה-SQL מכיל כלי עדכון סטטיסטים, מייעל שאילתות המסוגל ללמוד להיות יעיל בהתאם לשאילתות שלך, וכ"ו. נשאלת השאלה מדוע עלינו לבצע תחזוקה ? את התשובה לכך נסביר באמצעות תאור של המתרחש בשרת לאורך זמן.

כאשר אתה בונה אינדקס לטבלה, שרת ה-SQL  מחלק את הנתונים לדפים ולחבורות (extents). בשרת SQL מגרסה 7 ומעלה,  חבורות אלו יכולות להיות משותפות למספר אובייקטים (בגרסא 6.5 ומטה חבורות יכלו להכיל אובייקט יחיד). נניח שיצרת טבלה בה גודל השורות הוא רבע מגודל העמוד. אם ברשותך 20 שורות, אז כתוצאה מכך תקבל כחמישה עמודים גרועים של נתונים.

האם הנתונים שלך מאוחסנים בחמישה דפים?

רק אם ה-FILLFACTOR הוא 100%. ה- FILLFACTOR קובע כמה אחוזים מתוך העמוד שלך ימולאו. הבה נניח FILLFACTOR של 50%. אז יהיו ברשותך כעשרה עמודים של נתונים בטבלה. דבר זה מסתבך במהירות, הבה נבחן זאת.

אם נניח שנרחיב את הדוגמא הנ"ל למשך זמן מה, ייתכן והטבלה תדרוש 100 עמודים של נתונים. עמודים אלו ידרשו (לכל הפחות) 7 חבורות אם האובייקט לא משתף חבורות. כל עמוד בתחום החבורות מקושר לעמוד אחר באמצעות מחוון (pointer). העמוד הבא בשרשרת לא בהכרח יהיה באותה החבורה. לכן כאשר אנו קוראים את העמודים, ייתכן ונצטרך להחליף לחבורה אחרת. 

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

אלו הם שלושה עמודים כאשר העמוד ה- n מצביע לעמוד n+1, אז לעמוד n+2 וכן הלאה. כדי לקרוא את עמודים אלו אנו קוראים חבורה מס' 1, לאחר מכן עוברים לחבורה מס' 2, ולאחר מכן מחליפים חזרה ל- 1. החלפות אלו לא מהוות בהכרח זמן I/O אבל הם נסכמות ומורידות את הביצועים. הם לא בהכרח יהוו בעיה משמעותית בשרת המקומי שלך, או אפילו שרת עמוס קלות, אולם אפליקציית רשת בעלת מאות או אלפי משתמשים עלולה לסבול מנזק מצטבר של סריקות רבות על גבי הטבלה.

למה נוצר מצב שהטבלה נראית כך?

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

נשאלות השאלות:  מדוע SQL Server לא בונה מחדש את האינדקסים ? איך אנו מסירים את הפיצולים הללו ?  המשך לקרוא ...

הרצת  DBCC SHOWCONTIG

בגרסאות קודמות של SQL Server, היה מוטל עליך ראשית לקבל את ה-ID של האובייקט ע"י שימוש בפקודה :

select object_id('<object name>')

עבור טבלת המשתמש הרצתי :

select object_id('user')

אני מניח שמישהו התלונן על כך כיוון שב - SQL 2000 ביכולתך להשתמש בשם האובייקט באמצעות dbcc showcontig באופן הבא :

dbcc showcontig (user)

פעולה זו תפיק את הסטטיסטיקה הבאה על האינדקסים :

DBCC SHOWCONTIG scanning 'User' table... Table:'User' (962102468); index ID: 1, database ID: 7 TABLE level scan performed. -Pages Scanned................................: 899 -Extents Scanned..............................: 121 -Extent Switches..............................: 897 -Avg. Pages per Extent........................: 7.4 -Scan Density [Best Count:Actual Count].......: 12.58% [113:898] -Logical Scan Fragmentation ..................: 99.89% -Extent Scan Fragmentation ...................: 80.99% -Avg. Bytes Free per Page.....................: 2606.5 -Avg. Page Density (full).....................: 67.80%

הבה נפענח את הפלט הנ"ל :

Pages Scanned –  מספר הדפים שנסרקו באינדקס.

Extents scanned –  מספר החבורות שנסרקו. בדוגמה הנ"ל יש לנו 121 חבורות ו- 968 דפים. כיוון שהאינדקס הוא רק 899 דפים, שי לנו מספר חבורות משותפות. דבר זה אינו בהכרח דבר רע, אך זה ממחיש את הרעיון שיש לנו פיצול קל.

Extent Switches – מספר הפעמים שהסריקה כפתה מתג מחבורה אחת לאחרת.

Average Pages/Extent – נותן את החישוב של דפים\חבורות .

Scan Density –  המספר הראשון (113 בדוגמא שלנו) הוא המספר האידיאלי של שינויי חבורות אם הכל היה מקושר ברשימה רציפה. המספר השני (898 בדוגמא שלנו) הוא מספר החבורות שהועברו שהוא 1 יותר מאשר מספר המתגים (לפי ההגדרה). זוהי נקודת התבוננות נוספת על פיצול.

Logical Scan Fragmentation –  "אני עדיין איני בטוח מה זה אומר. לא קיבלתי הסבר הולם לכך בשום מקום וכאן אני מביא את האינטרפרטציה שלי לנושא". מציין את אחוז הדפים באינדקס שמכילים מחוון לעמוד הבא ששונה מהמחוון לעמוד הבא שנמצא בקובץ בנתונים.

Extent Scan Fragmentation –  מציין את אחוז הדפים באם העמוד הבא באינדקס לא ממוקם פיסית בסמוך לעמוד הנוכחי.

Avg. Bytes Free per Page –  מציין את מספר הבתים החופשיים לעמוד (ממוצע).

Avg. Page Density -  פועל יוצא של הפרמטר הקודם.)דוגמא שלנו 2606.5 / 8096 עם עיגול כלפי מעלה).

אז מה בעצם כל זה אומר ?

ובכן, לי זה אומר שאני צריך לאחות (defragment )  את הטבלה.

איחוי אינדקסים 

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

ב- SQL Server 2000, נוספה אופציית DBCC הקרויה INDEXDEFRAG. תהליך זה משפר את הביצועים בכך שסדר הפיסי יתאם לסדר הלוגי ובכך יפחת משמעותית זמן ה- I/Oהדרוש לשרת כדי לסרוק את האינדקס.

סיכום 

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