Excessive waits showing up in Performance Dashboard

SQL Server performance monitoring and alerting

Moderators: Colin Millerchip, priyasinha, Adam, chriskelly

Excessive waits showing up in Performance Dashboard

Postby Lxocram » Mon Mar 10, 2014 12:01 pm

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?
Lxocram
 
Posts: 16
Joined: Thu Jun 07, 2007 2:44 pm
Location: Belgium, Ghent

Postby Brian Donahue » Tue Mar 11, 2014 5:44 pm

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Altered the performance dashboard report

Postby Lxocram » Wed Mar 12, 2014 9:30 am

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'
Lxocram
 
Posts: 16
Joined: Thu Jun 07, 2007 2:44 pm
Location: Belgium, Ghent

And also on the main page

Postby Lxocram » Wed Mar 12, 2014 9:35 am

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'
Lxocram
 
Posts: 16
Joined: Thu Jun 07, 2007 2:44 pm
Location: Belgium, Ghent


Return to SQL Monitor 4

Who is online

Users browsing this forum: No registered users and 0 guests

cron