none
שאלת ביצועים ואינדקסים RRS feed

  • שאלה

  • היי.

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

    יש לי טבלת לוג גדולה (1,065,063 רשומות). אני צריך לבצע select * (דגש על *, אין מנוס) על כל השורות מ-7 הימים האחרונים (221,833 רשומות).

    יש על הטבלה PK על עמודת ID (שהיא identity מן הסתם) ואינדקס על עמודת ה-timestmp.

    ה-SELECT הנ"ל לוקח יותר מדי זמן (דקה פלוס), ומהתבוננות ב-EXECUTIOM PLAN רואים שהאינדקס על timestmp לא בשימוש, אלא מתבצע SCAN על ה-PK.

    ה-OPTIMIZER מעריך שיחזרו 180770 שורות. אולי בגלל זה הוא מעריך שיהיה לו טוב יותר לא להשתמש באינדקס. (?)

    מה שאני חושב לעשות זה עבודה בשני שלבים:

    declare @ID int
    select top 1 @ID = id from LogTable where Timestmp > dateadd(DAY, (-7), GETDATE()) order by Timestmp
    select * from LogTable where id >= @ID

    במקרה הזה ההשמה ל-@ID לוקחת 0 זמן, וב-SELECT יש SEEK על ה-PK.

    כאשר אני מריץ את 2 האפשרויות יחד, האפשרות הראשונה לוקחת 77% מהזמן, כלומר שכנראה שהשניה אכן עדיפה.

    אשמח לקבל תגובות, הצעות, הסברים וכו'. תודה!


    itaigitt, http://copypastenet.blogspot.com

    יום רביעי 28 מרץ 2012 09:21

תשובות

  • מאמין שאתה לא עובד עם גרסאת Enterprise אבל במידה וכן אז הפתרון האידיאלי רוב הסיכויים עבורך זה להפוך את הטבלה לPartitioned ברמה שבועית (כל פרטישיין יכיל 7-8 ימים)

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

    לחשוש לפעולות סוויצ'.

    בקיצור מקרה קלאסי ליצירת פרטישנים.


    במידה ולא, אז כמו שכבר אמרו לפני אז לא הייתי מוריד את הPK כי זה באמת עלול לפגוע באמינות של הנתונים, אבל כן לשקול להחליף תפקידים בין 2 האינדקסים הקיימים:
    להפוך את הPK לNon clustered ואת הNon על הTime להפוך לClustered בגלל שאתה עושה עליו שליפות Range.
    וכאן למרות שהID אידאלי להיות Clustered מבחינת ההכנסות וגרימה לPage splits (מספר רץ + Key קטן), עדיין עדיף לשנות כנראה בגלל הסוג שליפה שאתה מבצע.
    אבל שוב, זה לא מוחלט הכל תלוי בכמה פעמים אתה מבצע את השליפה הזאת לעומת שאר השליפות שמתבצעות אל מול הטבלה.
    ככה שאם זאת השליפה היחידה אז כן הדבר נכון כנראה לעשייה, אבל,
    במידה ויש עוד הרבה מאוד סוגי שליפות על הטבלה ובאופן תדיר הרבה יותר מהשליפה הזאת שמתבצעית דווקא על הID הפתרון כנראה לא יהיה הפתרון הנכון.
    ככה שצריך להכיר את השליפות שמתבצעות אל מול הנתונים בטבלה וברמת התדירות שלהן פחות או יותר.


    גם בנוגע לשאלה שלך לגבי השליפה מציאת הID על פי התאריך ואז לשלוף לפיו גם על זה ענו ובאמת לא נכון לעשות את זה כי אתה לא יכול להתחייב באמת שכל הIDs שנכנסו לאחר מכן
    הם באמת הID שאחרי הזמן של אותו הID, כי יכולים להכניס בצורה יזומה רשומות ישנות ולשנות את הIDENTITY כמו שכבר עמי ציין.
    בקיצור אוליי ברוב הפעמים זה יעבוד אבל יום אחד זה יחזיר נתונים שקריים/שגויים דבר שלא כדאי להסתכן בו ברוב הפעמים בתור DBA :]


    חיים פישנר.


    • נערך על-ידי haim fishner יום שישי 13 אפריל 2012 10:50 סידור הטקסט
    • הוצע כתשובה על-ידי haim fishner יום שישי 13 אפריל 2012 11:01
    • סומן כתשובה על-ידי itaigitt יום ראשון 15 אפריל 2012 05:29
    יום שישי 13 אפריל 2012 10:48
  • 1. הכל תלוי במערכת שלך ומה מבצעים בה, כלומר : אם ישנן שליפות אל מול הID הזה.
        תחשוב שאם הוא PK וישנן אליו FK מטבלאות אחרות כנראה שמתבצע JOIN בשבל כלשהו ביניהם ושם האינדקס עוזר.
        ככה שאם הID הזה באמת משמש לPK האינדקס יווצר ויהיה חייב להתקיים בלי שתבקש :)

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

    2. לגבי זמני הריצה משתנים זה לגיטימי והגיוני הכל תלוי בדפוסי הפעילות אל מול הDB, לדוגמא נגיד שבזמן הבוקר אין עדיין פעילות נרחבת אל מול הDB
        כך שהטבלה יכול להשאר בBuffer cache זמן רב ובמקום שכאשר תשלוף תצטרך לגשת לדיסק בכדי להביא את הנתונים הם ימצאו לך כבר בCache
        ולעומת זאת נגיד וישנה פעילות רבה יותר על הDB בשעות הצהריים והערב ככה שטבלאות אחרות נמצאות בBuffer cache אז תצטרך לקרוא את הנתונים
        שלמותם או חלקם מתוך הדיסק (איטי הרבה הרבה יותר)..

        זאת הייתה דוגמא לגבי המשאב של הMemory באותה מידה יכולים להשפיע כל שאר המשאבים של השרת (רשת/CPU וכו'...)

    בברכה,


    חיים פישנר.


    • נערך על-ידי haim fishner יום שישי 13 אפריל 2012 11:01
    • הוצע כתשובה על-ידי haim fishner יום שישי 13 אפריל 2012 11:01
    • סומן כתשובה על-ידי itaigitt יום ראשון 15 אפריל 2012 05:29
    יום שישי 13 אפריל 2012 11:00
  • היי איתי,

    1. הפתרון שלך מסוכן - יום אחד מישהו יעשה RESEED ל IDENTITY ואז תקבל תוצאות לא נכונות.

    2. חלק מהפתרונות האחרים שהוצעו כאן לשנות את המפתח הראשי מסוכנים אף יותר - לתקינות הנתונים...

    גם HINT לא יעזור כאן - האופטימיזר צודק בבחירה שלו כי עכשיו אתה מחזיר 20% מהטבלה, ואם אין COVERING INDEX העלות של ה LOOKUPS תהיה עצומה יחסית לסריקה מלאה.

    3. אני הייתי מנסה להפוך את המפתח הראשי ל NONCLUSTERED ולייצר CLUSTERED INDEX על טור התאריך\זמן.

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    • הוצע כתשובה על-ידי Eran Sharvit שבת 31 מרץ 2012 15:46
    • סומן כתשובה על-ידי Eran Sharvit יום שני 02 אפריל 2012 12:36
    יום חמישי 29 מרץ 2012 02:12
  • המיון והסינון בשאילתה שלך לא קשורים ל ID ולכן מה המשמעות של האינדקס עליו? וזהו עוד clustered index שהנתונים מסודרים פיזית לפי.

    1. אתה יכול בשאילתה המקורית לעבוד עם HINT כדי להכריח את השרת לעבוד עם האינדקס ולבדוק את התוצאה

    2. נסה להוריד את 2 האינדקסים (תבטל את PK) ותוסיף אינדקס אחד על שני העמודות (קודם הזמן ואחר כך ה ID) או אינדקס על עמודת הזמן בלבד. לעמודה הראשונה אתה יכול להוסיף IDENTITY בלי PK. אם אין שימוש באינדקס עדיין תעבוד עם HINT אבל בטח במצב כזה כבר יהיה שימוש באינדקס (אתה יכול גם לנסות לקבוע את עמודת ה timestamp כמפתח ראשי)

    3. הפתרון שלך אינו רע


    signature

    • סומן כתשובה על-ידי Eran Sharvit יום שני 02 אפריל 2012 12:36
    יום רביעי 28 מרץ 2012 13:42
    מנחה דיון

כל התגובות

  • המיון והסינון בשאילתה שלך לא קשורים ל ID ולכן מה המשמעות של האינדקס עליו? וזהו עוד clustered index שהנתונים מסודרים פיזית לפי.

    1. אתה יכול בשאילתה המקורית לעבוד עם HINT כדי להכריח את השרת לעבוד עם האינדקס ולבדוק את התוצאה

    2. נסה להוריד את 2 האינדקסים (תבטל את PK) ותוסיף אינדקס אחד על שני העמודות (קודם הזמן ואחר כך ה ID) או אינדקס על עמודת הזמן בלבד. לעמודה הראשונה אתה יכול להוסיף IDENTITY בלי PK. אם אין שימוש באינדקס עדיין תעבוד עם HINT אבל בטח במצב כזה כבר יהיה שימוש באינדקס (אתה יכול גם לנסות לקבוע את עמודת ה timestamp כמפתח ראשי)

    3. הפתרון שלך אינו רע


    signature

    • סומן כתשובה על-ידי Eran Sharvit יום שני 02 אפריל 2012 12:36
    יום רביעי 28 מרץ 2012 13:42
    מנחה דיון
  • כפי ש-Pituach ציין- יש ליצור אינדקס על עמודת התאריך,

    ולהשתדל שזה יהיה Covered Index, כלומר- שיכלול את כל העמודות.

    התאריך וה-Identity מתואמים, אך אין למערכת דרך לדעת זאת ולהשתמש בזה ולכן האינדקס על ה-Identity אינו שימושי.


    Geri Reshef http://gerireshef.wordpress.com

    יום רביעי 28 מרץ 2012 16:41
  • היי איתי,

    1. הפתרון שלך מסוכן - יום אחד מישהו יעשה RESEED ל IDENTITY ואז תקבל תוצאות לא נכונות.

    2. חלק מהפתרונות האחרים שהוצעו כאן לשנות את המפתח הראשי מסוכנים אף יותר - לתקינות הנתונים...

    גם HINT לא יעזור כאן - האופטימיזר צודק בבחירה שלו כי עכשיו אתה מחזיר 20% מהטבלה, ואם אין COVERING INDEX העלות של ה LOOKUPS תהיה עצומה יחסית לסריקה מלאה.

    3. אני הייתי מנסה להפוך את המפתח הראשי ל NONCLUSTERED ולייצר CLUSTERED INDEX על טור התאריך\זמן.

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    • הוצע כתשובה על-ידי Eran Sharvit שבת 31 מרץ 2012 15:46
    • סומן כתשובה על-ידי Eran Sharvit יום שני 02 אפריל 2012 12:36
    יום חמישי 29 מרץ 2012 02:12
  • הי,

    קטונטי אך אני מחזק את עמדתו של עמי.

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

    אין שום משמעות ל- ID והתאריך הוא אינקרמנטלי גם כן כך שאין סיבה לא להשתמש בו.

    יום טוב


    אסף שלם

    יום חמישי 29 מרץ 2012 08:02
  • היי ותודה לכולם.

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

    1. אם החלטנו לעשות CLUSTERED INDEX על טור התאריך\זמן, האם יש באמת צורך באינדקס נוסף על טבלת ה-ID?
    2. זמני הריצה משתנים לפי זמן ההרצה (בוקר\צהרים\ערב וכו'). אולי אין קשר לאינדקסים?

    תודה, איתי.


    itaigitt, http://copypastenet.blogspot.com

    יום ראשון 01 אפריל 2012 08:21
  • הי,

    אינדקס על ה- ID יעזור לך במידה ואתה משתמש בו בשאילתות אחרות אחרת אני לא רואה סיבה להגדיר עליו אינדקס.

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

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

    שבוע טוב


    אסף שלם

    יום ראשון 01 אפריל 2012 08:29
  • האינדקס על ה ID נדרש למפתח הראשי שלך.


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום שני 02 אפריל 2012 06:30
  • מאמין שאתה לא עובד עם גרסאת Enterprise אבל במידה וכן אז הפתרון האידיאלי רוב הסיכויים עבורך זה להפוך את הטבלה לPartitioned ברמה שבועית (כל פרטישיין יכיל 7-8 ימים)

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

    לחשוש לפעולות סוויצ'.

    בקיצור מקרה קלאסי ליצירת פרטישנים.


    במידה ולא, אז כמו שכבר אמרו לפני אז לא הייתי מוריד את הPK כי זה באמת עלול לפגוע באמינות של הנתונים, אבל כן לשקול להחליף תפקידים בין 2 האינדקסים הקיימים:
    להפוך את הPK לNon clustered ואת הNon על הTime להפוך לClustered בגלל שאתה עושה עליו שליפות Range.
    וכאן למרות שהID אידאלי להיות Clustered מבחינת ההכנסות וגרימה לPage splits (מספר רץ + Key קטן), עדיין עדיף לשנות כנראה בגלל הסוג שליפה שאתה מבצע.
    אבל שוב, זה לא מוחלט הכל תלוי בכמה פעמים אתה מבצע את השליפה הזאת לעומת שאר השליפות שמתבצעות אל מול הטבלה.
    ככה שאם זאת השליפה היחידה אז כן הדבר נכון כנראה לעשייה, אבל,
    במידה ויש עוד הרבה מאוד סוגי שליפות על הטבלה ובאופן תדיר הרבה יותר מהשליפה הזאת שמתבצעית דווקא על הID הפתרון כנראה לא יהיה הפתרון הנכון.
    ככה שצריך להכיר את השליפות שמתבצעות אל מול הנתונים בטבלה וברמת התדירות שלהן פחות או יותר.


    גם בנוגע לשאלה שלך לגבי השליפה מציאת הID על פי התאריך ואז לשלוף לפיו גם על זה ענו ובאמת לא נכון לעשות את זה כי אתה לא יכול להתחייב באמת שכל הIDs שנכנסו לאחר מכן
    הם באמת הID שאחרי הזמן של אותו הID, כי יכולים להכניס בצורה יזומה רשומות ישנות ולשנות את הIDENTITY כמו שכבר עמי ציין.
    בקיצור אוליי ברוב הפעמים זה יעבוד אבל יום אחד זה יחזיר נתונים שקריים/שגויים דבר שלא כדאי להסתכן בו ברוב הפעמים בתור DBA :]


    חיים פישנר.


    • נערך על-ידי haim fishner יום שישי 13 אפריל 2012 10:50 סידור הטקסט
    • הוצע כתשובה על-ידי haim fishner יום שישי 13 אפריל 2012 11:01
    • סומן כתשובה על-ידי itaigitt יום ראשון 15 אפריל 2012 05:29
    יום שישי 13 אפריל 2012 10:48
  • 1. הכל תלוי במערכת שלך ומה מבצעים בה, כלומר : אם ישנן שליפות אל מול הID הזה.
        תחשוב שאם הוא PK וישנן אליו FK מטבלאות אחרות כנראה שמתבצע JOIN בשבל כלשהו ביניהם ושם האינדקס עוזר.
        ככה שאם הID הזה באמת משמש לPK האינדקס יווצר ויהיה חייב להתקיים בלי שתבקש :)

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

    2. לגבי זמני הריצה משתנים זה לגיטימי והגיוני הכל תלוי בדפוסי הפעילות אל מול הDB, לדוגמא נגיד שבזמן הבוקר אין עדיין פעילות נרחבת אל מול הDB
        כך שהטבלה יכול להשאר בBuffer cache זמן רב ובמקום שכאשר תשלוף תצטרך לגשת לדיסק בכדי להביא את הנתונים הם ימצאו לך כבר בCache
        ולעומת זאת נגיד וישנה פעילות רבה יותר על הDB בשעות הצהריים והערב ככה שטבלאות אחרות נמצאות בBuffer cache אז תצטרך לקרוא את הנתונים
        שלמותם או חלקם מתוך הדיסק (איטי הרבה הרבה יותר)..

        זאת הייתה דוגמא לגבי המשאב של הMemory באותה מידה יכולים להשפיע כל שאר המשאבים של השרת (רשת/CPU וכו'...)

    בברכה,


    חיים פישנר.


    • נערך על-ידי haim fishner יום שישי 13 אפריל 2012 11:01
    • הוצע כתשובה על-ידי haim fishner יום שישי 13 אפריל 2012 11:01
    • סומן כתשובה על-ידי itaigitt יום ראשון 15 אפריל 2012 05:29
    יום שישי 13 אפריל 2012 11:00
  • תודה חיים.

    אני כן עובד על Enterprise, ולכן אשקול גם ברצינות partitioning.


    itaigitt, http://copypastenet.blogspot.com

    יום ראשון 15 אפריל 2012 05:30
  • אם אתה בעיקר כותב לטבלה ורק מעט קורה ממנה, כמו המון טבלאות לוג.

    אזי לא כדאי כלל קלסטרד כי אתה יותר מזיק ממועיל. אתה תקצר את השאילתא אבל תעמיס על השרת ותגרות זמן עדכון גבוהה יותר.

    יום רביעי 18 אפריל 2012 18:10