שינויים בחישוב אוטומטי של סטטיסטיקות ב- SQL SERVER 2008 R2 SP1 ע"י TRACE FLAG 2371 - ilDBA Portal

שינויים בחישוב אוטומטי של סטטיסטיקות ב- SQL SERVER 2008 R2 SP1 ע"י TRACE FLAG 2371

08/12/2011 | פורסם על ידי

יש לכם טבלאות ענק ? שימו לב לשינויים בחישוב אוטומטי של ססטטיסטיקות ב- SQL SERVER 2008 R2 SP1 ע"י TRACE FLAG 2371

אתחיל בהסבר קצר מה זה בכלל סטטיסטיקות ולמה זה טוב .

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

התפלגות לדוגמא היא הצגה מתמטית של משהו כמו המשפט הבא: "שם המשפחה כהן מהווה כ-17% מסה"כ הערכים בעמודת שם משפחה בטבלת תושבי מדינת ישראל".
סטטיסטיקות על אינדקסים ועמודות מהוות חלק מכריע בהחלטה של ה-QUERY PROCESSOR איך ובאילו אינדקסים, דרכי חיפוש ו–JOIN תתבצע שאילתא מסוימת.
סטטיסטיקות לא מעודכנות עלולות לגרום לשאילתא לרוץ בדרך שאינה אופטימלית, שאילתות שאמורות לרוץ זמן קצר ירוצו זמן ארוך ו/או לניצול שגוי של משאבי זיכרון/מעבד/IO.

כאשר SQL Server הציג איסוף ועדכון אוטומטי של נתונים סטטיסטיים בגרסת SQL Server 7.0 ב- 1998, העיצוב הבסיסי היה שברגע שמספר השינויים בטבלה חרג מאחוז מסוים התבצע עדכון אוטומטי של נתונים סטטיסטיים בהתאם לאינדקסים והסטטיסטיקות שמוגדרים כרגע על טבלה מסוימת.

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

http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx

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

1.בטבלה יש לפחות 500 שורות (למנוע עידכוני סטטיסטיקות תכופים על טבלאות קטנות)

2.בטבלה עודכנו/נוספו שורות המהוות 20% מהסה"כ מספר השורות בטבלה.

כלל  מספר 2  נראה מאוד הגיוני לפני 15 שנה, כאשר המתכננים דמיינו שבמקרה הטוב, בסיסי נתונים יכילו מאות אלפי שורות או מיליוני שורות בודדות בטבלה בודדת. אבל בימינו, ימי הענן העליזים, כאשר בסיסי נתונים מכילים עשרות מיליונים, מאות מיליונים, מיליארדים ואף יותר שורות בטבלה בודדת, הכלל של עידכון אוטומטי רק אחרי 20% שינוי הוא בעייתי מאוד.

בואו נבחן דוגמא פשוטה:
בעולם הטלפוניה למשל טבלאות האוספות את נתוני השיחות (CDR) יכולות להגיע במשך זמן לגדלים עצומים  (המספרים שאציג הם פרי הדמיון בלבד). לדוגמא בחברת הטלפון "ME" נכנסות מיליון שורות ביום לטבלת CDR. אחרי שנתיים יש בטבלה כ- 700 מיליון שורות.
במצב כזה, סטטיסטיקות אוטומטיות יתעדכנו רק אחרי 140 יום  (20%  שינוי) ! לא מעשי כלל וכלל.

אם כך יקפצו החכמים בינינו יש לעדכן סטטיסטיקות בפקודה מפורשת ע"י "UPDATE STATISTICS" כל לילה.
נכון, זהו פתרון סביר ברוב במיקרים, אך יש לזכור שהמערכת המדוברת היא  מערכת 24/7 ועדכון סטטיסטיקות (תלוי כמובן בכמה אינדקסים וסטטיסטיקות מדובר) עלול לגרום לעומס ואפילו לנעילות. בנוסף לאמור לעיל, לעיתים, עדכון פעם ביום אינו מספיק.

לדוגמא בוא נניח שבמערכת שלי בחברת "ME" מבצעים לצורך מעקב שאילתות המסכמות דקות השיחה רק של היום הנוכחי (מתחיל ב00:00), בשאילתא שלי מצוין התאריך של היום והיא נראית בערך כך והרצתי אותה באמצע יום העבודה :

Select Sum(minutes),count(*)
from CDR
Where Date = '2011/11/22'

עכשיו בואו נזכור שהסטטיסטיקות האחרונות רצו לצורך העיניין אתמול בשעה 23:00, כך אם נבחן את תוכן הסטטסיטיקות (כיצד עושים זאת נראה בהמשך) נראה שהתאריך (של היום הנוכחי) כלל אינו מופיע בטבלת ההתפלגות.
במקרה זה, אנו מבחינים בהשפעה הקריטית על השאילתה, מכיוון של–QUERY PROCESSOR אין כלל ידע מהי ההתפלגות האמיתית (כחצי מיליון שורות לערך) הוא עלול לבחור ב-QUERY PLAN רע במיוחד.

