July 27, 2015 at 6:43 am
What wait types do we need to analyse the slowness of queries in sql by using the dmv sys.dm_os_wait_stats
July 27, 2015 at 7:50 am
There isn't any specific wait type(s) to look for when you have slow queries. When you have a slow query, check what it is waiting on and proceed from there.
You can use the code below to identify if anything is currently waiting.
SELECT
[owt].[session_id],
[owt].[wait_duration_ms],
[owt].[wait_type],
[owt].[blocking_session_id],
[es].program_name,
CASE [owt].[wait_type]
WHEN N'CXPACKET' THEN
RIGHT ([owt].[resource_description],
CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
ELSE NULL
END AS [Node ID],
[est].text,
[er].[database_id],
[eqp].[query_plan]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_os_tasks [ot] ON
[owt].[waiting_task_address] = [ot].[task_address]
INNER JOIN sys.dm_exec_sessions [es] ON
[owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
[es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
[es].[is_user_process] = 1
ORDER BY
[owt].[session_id],
[owt].[exec_context_id];
July 27, 2015 at 11:16 am
Wait types tell you what's causing things to run slow on the system, what it's waiting on. That doesn't tell you what query to look at or what to do about that query. Instead, you have to look to other resources for that information (although, using Extended Events, you can also track and correlates the waits experienced by a certain query).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply