Parallelism
כאשר לשרת יש יותר ממעבד אחד הוא יכול לבחור לבצע פעולות במקביל וזה משפיע על בחירת ה-Execution Plan האופטימלי.
בפוסט הקודם הראיתי כיצד במקרה של Join בין טבלה לא מאונדקסת לטבלה עם Primary Key & Clustered Index המערכת תבחר בעיבוד במקביל:
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id;
מאוד יכול להיות שחלק שמי שניסה את הקוד אצלו קיבל Execution Plan שונה לגמרי.
בשרת עליו הרצתי את הדוגמה יש שני מעבדים ולכן קיימת אופציה לעבודה במקביל:
Select cpu_count
From sys.dm_os_sys_info;
(שתי דרכים כיצד לבדוק כמה מעבדים יש)
מי שיש לו מעבד אחד על השרת יקבל כאמור תוכנית שונה, וניתן לראות זאת אם נגביל את המערכת בעזרת Hint.
ניצור שוב את הטבלאות הדרושות:
--1. No Indexes
Select *
Into messages
From sys.messages;
Go
--2. Clustered Primary Key on message_id,language_id
Select *
Into messages_ClstInd
From sys.messages;
Go
Alter Table messages_ClstInd Add Constraint PK_messages_ClstInd Primary Key Clustered (message_id,language_id);
Go
וכעת נריץ את השליפה הנ"ל עם ובלי Hint, תוך הפעלת Statistics IO ו-Execution Plan:
Set Statistics IO On;
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id;
Select *
From messages T1
Inner Join messages_ClstInd T2
On T1.message_id=T2.message_id
And T1.language_id=T2.language_id
Option (MaxDop 1); --שימוש במעבד אחד בלבד
(97526 row(s) affected)
Table 'messages_ClstInd'. Scan count 3, logical reads 2985, physical reads 3, read-ahead reads 2722, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'messages'. Scan count 3, logical reads 2718, physical reads 97, read-ahead reads 2718, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(97526 row(s) affected)
Table 'messages_ClstInd'. Scan count 0, logical reads 433221, physical reads 138, read-ahead reads 13280, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'messages'. Scan count 1, logical reads 2718, physical reads 440, read-ahead reads 2703, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
כפי שניתן לראות- עם מעבד אחד המערכת אינה יכולה לעבוד במקביל, ובחרה ב-Nested Loops במקום במקום ב-Hash Match.
כדאי לשים לב בנוסף שבאייקונים ב-Execution Plan הראשון מופיעים עגולים צהובים קטנים עם שני חיצים המציינים שמדובר בעבודה במקביל (בנוסף לאייקונים הספציפיים של ה-Parallelism.
ה-Tale Scan על הטבלה ללא האינדקס זהה בשני המקרים, גם מבחינת הסטטיסטיקה.
הפניה לטבלה עם ה-Clustered Index נראית לכאורה יקרה יותר בשליפה הראשונה (Scan) וממוקדת וזולה יותר בשליפה השניה (Seek) אלא שזו טעות אופטית: בשליפה הראשונה המערכת פנתה פעם אחת לטבלה עם ה-Clustered Index ושלפה ממנה את כל 97526 השורות, ובשליפה השניה המערכת פנתה אליה 97526 פעמים ושלפה בכל פעם שורה אחת.
עיון בסטטיסטיקה ממחיש כמה ממעט Logical Reads התבצעו בה במקרה הראשון וכמה הרבה במקרה השני.
מסקנה- לא לנסות על שרת הייצור שימוש ב-Hint הנ"ל, אבל מי שמסיבות מוצקות יותר מעוניין לשנות את הגדרות השרת – למשל לקבוע שמתוך 4 מעבדים ירוצו במקביל 3 לכל היותר ואחד ישאר פנוי למשימות אחרות, יכול כך (תגידו לבוס שלכם שקראתם על זה באיזה בלוג באינטרנט ותמחקו את ההיסטוריה שלא יוכלו למצוא אותי):
Exec SP_Configure 'Show Advanced Options', 1;
Go
Reconfigure With Override;
Go
Exec sp_ Configure 'Max Degree of Parallelism', 3;
Go
Reconfigure With Override;
Go
האם ה-Execution Plan השני הוא זה שיקבל מי שיש לו מעבד אחד על השרת?
למרבה המזל- לא, ולא ברור לי למה המערכת בחרה בו.
ניסיתי על שרת עם מעבד אחד ולהלן התוצאות:
(76530 row(s) affected)
Table 'messages'. Scan count 1, logical reads 2016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'messages_ClstInd'. Scan count 1, logical reads 2021, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
הסטטיסטיקה נראית טוב יותר מהקודמת, אך זה נובע מכך שבשרת הראשון מותקן SQL Server 2008 בו יש 97526 שורות בטבלת הודעות השגיאה, ובשרת השני מותקן SQL Server 2005 בו יש 76530 שורות בלבד..
מה שחשוב יותר זה ה-Execution Plan השפוי המתבסס על מיון הטבלה ללא האינדקס וביצוע Merge Join בין שתי הטבלאות.