מרץ28
Written by:
ronen ariely
28/03/2013 18:39 
כניסה למצב המתנה - רקע
כניסה למצב המתנה הוא מצב בו טרנזקציה שנמצאת בהרצה נכנסת להשהיה (עצירה זמנית). אם אתם מריצים שאילתה שלפעמים רצה מהר ולפעמים נראה כאילו נתקעת למשך זמן רב אז ייתכן שהשאילתה (או בכללית הטרנזקציה המכילה אותה) נכנסה למצב המתנה. ישנן סיבות רבות שיכולות לגרור כניסה למצב המתנה החל מפעולות אחרות על מסד הנתונים כמו שאילתות אחרות היוצרות נעילה על אותם נתונים, פעולות בשרת SQL עצמו שאינן קשורות ישירות במסד הנתונים הספציפי (ולכן אם אתם עובדים בשרת משותף ייתכן שאין לכם בכלל דרך לנטר את הנושא), ועד לסיבות שאינן קשורות בכלל לשרת כי אם לאפליקציות אחרות היושבות על המחשב ומשתתפות באותם משאבים.
מייקרוסופט סיפקו לנו DMV בשם DM_OS_WAIT_STATS המספק לנו אינפורמציה כללית על מצבי ההמתנה השונים שהיו בשרת מרגע ששירות האפליקציה החל לעבוד.
נציץ ב DMV הבא:
SELECT * FROM SYS.DM_OS_WAIT_STATS
GO
אם רוצים לנטר את השרת אז כדאי תחילה לאפס את הנתונים ורק אז להתחיל בניטור יעיל.
ניקוי נעשה בעזרת השאילתה הבאה:
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
באופן כללי ניתן לחלק את סוגי ה"המתנות" לשלוש:
* המתנה למשאב מסוים בעקבות "תפיסה" שלו למשל נעילה על נתונים שרוצים לגשת אליהם. זהו המצב הכי נפוץ למיטוב בעיות ביצועים.
* המתנה לפעולה חיצונית.
* המתנה בתור בדרך כל חלה על משימות הפועלות ברקע כגון פעולות תחזוקה שונות שהשרת מפעיל מאחורי הקלעים. בדרך כלל אין לנו צורך לדאוג לחלק זה.
אנחנו צריכים לצפות למצב של המתנה במערכת עמוסה. זה נורמאלי לחלוטין ולא בהכרח נובע מבעיית ביצועים. כניסה למצבי המתנה הופכים לבעיה אם הם נוטים להיות ארוכים באופן עקבי על פני תקופה משמעותית של זמן. לדוגמה, ריצה של שעה עם זמן המתנה של כמה אלפיות שנייה לא תעניין אותנו. אבל מצב של כניסה להמתנה של 15 דקות בריצה שלוקחת 20 דקות צריכה להיחקר באופן הדוק יותר.
בקישור הבא (Book On Line) ניתן לראות כיצד עובדים עם ה DMV הנ"ל וכן טבלה יבשה כללית של סוגי מצבי ההמתנה השונים. סוג מצב ההמתנה המתואר בטבלה הוא זה המתקבל בעמודת wait_type ב DMV. ליד כל סוג סיפקו לנו במייקרוסופט תיאור כללי מאוד של משפט אחד שבדרך כלל אינו מספיק להבין מה קרה, מדוע קרה וכיצד לטפל בנושא למשל.
http://technet.microsoft.com/en-us/library/ms179984.aspx
שאילתות לניטור מצבי המתנה:
1. השאילתה הבאה מאפשרת לבדוק מה המצב כרגע בשרת ולתפוס את מצבי המתנה הקיימים כרגע עם אינפורמציה נוספת לניטור.
DECLARE @OpenQueries TABLE (cpu_time INT, logical_reads INT, session_id INT)
INSERT INTO @OpenQueries(cpu_time, logical_reads, session_id)
select r.cpu_time ,r.logical_reads, r.session_id
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
where is_user_process = 1
and s.session_id <> @@SPID
waitfor delay '00:00:01'
select
substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text) else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
, r.cpu_time-t.cpu_time as CPUDiff
, r.logical_reads-t.logical_reads as ReadDiff
, r.wait_type
, r.wait_time
, r.last_wait_type
, r.wait_resource
, r.command
, r.database_id
, r.blocking_session_id
, r.granted_query_memory
, r.session_id
, r.reads
, r.writes, r.row_count, s.[host_name]
, s.program_name, s.login_name
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
left join @OpenQueries as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
where is_user_process = 1
and s.session_id <> @@SPID
order by 3 desc
2. מציאת סוגי מצבי ההמתנה הארוכים ביותר שהיו בשרת מאז שהשירות של השרת הופעל:
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS (
SELECT
wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
)
)
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
OPTION (RECOMPILE);
GO
3. שימוש בפרוצדורה השמורה המובנית sp_who על מנת למצוא נתונים על כל השאילתות הרצות כרגע בשרת.
4. שימוש בפרוצדורה החדשה יותר sp_who2
5. שימוש בשאילתה sp_WhoIsActive של אדם מקניק (Adam Machanic). הסבר מקיף על השמוש בפרוצדורה זו ניתן לראות כאן:
http://www.littlekendra.com/2011/02/01/whoisactive
הסבר על סוגי מצבי ההמתנה השונים:
ישנם הרבה מעל ל 500 סוגים שונים של מצבי המתנה שמקבלים חזרה בשאילתה על DM_OS_WAIT_STATS. אם נרצה לכתוב מדריך מעמיק על כל המצבים אז יהיה עלינו לכתוב ספר כבד מאוד. מדריך זה ישמש כמדריך דינאמי בו אני אוסיף מפעם לפעם הסברים על מצבי המתנה בהתאם לשאלות שיועלו בפורומים ולזמן שיהיה לי (וכמובן לחשק).
מייקרוסופט לא משחררים לנו מידע מלא על השרת ועל העבודה שלו מאחורי הקלעים בהיותו מערכת קוד סגור. לעיתים ההסברים התיאורטיים הרשמיים כאמור אינן מספיקים וכאן אנו מפעילים שיקול דעת המבוסס על התיאוריה והניסיון שלנו. בכל מצב המתנה בו אדון אני ארשום את ההסבר המקוצר של מייקרוסופט, את התובנות השונות שמבוססות בעיקר על הניסיון שלי ושל אחרים (בשביל זה יש לנו את גוגל) וכמובן את הנקודות שנראות לי כחשובות בתקווה שהן יוכלו לעזור.
Sleep_Task
התיאור הרשמי:
Occurs when a task sleeps while waiting for a generic event to occur.
הסבר:
מצב המתנה SLEEP_TASK הוא מצב המתנה כללי של כניסה להמתנה שלא בעקבות מצבי המתנה ספציפיים יותר (ישנם כמה מצבי המתנה כלליים). מצב זה יכול לקרות כאשר יש לנו פעולות מורכבות כגון פעולות גיבוב (Hashing) שעושות שימוש ב tempdb. השלב בו "שופכים את הנתונים" אל הטבלה הזמנית והפוך מהטבלה הזמנית, אל ומתוך הזיכרון.
בקישור הבא ניתן לצפות בהרצאה ברמה 400 (הרצאה מקצועית ל DBA) הכוללת הדגמה מעשית של שחזור מצב המתנה זה (ואחרים) והסבר קצר על מקרה מסויים היוצר המתנה מסוג זה. שידור 3 מספק דרכים לזהות בעיות ביצוע הקשורות פעולות התאמת Hash שיצרו צורך בשפיכת הנתונים אל או מ tempdb. שידור זה מסביר כיצד סוג המתנת SLEEP_TASK קשור לאזהרות Hash, ומסביר את הסיבה לבעיית ביצועים וכיצד לזהות את הבעיה עם סוג המתנת SLEEP_TASK.
http://www.sqlworkshops.com/cpupressure.htm
.
קישורים:
sys.dm_os_wait_stats (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms179984.aspx
SQL SERVER – PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type:
http://blog.sqlauthority.com/2011/02/12/sql-server-pagelatch_dt-pagelatch_ex-pagelatch_kp-pagelatch_sh-pagelatch_up-wait-type-day-12-of-28/
Misc DMV queries
http://sqlserverpedia.com/wiki/Misc_DMV_queries
Adam Machanic's excellent sp_WhoIsActive stored proc:
http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/
Using sp_WhoIsActive
http://www.littlekendra.com/2011/02/01/whoisactive/
Wait Types
http://sqlserverpedia.com/wiki/Wait_Types#What_Are_SQL_Server_Waits.3F
CPU Pressure
http://www.sqlworkshops.com/cpupressure.htm
Glenn Berry (Blog|Twitter) excellent resource pack of SQL Server 2008 Diagnostic Queries
.
.