SQL Server Performance Forum – Threads Archive
Slow query performance over the networkHi: I am trying to troubleshoot an issue related to slow result being returned over a WAN. I have run the benchmark query on the server and across the WAN and seeing quite a bit of difference (1-2 sec. vs 46-48 secs.) During this time the process shows it is waiting on a NETWORKIO wait type. I have benchmarked the same query on different servers and have got consistently good results in the 8-10 second range which is more acceptable for us. I have run Iperf across the WAN and got about 6 Mbit/secs from the same server. The server is dual xeon 3.6 Ghz/8gig RAM/IBM x346/Win2k3 ent/sql 2k ent sp3a – dedicated box Any suggestions on how I can figure this out further?
Add SET NOCOUNT ON to the start of the procedure. It is recommended to start each and every SP with SET NOCOUNT ON.
This problem occurs because SQL Server 2000 does not correctly process a fragmented Tabular Data Stream (TDS) network packet and latest SP (SP4) for SQL 2000 fixes this, see that helps. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.