מה שעוד יותר גרוע מבחינתנו, ששאילתות שבוחרות ב-PLAN גרוע במיוחד עושות את זה באופן בלתי צפוי בדרך כלל, וששאילתה כזו גם נוטה לצרוך משאבי IO  או CPU  מטורפים – מה שגם בדרך כלל חונק את השרת.
תופעה זו בעייתית ואין לה פתרון חד וחלק, פתרון אחד שראיתי הוא מעקב אחרי שינוי ב- QUERY PLANS של שאילתות, ועידכון סטטיסטיקות
ו-RECOMPILE. פתרון זה מצריך בדרך כלל ידע נרחב במימוש .

אז מה עושים?

ב- Sql Server 2008 R2 SP1 וב –Sql Server 2012, נוסף 2317   TRACE FLAG שישנה את שיעור קבוע של הסף 20% עבור עדכון הססטיסטיקה לאחוז דינמי.
ככל שמספר השורות בטבלה גדול יותר אחוז השינוי להפעלת עידכון אוטומטי נמוך יותר.
לדוגמה, בטבלה עם 1 מיליארד שורות  יתרחש עידכון סטטיסטיקות אוטומטי כבר אחרי שינוי 1 מיליון שורות.
אם לא הפעלת את ה- TF, באותה הטבלה עם מיליארד רשומות נצטרך 200 מיליון שינויים לפני עדכון הסטטיסטיקה האוטמטי .
הפעלת TF  זה אינה משנה את הכלל שעדיין חייבים להיות 500 שורות בטבלה ,כמו כן חשוב להבין שאחוז השורות הנדגמות עדיין נוהג לפי ה-DEFAULT ומחושב לפי כמות השורות בטבלה ,כך שמצבים שבהם ישנם שגיאות סטטיסטיות הנגרמות מנתונים שאינם מבוזרים בצורה שווה (SKEWED DISTRIBUTION) אינם נפתרים או משתנים עקב שימוש ב-TF זה.

בגרף להלן ניתן לראות כיצד את הנוסחה החדשה עובדת.
עם טבלאות  קטנות יותר, הסף עדיין יהיה סביב 20% אחוז. רק כאשר עולים מעבר  ל- 25,000 שורות מתחיל הכלל הדינמי לעבוד ,מכאן והלאה אחוז השינוי הדרוש להפעלת עידכון הסטטיסטיקות הולך וקטן , למשל בטבלה עם 100,000 שורות זה מצריך 10% בלבד. בטבלה עם 1,000,000 שורות זה מצריך שינויים של 3.2% כדי להפעיל את העידכון האוטומטי.

בטבלה עם 10000000 או 50000000 שורות, פחות מ 1% או 0.5% שינויים דרושים כדי להפעיל עדכון אוטומטי של סטטיסטיקה. או טבלה עם 100,000,000 שורות דרושים רק שינויים סביב 0.31% להפעיל את העידכון האוטמטי.
מהניסיון הקצר שהצטבר בינתיים לקוחות שמשתמשים ב-TF מדווחים על עידכוני סטטיסטיקות אוטומטיים בתדירות הגבוהה בין פי20 לפי 60 מאשר קודם.

בוא נראה דוגמא איך משפיע  עידכון סטטיסטיקות ועל QUERY PLANS :

לפנינו טבלה המכילה 16,000,000 שורות לערך ,במבנה הבא :

CREATE TABLE [dbo].[BigTable](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Descr] [varchar](100) NULL,
  [DT] [datetime2](0) NULL,
  [Hash1] [uniqueidentifier] NULL
 ) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [inx_id] ON [dbo].[BigTable]
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [inx_dt] ON [dbo].[BigTable]
(
  [DT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

שימו לב לאינדקס על שדה התאריך ,בואו נבחן מה מראה ההיסטוגרמה על האינדקס INX_DT

DBCC SHOW_STATISTICS ('bigtable','inx_dt') with stat_header,histogram

שימו לב לכמה דברים מענינים.
למרות שהאינדקס הכיל במקור סטטיסטיקות על דגימה מלאה של כל השורות בטבלה מרגע שבוצע עידכון סטטיסטיקות אוטומטי (או ידני) הדגימה מתבצעת על  דגימה של חלק משורות הטבלה במקרה שלנו  1.5% (247302  מתוך 16001389) ,הנה גרף ההתפלגות (חלק ממנו) :

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

שימו לב שבגרף ההתפלגות הסטטיסטי לימים 30/11 וה – 20/11  ההערכה הסטטיסטית היא די קרובה למציאות: 454000 הערכה לעומת 500000  לכל יום במציאות.

אם כך בואו ננסה לראות  מה יהיה ה-QUERY PLAN  של השאילתא  הבאה :

select * from BigTable
where dt between '2011/11/20 00:00:0' and  '2011/11/20 23:59:59'

ה-QP שלעיל אולי יפתיע  את חלקכם אבל במקרה הזה אנו מבקשים להחזיר כ-3% מכל הטבלה ואת כל העמודות בטבלה לכן הגיוני ששימוש באינדקס על התאריך INX_DT לא יהיה יעיל כי על כל שורה באינדקס יהיה צורך לבצע BOOKMARK LOOKUP  ל-CLUSTERD  INDEX מה שיגרום ל-הרבה יותר IO מאשר CLUSTERED INDEX SCAN.

ע"י שימוש ב- SET STATISTICS IO קיבלתי את כמות ה-IO  שביצעה השאילתא :

Table 'BigTable'. Scan count 1, logical reads 152656, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

עכשיו  אבצע INSERT של 500000 שורות ל- BIGTABLE  עבור תאריך '22/11/2011'

insert BigTable (Descr,DT ,Hash1 )
values ('statistics test ILDBA' ,'2011/11/22 14:00:00',newid())
go 500000

עכשיו בואו נריץ שאילתא זהה לשאילתא הקודמת רק נשנה את התאריך :

select * from BigTable
where dt between '2011/11/22 00:00:00' and  '2011/11/22 23:59:59'

למרות שגם כאן מדובר ב-500000 שורות ה-QP נראה שונה :

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

Table 'BigTable'. Scan count 1, logical reads 1532257, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

כפי שאתם רואים  כמעט פי 10  IO מאשר ה-PLAN הקודם .

לאחר עידכון סטטיסטיקות חזר ה-PLAN ל"קדמותו" ולהשתמש ב-CLUSTERED INDEX SCAN.

חזרתי על כל התהליך הפעם הוספתי ל-STARTUP PARAMETERS  של  SQL SERVER את הדגל: -T2371.
הפעם  לאחר ביצוע פעולת הINSERT, בפעם הראשונה שהרצתי את השאיתא התבצע עידכון סטטיסטיקות וה-PLAN  האופטימלי יותר נבחר.

לסיכום,

בוצע שינוי באופן החישוב אוטומטי של סטטיסטיקות בגרסת SQL SERVER 2008 R2 SP1 והלאה, ע"י TRACE FLAG 2371.
למי שעובד עם בסיסי נתונים גדולים, השינוי יכול להיות משמעותי עד מאוד ושווה להיות מודע אליו.

לפרטים נוספים ניתן לקרוא בלינק:

http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

נתראה ברשומה הבאה !

דני רביד.

2 תגובות ל- “שינויים בחישוב אוטומטי של סטטיסטיקות ב- SQL SERVER 2008 R2 SP1 ע"י TRACE FLAG 2371”

גרי רשף | 26/12/2011 בשעה 23:47
commenter

תודה רבה: למדתי כמה דברים חדשים!

david Itshak | 05/02/2012 בשעה 04:50
commenter

שלום רב
מוזמנים להרצאה שלי במיקרוסופט רעננה על sql 2012
http://Israel.sqlpass.org
 
חומר ייחודי מעניין (לא עוד adventure work כדוגמא )
להרצאות ה קודמות חפשו בגוגל : דוד יצחק  dba
 
שבת שלום
דוד יצחק
 
 

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

קהילת

קהילת ה- BI וה- BIG DATA מתכנסת ב- 28-10 !

שלום רב, למי שלא ידע 🙂 קהילת ה- BI וה- BIG DATA בעולמות Microsoft מתכנסת מידי חודש על מנת להפגש, להכיר ולשמוע הרצאות במגוון נושאים טכנולוגיים מרתקים בתחום. במפגש הקרוב (מספר 63) שיתקיים ב- 28-10-2015, יום רביעי [...]
הזמנה

הזמנה ל-SQL Saturday #481 – Israel 2016

שלום רב, בקרוב יתקיים בישראל כנס טכנולוגי מרכזי קהילת ה-DB וה-BI בתחום ה-SQL Server – ה-SQL Saturday! הכנס אשר מאורגן בהתנדבות על ידי אנשי הקהילה יכלול במהלכו מספר מסלולי לימוד בתחומים טכנולוגיים שונים. [...]

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]

תיעוד בסיס הנתונים – למה זה טוב?

ישנם מספר נושאים חשובים שיש לתת עליהם את הדעת בפיתוח ותכנון בסיס נתונים. כמובן שצריך לשים לב לנירמול נכון של הטבלאות, וכמובן שצריך להגדיר סטנדרט לכתיבה נכונה של הקוד, וכמובן שאי אפשר בלי [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: