Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1316
  • Last Modified:

Data ODBC connection slow to load into SQL database

I have a SQL Server 2008 Enterprise Edition database running on Windows Server 2008 R2 in a major US city that is working fine when data is loaded from a server in the same city.

I have an identical server setup in India but the data from that server is loading into the database very slowly.

The connection to India is 40mb and about half utilized.

Currently we are using odbc connections at both locations but I am considering changing the India site to use bcp.

How can I tell what is causing the slow connection without just assuming it is the WAN connection?

Also will the database care that one server is using odbc and the other is using bcp?

I checked the SQL logs and there is not any obvious errors to point to anything wrong with the connection. The connection will not keep up with the amount of data that India is generating. The amount of data coming from India is much less than the server in the US is is sending to the same database.

I am thinking it is not the database because the database is processing the data from the local server quickly.

The server in India does not show that it is overloaded. CPU, memory usage is low.

Will bcp make a big difference?

Is SQL profiler the tool to use?

Thanks
0
Dragon0x40
Asked:
Dragon0x40
4 Solutions
 
Aaron TomoskySD-WAN SimplifiedCommented:
Probably latency/jitter. Try pingtest.net
0
 
Dragon0x40Author Commented:
I agree that Latency could be a problem but pingtest.net is the way to test your ISP speed to one of the pingtest.net servers.

I don't think you can test a WAN link between the US and India using pingtest.net?

http://www.serviceassurancedaily.com/2008/06/latency-and-jitter/
0
 
David ToddSenior DBACommented:
Hi,

Why use pingtest? Why not just use the inbuilt ping?

Regards
  David
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Dragon0x40Author Commented:
Maybe I can run wireshark on the SQL server and filter for traffic coming from the US server and compare that to a filter of the traffic coming from the India server?

Then I should be able to look for retransmissions, the amount of time between packets, errors, etc.

Of course any latency could be in the WAN link or the Server in India.

Maybe SQL profiler would be a better tool but I don't know how to use it well enough to know what to capture and what to look for.
0
 
David ToddSenior DBACommented:
Hi,

Instead of SQL profiler, there is a netmon from MS or Performance monitor would be my suggestion. SQL Profiler will only pull out sql queries, and durations, which possibly isn't the most meaningful when trying to look at network times.

Regards
  David

PS Are there more network oriented zones that a netadmin could add to this question, that might get a more informed opinion?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Analyzing on SQL level won't bring you further here. Most probably you have issues with the "pipe" - TCP/IP response time, receive window, timeout/resent tracffic, and that is all network related stuff. WireShark or MS NetMon or any other Network Analysis tool can reveal the issue, but need someone experienced to interpret the results.
What will be obvious, however, are resends (retries) and missing or delayed ACKs. It sounds as if the ACKs are not sent as fast as needed, and each side is waiting for a "receipt" for transferred packets.
Also, heavy fragmentation could be an issue, leading to extensive overuse of bandwidth, or even dismissal.
0
 
Dragon0x40Author Commented:
Still working on this.

Will post something when I find out any new information.
0
 
Dragon0x40Author Commented:
Still working on this.
0
 
Dragon0x40Author Commented:
never figured this out so I will assign points for those that helped
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now