Excessive waits showing up in Performance Dashboard

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  March 11, 2014 12:44PM

  SQL Monitor is using timed waits to space out the sampling so there is less impact on the server. WAITFOR is a SQL command you can use in a script that will pause it for the specified period of time.
  
  I'm afraid I don't know how to exclude this from your Microsoft monitoring tool.

  0 · Share on Twitter
• Options

  Lxocram Posts: 25 Bronze 3

  March 12, 2014 4:30AM

  LTER PROC MS_PerfDashboard.usp_RequestWaits
  as
  begin
  select r.session_id,
  r.request_id,
  master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
  master.dbo.fn_varbintohexstr(r.plan_handle) as plan_handle,
  case when LEN(qt.query_text) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N'...' end as query_text,
  r.statement_start_offset,
  r.statement_end_offset,
  r.wait_time,
  r.wait_type,
  r.wait_resource,
  msdb.MS_PerfDashboard.fn_WaitTypeCategory(wait_type) as wait_category
  from sys.dm_exec_requests r
  join sys.dm_exec_sessions s on r.session_id = s.session_id
  outer apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(r.sql_handle, r.statement_start_offset, r.statement_end_offset) as qt
  where r.wait_type is not null and s.is_user_process = 0x1
  AND r.wait_type<>'WAITFOR'

  0 · Share on Twitter
• Options

  Lxocram Posts: 25 Bronze 3

  March 12, 2014 4:35AM

  ALTER PROC MS_PerfDashboard.usp_Main_GetRequestWaits
  as
  begin
  SELECT
  r.session_id,
  MS_PerfDashboard.fn_WaitTypeCategory(r.wait_type) AS wait_category,
  r.wait_type,
  r.wait_time
  FROM sys.dm_exec_requests AS r
  INNER JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id
  WHERE r.wait_type IS NOT NULL
  AND s.is_user_process = 0x1
  AND r.wait_type<>'WAITFOR'

  0 · Share on Twitter