Set ArithAbort, Execution plans, ושיפור ביצועים

08/12/2017

אין תגובות

תודה מראש לעדי כהן על העזרה בפתרון הבעייה ובכתיבת הפוסט
את ההרצאה על Set ArithAbort שמעתי כבר כמה פעמים: הפרוצדורה רצה לא טוב דרך האפליקציה, רצה טוב ב-SSMS, וכל זה בגלל שהגדרת ברירת המחדל של ArithAbort (שאין לה שום השפעה ישירה על הפרוצדורה) גורמת לכך שהמערכת יוצרת Execution Plan אחד באפליקציה ואחר ב-SSMS (שוב- רק בגלל שכל סביבה נחשבת לשונה בגלל הנ"ל), וכך המערכת שומרת שני Execution Plans שונים, מבזבזת משאבים ומבלבלת את ה-DBA-ים שאינם מבינים מה השתבש.

כאמור – שמעתי מספר פעמים, אבל רק השבוע זה קרה לי בפעם הראשונה: יש אפליקציה שרצה, מריצה אחת לכמה דקות פרוצדורה – לרוב עם פרמטרים "רזים" (מעט נתונים) ולעמים עם פרמטרים "שמנים" (הרבה נתונים). כצפוי, התוכנית שקומפלה היא התוכנית הרזה, אלא שכשהיא רצה עם הפרמטרים השמנים – היא נתקעת כי אינה מתאימה (לרוב זה נגרם מכך שהיא מבצעת אינספור key lookups או seeks במקום scan אחד מתוך מחשבה שיהיו מעט כאלה). כמובן שמשהוזעקתי לברר מה קרה והרצתי מה-SSMS עם הפרמטרים השמנים, נוצר Execution Plan אחר רק בשל ההבדל בהגדרות ה-ArithAbort בין הסביבות, ואצלי זה רק מהר.

איך מזהים שיש שתי תוכניות? אני נעזר בשליפה הבאה:

Select  PS.total_logical_reads/NullIf(DateDiff(Minute,PS.cached_time,GetDate()),0) N,

        Coalesce(DB_Name(PS.database_id),DB_Name(PSh.dbid),DB_Name(PSph.dbid)) 

        DBName,

        Object_Schema_Name(Coalesce(PS.object_id,PSh.objectid,PSph.objectid),Coalesce(PS.database_id,PSh.dbid,PSph.dbid)) 

        SchemaName,

        Coalesce(Object_Name(PS.object_id,PS.database_id),Object_Name(PSh.objectid,PSh.dbid),Object_Name(PSph.objectid,PSph.dbid)) 

        ObjectName,

        PS.*,

        PSh.text,

        PSph.query_plan

From    (Select Top 10 *

        From    sys.dm_exec_procedure_stats (NoLock)

        Where   database_id=DB_ID('etoro')

                And Object_Schema_Name(object_id)='Compliance'

                And Object_Name(object_id)='GetQuestionsExpirationPopulation'

        Order By total_logical_reads/NullIf(DateDiff(Minute,cached_time,GetDate()),0) Desc) PS

Outer Apply sys.dm_exec_sql_text(PS.sql_handle) PSh

Outer Apply sys.dm_exec_query_plan(PS.plan_handle) PSph;

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

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

קודם כל ננסה ליישר את הסביבות כך שניתן יהיה לשחזר את הבעייה. ניתן לשנות את הגדרות ה-ArithAbort ב-Session כך:

קליק ימני בתוך ה-Query ו-

אם נשנה את ההגדרה – הבעייה של האפליקציה תשוחזר ב-SSMS, אבל לא להשאיר את זה – זה רק נועד לשיחזור, ובהמשך יש להתאים את ההגדרות באפליקציה לאלו שב-SSMS: משנים את הגדרות ה-ArithAbort בשרת כך שהן יעקפו ויאכפו על ברירות המחדל של האפליקציה:

Declare @UserOption Int;

Select  @UserOption=value From sys.sysconfigures Where comment='user options';

Select  @UserOption=@UserOption |64;

Exec    sp_configure 'user options', @UserOption;

Reconfigure With Override;

כך או כך – התאמנו את ה-SSMS לאפליקציה או להיפך, כעת ניתן לשחזר את הבעייה, ולנסות לפתור אותה.
יש למחוק את ה-Execution Plan הסורר כך:

DBCC FreeProcCache (0x05000900858F0810208AD1E40601000001000000000000000000000000000000000000000000000000000000);

(הקוד ההקסדצימלי הוא מה שמופיע בעמודת ה-plan_handle בשליפה הנ"ל),
ומהר מהר מהר להריץ את הפרוצדורה עם הפרמטרים השמנים: במקרה שלנו זה דפק קצת את הריצה הרזה, אך שיפר פלאים את השמנה.

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

הפתרון במקרה זה (פרןצדורה שרצה אחת לכמה דקות) היא לבצע Recompile בכל ריצה: המחיר של זה (recompile בכל ריצה) זניח ביחס לשיפור בביצועים. או שבפקודת ההפעלה של הפרוצדורה באפליקציה מוסיפים-

Exec MyProc @P1=1 With Recompile;

או שבפרוצדורה עצמה מוסיפים בסוף פקודת ה-Select –

Select .. From .. Where .. Option(Recompile);

או שמבצעים Recompile על כל הפרוצדורה –

Create Proc MyProc With Recompile As ..

ביצועים משובחים לכולם!

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

כתיבת תגובה

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