Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are developing a desktop application, with client-server architecture.At present, our SQL server is hosted on a PC.When, someone uses application from a location(different country than where server is hosted) the response is very slow.One area we are looking at queries we are executing, and the number of roundtrips when a form loads.But, I wanted to know how to measure performance of the server itself - ie how much data is being transferred per some unit of time.What else should I measure? What factors will influence this data transmission?.

thanks

share|improve this question

1 Answer 1

up vote 1 down vote accepted

As you indicate when you refer to roundtrips - latency is probably much more important than bandwidth in this case.

Take a simple example if you have a form that requires (say) 20 sequential queries when a form loads and each of those queries is well formed and reasonably efficient then the total time spent gathering all of the results back on a LAN connection is probably a couple of milliseconds or less.

The local network's round trip time (as seen by pinging the SQL server from a client on the same LAN) will be <1ms, probably a lot less on a good modern network. However once you move to a WAN connection the round trip time rises. Typically you are looking at up to 10-50ms for connections between countries that are nearby (say UK to Germany) and 50-100ms for connections that cross a significant ocean (UK to US say).

Your set of queries that completed in <2ms locally will now take 1-2 seconds to complete and maybe more. If you have things like drop down lists in forms that are re-populated interactively then a user with a significant network lag will have a really awful experience.

The other side effect of this is that if any of these queries result in locking of any sort then as you add more of these users overall performance for everyone else will degrade too as the length of time those locks remain in place will be much longer for these remote users.

There are a number of ways to improve this, but I'm not really the person to give you advice on this. However I think that consolidating your queries into a smaller number of stored procedure calls is probably the most effective way to start and changing your app behaviour so that queries are not triggered for every user action would be smart too. Once you have an idea how expensive (in terms of remote client performance) each query is then you should be in a better position to make a call on where to focus your efforts though.

share|improve this answer
    
wow! got lot of insight from your answer. thanks –  fats_fast Feb 14 '10 at 10:14

Your Answer

 
discard

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