What are you waiting for?
עדי כהן
מומחה SQL Server בעל כ-15 שנות ניסיון מגוון בתחום. ר"צ DBA בחברה למסחר פיננסי באינטרנט מטעם נאיה טכנולוגיות.
אחד ה-views הכי יעילים באיתור בעיות ביצועים הוא sys.dm_os_waiting_tasks. כדי להבין את הView, צריך קצת להכיר את צורת העבודה של השרת. בצורה מאד מופשטת, אפשר לחלק את תהליכים שרצים בשרת לשלושה סוגים:
הסוג הראשון משתמש במעבד ורץ. הסטטוס של תהליך מסוג זה הוא running.
סוג שני מוכן לרוץ ומחכה לזמן מעבד. הסטאטוס של סוג תהליך מסוג זה הוא runnable.
הסוג השלישי אינו רץ, ומחכה למשאב מסוים (זיכרון, נעילה, latch, פעולת I/O כד'), בכדי להתחיל לרוץ. הסטטוס של תהליך המחכה למשאב מסוים הוא suspended. כאשר התהליך מקבל את המשאב שהוא חיכה לו, הסטטוס שלו הופך להיות runnable.
לאחר שתהליך בסטטוס running סיים להשתמש בזמן המעבד שהוקצע לו, הוא מפסיק לרוץ ומפנה את המעבד לתהליך אחר. התהליך הבא שיתחיל לרוץ יהיה אחד מהתהליכים, שהסטטוס שלהם הוא runnable. התהליכים עם סטטוס suspended ימשיכו לחכות עד שהמשאב, שלו הם ממתינים יתפנה.
Sys.dm_os_waiting_tasks, מראה לנו את כל התהליכים, שממתינים למשאב מסויים. בנוסף הוא מראה לנו את סוג המשאב שהתהליך ממתין לו, ובמקרה שזה רלוונטי, את המשאב המדויק שממתינים לו.
בשלב זה דוגמא יכולה להסביר את ה-DMV בצורה הטובה ביותר (ב-DMV יש כמובן יותר עמודות, ואני ממליץ לקרוא על כל העמודות. בשלב זה העמודות הללו יספיקו).
הרצה של השאילתה הבאה:
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id, resource_description
FROM sys.dm_os_waiting_tasks
החזירה את הנתונים הבאים:
נתחיל בהסבר על העמודות:
Session_id – מספר התהליך, שכרגע נמצא בסטטוס suspended.
Wait_duration_ms – הזמן (באלפיות שניה) שהתהליך מחכה למשאב.
Wait_type – סוג ההמתנה.
Blocking_session_id – מראה את מס' התהליך שמבצע blocking לתהליך הנוכחי (במידה ויש לנו בעיית blocking).
Resource_description – המשאב, שהתהליך מחכה לו. שימו לב, שיש מקרים, שבהם זה לא רלוונטי, ולכן לא תמיד יופיע נתון בעמודה.
אם נבדוק את התהליכים שמופיעים בטבלה שלנו, אפשר לראות שתהליך מס' 54 מחכה מעל 15 שניות לנעילת intent shared, אבל הוא לא יכול לקבל את הנעילה בגלל תהליך מס' 53. בעמודה resource_description אפשר לראות על מה הנעילה אמורה להיות (מס' partition ומס' אובייקט). תהליך מס' 55 מחכה 3 אלפיות שנייה ל-letch על דף בדיסק. לפי העמודה resource_description אפשר לראות לאיזה page הוא מחכה – מדובר על page מס' 345 בקובץ מס' 1 של בסיס הנתונים מס' 5. Pageiolatch קורה כאשר השרת מנסה לסנכרן בין page מהדיסק ל-page בזיכרון. מאחר שמדובר ב-shared latch, כנראה שמדובר על העברה של page מהדיסק לזכרון. הרשומה האחרונה מראה שתהליך מס' 57 מחכה 37 אלפיות שנייה לlatch על דף בזיכרון. הפעם מדובר על page מס' 16 בקובץ מס' 1 בבסיס הנתונים מס' 2 (שהוא כידוע tempdb). בנוסף ניתן לראות שמדובר על exclusive latch, מה שאומר שתהליך מס' 57 מנסה לשנות את הדף בזיכרון.
כפי שניתן להבין, באמצעות sys.dm_os_waiting_tasks ניתן לפתור בעיות נפוצות (לדוגמא blocking). אבל לדעתי הכח הגדול של הDMV הזה הוא בעיקר במקרים פחות שגרתיים ויותר קשים לאבחון. בשלב הזה אני רוצה לשתף אתכם במספר מקרים, שהייתי מעורב בהם אצל לקוחות, בהם בעיות ביצועים נפתרו בעזרת האינפורמציה שקיבלנו מ-sys.sm_os_waiting_tasks.
במקרה הראשון הגעתי ללקוח שהתלונן על שאילתה ספציפית. היה מדובר בדו"ח שביצע עיבוד למליוני רשומות. ללקוחות החברה היה כלים להוצאת הדוחות באופן עצמאי. לחברה היו לקוחות רבים, ובזמנים מסוימים מספר לקוחות היו מוציאים דוחות במקביל. הבעיה הייתה שהיו מקרים בהם לקוחות החברה היו מחכים מס' דקות לקבלת תוצאות הדו"ח. למרבה הצער לא נמצא מכנה משותף לאותם מקרים. זה קרה ללקוחות בכל הגדלים, זה קרה בשעות שונות, והכי חשוב, לקוח יכל לקבל תוצאות במהירות ולאחר מכן לחכות מס' דקות לאותו דו"ח בדיוק. המחשבה הראשונה שלי היתה blocking, אבל אז נאמר לי שבסיס הנתונים נמצא ב-snapshot isolation level, ושהפרוצדורה שמפיקה את הדוח לא משנה נתונים. זה כמובן פסל את האפשרות שמדובר ב-blocking. באופן אישי לא היה לי שמץ של מושג מה יכולה להיות הסיבה, והחלטתי לכתוב פרוצדורה, שכל חצי דקה מכניסה לטבלה את כל הרשומות מ-sys.dm_os_waiting_tasks יחד עם עמודה נוספת שמתעדת את תאריך וזמן הרשומות. לאחר מכן בדקתי בטבלה למה חיכו התהליכים של שליפת הדוחות, וראיתי את הפרטים הבאים (הוצאתי מהפלט עמודות לא רלוונטיות):
ניתן לראות שהתהליכים חיכו ל-resource_semaphore. כל שאילתה צריכה לקבל הקצאת זיכרון ע"מ שהיא תרוץ. שימו לב שלא מדובר על הcache memory. השאילתה עצמה צריכה זיכרון בשביל האופרטורים שלה. ישנם אופרטורים (למשל sort) שיכולים לדרוש זיכרון רב. השרת מבצע הערכה לגבי כמות הזיכרון שהשאילתה צריכה. במידה ואפשר לתת לשאילתה את הזיכרון, השאילתה מתחילה לרוץ. במקרה שאין לשרת מספיק זיכרון פנוי, השאילתה ממתינה שזיכרון יתפנה. סוג המתנה לזיכרון נקרא resource_semaphore. לאחר שגילינו מה היתה הבעיה, אפשר היה לטפל בה. בלי לבדוק את סוג ההמתנה באמצעות sys.dm_os_waiting_tasks היה מאד קשה למצוא את הבעיה.
דוגמא נוספת למקרה שבו הצלחתי לפתור בעיית ביצועים רק באמצעות sys.dm_os_waiting_tasks ארעה אצל לקוח שהייתה לו פרוצדורה שעבדה עם טבלאות זמניות. הפרוצדורה הייתה מופעלת הרבה פעמים במקביל, כאשר לרוב היא רצה במשך 20 מילי שניות. אבל היו מקרים, שבהם הפרוצדורה רצה מעל 50 מילי שניות. הלקוח ידע, לפני שהוא פנה לנאיה, שהפרוצדורה רצה 50 מילי שניות ומעלה כאשר היא מופעלת מאות פעמים בצורה מקבילית ע"י שרתי האפליקציה שלו, אבל הוא לא הצליח לגלות את הסיבה לכך. גם כאן הפעלתי פרוצדורה שמתעדת במשך מס' שעות את הפלט של sys.dm_os_waiting_tasks. גם כאן הבעיה נפתרה, כאשר ראינו למה התהליכים מחכים:
אפשר לראות שכל התהליכים מחכים לlatch על דף זיכרון, ושכולם מחכים לאותו דף – דף מס' 1 בקובץ מס' 3 של הtempdb. מדובר בדף שמנגנון הקצאת הדפים לטבלאות משתמש בו. מאחר ולעיתים הפרוצדורה רצה מאות פעמים במקביל, והיא בונה טבלה זמנית ומאכלסת את הטבלה, נוצר לחץ על הדפים שמטפלים בהקצאת דפים לטבלאות. גם כאן, לאחר שהבנו מה הבעיה, יכלנו לטפל בה.
לסיכום: sys.dm_os_waiting_tasks נותן לנו תמונה מדויקת למה התהליכים על השרת שלנו מחכים. ה-view לא יעזור לנו במקרים בהם יש לנו שאילתה ספציפית עם query plan לא יעיל. אך לעומת זאת, במקרה שדברים מסוימים רצים לאט, ואין לנו מושג מה רץ לאט ולמה, נוכל לקבל את התשובה באמצעות ה-view. במקרים אלו נתקשה מאוד לקבל את התשובה ללא שימוש בשאילתה על sys.dm_os_waiting_tasks.
Comments