מאמר זה הוא מאמר שלישי בנושא – מומלץ לקרוא קודם את חלק 1 ו- חלק 2
בדיקת סטטיסטיקות של Top Wait events
Isolation Level
כל הדוגמאות שפונות ל- Data Management Views) DMVs) מתחילות בקוד SQL שקובע את ה- Isolation level ל- READ UNCOMMITED. כך מובטח שיהיה אפשר לקרוא נתונים בצורה מהירה ללא המתנה לשחרור הנעילות או ביצוע נעילות באופן הבא:
SET TRAN ISOLATION LEVEL READ UNCOMMITTE
בדיקת Top Wait events וזיהוי bottlenecks
Top Wait Event שהסתיימו בלבד, נשמרים ב- (Data Management View (DMV בשם sys.dm_os_wait_stats, של MSSQL גרסאות 2005/2008/2012/2014. ה- DMV שומר סטטיסטיקות מידע שמצטברות לגבי ההמתנה של ה- threads של SQL Server ע"פ הבא:
Resource Waits– המתנה למשאבים לא זמינים או תפוסים ע"י thread אחר כמו רשת, נעילות, latches, דיסקים ועוד.
External Waits – קריאה והמתנה לסיום הרצה של קוד חיצוני. למשל דרך Linked Server, extended stored procedure.
Queue Waits – המתנה של תהליכי רקע לקבלת משימה והשמה בתור של תהליכים שפעילים. למשל: deadlock monitor, LAZYWRITER ועוד.
בכל פעם שמבוצע restart ל- MSSQL או מבוצע איפוס של הסטטיסטיקות באופן ידני (ע"י DBCC SQLPERF ראה בהמסך), המידע מתחיל להיאסף מחדש ב- DMV ע"י MSSQL . MSSQL מקטלג את ה- WAITS למספר סוגים. לצערי אין עוד מנגנון מובנה כמו ב- Oracle, שמאפשר לקיחת snapshots של נתונים סטטיסטיים של ה- DB וביצוע דוחות השוואתיים בין 2 snapshots או יותר.
השלבים :
1. במידה והתחלנו תהליך כוונון חדש, בשביל למצוא את ה-TOP WAIT EVENTS נאפס קודם כל את ה- DMV sys.dm_os_wait_stat.
DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR)
פלט לדוגמא :
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2. הרץ את השאילתה מתוך SSMS כל מספר שניות במשך מספר דקות מול הסביבה הנבדקת.
/* Isolate top waits for server instance since last restart or statistics clear ============================================================================= */ SET TRAN ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 15 wait_type, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE',N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE' ,N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'ONDEMAND_TASK_QUEUE', N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'SP_SERVER_DIAGNOSTICS_SLEEP', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE' ) ORDER BY wait_time_ms DESC
3. שים לב שהערך בשדה wait_time_ms בפלט גדל מפעם לפעם. פלט לדוגמא:
הסבר : השאילתה מביאה את 15 ה-Wait Events הארוכים ביותר שממתינים למשאב של ה- threads של MSSQL. הזמנים הם זמנים מצטברים מאז בוצע אתחול ל- MSSQL או איפוס ע"י DBCC SQLPERF. תפקיד ה- WHERE לסנן כל ה- Wait Events שהם לא חיצוניים ל- MMSQL או שאינם קשורים להמתנה למשאבים (תהליכי רקע של SQL Server).
פירוט עבור ה-Wait Events של SQL 2008 ניתן למצוא תחת ה- White Paper הבא של מיקרוסופט: http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx. פירוט עבור ה-Wait Events של SQL 2012\2014 ניתן למצוא תחת sys.dm_os_wait_stats ב- SQL 2014 books online.
הערה : ה- events החדשים ב- SQL 2014 שבשאילתה זו הם:
- QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
- QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
- WAIT_XTP_HOST_
- WAIT_XTP_OFFLINE_CKPT_NEW_LOG
- WAIT_XTP_CKPT_CLOSE
בחלקים הבאים של המאמר נדגים איך מנתחים את הפלט של השאילתה שמביאה את ה-Wait Events הארוכים ביותר ומה עושים בכל אחד מהמקרים.
דוד יצחק
Latest posts by דוד יצחק (see all)
- MongoDB ל DBA ומפתחים הלכה למעשה – חלק ב - 16/02/2016
- MongoDB ל DBA ומפתחים הלכה למעשה - 07/02/2016
- בדיקת ביצועים של Clustered ColumnStore Index - 06/11/2014
השאר תגובה: