January 8, 2018 at 1:06 pm
Comments posted to this topic are about the item SQL Server WaitStats Script
SQL Server Consultant
yusufkahveci@sqlturkiye.com
www.sqlturkiye.com
January 18, 2018 at 3:05 am
Thanks for your effort, but why inventing the wheel once again...?
Hashtag Paul Randal...
June 12, 2018 at 9:01 am
Even better is to capture the wait_stats at two (or more) different times and query the delta. I have this script that gives me a quick 10 second snapshot of server activity. I have a much more complex system that captures snapshots every 30 minutes and an SSRS report that shows the deltas graphically.
-- Report waits over a 10 second interval. Ignore's ignorable waits.
-- To change the interval change next line.
DECLARE @delay VARCHAR(10) = '0:0:10' -- hh:mm:ss
IF object_id('tempdb.dbo.#waits1') IS NOT NULL
DROP TABLE #waits1
IF object_id('tempdb.dbo.#waits2') IS NOT NULL
DROP TABLE #waits2
DECLARE @start BIGINT, @start_time datetime
,@end BIGINT, @end_time datetime
SELECT @start = cntr_value, @start_time = getdate()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec'
SELECT *
INTO #waits1
FROM master.sys.dm_os_wait_stats
WAITFOR DELAY @delay
SELECT *
INTO #waits2
FROM master.sys.dm_os_wait_stats
SELECT @end = cntr_value, @end_time = getdate()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec'
SELECT @start_time as start_time, @end_time as end_time,
(@end - @start) / datediff(s,@start_time, @end_time) AS [Batch Requests/sec]
SELECT b.wait_type [Wait Type]
,b.wait_time_ms - a.wait_time_ms AS [Wait Time in MS (Delta)]
,(b.wait_time_ms - a.wait_time_ms)/datediff(second,@start_time,@end_time) as [Wait Time ms/sec]
,b.max_wait_time_ms [Max Wait Time in MS (Since Startup)]
,b.signal_wait_time_ms - a.signal_wait_time_ms AS [Signal Wait Time in MS (Delta)]
,b.waiting_tasks_count - a.waiting_tasks_count as [Number of Waits]
,(b.wait_time_ms - a.wait_time_ms) / (b.waiting_tasks_count - a.waiting_tasks_count) as [Avg Wait in MS]
FROM #waits1 a
JOIN #waits2 b ON b.wait_type = a.wait_type
WHERE b.wait_type NOT IN ( -- ignore "idle" events
'REQUEST_FOR_DEADLOCK_SEARCH' ,'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
,'SQLTRACE_BUFFER_FLUSH' ,'LAZYWRITER_SLEEP'
,'XE_TIMER_EVENT' ,'XE_DISPATCHER_WAIT'
,'FT_IFTS_SCHEDULER_IDLE_WAIT' ,'LOGMGR_QUEUE'
,'CHECKPOINT_QUEUE' ,'BROKER_TO_FLUSH'
,'BROKER_TASK_STOP' ,'BROKER_EVENTHANDLER'
,'SLEEP_TASK' ,'WAITFOR'
,'DBMIRROR_DBM_MUTEX' ,'DBMIRROR_EVENTS_QUEUE'
,'DBMIRRORING_CMD' ,'DISPATCHER_QUEUE_SEMAPHORE'
,'BROKER_RECEIVE_WAITFOR' ,'CLR_AUTO_EVENT'
,'DIRTY_PAGE_POLL' ,'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
,'ONDEMAND_TASK_QUEUE' ,'FT_IFTSHC_MUTEX'
,'CLR_MANUAL_EVENT' ,'SP_SERVER_DIAGNOSTICS_SLEEP'
,'UCS_SESSION_REGISTRATION' ,'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
,'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
)
and b.wait_type not like 'BROKER\_%' escape '\'
and b.wait_type not like 'HADR\_%' escape '\'
and b.waiting_tasks_count > a.waiting_tasks_count -- ignore events that didn't occur during window
ORDER BY 2 DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy