מרץ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
.
.