Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'am doing an investigation about a slow running query (see below info) when accessing our production SQL Server located in an other country. Now I'm looking for tweaks on how to solve this and make it fast.

See below information:

  • Database Transaction Table Records: 17,000 +

  • SQL Server(2005), Web Server, Report Server (where reports are on our ASP.NET Web Application) location: Illinois, USA (no jobs or any linked server on the Database)

  • Query executed: select * from TransactionTable

Test results (all locations have only one domain):

|  Location       | Time Elapse  | Executed Using
   Illinois         00:00:00       SQL Management Studio,Web App,RS(Prod Server,local)
   Brazil           00:2:21 +      SQL Management Studio,Web App,RS 
   Philippines      00:2:21 +      SQL Management Studio,Web App,RS 
   Others           00:2:21 +      SQL Management Studio,Web App,RS 

This is what I did: restart production SQL Server, stop Web and Report Server then I tried to query the SQL Server on different locations with the same result.

I found that the problem is not the query but it is caused by network

Anyone of you guys encountered this? What have you done? I need some recommendation.

share|improve this question
    
can you run a trace to see what the actual experienced latency for the application itself is? also, what is the query in question? from the looks of things, you're distributing your SSRS instances all over the world, and they're hitting one central database server. based on the numbers you provided, it appears as though the local SSRS instance performs well. try running that with SET STATISTICS TIME ON and see how long it takes in milliseconds. My hunch is that it's not a database/query issue. –  swasheck Nov 6 '12 at 4:44
1  
additionally, you've already said that it's probably network so what kind of bandwidth are you looking at between each site? check your waits to see what your ASYNC_NETWORK_IO numbers look like. what are you doing on the application side (SSRS)? are you doing any complex calculations that would cause waits, or are you just dumping the result set to the SSRS instance? finally, is it possible for your end-users in Brazil to access the SSRS instance in Illinois? that may provide valuable troubleshooting information. –  swasheck Nov 6 '12 at 4:51
    
RS Reports is just attached into our ASP.net Web Application where the query is not complex. –  BizApps Nov 6 '12 at 5:51
    
Now that the query is posted, I agree with Remus - there's really no need to pull an entire table, is there? If there is, then there are much better ways of achieving reporting. –  swasheck Nov 6 '12 at 15:54
    
It is just a test query @swasheck where on the above info time elapse when iexecute it on Remoting the actual local Server on warrenville is 0 but more than two minutes when executed in the other countries by using Management Studio.The actual query used on reporting services is not that complex w/c includes joins on other tables –  BizApps Nov 7 '12 at 1:10
add comment

2 Answers

Any reason why you need to download the entire table every time to every location? select * from table w/o filters across geo locations is an uphill battle you're bound to loose. Filter the data remotely before download. Or make local read-only copies (eg. via log shipping).

share|improve this answer
    
It is just a test query @Remus where on the above info time elapse when iexecute it on Remoting the actual local Server on warrenville is 0 but more than two minutes when executed in the other countries by using Management Studio.The actual query used on reporting services is not that complex w/c includes joins on other tables –  BizApps Nov 7 '12 at 1:11
add comment

Absolutely sounds like a network issue. I've been working for a CDN with POPs all over the world and I've ran into similar issues.

When the server is not being heavily used clear the SYS.DM_OS_WAIT_STATS and run a query against it, then check it again. A much better solution would be to use Extended Events to see what wait times are adding up on your query. Most likely it's ASYNCH_NETWORK_IO.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.