Query take different time depends on location

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Query take different time depends on location

Author

Topic

AustraliaDBA
Starting Member

38 Posts

Posted - 2015-05-06 : 21:30:08

Hi All,
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..

Thanks

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-06 : 21:43:24

Compare the execution plans.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

AustraliaDBA
Starting Member

38 Posts

Posted - 2015-05-06 : 21:56:26

interestingly execution plan is exactly same

Thanks

AustraliaDBA
Starting Member

38 Posts

Posted - 2015-05-06 : 22:01:15

Tara for example when i run following (found from http://geekswithblogs.net/DevJef/archive/2011/09/28/quick-performance-test-in-sql-server.aspx) from SSMS in Australia to India db server it takes 20minutes plus when i remotely logon to the db server and run it, it takes 3 seconds.... so distance with client/app and db server imapcts it... what can we do to improve it or how we can find why it is taking that long....
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @Loops <= 100
BEGIN
IF COALESCE('123', '456') = '456'
PRINT 1
SET @Loops = @Loops + 1
END
PRINT 'COALESCE, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) PRINT ''
GO
--==================================================
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @Loops <= 100
BEGIN
IF ISNULL('123', '456') = '456'
PRINT 1
SET @Loops = @Loops + 1
END
PRINT 'ISNULL, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-06 : 22:12:50

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

AustraliaDBA
Starting Member

38 Posts

Posted - 2015-05-07 : 01:31:42

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?

Thanks

AustraliaDBA
Starting Member

38 Posts

Posted - 2015-05-10 : 20:54:18

SQL Gurus please clarify me if am wrong

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...

please advise if there is any other solution...

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources