SQL Server lots of overhead from network communication? - 【StackMirror】|sql-server|performance|sql-server-2008|networking

We have a 'server' that is configured with SQL Server 2008 R2 on machine A. In the same LAN we have machine B which is a machine with identical hardware without the SQL Server installation.

We have a set of unit tests that request some records from the database and maps them to the proper CLR objects. When this unit test is run from Machine A, with SQL Server, the tests run in 5ms.

If I copy the code of the test to machine b and run it the tests take a total of ~50ms which is a significant increase if this is only caused by network overhead.

The queries executed do not send a lot of data (there are no blobs/xml data in the columns), the total data is around a few KB. The data is read using an IDataReader. We use a single connection which is opened once and is used to fire about 24 queries to the database.

Are there any tips/tricks that I can try in order to test if it's really the network overhead slowing the execution? I have a feeling something is just not configured properly in SQL Server.

2012-04-04 06:45
by thekip

I'm not sure what answer you expect here: anything you do over the network will be significantly slower than doing it locally, so by itself this isn't surprising. What is surprising is that you are trying to save 45ms in your unit test execution. If you can explain why you need to do that, someone may have a suggestion. You might also want to try the dba or serverfault sites, since this doesn't seem to be a programming issue - Pondlife 2012-04-04 07:54

@Pondlife, thx for the help. I'm sure network overhead might be the issue but I'm wondering if I'm not missing something and/or if there is a way you can profile such a thing. Moving it to serverfault/dba might be a good idea, but I've no idea on how to do so - thekip 2012-04-04 08:31

You haven't actually clarified what "the issue" is, since most people don't complain about their unit tests taking 45 milliseconds to execute : - Pondlife 2012-04-04 11:44

Well the issue is the difference between a local sql server running the test in 5ms and remote sql server taking about 10 times as long - thekip 2012-04-04 14:02

I'm afraid I still don't see the issue: it's obvious that any operation will take longer over a network than locally. And so far you haven't shown that it takes 10 times longer, only that it takes an extra 45ms. How long does a 1s operation take remotely? 1.045s or 10s? I don't want to sound dismissive, but what's important is that things happen as fast as necessary, not as fast as possible and that means investigating this is almost certainly not worth your time. Unless your motivation is simple curiosity, of course. Either way, serverfault is probably the better place for this - Pondlife 2012-04-05 14:37

This sounds like normal behaviour, its just network latency... To confirm if these figures sound reasonable, ping one server from the other, I would not be surprised if this reported a roundtrip well into double digit milliseconds whereas a ping of your local machine should come in a 1ms... I agree with Pondlife, sounds like par for the course for a healthy LAN.

2012-05-17 22:27
by Chris Wood

I've pinged the server machine and it comes in at about 1ms sometimes lower sometimes 1ms. My problem is that I can't measure why this is happening - thekip 2012-05-22 07:17