למה השרת משתמש בכל כך הרבה query plans לאותה פרוצדורה?
אחד הייתרונות בשימוש בפרוצדורות בSQL Server, הוא שימוש חוזר בquery plan. כאשר אנו משתמשים בפרוצדורה, השרת יוצר query plan, ולאחר מכן כל שימוש נוסף בפרוצדורה עובד לפי הquery plan, שנמצא כבר בcache, ולא צריך לגרום לייצור נוסף של query plan. כמובן שלא תמיד ניתן להשתמש באותו query plan. לפעמים יש נסיבות, שבהן למרות שיש כבר query plan לפרוצדורה בתוך הcache, השרת יוצר query plan נוסף לאותה פרוצדורה. הסיבה הידועה ביותר ליצירה של query plan נוסף, היא set options שונים בsessions שונים (מי שלא מכיר את הנושא של set options יכול למצוא יותר פרטים בURL כאן.
במאמר הזה אני אתאר מקרה, שבו נתקלתי בפרוצדורה עם מספר גבוה של query plans, וסיבה שלא הכרתי, ליצירה של מספר query plans לאותה פרוצדורה.
במהלך יום עבודה הגיע אלי בקשה לבדוק איטיות באחת המערכות, שאני לא באמת מכיר. המערכת מבוססת על SQL Server בסביבת Azure (קונפיגורציית PAAS), כך שמבחינת הבדיקות הייתי יכול לבדוק רק את הדברים שקשורים ישירות לSQL Server ללא אפשרות לבדוק גורמים שקשורים לחומרה. הנתון היחיד שניתן לי היה, שהפרוצדורה מופעלת כמה עשרות פעמים בשנייה, ושהיא רצה לאט מידי. הדבר הראשון שבדקתי היה הקוד של הפרוצדורה. כבר בבדיקה ראשונה ראיתי שגיאה בקוד, שמונעת מהשרת להשתמש באינדקס שקיים (שימוש בפונצקיה על עמודה בטבלה בwhere clause). הסברתי למפתחים איזה שינוי הם צריכים לבצע, אבל כדי לבדוק אם יש בעיות נוספות, רציתי לבדוק איך נראה הquery plan של השאילתה. מאחר שלא יכלתי להפעיל את הפרוצדורה ולראות את הquery plan, החלטתי לבדוק את הquery plan שנמצא בcache. בשלב הראשון רציתי למצוא את הplan_handle באמצעות השאילתה הבאה (בשאילתה המקורית השתמשתי כמובן בשם האמיתי של הסכימה והפרוצדורה):
select qs.*, text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where text like '%CREATE PROCEDURE%[MySchema].[MyProc]%' and text not like '%select qs.*%'
להפתעתי קיבלתי קרוב ל100 query plans. מאחר שהפרוצדורה הכילה 2 משפטי SQL, חשבתי שאני אקבל 2 רשומות. ידעתי שיכול להיות קצת יותר (למשל אם יש כמה סשנים עם set options שונים), אבל 100 query plans היה מאד מפתיע.
רציתי לנסות ולראות האם למרות שמדובר בquery plans שונים, או בquery plans זהים לחלוטין. לצורך הבדיקה השתמשתי ב2 עמודות בsys.dm_exec_query_stats - query_hash וquery_plan_hash. שתי העמודות האלו נועדו להראות חתימות של משפטי SQL וquery plans. נניח שאני מריץ את השאילתות הבאות בשרת:
SELECT * FROM MyTable WHERE COL1 = 1
SELECT * FROM MyTable WHERE COL1 = 2
SELECT * FROM MyTable WHERE COL1 = 3
SELECT * FROM MyTable WHERE COL1 = 4
בהנחה שCOL1 לא מוגדר כשדה ייחודי, לcache של השרת ייכנסו ארבעת המשפטים, ולכל אחד מהם יהיה sql_handle שונה. מאחר שהמשפטים כמעט זהים, וההבדל היחיד שלהם הוא הערך, שאנחנו מחפשים, בעמודה query_hash, יהיה להם אותו ערך. כל אחד מהמשפטים האלו, יקבל כמובן query plan. גם אם כולם ישתמש בדיוק באותו query plan, בשרת יהיו לי ארבעה query plans, שלכל אחד מהם יהיה ערך שונה בעמודה plan_handle. במידה שיהיו לי שאילתות שקיבלו בפועל query plan זהה, הם יקבלו את אותו ערך בעמודה query_plan_hash למרות שיש להם ערך שונה בעמודה plan_handle.
על מנת לבדוק אם כל הquery plans שונים, שלפתי את הquery_hash של שני משפטי הSQL, והרצתי את השאילתה הבאה:
select query_hash, query_plan_hash, count(*)
from sys.dm_exec_query_stats
where query_hash in (0x8BD28547E17A85C4, 0xCE24663CF14BCE3C)
group by query_hash, query_plan_hash
לפי התוצאה ראיתי ש הquery plans היו באמת זהים.
עכשיו ניסיתי להבין למה בעצם השרת בונה query plans שונים לפרוצדורה במקום להשתמש באותו query plan בכל הפעלה של הפרוצדורה (במיוחד כאשר מדובר על אותו query plan בדיוק). ע"מ להבין מה גורם ליצירה של query plans נוספים, השתמשתי בDMV בשם sys.dm_exec_plan_attributes. לsys.dm_exec_plan_attributes יש שלוש עמודות:
Attruibute – שם התכונה של הquery plan (לדוגמא set options, db_id וכד').
Value – הערך שיש לאותה תכונה.
Is_cache_key – האם מדובר על תכונה, שכל ערך שונה שלה, גורם ליצירת query plan נוסף. אם הערך של העמודה הוא 1, אז כל שינוי בערך של התכונה, שאותה הוא מתאר גורם ליצירה של query plan חדש.
כדי להבין מה גורם ליצירה של כל הquery plans הרצתי את השאילתה הבאה:
With MyPlanHandles as (
SELECT plan_handle
from sys.dm_exec_query_stats
where query_hash = 0x8BD28547E17A85C4)
select *
from MyPlanHandles cross apply sys.dm_exec_plan_attributes(plan_handle)
where is_cache_key = 1
order by attribute
השאילתה החזירה לי את כל התכונות של כל הquery plans, שגורמות ליצירה של query plan חדש עם הערכים שלהם, כאשר המיון נעשה לפי התכונה, לכן קל מאד לראות מה התכונה הספציפית שגורמת ליצירה של query plan נוסף במקום להשתמש בquery plan שכבר קיים (התכונה, שבה יש ערך שונה לכל query plan).
לפי התוצאות של השאילתה ראיתי שלכל אחד מהquery plans יש הבדל בתכונה שנקראת optional_spid. עכשיו הגיע הזמן להבין מה בעצם אומרת התכונה הזאת. בתיעוד הרשמי של מייקרוסופט התכונה הזאת לא מופיע, אבל חיפוש מהיר בגוגל מביא את ההסבר. הפרוצדורה עובדת עם טבלה זמנית מקומית, אבל הטבלה הזמנית לא נוצרה בפרוצדורה, אלה בפרוצדורה אחרת, שיצרה את הטבלה הזמנית, הכניסה אליה נתונים ואז הפעילה את הפרוצדורה שבדקתי. מאחר שאפשר לעבוד טבלה זמנית מקומית רק בsession שיצר אותה, התכונה optional_spid שומרת את הsession_id, שבה נוצרה הטבלה הזמנית. במידה שמספר sessions שונים מפעילים את פרוצדורת האב, פרוצדורת הבן מקבלת ערך שונה לoptional_spid ולכן לכל session_id קיים query plan שונה.
