ASYNC_NETWORK_IO, TRACEWAIT wait types
-
Hi all,
I am seeing to many waits on TRACEWAIT and ASYNC_NETWORK_IO. If i go by theory the threads are waiting for the buffers to get freed up. So can I assume that data is not being consumed quickly enough by the application.
Please advise.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
What does this return?
/*
Cost threshold for parallelism (CXPACKET) http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx
Paul White: Understanding parallelism http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
Microsoft White Paper on waits http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Next query by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
*/
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')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.WaitS * 1000 / W1.WaitCount AS DECIMAL(14, 2)) AS AvgWait_MS,
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
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 < 97 -- percentage threshold
AND W1.WaitCount > 0;
GO
-
What's the question here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
NINJA Please find the result set below:
WaitType Wait_SAvgWait_MSResource_S Signal_SWaitCountPercentage
ASYNC_NETWORK_IO137941.7179.08 137817.92 123.79 174439781.58
LCK_M_S 8574.75802.50 8468.62 106.13 106855.07
PAGEIOLATCH_EX 6562.528.85 6559.13 3.39 7416713.88
LCK_M_RS_S 4083.855918.62 4083.77 0.08 690 2.42
OLEDB 3762.800.16 3762.80 0.00239184482.23
LCK_M_X 2945.592369.75 2945.09 0.511243 1.74
LCK_M_U 2015.39563.74 2010.27 5.123575 1.19
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
Sapen (11/14/2011)
Please upload it on the thread.
Did you read the ms doc in the links of the script?
-
Gail,
I noticed too many sql timeouts yesterday night. WHen I query on the monitor table i have in place I was seeing some of the sessions were in suspended status with wait types TRACEWRITE and ASYNC_NETWORK_IO.
Apart from this I was seeing blocking where the threads are in suspended status with LCK_M_XX wait types. The wait resources were on
KEY: 6:72057594049200128 (0e0380521dca)
KEY: 6:72057594049265664 (4503a2eb32ce)
when I used the query to identify the objects(based on hobt_id) I came to know that the objects were a table and an index on that table.
So I just started with my analysis with wat types TRACEWRITE and ASYNC_NETWORK_IO. So wondering if this was due to slow application.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
Ninja
Please find the image attached
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
You're blocking could be resolved by better indexing.
Tracewrite, how many traces do you have running at the moment? Server side or GUI?
network, this is the server waiting on the network & final client to consume the data.
-
Ninja..for the indexing I have identified the top 50 high cpu consuming queries and went by each individual graphical execution plans and created the non clustered indexes suggested by the query plan. this was recently done and i think last thursday. I ended up with creating 15 indexes for 4 different databases. I will see if i can create more.
for the networking wait type I identified it happening at the application server end and increased the bandwidth to a 1 GB from 100MB but still dont see any change. So I was wondering if it was the client application that was not being able to fetch the data fast enough.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
-
It's that the client application is not processing data fast enough. SQL is waiting for the client app to accept the rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
OK Thanks...
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply