none
הבדלים בביצועים בין קריאה לפרוצדורה לבין הפעלת השאילתה ישירות RRS feed

  • שאלה

  • יש לי פרוצדורה במערכת דוחות,שאני קורא לה עם פרמטרים מחד, שהביצועים שלה די גרועים (1:04 דקות)

    מצד שני אני מריץ את השאילתה ישירות ע"י מילוי הפרמטרים והיא מסתיימת די מהר (3 שניות).

    מצ"ב לינק ל EXECUTION PLANS (הסיסמא 123456)

    https://www.box.com/s/zlxnho7copenfu7mtzm2

    אחד העניינים שלו ברורים או איך להתגבר עליהם הוא שימוש ב INXED שמכיל רק את עמודת START DATE

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

    תודה מראש

    יום שני 11 פברואר 2013 09:58

כל התגובות

  • חבל שלא סיפקת לנו DDL+DML אבל בינתיים אני מקווה שזה יעזור לך:

    שאילתות מורכבות עם הרבה JOIN כדאי לפעמים לעשות שימוש ברמזים (HINT) כדי לכוון את השרת לתוכנית ההרצה המתאימה לך.

    * לגבי למה נוצרות תוכניות שונות הרי שצריך לבדוק הגדרות השרת והגדרות הגישה לשרת, הגדרות המשתמש שמריץ והגדרות ה סשן אם יש.

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

    OPTION (LOOP JOIN)

    לדוגמה

    SELECT OC.CustomerID, OH.SalesOrderID
    FROM Sales.SalesOrderHeader AS OH
    JOIN Sales.Customer AS OC
    ON OH.CustomerID = OC.CustomerID
    OPTION (LOOP JOIN)

    * תוספת: אולי כדאי לבדוק אפשרות של שינוי ה DDL כך שתוכל לבנות CLUSTERED INDEX מתאים ל JOIN שלך כך שיתאפשר לך למשל לעשות שימוש יעיל ב Merge JOIN


    signature

    יום שני 11 פברואר 2013 17:21
    מנחה דיון
  • שלום רב

    נסה את הבא לבדיקת שאילתות ad hoc

    כללי עבודה עם כוונון

    דוגמא השאילתה הבאה

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT SUM(qs.total_logical_reads) AS [Total Reads]

    , SUM(qs.total_logical_writes) AS [Total Writes]

    , DB_NAME(qt.dbid) AS DatabaseName

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

    WHERE DB_NAME(qt.dbid) = 'AdventureWorks'

    GROUP BY DB_NAME(qt.dbid)

    צמצום ה- DMV ל- DB ספציפי ע"י הפונקציה DB_NAME .

    חובה שכל ה- SQL לטובת הכוונון יתחילו עם הבא :

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

    גבי שאילתות adhoc  

    כאשר עובדים עם SP , מידע על ה- DB נשמר בתוך ה- SP .כאשר עובדים עם שאילתת adhoc ה- DB מופיעה כ- NULL ב sys.dm_exec_sql_text.את שם ה- DB מיתן לקחת מ- DMF בשם sys.dm_exec_plan_attributes. הקלט הוא מזהה ה plan מ DMV sys.dm_exec_cached_plans. sys.dm_exec_plan_attribute מכיל מידע רב לגבי ה- DBs . בדוגמא הבאה נבחר ב- DBId באופן הבא :

    WHERE pa.attribute = 'dbid'

    השאילתה השלמה תראה כך

    SELECT TOP 20

    st.text AS [SQL]

    , cp.cacheobjtype

    , cp.objtype

    , COALESCE(DB_NAME(st.dbid),

    DB_NAME(CAST(pa.value AS INT))+'*',

    'Resource') AS [DatabaseName]

    , cp.usecounts AS [Plan usage]

    , qp.query_plan

    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa

    WHERE pa.attribute = 'dbid'

    ORDER BY cp.usecounts DESC;

    הסבר לגבי הטכניקה :

    אנו מנסים למצוא את שם ה- DB ע"י הפונקציה COALESCE , שמחזירה את הערך הראשון ששונה מ- NULL מתוך רשימה של ערכים .DBID מתוך DMF sys.dm_exec_sql_text  (DB_NAME(st.dbid),) . אם הערך הוא NULL אז DBID מתוך sys.dm_exec_plan_attributes . אם זה NULL אז מניחים שזה רץ מתוך RESOURCE DB .

    שימו לב בדוגמא זיהנו שאילתות ad hoc ע"פ התוספת של * לשם ה- DB . ניתן לבצע זאת גם ע"י העמודה objtype .

    בברכה

    דוד יצחק DBA

    BSC Information system , ME System Engineering ,MBA

    מנהל פורום DBA צפון

    http://www.iloug.org.il/Event_Page.php?EventID=159&BannID=1907

    יום שלישי 12 פברואר 2013 05:32
  • הי,

    לא כל-כך הצלחתי להבין מה ההבדל בין שתי הריצות. אמנם ה-Plans נראים שונים, אבל השאילתות שמאחוריהן נראות זהות. שלח בבקשה את הקוד שאתה מריץ בשני המקרים.

    בכל מקרה, הסיבה להבדל בביצועים בין שתי הריצות היא מספר הרשומות שחוזרות מהטבלה "Con_RecipientToMessages". במקרה הטוב חוזרות 1,024 רשומות, בעוד שבמקרה הרע חוזרות 55,280 רשומות. מכיוון שה-Optimizer העריך שתחזורנה רק 1,182.6 רשומות, הוא השתמש ב-Nested Loops מספר פעמים (גם בשביל לבצע Key Lookup על הטבלה "Con_RecipientToMessages" וגם בשביל לבצע Join מול טבלאות אחרות). מכיוון שכל פעולת Nested Loops כזאת צריכה להתבצע 55,280 פעמים, הביצועים לא משהו...

    לגבי השאלה שלך בעניין האינדקס, אני רואה שבשני המקרים היה שימוש באינדקס "IX_StartDate_UserRef", מכיוון שאתה משתמש ב-Index Hint. אז גם במקרה הזה אני לא כל-כך מבין את השאלה...

    אם תוכל להרחיב, אני מקווה שאוכל לעזור יותר...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום שלישי 12 פברואר 2013 06:15
    מנחה דיון
  • גיא, אם הבנתי אותו נכון אז הרי שזו בדיוק הבעיה שהוא מציג. יש לו 2 שאילתות זהות שרצות בתוכנית הרצה שונה בגלל שהן רצות בסביבה שונה (אחת הוא אמר שהוא מריץ דרך SP דרך אפליקציה חיצונית של מערכת דוחות, ואחת ישירות דרך ה SSMS). אני יכול לנחש שהוא הוציא את תוכניות ההרצה מה CASH ומצא שהן שונות.

    signature

    יום שלישי 12 פברואר 2013 06:53
    מנחה דיון
  • קודם כל תודה לכולם על התשובות.

    גיא וpituach :

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

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

    יום שלישי 12 פברואר 2013 07:31
  • הי דויד,

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

    תודה,

    צוות הפורומים.


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

    יום שלישי 12 פברואר 2013 10:55
  • הי pelegk1,

    הפילטר שמופיע ב-WHERE אכן משתמש אך ורק באינדקס המורכב ("IX_StartDate_UserRef"), אולם מכיוון שהאינדקס הזה הוא Non-Clustered, מתבצעת פעולה של Key Lookup מול ה-Clustered Index לפי המפתח של ה-Clustered Index (במקרה הזה - "StartDate"). הפעולה הזאת נועדה להביא מתוך ה-Clustered Index עמודות נוספות שלא קיימות ב-Non-Clustered Index. היא לא נועדה לסנן רשומות.

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

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום שלישי 12 פברואר 2013 19:59
    מנחה דיון
  • שלום

    ביצירה של הפרוצדורה, לפני שמילה AS תוסיף WITH RECOMPILE 

    ההסבר הוא פשוט, הפרצדורה מתקמפלת/נבנה לה תוכנית ביצוע (Execution plan) בהרצה הראשונה שלה

    תכונית הביצוע הראשונה כנראה לא מתאימה להרצה הנוכחית שלך


    יום שלישי 12 פברואר 2013 20:00
  • לבצע פעולה של יצירת תוכנית הרצה מחדש בכל הרצה של הפרוצדורה היא פעולה קיצונית שגוררת איבוד ייתרונות רבים ותעלה בזמן/משאבים. יש סיבה שתוכניות ההרצה נשמרות ב CASH כמובן. במקרים קיצוניים בהם אכן יש הבדלים קיצוניים בין פרמטרים שונים שמועברים לפרוצדורה יש עדיפות ליצור פרוצדורה שונה לקבוצת פרמטרים כך שתוכנית ההרצה תתאים לקבוצה זו. השיטה היא להכין פרוצדורה לניהול A שקוראים לה. פרוצדורה A בודקת את הפרטרים ו"מחליטה" אם להריץ את פרוצדורה B או C. כאשר B ו  C הן פרוצדורות זהות. בגלל הפרמטרים שמועברים אליהן הן בעלות תוכניות הרצה שונות וכל אחת מהן תתאים לקבוצה של פרמטרים (יש עוד דרכים כמו למשל לטעון תוכנית הרצה מוכנה ועוד, אבל זו הדרך הכי קיצונית למיטוב מצבים קיצוניים של תלות בפרמטר).

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


    signature

    יום שלישי 12 פברואר 2013 23:35
    מנחה דיון
  • היי גיא, אפשר לשלוח לך למייל?

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

    יום חמישי 14 פברואר 2013 07:53
  • הי,

    כשאתה משתמש בפרמטרים באופן ישיר (ולא דרך משתנים, כמו שעשיתם עכשיו), אז SQL Server יודע מה ערכי הפרמטרים בזמן קומפילציה (Parameter Sniffing), והוא מייצר Execution Plan על סמך ערכי הפרמטרים האלה. לעומת זאת, כאשר אתה משתמש במשתנים, אז בזמן קומפילציה SQL Server לא יודע מה ערכי המשתנים, ולכן הוא מייצר Execution Plan על סמך הממוצע הכללי בטבלה (לפי סטטיסטיקות).

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

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

    אתה יכול לשלוח אלי את הסקריפטים במייל: guy AT madeira DOT co DOT il.

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום חמישי 14 פברואר 2013 11:49
    מנחה דיון
  • הי pelegk1,

    יש חדש?

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום חמישי 21 פברואר 2013 05:43
    מנחה דיון
  • שלחתי לך במייל את הקוד תגיד לי אתה :)

    ושוב תודה על העזרה

    יום חמישי 21 פברואר 2013 07:25
  • הי,

    לא קיבלתי.

    אתה לא יכול לפרסם את הקוד כאן בפורום?

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום חמישי 21 פברואר 2013 08:09
    מנחה דיון
  • תמיד עדיף לא לחשוף שמות של טבלאות וכדומה

    מצ"ב הפרוצדורה

    SP

    הסיסמא : 654321

    יום חמישי 21 פברואר 2013 08:36
  • הי,

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

    לצערי, בשביל להמשיך לחקור את הפרוצדורה הזאת, צריך לעבוד על השרת עצמו ולשחק עם השאילתה ב-Live. זה כבר חורג מגבולות הפורום, לפחות בשבילי.

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום חמישי 21 פברואר 2013 13:06
    מנחה דיון
  • תודה בכל מקרה
    יום חמישי 21 פברואר 2013 13:54
  • כמובן שחקירה אמיתית אפשר לעשות כמו שכתב גיא רק על השרת עצמו (אחרי הכל מה שטוב באחד לא זהה באחר) אבל אני ממליץ לך לחזור לתגובה הראשונה שלי כי הפתרון גם בלי הלכנס לעומק יכול להיות פשוט שימוש ב HINT כזה או אחר

    signature

    יום חמישי 21 פברואר 2013 19:57
    מנחה דיון