June 18, 2015 at 11:31 pm
Hi,
I am using Paul Randal's query to check for the wait types and the duration of the waits in the wait stats dmv.
I reset the wait stats and after just 2 minutes, I see that waitS colum generated in this query went past 1000 for a particular wait type. So, I am assuming that this counter is in milliseconds even though it is being divided by 1000 in the select statement. Can anybody please confirm my finding? thanks,
Here is that query for reference :
SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
June 19, 2015 at 1:15 am
Quick question, how many cores does your server have? (my guess is 8; 8 x 60 x 2 = 960)
😎
The values from sys.dm_os_wait_stats which are suffixed "_ms" are in milliseconds, more details here: sys.dm_os_wait_stats
June 19, 2015 at 2:28 am
Waits has little to do with physical time, and nothing to do with cores either. It's the collective time that processes have spent waiting.
Imagine a queue in a bank. If 10 people are in the queue and you observe them for one minute, the collective time spent waiting is 10 minutes (10 people waited one minute each). If you looked at a large supermarket at a busy time, in 10 minutes collectively the customers could easily have waited hours, because lots of customers are waiting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2015 at 2:54 am
Thanks for the wonderful Analogy Gail.
No wonder the wait time inspite of being converted into seconds(divide by 1000) from milliseconds show such a long time.
I understand now that many Spids are spending wait times on the Async Network wait type and this is why it shows a large number much more than the duration of the last clearing of the wait stats DMV.
Mostly could be issues with the client code of this huge Engineering app our company is using for field engineers to use.
thanks a lot.
June 20, 2015 at 4:43 am
GilaMonster (6/19/2015)
Waits has little to do with physical time, and nothing to do with cores either. It's the collective time that processes have spent waiting.Imagine a queue in a bank. If 10 people are in the queue and you observe them for one minute, the collective time spent waiting is 10 minutes (10 people waited one minute each). If you looked at a large supermarket at a busy time, in 10 minutes collectively the customers could easily have waited hours, because lots of customers are waiting.
Thank you for the correction Gail, posted the reply without thinking:ermm:
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply