הבלוג של גרי רשף

23/06/2010

Execution Plan: שליפה, פילטר ומיון בעזרת אינדקס

נפתח בדוגמה- נציג את ה-Actual Execution Plan יל ידי Ctrl M ונריץ את הקוד הבא:

Use AdventureWorks;

Go


Set Statistics IO On;


Select    ProductID

From    Production.TransactionHistoryArchive;

Go

בלשונית Messages נקבל את הפלט הבא פחות או יותר-

(89253 row(s) affected)

Table 'TransactionHistoryArchive'. Scan count 1, logical reads 124, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

בהרצה הראשונה יתכן ויהיו physical reads מכיוון שיש לקרוא את ה-Pages הרלוונטיים ל-Buffer, אך לאחר מכן הם יעלמו והקריאה תהיה רק מה-Buffer (מתבטא ב-Logical Reads).

Scan count שווה 1 מכיוון שהתבצע Scan אחד של כל האינדקס, כפי שניתן לראות ב-EP:

image

התבצע Index Scan מכיוון שיש להציג רק את Product_Id ואין צורך בשאר העמודות בטבלה.

מה יקרה אם נפלטר לפי עמודה זו?

Select    ProductID

From    Production.TransactionHistoryArchive

Where    ProductID<=100;

Go

(803 row(s) affected)

Table 'TransactionHistoryArchive'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

image

במקום Index Scan מתבצע Index Seek: המערכת מבצעת Scan עד לערך 100 אותו היא מחפשת כתנאי עצירה. זו הסיבה שמופיע Scan Count 1.

גם אם בפילטר נכתוב Where    ProductID<=1000 – תנאי שאמור להחזיר את כל הרשומות – יתבצע Index Seek, אך הביצוע יהיה כמו ב-Index Scan, כלומר- Scan count 1, logical reads 124.

אם ב-Select נשלוף את כל העמודות שבטבלה:

Select    *

From    Production.TransactionHistoryArchive;

Go

(89253 row(s) affected)

Table 'TransactionHistoryArchive'. Scan count 1, logical reads 622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

image

מתבצע Clustered Index Scan ל-Clustered Index של הטבלה (Table Scan מתבצע כשאין Clustered Index),

ואין כל צורך באינדקס שעל עמודה ProductId.

אם נשלוף שוב את כל העמודות ונפלטר לפי ProductId אזי אחת משתיים:

Select    *

From    Production.TransactionHistoryArchive

Where    ProductID<=2;

Go


Select    *

From    Production.TransactionHistoryArchive

Where    ProductID<=100;

Go

image

כאשר המערכת צופה מספר קטן של שורות מהטבלה (11)- הדרך המועדפת היא לבצע Index Seek על האינדקס של ProductID,

ועל כל אחד מהערכים לבצע Key Lookup על ה-Clustered Index כדי לשלוף את ערכי העמודות האחרות.

כאשר צפוי מספר רב של שורות (803)- המערכת תעדיף לעבור על כל הטבלה באמצעות Clustered Index Scan ולשלוף את השורות שעונות על התנאי מבלי להשתמש באינדקס של ProductID.

כיצד המערכת יודעת כמה שורות צפויות לחזור?

קיימת סטטיסטיקה לגבי ההתפלגות של ערכי ProductID שניתן לקבל אותה על ידי-

DBCC Show_Statistics("Production.TransactionHistoryArchive",IX_TransactionHistoryArchive_ProductID)

או ב-SSMS על ידי קליק ימני על הסטטיסטיקה של האינדקס:

image

כאשר התנאי הוא 2=> אזי המערכת מחשבת 11=5+6+0 (0 = ערכים קטנים מ-1, 6= ערכים שווים ל-1, 5= ערכים קטנים מ-3).

כאשר התנאי הוא 100=> אזי המערכת מחשבת 803=6+786+5+6+0 (עלול להיות כאן אי דיוק והטייה כלפי מעלה מכיוון שהסטטיסטיקה מאפשרת חלוקה ל-200 מרווחים לכל היותר).

אפשר לראות ב-EP שעבור 100=> המערכת ממליצה להוסיף אינדקס על ProductID שיכלול גם את שאר העמודות, וכך Index Scan עליו יוכל להחזיר את כל העמודות ללא צורך ב-Key Lookup.

אם נוסיף לשליפות מיון:

Select    *

From    Production.TransactionHistoryArchive

Where    ProductID<=2

Order By ProductID;

Go


Select    *

From    Production.TransactionHistoryArchive

Where    ProductID<=100

Order By ProductID;

Go

image

עבור 2=> אין שום שינוי מכיוון שהשליפה ממילא ממויינת (Index Seek נעשה במעלה האינדקס),

ועבור 100=> יש צורך במיון של מה שנשלף מה-Clustered Index.

לסיום לא לשכוח "לכבות" את מדידת הסטטיסטיקה:

Set Statistics IO Off;

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: