Lots of ASYNC_NETWORK_IO for SQL Server 2012
-
Dear Everyone
I am not a performance expert so i needed help in resolving the following item.
I ran the script for Randal from the link below and the result is below:https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
ASYNC_NETWORK_IO 204478.62 199447.71 5030.91 19190981 90.10 0.0107 0.0104 0.0003 https://www.sqlskills.com/help/waits/ASYNC_NETWORK_IO
PREEMPTIVE_OS_CREATEFILE 6072.95 6072.95 0.00 469561 2.68 0.0129 0.0129 0.0000 https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CREATEFILE
PAGEIOLATCH_SH 5002.40 4967.22 35.18 1161031 2.20 0.0043 0.0043 0.0000 https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
PREEMPTIVE_OS_FILEOPS 3475.41 3475.41 0.00 7037735 1.53 0.0005 0.0005 0.0000 https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_FILEOPSI dont know how to use profiler but one person said if you check the option "Discard results after execution in SSMS" then it might solve the issue.
Please advise
Kal -
Read the link you've included about network io in your post.
Solutions: revamp the application (select data, improve data handling)
It usually means that sql server is streaming data to the client. Most likely the client isn't fast enough to process it instantly. Or perhaps the amout of data is saturating your network/router/firewall bandwidth... -
Yeah but the database is also using Filestreaming so i dont know if that will affect it also
What else can i do?Kal
- hurricaneDBA - Wednesday, September 19, 2018 3:07 AMDear Everyone
I am not a performance expert so i needed help in resolving the following item.
I ran the script for Randal from the link below and the result is below:https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
ASYNC_NETWORK_IO 204478.62 199447.71 5030.91 19190981 90.10 0.0107 0.0104 0.0003 https://www.sqlskills.com/help/waits/ASYNC_NETWORK_IO
PREEMPTIVE_OS_CREATEFILE 6072.95 6072.95 0.00 469561 2.68 0.0129 0.0129 0.0000 https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_CREATEFILE
PAGEIOLATCH_SH 5002.40 4967.22 35.18 1161031 2.20 0.0043 0.0043 0.0000 https://www.sqlskills.com/help/waits/PAGEIOLATCH_SH
PREEMPTIVE_OS_FILEOPS 3475.41 3475.41 0.00 7037735 1.53 0.0005 0.0005 0.0000 https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_FILEOPSI dont know how to use profiler but one person said if you check the option "Discard results after execution in SSMS" then it might solve the issue.
Please advise
KalThat setting for discarding the results after execution just applies to SSMS and I would guess it was suggested as a test to see how the client side can impact that wait type. That's all.
You don't necessarily need to use profiler to work on that issue either.
It can be network related but I think more often it's just from large result sets and/or row by row processing of large result sets. These articles give some suggestions on addressing the wait type. The first thing to look for is queries with large result sets:
SQL Server ASYNC Network IO Wait Type
ASYNC_NETWORK_IO or NETWORK_IOSue
-
Hi Sue
This database is linked to an edms using share point and filestreaming so finding the query might not be straight forward
What do I do here if it’s an already built application?
Kal
- hurricaneDBA - Wednesday, September 19, 2018 8:17 AM
You could start by going through sys.dm_exec_query_stats. The queries could be those where the parameters the users pass in the application are kind of stupid - something like a data range and they select from the beginning of time to the end of time and have 100 billion records returned. So it still may be more of a user issue. You won't know until you start to look at the queries.
Sue
-
I've seen this kind of waits many times, most of the time, the cause is a combination of complex queries with DISTINCT and ORDER BY clauses which would reach half way to the moon, hash table spills in tempdb and last but not least, non-responsive systems having lots of users staring at the screen in the beggar's believe that something will eventually happen.
😎Are any of those queries ORM generated?
-
Other places to check when seeing elevated ASYNC_NETWORK_IO waits:
- Check the entire network path between the database server and the client(s). A switch connection somewhere on the path may have auto-negotiated itself down to a slower speed, a device in the path is under load, a busted route table change sends your packets on a few extra laps around the datacenter before finally arriving at their targets, etc., etc., etc.
- Note that queries that fetch data using SSMS use a specific driver and cursor to return results that will show lots of ASYNC_NETWORK_IO waits-Eddie
Eddie Wuerch
MCM: SQL - Sue_H - Wednesday, September 19, 2018 8:29 AM
Hi Sue
I ran the query but you will have to explain what it means 🙂 - hurricaneDBA - Monday, September 24, 2018 5:02 AM
You make me laugh sometimes!
You can order queries against dm_exec_query_stats in a ton of different ways to find a lot of interesting info -
Order by the row_counts to see what is pulling a ton of rows. Order by execution_counts to see what is executed frequently, order by the grants to see what is using a lot of memory, physical reads/writes tell you what queries are hitting the disks, execution times for what is running the longest, etc. Glenn Berry has some good examples in his diagnostic scripts:
SQL Server Diagnostic Information Queries for August 2018
The caveat is that the DMV only has information on what queries are in cache so you'd want to keep that in mind. The DMV is pretty well documented so follow what MS explains for the different columns. Follow Glenn Berry's examples (you can add whatever other columns) and join to the other DMVs to get the sql statements. You'd want to grab all of his diagnostic queries for a good reference. He comments things pretty well so you can get an idea of what each are looking at.
There are a lot of other different examples - search on: sys.dm_exec_query_stats
You will find plenty of other examples as well. And some of those may make more sense to you.Sue
-
Thanks Sue
I’ll try them tomorrow
Glad I made you laugh because there are a lot of people crying in my office lol
Kal
- hurricaneDBA - Monday, September 24, 2018 2:56 PM
That's why DBAs are required to have a box of Kleenex/tissues in their offices.
Sue
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply