סטטיסטיקה: עדכון וקבלת החלטות

13/04/2016

הפוסט שלהלן ממחיש את השימוש בסטטיסטיקה על ידי המערכת לבחירת ה-Execution Plan, ואת השפעתה של סטטיסטיקה לא מעודכנת על שימוש זה ואיכות הבחירה.

בעזרת טבלת sys.messages (טבלת מערכת בת כ-250,000 שורות) אבנה טבלה עם נתונים היסטוריים, כלומר- אכניס את תוכן טבלת המערכת לטבלה שאבנה, בתוספת תאריך:

If Object_ID('T_Messages','U') Is Not Null Drop Table T_Messages;

 

Create    Table T_Messages

        ([Date] DateTime Not Null,

        message_id Int Not Null,

        language_id Int Not Null,

        severity Int,

        is_event_logged Bit,

        text NVarchar(Max),

        Constraint PK_T_Messages Primary Key Clustered([Date],language_id,message_id));

 

Truncate Table T_Messages;

Declare    @D DateTime='20150101';

While    @D<'20160401'

        Begin

        Insert

        Into    T_Messages

        Select    @D, 

                *

        From    sys.messages;

        Set        @D=DateAdd(Month,1,@D);

        End;

כמה שורות יש בטבלה?

Select    Count(*)

From    T_Messages;

זו אינה הדרך היעילה לבדוק זאת, אבל אני נוהג כאן כך כדי לגרום למערכת לבצע Full Scan:

image

ניתן לראות שהסטטיסטיקה מעודכנת, מכיוון שההערכה מראש שווה למספר השורות בדיעבד (לאחר ביצוע ה-Scan).

כעת נתחיל להכניס לטבלה שורות חדשות הנכונות לתאריך 01/04/2016 (החודש הנוכחי בו פוסט זה נכתב), ומכיוון שהסטטיסטיקה מתעדכנת רק אם 20% מהשורות התעדכנו, היא תישאר לא מעודכנת ולא תדע להערכיך נכון כמה שורות יש נכון לתאריך הנ”ל:

Declare    @RC Int=1,

        @D DateTime='20160401';

While    @RC>0

        Begin

        Insert

        Into    T_Messages

        Select    Top 1000 @D, 

                *

        From    sys.messages T1

        Where    Not Exists (Select    *

                            From    T_Messages T2

                            Where    @D=T2.[Date]

                                    And T1.language_id=T2.language_id

                                    And T1.message_id=T2.message_id);

        Set        @RC=@@RowCount;

        End;

תוך כדי ריצה (זה אמור להימשך כמה וכמה דקות, כמובן – נכון למועד בו נכתב פוסט זה..) נבצע Join בין נתוני 01/04/2016 לנתוני 01/01/2016. האינטואיציה אומרת שהמערכת תבצע Merge Join: תיקח את שני הסטים, תסרוק את הערכים היחודיים במקביל, ובכל פעם שתמצא זוג זהה תיספור אותו.

Select    Count(*)

From    T_Messages T1

Inner Join T_Messages T2

        On T1.language_id=T2.language_id

        And T1.message_id=T2.message_id

Where    T1.[Date]='20160401'

        And T2.[Date]='20160101';

image

מדוע היא מבצעת Nested Loops? מדוע לכל ערך מ-01/04/2016 היא מחפשת את הערך המתאים בסט של 01/01/2016? חצי תשובה ניתן לקבל מהפער בין ה-Estimated Number of Rows לבין ה-Actual Number of Rows.
כדי לקבל תשובה מלאה ננסה להריץ גם את השאילתה הנ”ל, וגם שאילתה דומה ש”נכריח” אותה בעזרת hint מתאים להשתמש ב-Merge Join:

Set Statistics IO On;

Select    Count(*)

From    T_Messages T1

Inner Join T_Messages T2

        On T1.language_id=T2.language_id

        And T1.message_id=T2.message_id

Where    T1.[Date]='20160401'

        And T2.[Date]='20160101';

 

Select    Count(*)

From    T_Messages T1

Inner Merge Join T_Messages T2

        On T1.language_id=T2.language_id

        And T1.message_id=T2.message_id

Where    T1.[Date]='20160401'

        And T2.[Date]='20160101'

Set Statistics IO Off;

image

לפי ה-Execution Plan ה-Merge מהווה 100% מהעלות, ולכן אינו יעיל בצורה קיצונית. האמנם?

לפי ה-Statistics IO בדיוק להיפך:
image

מספר ה-logical reads ב-nested loops גדול פי ~15 ממספרם ב-Mege Join.
ובכן: בשל הסטטיסטיקה השגויה המערכת בטוחה שבסט של 01/04/2016 אין שורות בכלל (ובאופן אופטימי הוא מעריך שיש שורה אחת), ואז עדיף לו לבצע לשורה הבודדת הזו (או אפילו ל-10 השורות הבודדות אם הוא קצת טעה) Index Seek לכל אחת בתאריך 01/01/2016, מאשר לבצע Scan מלא על כל 250,000 השורות של תאריך זה. זו משמעות התמחור ב-Execution Plan. ה-Statistics IO נותן את התמונה בפועל ללא קשר לסטטיסטיקה, ובנקודת הזמן בה השאילתה הורצה, בה יש 71,000 שורות ב-01/04/2016, המערכת נאלצת לבצע 71,000 פעולות Seek ולא 1 כפי שחישבה מראש, וכתוצאה מכך כמות ה-IO גדולה בהרבה מאשר ב-Merge Join. כל זה כי הסטטיסטיקה אינה מעודכנת, וההערכה של מספר השורות ב-01/04/2016 שגויה.

מה יקרה אם הסטטיסטיקה תתעדכן? נעדכן וננסה:

 

Update Statistics T_Messages;

 

Select    Count(*)

From    T_Messages T1

Inner Join T_Messages T2

        On T1.language_id=T2.language_id

        And T1.message_id=T2.message_id

Where    T1.[Date]='20160401'

        And T2.[Date]='20160101';

image

אופס: המערכת עדכנה את הסטטיסטיקה (ה-estimated אינו זהה ל-actual אך הם באותו סדר גודל כעת), ובחרה לבצע Hash Match.
הערה: יתכן ובמערכות אחרות המערכת כן תבחר ב-Merge Join. מדובר במקרה זה בהחלטה שהתקבלה “על הקשקש”..
בכל מקרה- ננסה שוב להשוות ל-Merge Join:

image

מבחינת ה-Statistics IO אין הבדלים משמעותיים, וגם ב-Execution Plan היתרון של ה-Hash Match על ה-Merge מזערי, אך מה שבולט מעבר להבדל בתוכניות, הוא החיצים הצהובים הקטנטנים בתוכנית העליונה, שמלמדים על השימוש בפרלליזם. כלומר- במקרה של Hash Match ניתן לפצל את המשימה בין המעבדים השונים של השרת, ולבצע את המשימה במקביל; מה שלא ניתן לעשות במקרה של Merge Join; כלומר- החלוקה של Hash Match לקבוצות כך שכל אחת תטופל על ידי מעבד אחר מבטיחה ששתי שורות מתאימות יהיו בקבוצות דומות והן לא יפספסו זו את זו, ואילו החלוקה של Merge כן עלולה לגרום לכך.
בכל מקרה, עם נבחר ב-Maxdop 1 שיגרום לכך שרק מעבד אחד יטפל בשאילתה, המערכת תבחר ב-Merge שיהיה יעילה מ-Hash Join  ללא פרלליזם:

Select    Count(*)

From    T_Messages T1

Inner Join T_Messages T2

        On T1.language_id=T2.language_id

        And T1.message_id=T2.message_id

Where    T1.[Date]='20160401'

        And T2.[Date]='20160101'

Option (Maxdop 1);

 

Select    Count(*)

From    T_Messages T1

Inner Hash Join T_Messages T2

        On T1.language_id=T2.language_id

        And T1.message_id=T2.message_id

Where    T1.[Date]='20160401'

        And T2.[Date]='20160101'

Option (Maxdop 1);

image

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *