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

15/09/2010

סטטיסטיקה על עמודות לא מאונדקסות

SQL Server שומר מידע סטטיסטי לגבי עמודות בטבלאות: בדרך כלל, בעת שמתבצעת לראשונה שליפה עם תנאי Where על עמודה-המערכת מחשבת סטטיסטיקה לגביה, ועושה בה שימוש בהמשך ליעול השליפות.
הסטטיסטיקה היא מידע לגבי התפלגות הנתונים לפי טווחים, כלומר- היסטוגרמה (לא גרף אלא טבלה שניתנת לתיאור גרפי כהיסטוגרמה), ובה עד 200 שורות. אלו לא הנתונים המדוייקים – בשביל זה יש טבלה – אלא תצוגה סיכומית שמאפשרת להעריך כיצד הנתונים מפוזרים.
למשל- אם יש טבלת עובדים ובה עמודת גובה, הסטטיסטיקה תאפשר למערכת לדעת שמעט עובדים יכולים להיות בגובה 200 ס"מ, ושמספר רב של עובדים הם בין 160 ס"מ ל-170 ס"מ. כמובן שהמערכת אינה פטורה מלבדוק בעצמה, אך בעזרת הסטטיסטיקה תוכל להחליט אם לבצע Scan מלא על הטבלה (כשיש הרבה) או לבצע Seek (כשיש מעט).
הנתונים משתנים ואיתם גם הסטטיסטיקה, וכשאחוז משמעותי מהנתונים משתנה על ידי פקודות Delete/Insert/Update – הסטטיסטיקה מתעדכנת; בדרך כלל לאחר שכ-25% מהנתונים השתנו.
בצד הסטטיסטיקה על העמודות, יש סטטיסטיקה גם על האינדקסים, אך לא אכתוב על זה כאן.
יצירת הסטטיסטיקה (is_auto_create_stats_on) ועדכונה (is_auto_update_stats_on) מתבצעים אוטומטית בדרך כלל, אלא אם כן הוגדר אחרת לגבי הדטבייס או הטבלה, וניתן לראות זאת כך:

Select name, is_auto_create_stats_on,is_auto_update_stats_on From sys.databases Where database_id=db_id();

Go

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

--ALTER DATABASE tempdb SET AUTO_UPDATE_STATISTICS Off;

--ALTER DATABASE tempdb set AUTO_CREATE_STATISTICS Off;

Go

ניצור טבלה להדגמה:

Use tempdb;

Go

If object_id('SalesOrderDetail') Is Not Null Drop Table SalesOrderDetail;

Go

CREATE TABLE SalesOrderDetail(SalesOrderID int,

                                                        SalesOrderDetailID int,

                                                        CarrierTrackingNumber nvarchar(25) NULL,

                                                        OrderQty smallint,

                                                        ProductID int,

                                                        SpecialOfferID int,

                                                        UnitPrice money,

                                                        UnitPriceDiscount money,

                                                        LineTotal money,

                                                        rowguid Varchar(Max) ,

                                                        ModifiedDate datetime)

Go

Insert Into        SalesOrderDetail

Select        *

From        AdventureWorks.Sales.SalesOrderDetail;

Go

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

Exec SP_HelpStats 'SalesOrderDetail', 'ALL'

Select         Object_Name(Object_Id) Tbl,

                Stats_Date(Object_Id, stats_id) As [Stats_Date],

                *

From        sys.stats

Where        Object_Id = Object_Id('SalesOrderDetail');

Select        *,

                Object_Name(Object_Id) Tbl,

                Stats_Date(Object_Id, index_id) As [Stats_Date]

From        sys.indexes

Where        Object_Id = Object_Id('SalesOrderDetail');

Select        Object_name(id),

                rowmodctr,

                *

From        sysindexes

Where        Object_name(id)='SalesOrderDetail'

DBCC Show_Statistics(SalesOrderDetail,'SalesOrderDetailID') With Histogram;

image

SP_HelpStats מציין שאין סטטיסטיקה,

בטבלה sys.Stats אין נתונים כלל לגבי טבלה זו,

בטבלה sys.Indexes יש שורה אחת לגבי הטבלה המציינת שזו טבלה ללא Clustered Index (כלומר- Heap),

בטבלה sysindexes יש גם שורה אחת כאשר מצויין בה rowcnt – מספר השורות בטבלה (121317), ו-RowModCtr מונה מספר השורות שהשתנו עד כה (121317 שהתווספו),

ופקודת DBCC Show_Statistics מחזירה שגיאה כי אין סטטיסטיקה.

נבצע שליפה עם תנאי על עמודה SalesOrderDetailID:

Select   *

From     SalesOrderDetail

Where    SalesOrderDetailID<=10000

Order By SalesOrderID,

         SalesOrderDetailID;

Go

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

image

HelpStats מציין שיש סטטיסטיקה על עמודה SalesOrderDetailID,

בטבלה sys.Stats מצויין שיש סטטיסטיקה מתאריך 15/09/2010 שעה 16:29:36.060(השם מתחיל ב-_WA_ מציין שזו סטטיסטיקה על עמודה ולא שם של אינדקס),

בטבלה sys.Indexes אין שינוי משמעותי,

בטבלה sysindexes נוספה שורה עבור העמודה החדשה, ולה מונה מספר השינויים שחלו בה (בעמודה):RowModCtr המציין ש-0 שינויים חלו בה,

ופקודת DBCC Show_Statistics מציגה את ההיסטוגרמה, כשמהשורה הרביעית – למשל – ניתן ללמוד על הטווח 1742-2870 (מהשורה הקודמת – לא כולל, ועד 2870 כן כולל): יש שם הערכות לא מדוייקות המתבססות על דגימה כמה ערכים וכמה ערכים שונים יש בטווח (בפועל בעמודה הזו מופיעים ברצף המספרים 1-121317 ללא רווחים וכפילויות).

נעדכן 30000 שורות בטבלה ונוסיף להן מיליון:

Update       SalesOrderDetail

Set          SalesOrderDetailID=SalesOrderDetailID+1000000

Where        SalesOrderDetailID<=30000;

Go

והפלט של בדיקת הסטטיסטיקה:

image

HelpStats מציין שיש סטטיסטיקה על עמודה SalesOrderDetailID,

בטבלה sys.Stats לא חל שינוי והסטטיסטיקה היא מאותה שעה בה נוצרה לראשונה,

בטבלה sys.Indexes אין שינוי משמעותי,

בטבלה sysindexes המונה RowModCtr גדל ב-30000 הן ברמת הטבלה והן ברמת העמודה,

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

נעדכן שוב את העמודה ונוסיף מיליון לעוד 30000 שורות:

Update       SalesOrderDetail

Set          SalesOrderDetailID=SalesOrderDetailID+1000000

Where        SalesOrderDetailID<=60000;

Go

והפלט של בדיקת הסטטיסטיקה:

image

HelpStats מציין שיש סטטיסטיקה על עמודה SalesOrderDetailID,

בטבלה sys.Stats מועד יצירת הסטטיסטיקה השתנה כי נוצרה כעת סטטיסטיקה חדשה,

בטבלה sys.Indexes אין שינוי משמעותי,

בטבלה sysindexes המונה RowModCtr גדל ב-30000 ברמת הטבלה, וברמת העמודה – נוצרה סטטיסטיקה חדשה ואחריה השתנו עוד 30000 שורות,

ופקודת DBCC Show_Statistics מציגה את ההיסטוגרמה החדשה שנכונה לנקודת הזמן שלפני תחילת העדכון השני ולכן היא מתחילה מ-30001 (בפועל הטבלה מתחילה מ-60001).

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

תגובה אחת »

  1. תודה רבה

    תגובה של חיים — 15/09/2010 @ 22:45


RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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