August 12, 2015 at 11:19 pm
Hi everyone,
I've been put in charge of a SQL server recently and I'm completely new to maintaining SQL. For some reason several clients when they are doing their database processing I get network_io hangs and everything comes to a halt. I have to go in there and manually kill the SPID and everything continues where it left off.
SQL Setup
Server 2012 R2
SQL 2014 SP1
Dell 850
VM running in Hyper-V
50GB RAM
FusionIO Card
10GB Ethernet between clients and server
All workstations are pretty much
Windows 7/8.1
10GB
64+GB of RAM
Several CPUs
SSD and SATA hard drives
It's very hit or miss when this occurs as well, however, it's always the same NETWORK_IO cause.
I've read on the Internet about how the client keep up with what SQL is sending. Could this be poor programming or do I need to investigate my network setup?
August 13, 2015 at 12:58 am
Short answer: Yes, commonly poor programming.
Longer answer: If there's someone where you work, and whom you trust, that can assure you that network capacity isn't being tested, then it would most likely be the client application taking longer than SQL Server would like to process the data it (SQL) is trying to deliver. There can be other factors apart from the time it takes to transmit data, and without knowing more about what's going on, it's hard to give a really clear answer (although others here most certainly can do better than I).
Anecdotal: A payroll application I was providing support for at a previous employer was dumping heaps out of the database (500 out of 502 tables were heaps, so that gives you some idea as to the DB design) to the thick client application for processing. Sometimes this was hundreds of megabytes of data, and transmitting that to multiple clients proved time consuming, and NETWORK_IO waits appeared. Subsequent upgrades of the application and the server hardware resolved this.
If you have any experience reading execution plans, and you have an idea what queries are being run, you could search for those queries execution plans and view them in something like SQL Sentry Plan Explorer (It's free, I don't work for them, but I use the app often for this) to see how much data ends up being returned at the end of the execution plan. This query will get you started:
SELECT
[TEXT], query_plan
FROM
sys.dm_exec_cached_plans decp -- Contains the cached plans
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) -- Contains query text for plan with given plan_handle
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) -- Contains the XML for the plan
where
decp.[TEXT] like '%some identifying part of the query%'
From there, you can at least see what's happen in the queries, if there actually is a lot of data being transferred, and what other improvements could be made.
August 13, 2015 at 5:41 am
It's also possible that in addition to the client not keeping up, the client is asking for too much data. In addition to looking to the client coding, you should also get a peek at what the query is doing. Assess that against the actual needs of the application.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply