Excessive waits showing up in Performance Dashboard

LxocramLxocram Posts: 35
edited March 12, 2014 4:35AM in SQL Monitor Previous Versions
If i open performance dashboard (http://www.microsoft.com/en-us/download ... x?id=29063 ), SQL Monitor is allways showing up in the section "Current Waiting Requests" under category User Waits.

If i look at the details(general waits(wait_generic) it shows me sql-statement "WAITFOR DELAY '00:00:00.200'

Is there a way to adapt the performance dashboard to not show up this wait type?

Or can i change an option in SQL Monitor?

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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'
  • 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'
Sign In or Register to comment.