my question might sound bit silly but if someone can explain me what is happening that will be great.
i have a SQL Server in India and application server in Australia. when application try to insert some records into database it takes about 10hours. i looked at db level there were no bottle necks. network administrator looked at metwork side and couldn't find anything. everyting else is working fine between these 2 locations. when i copy those database in Australia and did same thing from application it took 2mins only. i understand location of db server and application server does matter.. but this huge difference in amount of time taken for same thing is bit too much.
please explain what is happening here... or what can i do to find the root cause..
--reply from Tara--
You need to look at what it's waiting for. Sysprocess/whoisactive/etc
What is the purpose of the code you posted? Is that what you are testing? If that's it, then this is not a SQL Server issue but rather a network or client issue.
Tara, the above mentioned query if just to check the performance of the sql server as described in the article. i didn't post actual queries. when i execute above query which i purely run from SSMS. what i found spid has wait type ASYNC_NETWORK_IO.. same is when my application does its stuff.....
most of the people say for significant wait times on ASYNC_NETWORK_IO review the client applications.
but the query above mentioned is purely a t-sql.... does that mean i need to fine tuned the query?
what i noticed when DB and App sevrer are in same data centre no issues... when we move database to datacentre in India and application is in Australia than it takes hours to finish simple inserts into database through application. i looked at wait types and it was ASYNC_NETWORK_IO.....
network guy looked at the network and he found there isn't any packet loss.
so what i concluded there isn't anything can be done at query level because all works find when DB and App server are at same location. issue arrise when they are apart. when they r apart there isn't any bottle neck at network level.. so solution is to bring db and app server closer...
Where is the data, being inserted, coming from? Workstations / Files in Australia or workstations / files in India? If the original data, being inserted, is in Australia it has to travel to India first. If the inserts are row-by-row (rather than a single bulk insert) then each one will be effected by the latency on the network. You could try a PING to your local server in Australia and another to the server in India - is there a large difference in PING time? Multiply that difference by the number of rows being inserted and times by at least 4 (every network message have an ACK and then any result (e.g. "1 row inserted") will also have an ACK.
If this is a bulk insert I would upload the file to a computer local to the SQL Server in India, and then execute the insert "locally" in India.
You could look at improving the latency on the network - normally that involves reducing the number of HOPs between the two servers - and tends to be expensive!. If your system is currently using satellite for the links (sounds unlikely, but ...) then that would dramatically increase the latency (but the bandwidth width may well be fantastic! so, for example, uploading a huge file of transactions to India might take very little time, but each individual action would be relatively slow.
This is the key. It typically is not related to the network, but instead it's the client application. If it's just SSMS that's receiving the data, then it's the time spent on the network and the time to load it into the memory of the client. There isn't anything we can do on the SQL Server side.
Kristen and Tara thank you so much for your response.
Kristen data is in csv file. it is on app server in Australia. source data is in Australia, App server is in Australia DB server is in India.. wait type is ASYNC_NETWORK_IO.... netwrok can't be improved ....
when i move database in Australia as well all worked fine....
so can i conclude that we can't do anything to improve the whole process. it takes time only because of the location of DB and App server.
I would copy/"move" the CSV file to the server in India and then perform the "import" on the server in India. The CSV file will then be local. If the CSV file is large, and needs to be imported regularly, I would build a process that compresses/ZIPs the CSV file, copies it to server in India, unzips it and then imports it.