none
כדאות השקעה בהתאמת השאילתות ליעילות ב query-plan-cache

    שאלה

  • קראתי רבות על כללים שיש למלא כדי שמערכת query-plan-cache תהיה יעילה, כלומר שמספר ההתאמות יהיה גדול. אני מניח שלמערכת קיימת כפי שיש לנו על שרת הווב תהיה זו השקעה גדולה להתאים את השאילתות (רובם שאילתות כתובות ב SQL Data Source , כלומר מחרוזות כתובות בתוך C# ,אשר נשלח לשרת כשאילתות פרמטריות. אמנם נעשה שימוש נרחב ב * select במקום פירוט שדות, וגם פניה לאובייקטים ללא קידומה הסכימה וכדומה, דברים הפוגעים ביכולת השימוש החוזר של המערכת.

    חשבתי בתור התחלה לבדוק האם בכלל יש בעיה אמיתית בעניין זה, באמצעות בדיקת העומס הכללי על ה CPU , כלומר אני יוצא מתוך הנחה שהקומפילציה ויצירת ה Plan היא פעולה שאינה מצריכה גישה לדיסק. אם זה נכון, אוכל להניח כי מכיוון שאין אצלנו מצוקת CPU על שרת הנתונים, הרי שאין סיבה להשקיע בהתאמה ליעילות מערכת ה query plan cache.


    האם זו התחלה נכונה לעניין זה?

    יום שישי 05 מאי 2017 11:09

תשובות

  • אכן הגישה להנחיל מראש צורת עבודה נכונה היא חשובה ונכונה.

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

    1) האם שימוש בשאילתא * Select אכן מבטלת את האפשרות לשימוש חוזר ב Plan , ולמה זה בעצם כך?

    2) האם יש רצוי לכתוב משפט IF הקובע מה השאילתא שיש להריץ בתוך C#, או שבמקרה זה לא יהיה הבדל בביצועים ויש לבחור את הדרך האלגנטית יותר ומובנת למתכנת.

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

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

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

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


    אהלן מתניה,

    פספסתי את התגובה שלך :-)

    תן לי לעבור נקודה אחרי נקודה..

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

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

    >> 1) האם שימוש בשאילתא * Select אכן מבטלת את האפשרות לשימוש חוזר ב Plan , ולמה זה בעצם כך?

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

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

    https://www.google.co.il/search?q=sql+server+using+select+with+asterisk&oq=sql+server+using+select+with+asterisk

    >> 2) האם יש רצוי לכתוב משפט IF הקובע מה השאילתא שיש להריץ בתוך C#, או שבמקרה זה לא יהיה הבדל בביצועים ויש לבחור את הדרך האלגנטית יותר ומובנת למתכנת.

    כמו תמיד התשובה היא "זה תלוי"

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

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

    במקרים אחרים הדרך היעילה היא להעביר את את השאילתה ולבצע את התץנאים ברת השאילתה עצמה.

    כל מקרה צריך להיבדק לגופו ובשאלהשלך אין דוגמאות אלא דיון כללי, לכן התשובה היא "תלוי"

    לא ברור לי מה אתה רואה כ דרך האלגנטית יותר ומובנת למתכנת

    >> 3) יש הטוענים אצלינו כי הפיזור הגדול של השאילתות בדפים השונים,

    אני לא מבין מה הכוונה לפזר שאליתות לדפים.

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

    >> 4) יש גם את הטענה שבחלק מהמקרים, שאילתא פרמטרית עשויה לדרוש Plan שונה לערכים שונים.

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

    שאילתה עם טקסט שונה גוררת יצירה של תוכנית הרצה שונה. למשל השאילתה

    select c from T where c > 1 

    מבחינת השרת היא שונה מהשאילתה

    select c from T where c > 12

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

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

    אני ממליץ לחפש חומר על:
    sql server parameter sniffing

    >> הסיעה האחת טוענת..

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

    >> משאבי המתכנת וה DBA הם היקרים ביותר

    זה נכון לטווח הקצר ולאנשיםן שקצרי ראייה.

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

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

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

    >> עד מצב שנוכל להעביר את לב בסיס הנתונים לדיסקים SSD

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

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


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

    • סומן כתשובה על-ידי Matanya Zac יום רביעי 10 מאי 2017 19:31
    יום רביעי 10 מאי 2017 09:18
    מנחה דיון

כל התגובות

  • קראתי רבות על כללים שיש למלא כדי שמערכת query-plan-cache תהיה יעילה, כלומר שמספר ההתאמות יהיה גדול. אני מניח שלמערכת קיימת כפי שיש לנו על שרת הווב תהיה זו השקעה גדולה להתאים את השאילתות (רובם שאילתות כתובות ב SQL Data Source , כלומר מחרוזות כתובות בתוך C# ,אשר נשלח לשרת כשאילתות פרמטריות. אמנם נעשה שימוש נרחב ב * select במקום פירוט שדות, וגם פניה לאובייקטים ללא קידומה הסכימה וכדומה, דברים הפוגעים ביכולת השימוש החוזר של המערכת.

    חשבתי בתור התחלה לבדוק האם בכלל יש בעיה אמיתית בעניין זה, באמצעות בדיקת העומס הכללי על ה CPU , כלומר אני יוצא מתוך הנחה שהקומפילציה ויצירת ה Plan היא פעולה שאינה מצריכה גישה לדיסק. אם זה נכון, אוכל להניח כי מכיוון שאין אצלנו מצוקת CPU על שרת הנתונים, הרי שאין סיבה להשקיע בהתאמה ליעילות מערכת ה query plan cache.

    האם זו התחלה נכונה לעניין זה?

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

    >> אני יוצא מתוך הנחה שהקומפילציה ויצירת ה Plan היא פעולה שאינה מצריכה גישה לדיסק

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

     >> מכיוון שאין אצלנו מצוקת CPU על שרת הנתונים, הרי שאין סיבה להשקיע בהתאמה ליעילות מערכת ה query plan cache

    זו וטעות גדולה בדרך כלל!

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

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

    * יש עניין של גישה כללית שכדאי לאמץ! גם אם אנחנו במצב בו אנחנו לא מנצלים היום את המשאבים שיש לנו, עבודה נכונה יכולה לתת לנו את הכלים לעתיד

    >> אני מניח שלמערכת קיימת כפי שיש לנו על שרת הווב תהיה זו השקעה גדולה להתאים את השאילתות

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

    >> האם זו התחלה נכונה לעניין זה?

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


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

    יום שישי 05 מאי 2017 14:45
    מנחה דיון
  • אכן הגישה להנחיל מראש צורת עבודה נכונה היא חשובה ונכונה.

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

    1) האם שימוש בשאילתא * Select אכן מבטלת את האפשרות לשימוש חוזר ב Plan , ולמה זה בעצם כך?

    2) האם יש רצוי לכתוב משפט IF הקובע מה השאילתא שיש להריץ בתוך C#, או שבמקרה זה לא יהיה הבדל בביצועים ויש לבחור את הדרך האלגנטית יותר ומובנת למתכנת.

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

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

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

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


    יום ראשון 07 מאי 2017 05:52
  • אכן הגישה להנחיל מראש צורת עבודה נכונה היא חשובה ונכונה.

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

    1) האם שימוש בשאילתא * Select אכן מבטלת את האפשרות לשימוש חוזר ב Plan , ולמה זה בעצם כך?

    2) האם יש רצוי לכתוב משפט IF הקובע מה השאילתא שיש להריץ בתוך C#, או שבמקרה זה לא יהיה הבדל בביצועים ויש לבחור את הדרך האלגנטית יותר ומובנת למתכנת.

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

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

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

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


    אהלן מתניה,

    פספסתי את התגובה שלך :-)

    תן לי לעבור נקודה אחרי נקודה..

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

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

    >> 1) האם שימוש בשאילתא * Select אכן מבטלת את האפשרות לשימוש חוזר ב Plan , ולמה זה בעצם כך?

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

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

    https://www.google.co.il/search?q=sql+server+using+select+with+asterisk&oq=sql+server+using+select+with+asterisk

    >> 2) האם יש רצוי לכתוב משפט IF הקובע מה השאילתא שיש להריץ בתוך C#, או שבמקרה זה לא יהיה הבדל בביצועים ויש לבחור את הדרך האלגנטית יותר ומובנת למתכנת.

    כמו תמיד התשובה היא "זה תלוי"

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

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

    במקרים אחרים הדרך היעילה היא להעביר את את השאילתה ולבצע את התץנאים ברת השאילתה עצמה.

    כל מקרה צריך להיבדק לגופו ובשאלהשלך אין דוגמאות אלא דיון כללי, לכן התשובה היא "תלוי"

    לא ברור לי מה אתה רואה כ דרך האלגנטית יותר ומובנת למתכנת

    >> 3) יש הטוענים אצלינו כי הפיזור הגדול של השאילתות בדפים השונים,

    אני לא מבין מה הכוונה לפזר שאליתות לדפים.

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

    >> 4) יש גם את הטענה שבחלק מהמקרים, שאילתא פרמטרית עשויה לדרוש Plan שונה לערכים שונים.

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

    שאילתה עם טקסט שונה גוררת יצירה של תוכנית הרצה שונה. למשל השאילתה

    select c from T where c > 1 

    מבחינת השרת היא שונה מהשאילתה

    select c from T where c > 12

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

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

    אני ממליץ לחפש חומר על:
    sql server parameter sniffing

    >> הסיעה האחת טוענת..

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

    >> משאבי המתכנת וה DBA הם היקרים ביותר

    זה נכון לטווח הקצר ולאנשיםן שקצרי ראייה.

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

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

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

    >> עד מצב שנוכל להעביר את לב בסיס הנתונים לדיסקים SSD

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

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


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

    • סומן כתשובה על-ידי Matanya Zac יום רביעי 10 מאי 2017 19:31
    יום רביעי 10 מאי 2017 09:18
    מנחה דיון
  • תודה על הפירוט וההתייחסות. אני התחלתי ללמוד לעומק את הנושא עם הלינקים שהיצעת למעלה.
    אכן הצדק אתך שהרקע לשאלה לא מספיק מוגדר ועל כן יש פרטים רבים הניתנים לניחוש.

    1)
    ובכן אני מדבר על חברה קטנה, שמספקת שירות ווב ייעודי ללקוחותיה, עם בסיס נתונים שאינו עובר את הטרה, אשר ניתן להקטין את הליבה שלו ל 100 גיגה אם היו מוציאים את המסמכים המאוחסנים בו למקום אחר (נתונים אינקרימינטאליים שניתן לאחסן אותם אפילו בסתם קבצים). ובכך היינו מקבלים שרת שווה לכל נפש המתאים לליבת המערכת.
    2)
    לחברה בסדר גודל כזה, אכן רצוי שיהיה יועץ מומחה בכל תחום, אך לצערי זה לא קיים ואין לזה תקציב. על כן יש צורך בכללי אצבע שיתנו כיוון במה בעיקר כדאי להשקיע.
    3)
    בכל דבריי אני מדבר על שאילתות אד-הוק פרמטריות שמגיעות מדפי ה ASP, אף פעם לא על שאילתות שערכי ה WHERE נבנים במחרוזת השאילתא עצמה. כלומר גם במקרה של שאילתא המגיעה עם ערכי פילטר מהמשתמש, גם אם אנו בונים את המחרוזת דינאמית (ברור לי שזה הרבה יותר טוב, מכיוון שמכריח ליצור Plan חדש לכל פילטר, וחוסך בביצוע השאילתא עצמה), הערכים מועברים בפרמטרים. כך שאם המשתמש יחזור על אותו פילטר, אמור להיות Hit.

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

    3ב) כאשר אני מדבר על פיזור גדול, שאין לי מידע ברור בשרת שלנו כמה זמן Plan מחזיק מעמד בקאש (נוכח העובדה שתמיד חסר מקום בזיכרון ל Pages של נתוני ה SQL עצמם ), באופן שאכן יש סיכוי לאחוזי Hit גבוהים.

    4)
    אכן לגבי מה שאתה מציין לגבי IF בתוך הסקריפט, אני מעט מתפלא. אני מניח ש SQL מכין PLAN עבור שאילתות ולא עבור בלוקים של IF בתוך סקריפט (אני מניח שהקומפילציה עצמה של משפטי הסקריפט זניחה). בדיקה קצרה ב SSMS מראה שהמערכת מזהה לפי הפרמטרים איזה בלוק צריך לפעול לפי הפרמטרים שנתקבלו (אני מדבר על מצב בו הפרמטר מתקבל מבחוץ והוא קובע איזה בלוק ירוץ), ורק עבורו המערכת מייצרת PLAN . זה אומר לדעתי הענייה, שלאחר בירור השאילתה הנדרשת להרצה לאחר חישוב ה IF המערכת ניגשת לחפש התאמה בקאש עבור ה PLAN . 
    ישנם מקרים רבים ששימוש ב IF בתוך הסקריפט נוח ואלגנטי וקריא יותר עבור המתכנת, וחוסך כתיבה מפוזרת, באשר ניתן מיד עם טעינת הדף (ASPX ->Page_Load) לכתוב את המחרוזת של השאילתה ללא תלות בבחירת ערך הפרמטר שיתברר רק בהמשך במספר אירועים שונים בתוך הדף (למשל בחירת ערך ב dropdown).

    למעשה, אם מה שאני אומר כאן לא נכון, אז כדאי לייעץ לכולם להעביר את כל Storage Procedure ל C#. אני יודע שיש שם קומפילציה, אבל שוב, העלות של חישוב IF בודד על בסיס פרמטרים הוא שולי וניתן להחשיב כזהה לקוד C#.

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






    • נערך על-ידי Matanya Zac יום שישי 12 מאי 2017 09:48
    יום רביעי 10 מאי 2017 19:31
  • אני שמח אם זה עזר במעט לכוון לשלב הבא

    >>  עם בסיס נתונים שאינו עובר את הטרה

    הערה מהצד Off-Topic כמו שאומרים
    גודל מסד הנתונים אינו הגורם היחיד להחליט האם מדובר ב Big Data או במסד נתונים רגיל.

    בעוד שבועיים נערך אירע נחמד בנושא ואני ממליץ להירסם:
    https://www.microsoft.com/middleeast/azure/cloud-society/engage-data-science-roadshow.aspx

    אני במקרה מעביר את ההרצאה בנושא של גודל מסדי הנתונים Volume:

    Understanding the three V's of big data (Volume)

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

    יש לי לקוח עם מסד נתונים של כמה עשרות גיגה בסך הכל שהוא Big Data ואחר שמחזיקים מסד נתונים של 100 טרה שהוא לא Big Data אלא data warehouse. המשפט אחדהייתי אומר ש Big Data זה השילוב המשולש של גודל, גיוון, ומהירות

    >> אכן רצוי שיהיה יועץ מומחה בכל תחום

    כמו תמיד הכל עניין של עלות מול תועלת

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

    >> אני מדבר על אותם שאילתות המפוזרות בכל דפי הווב, והשאלה עד כמה יש תועלת ללמוד ולהפיץ בין המפתחים כללים לצורך שימוש חוזר ב Plan.

    אני לא מכיר את המערכת כדי לתת הערכה כלשהי כמובן :-)

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

    >> ברור לי לחלוטין, שאין במערכת שום עיכוב של יותר מ 5 שניות לצורך חישוב Plan מחדש

    חמש שניות זה המון זמן. שאילתות נמדדות בדרך כלל באלפיות שניה מסיבה טובה.

    הרצה של אילתה שרצה 10 דקות יכולהלהיות פחות כבדה למערכת מהרצה של מליוני שאילתות שרצות באלפיות שנייה. מערכות אלו שמריצים שאילתות רבות נקראות מערכות OLTP ויש להן אתגרים הרבה יותר גדולים בדרך כלל ממערכות עיבוד נתונים. כל הפוקוס של גרסבת SQL Server 2014 הוא לגבי מערכות כאלה. התכונות החדשות שנכנסו בכוללות טבלאות ממוטבות לסכרון נועדו לעבודה עם מערכות OLTP

    OLTP (online transaction processing) is a class of software programs capable of supporting transaction-oriented applications on the Internet. Typically, OLTP systems are used for order entry, financial transactions, customer relationship management (CRM) and retail sales.(from http://searchdatacenter.techtarget.com/definition/OLTP)

    >>לגבי מפגשים העוגות והשתיה, אני אכן מתגעגע לימים שיכולתי להגיע למפגשים, היום זה בלתי אפשרי עבורי מסיבות אישיות.

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

    * האירוע שהזכרתיח מעל יערך גם ONLINE לפי הבנתי למשל


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


    יום שישי 12 מאי 2017 16:18
    מנחה דיון