'Other' wait oddity in Activity Monitor

Question

Post reply

'Other' wait oddity in Activity Monitor

Scott H

Old Hand

Points: 332
More actions
August 11, 2011 at 4:03 am

#241606

Hi,
I've just been asked to look at our SQL server performance, and I've come across an anomaly I'm not entirely sure how to handle, and I'm hoping someone here might be able to suggest something.
According to the Activity Monitor window, under the 'Resource Waits' category, the server appears to have a constant lag of over 8000ms of 'Other' wait.
My first question is what consitutues 'Other' wait?
I've looked over the net and I can't find anything definitive (unless I'm using the wrong search terms), and everything I've read points me back to sys.dm_os_wait_stats, which doesn't contain any wait measurements which match the value appearing under 'other' in the Activity Monitor window.
One of the pointers I have tried is clearing down the wait stats DMV and this is where things get a little odd.
As expected, the contents of the DMV are cleared and everything in the Activity monitor window is reset, with one major exception - the 'Other' wait entry here persists and doesn't look like it's changed one bit.
Can anyone think of anything that would persist despite clearing down the DMV? And what else would I query to identify what's causing this issue?
Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

When you first checked the sys.dm_os_wait_stats, what all types did you see?

If you look at this link you will see what is part of Wait type Other.

Search for "Other waittypes" in the page and you will see what it constitutes of.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

This is the query I usually run to find the wait types. I got this query from Glenn. It gives me a good break down of the wait types I am interested in.

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats with (readuncommitted)

WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'

,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'

,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

-Roy

Paul White SSC Guru Points: 150468 More actions · Answer 3

Scott H (8/11/2011)
According to the Activity Monitor window, under the 'Resource Waits' category, the server appears to have a constant lag of over 8000ms of 'Other' wait.

Activity Monitor is a horrible tool, might I suggest the following as a replacement?

http://www.brentozar.com/archive/tag/sp_whoisactive/

http://www.sqlmag.com/article/sql-server/sp_whoisactive

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Scott H Old Hand Points: 332 More actions · Answer 4

Hi,

Thanks for responding guys.

Paul - Thanks for the link. I've put that on the server and run it, but I'm not getting any wait_info back for 'normal' spids - I'm only getting wait information back for system processes (and most of which are showing as mirroring waits).

Roy - At the moment this is what I'm seeing when I query sys.dm_os_wait_stats

wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms

DBMIRRORING_CMD3573659833674109528145381078

BROKER_TASK_STOP1074654024337103110000163390

CLR_AUTO_EVENT36211396234206650780

LAZYWRITER_SLEEP10644610395348418434203

SQLTRACE_BUFFER_FLUSH25988103952062401546

Here is what I'm getting out of the query you provided...

wait_typewait_time_spctrunning_pct

DBMIRRORING_CMD833673.9594.7794.77

BROKER_RECEIVE_WAITFOR20590.642.3497.11

DBMIRROR_DBM_EVENT10500.381.1998.31

ASYNC_NETWORK_IO3632.860.4198.72

LCK_M_S2040.130.2398.95

CXPACKET1882.470.2199.17

And here is what Activity Monitor is showing...

Wait CategoryWait time (ms/sec)Recent Wait Time (ms/sec)Average Waiter CountCumulative Wait Time (sec)

Other907091029.14840497

Network I/O26260.03404

Logging320.0542

Now as you can see, the query results seem to match, but I'm still getting 'Other' wait registering in Activity Monitor (which I thought was driven by the DMV sys.dm_os_wait_stats).

Matching the wait_type values from Roys' link with the DMV, I still don't get a number matching that displayed in the Activity Monitor window, so the question now is, can I rely on the data in Activity Monitor to be right? (I'd hope so, given that it's written by MS for use with SQL server!)

Failing that, does anyone have any suggestions on how I'd diagnose whether this is a bug or not? Are there any other internal DMV's that are called when you use Activity monitor?

Thanks.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

I have a feeling that it takes the data from sys.sysprocesses based on the lastwaittype. Just a guess here.

-Roy

Paul White SSC Guru Points: 150468 More actions · Answer 6

Scott H (8/12/2011)
Paul - Thanks for the link. I've put that on the server and run it, but I'm not getting any wait_info back for 'normal' spids - I'm only getting wait information back for system processes (and most of which are showing as mirroring waits).

sp_WhoIsActive is very good at showing you things you need to worry about - one of the reasons it doesn't even display system session information unless you specifically ask for it. The mirroring waits are normal, and Activity Monitor is wasting your time by showing them at all. If you are keen to see where AM gets its information from, run a profiler trace to capture the SQL it sends to the server.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 7

Roy Ernest (8/12/2011)
This is the query I usually run to find the wait types. I got this query from Glenn. It gives me a good break down of the wait types I am interested in.
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats with (readuncommitted)
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

Thank you for sharing the query!

My blog