SQL Server 2016 Query Store

אלון זנטנר, DBA בכיר בחברה בשלבי בחינת SQL Server 2016 מספר,

SQL Server 2016 מביא אתו תכונה חדשה בנושא ניטור ושיפור ביצועים.

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

מה זה Query Store ?

Query Store היא תכונה (feature) חדשה ב SQL Server 2016 המאפשרת ניטור ביצועי שאילתות בצורה קלה ונוחה ללא צורך בניסיון ,תוכנות צד שלישי או תפירת פתרונות מקומיים כלשהם.

עד היום אם רצינו לבחון שאילתות ותוכניות פעולה (Query Plans) בדיעבד , הן היו זמינות עבורנו בזיכרון השרת (Proc Cache) ויכולנו לראות אותן דרך טבלאות ניהול ייעודיות כגון sys.dm_exec_query_plan.

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

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

איך זה עובד?

Query Store למעשה מבצע שמירת מידע על ריצה של שאילתות. איזה מידע נשמר? נשמרות תוכניות הפעולה שהיו בשימוש ומידע על צריכת המשאבים וביצועי השאילתות בפועל. המידע נשמר תחילה לזיכרון ואז עובר לקובץ בבסיס הנתונים שתושאל (primary filegroup user DB) . כך, כשהנתונים בדיסק, הם זמינים לנו גם אחרי מחיקת הזיכרון, אם קרה במכוון ואם לא.

למידע יותר מפורט על מה קורה מאחורי הקלעים ב Query Store אני ממליץ לקרוא : How Query Store Collects Data

כיצד מתחילים?

תחילה יש צורך להפעיל את Query Store . מדובר בתכונה ברמת בסיס הנתונים (User database) ויש צורך להגדיר אותה. ניתן להפעיל בשתי דרכים. באמצעות קוד :

ALTER DATABASE [Test_QueryStoreDB] SET QUERY_STORE = ON;

ניתן גם להפעיל את Query Store בממשק המשתמש (SSMS - איור 1 ).

איור 1 - Query Store בממשק המשתמש.

חשוב לציין שיש להשתמש בגרסאות האחרונות של Sql Server Management Studio , אני השתמשתי ב SQL 2016 CTP3 , יתכן ומעט מהדברים ישתנו בגרסאות הרשמיות.

כיום ניתן להוריד את גרסאת SQL Server 2016 Release Candidate 0

ולענייננו: להפעלה נלחץ קליק ימני אל בסיס הנתונים שנרצה לנטר, במאפייני בסיס הנתונים - יש לנו אפשרות חדשה – Query Store (איור 1).

דבר ראשון על מנת להפעיל את Query Store נגדיר את המאפיין Operation Mode , נבחר ב Read Write.

ישנם מספר מאפיינים נוספים שניתן להגדיר:

Operation Mode – אם נבחר באפשרות Only Read, אחרי ש Query Store כבר היה בשימוש, הדבר יפסיק צבירה ותיעוד של נתונים חדשים אבל ישמור על הנתונים שנצברו עד כה. האפשרות Off תעצור את Query Store כליל ותמחק את הנתונים שנצברו.

ALTER DATABASE [Test_QueryStoreDB]

SET QUERY_STORE (OPERATION_MODE = READ_WRITE );

(Stale Query Threshold (Days – משמש להגדרת מספר ימים לשמירת הנתונים ב query store .

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

ALTER DATABASE [Test_QueryStoreDB]

SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

(Max Size (MB – באמצעותו נגדיר את הגודל המרבי שאנו רוצים ש query store יתפוס בדיסק. כש query store יגיע לגודל שנגדיר באמצעות מאפיין זה , אוטומטית יופסק איסוף נתונים חדשים ו למעשה query store יעבור ממצב Read Write למצב (!) Read Only .

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

ALTER DATABASE [Test_QueryStoreDB]

SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

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

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

ALTER DATABASE [Test_QueryStoreDB]

SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);

Size Based Cleanup Mode – מגדיר אם לבצע ניקיון אוטומטי כאשר צריכת המקום בדיסק מתקרבת לגודל המרבי שהגדרנו.

ALTER DATABASE [Test_QueryStoreDB]

SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Query Store Capture Mode – מאפשר להגדיר אם query store יתעד את כל השאילתות שרצות בבסיס הנתונים (All mode) או רק כאלה שהיה להן מספר חוזר של ריצות או צריכה גבוהה של משאבים (Auto mode). לחילופין ניתן גם להגדיר באמצעות מאפיין זה לא לתעד שאילתות חדשות אך כן לעקוב אחר מאפייניהן של אלו שכבר תועדו (None mode).

ALTER DATABASE [Test_QueryStoreDB]

SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

מאפינים נוספים:

MAX_PLANS_PER_QUERY – משמש להגדרת מספר מרבי של תוכניות פעולה לכול שאילתה.

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

ולבסוף , ניתן לראות איך Query Store מוגדר ברגע נתון על ידי תשאול sys.database_query_store_options

מה ניתן לראות כאשר Query Store מוגדר?

לאחר שהגדרנו את Query Store המידע על הביצועים מתחיל להישמר. על מנת לראות את המידע אפשר להשתמש בדוחות בממשק המשתמש (SSMS) : בבסיס הנתונים נוספה תיקייה מתאימה ובה דוחות ייעודיים (ראה תמונה מטה) :

הדוחות מוצגים בתצוגה גראפית מובנת ומסודרת ,ממש " גזור ושמור ".

בכל הדו"חות ניתן בקלות לסנן את התוצאות ולמיין אותן לפי מדדי ביצועים שונים (זמן ריצה , זמן עיבוד , קריאה\כתיבה וכו'). אנו מקבלים 4 דוחות מובנים:

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

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

Top Resource Consuming Queries: דו"ח זה מציג את השאילתות היקרות ביותר בהיבט ביצועים , בטווח זמן שנדגיר ועל פי המדדים שנגדיר (זמן עיבוד \ זמן ריצה \ מספר ריצות \שימוש בזיכרון \ קריאות וכתיבות לוגיות \ פניות לדיסק) - איור 3

Tracked Queries: כאן נעקוב אחר ביצועיהן של שאילתות בזמן אמת. בעיקר נרצה לעקוב אחר שאילתות שכפינו עליהן תוכנית פעולה - נוודא שהשינוי שביצענו אכן עוזר לביצועי השאילתה.

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

איור 3 : דוגמא לדוח שימוש במשאבי מערכת

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

sys.database_query_store_options – כאן ניתן לראות איך Query Store מוגדר.

sys.query_context_settings– מכיל מידע על ההקשר של שאילתות. כאן חשוב לציין שאילתות שהטקסט שלהן זהה יכולות לרוץ בדרך שונה ולהחזיר נתונים שונים אם הן רצות עם נתוני סביבה שונים - מאפיינים כגון שפה, התייחסות ל NULL , סכמה דיפולטית , וכו'.

sys.query_store_plan – מכיל את תוכניות הפעולה ואת הקישור שלהן לשאילתות.

sys.query_store_query – כאן נראה את השאילתות ואת המידע הסטטיסטי שנצבר עד כה על ביצועיהן.

sys.query_store_query_text – מכיל את השאילתות עצמן (T-SQL ) ו ה handle שלהן.

sys.query_store_runtime_stats – מידע סטטיסטי על הריצה האחרונה של שאילתות.

sys.query_store_runtime_stats_interval – מכיל מידע על פרקי זמן שעל-פי הם הנתונים נסכמים.

מה עוד ניתן לעשות עם Query Store ?

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

• sp_query_store_flush_db – מעביר את המידע שה Query Store צבר מהזיכרון לדיסק.

• sp_query_store_force_plan - מאפשר לכפות תוכנית פעולה לשאילתה מסוימת.

• sp_query_store_remove_plan – מסיר תוכנית פעולה מה Query Store.

• sp_query_store_remove_query – מסיר מה Query Store שאילתה ואת כל המידע שנאסף לגביה.

• sp_query_store_reset_exec_stats – מוחק את המידע הסטטיסטי שנצבר.

• sp_query_store_unforce_plan - מאפשר להסיר כפיה של תוכנית פעולה לשאילתה מסוימת.

ניתן לקרוא בהרחבה על שימוש בפרוצדורות כאן - Query Store Stored Procedures

בקישור הבה מפורטים נושאים רבים על Query Store. איך מגדירים, שימושים אפשריים, שאילתות לדוגמה ועוד : Monitoring Performance By Using the Query Store. ממליץ גם על Best Practice with the Query Store

לסיכום

Query Store מאפשר לנו דרך נוספת, בלתי אמצעית, להבין מה מצב ביצועי השאילתות בסביבה שלנו. מיקרוסופט פרגנו לנו יכולת תחקור באמצעות קוד ייעודי וממשק משתמש ברור. בנוסף ניתנה לנו דרך קלה להשפיע על הביצועים הללו על ידי כפיית תוכניות – באמצעות ממשק המשתמש ( לטעמי דרך קלה מדי - אני ממליץ קודם להבין למה השתנתה התוכנית ורק אחר כך להחליט האם לכפות תוכנית כן או לא ). בהחלט כיף לראות שיפור בכיוון ניטור הביצועים של השאילתות בסיסי הנתונים ב Sql Server 2016 ובAzure.

#SQLServer #SQLServer2016 #QueryStore #Azure #AlonZentner #אלוןזנטנר