March 3, 2011 at 3:30 am
Hi friends,
I collected Top wait stats as follows, if clear these waits stats by using DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR), what will happend in production database?
Please suggestion me, If any performance affected at database level & server level after clear these wait stats.
BROKER_TASK_STOP 295414.96 13.39
REQUEST_FOR_DEADLOCK_SEARCH 1284275.4513.28
XE_TIMER_EVENT, 1284271.9513.28
FT_IFTS_SCHEDULER_IDLE_WAIT 1273638.4713.17
BROKER_EVENTHANDLER 1265965.5613.09
XE_DISPATCHER_WAIT 1237231.9412.79
DISPATCHER_QUEUE_SEMAPHORE 1213664.7212.55
BROKER_TO_FLUSH 640838.39 6.62
BROKER_RECEIVE_WAITFOR 140319.35 1.45
Thanks in advance
ananda
March 3, 2011 at 4:21 am
Hi Ananda
As for what will happen when you clear stats - the numbers in the DMV are cummulative over time, and clearing them would start values at zero again (wait stats are also cleared during a server retsart)
I can't imagine it would cause a performance issue at all.
For info, see a blog post by Paul Randal for a very useful query which removes all the wait types which are system ones that are normal and will always happen. Looks like most if not all of yours are those, which is good news 🙂
Paul Randal's query:
WITH Waits AS
(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 (
'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', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO
March 3, 2011 at 4:54 am
Hi Mazzz,
Thanks for your reply and also provide Mr. Paul written by valuable script for analysis different wait stats on SQL server 2008.
As per this script
ASYNC_NETWORK_IO
ASYNC_IO_COMPLETION
BACKUPBUFFER
Those counters display very low percentage value like 3.91,2.94 and 2.91, As I comes to know the above wait stats are maintaining good, there is no any issues.
One more wait type is called - WRITELOG,
WaitType - WRITELOG
Wait_S - 32394.7
Resource_S - 32336.92
Signal_S - 57.78
WaitCount- 4942199
Percentage - 86.28
AvgWait_S - 0.0066
AvgRes_S - 0.0065
AvgSig_S - 0
This is used for worker thread is waiting for Logwriter to flush the log block, this precentage - 86.28, so might be High waits and wait times indicate an I/O bottleneck on the storage system associated with the transaction log.
How can clear this wait count? or by using dbcc statement only.
Thanks.
March 3, 2011 at 5:02 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply