2

I have a program written in Delphi that uses SQL Server 2008 as database engine. I am on a p2p link provided by two nano station m5. When I run a specific query inside of the program, the network monitor shows that the client is grabbing some data from server with 8Mb/second for 6 seconds duration.

Also when I connect the client directly to the server by ethernet cable and run the same query it grabs the same data but faster (Getting those data with 20mb/second just for 2 seconds duration)but when I copy a large file from server to the client , it will be copied with up to 55Mb/second.

It is bothering me because it causes the client application to run so slow than real network bandwidth is capable to, I want to know if there is a way to force sql server use maximum network bandwidth ?

Windows Server 2012 R2 and SQL Server 2008 running just with tcp/ip network protocol enabled and 100MB full duplex network server core i5 with 8GB DDR 3 and 160GB SSD and gigabit NIC

2
  • copying a file is not comparable to running a SQL query. Apples and oranges.
    – Keltari
    May 18, 2016 at 0:41
  • I can't do anything to Delphi.The query is about fetching some huge tables (with more than 40000 records) to the client computer and then process on them right there.,I got a trace file from the profiler. May 18, 2016 at 10:44

1 Answer 1

0

You won't get the same speed as transferring a file, but hopefully you can get faster.

Use the SQL Profiler to record the query with the database. Have a look at the results to ensure the program isn't sending unnecessary queries accompanying the main query, which due to latency may be slowing things down. Ideally you want to see a connection and just the one query.

Also can you make changes to the Delphi program? If you can, make sure it is using connection pooling. (Creating a connection once and reusing it when it needs to make a query) The SQL connection itself requires about 16 packets interchanged, multiply that by your latency and it can add up. Say you have a 100ms latency, then the connection could take 1.6 seconds to complete.

4
  • thanks pholcraft but connection latency between server and client while I am on nanostation m5's is about 6 ms and while I am using direct ethernet link is around 3ms.I am wondering if increasing the network packet size accordingly with network MTU can do something May 18, 2016 at 10:37
  • Did you look at the SQL trace using the profiler?
    – pholcroft
    May 19, 2016 at 8:32
  • solved.thank you all .setting the network packet size to a double value than the default value (8096) solved the problem .now sql server clients use maximum bandwidth available at the moment May 19, 2016 at 15:00
  • @EhsanNourani Please read Can I answer my own question?. You might want to convert your comment into an answer ;)
    – DavidPostill
    Jun 6, 2016 at 9:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .