Red Gate forums :: View topic - Excessive waits showing up in Performance Dashboard
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 4
SQL Monitor 4 forum

Excessive waits showing up in Performance Dashboard

Search in SQL Monitor 4 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Lxocram



Joined: 07 Jun 2007
Posts: 16
Location: Belgium, Ghent

PostPosted: Mon Mar 10, 2014 12:01 pm    Post subject: Excessive waits showing up in Performance Dashboard Reply with quote

If i open performance dashboard (http://www.microsoft.com/en-us/download/details.aspx?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?
Back to top
View user's profile Send private message MSN Messenger
Brian Donahue



Joined: 23 Aug 2004
Posts: 6646

PostPosted: Tue Mar 11, 2014 5:44 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Lxocram



Joined: 07 Jun 2007
Posts: 16
Location: Belgium, Ghent

PostPosted: Wed Mar 12, 2014 9:30 am    Post subject: Altered the performance dashboard report Reply with quote

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'
Back to top
View user's profile Send private message MSN Messenger
Lxocram



Joined: 07 Jun 2007
Posts: 16
Location: Belgium, Ghent

PostPosted: Wed Mar 12, 2014 9:35 am    Post subject: And also on the main page Reply with quote

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'
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group