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.

While connected to our production server (SQL2008. very powerful machine), this select statement takes 2 seconds, spitting back all fields (4 MB of data in total).

select top 30000 * from person with(nolock)

From any other box on the same network (connecting using SQL authentication or windows authentication), the same query takes 1 minute, 8 seconds.

I am testing with this very simple statement to illustrate that it's not an indexing problem or query-related problem. (We have performance issues with all queries at the moment)

The rows come in chunks, and not all at once. I get my first rows instantly, and then wait for over 1 minute for the batches of rows to come in.

Here are the Client Statistics from the query (ran from the remote box):

Client Execution Time 15:30:48
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 0
Rows affected by INSERT, DELETE, or UPDATE statements 0
Number of SELECT statements 2
Rows returned by SELECT statements 30001
Number of transactions 0

Network Statistics
Number of server roundtrips 3
TDS packets sent from client 3
TDS packets received from server 1216
Bytes sent from client 266
Bytes received from server 4019800

Time Statistics
Client processing time 72441 (72 seconds)
Total execution time 72441
Wait time on server replies 0

We can see that the "Client Processing Time" is equal to the total execution time.

Does anyone know what steps I can take to diagnose why the transfer of the actual data is taking a long time?

Is there an SQL configuration parameter that restricts or limits data transfer speed between machines?

Thanks in advance!

share|improve this question
    
By the way, we tried copying the file of same size (4 MB) between the DB server and another box, and that took a second. So doesn't seem like a network issue. –  Alex Apr 30 '12 at 20:02
    
What is the client application? SSMS on the end-user workstations? –  Thomas Stringer Apr 30 '12 at 20:02
    
Yes Microsoft SQL Server Management Studio 10.50.1600.1. 2008 R2 –  Alex Apr 30 '12 at 20:10
    
This problem started since we moved datacenters, and the entire machine was re-installed (everything including SQL). We are with a very respectable hosting provider. –  Alex Apr 30 '12 at 20:12
add comment

4 Answers 4

up vote 2 down vote accepted

The problem is network related. As such, it has to be dealt with with network professionals (I am not the one). Faster NIC cards (on SQL server) might help. Adding of allocated/specific NIC card/subnet between the servers (web-server and SQL Server) might help. Is the web-server is in the same sub-net as back-end (SQL server)? Are there routers/bridges etc between them?

Not much of possible changes on SQL server: Ouput data is being sent by SQL Server with proprietary MS "TDS protocol". Default size of the TDS buffer is 4 KB. See in MSDB: "network packet size Option"

You are using a default size: see your stats: "TDS packets received from server 1216" (4MB/1K=4KB). Yes, size of the TDS buffer can be changed: see in google: "TDS protocol batch size" Good discussion: "does sql's network packet size really determine round trip traffic?" However, changing of TDS package size will (inevitably) have unpredicted effects and can be recommended on production only in exceptional cases. Changing of architecture or introduction of caching of data on mid-tier would help. Introduction of compression (with SQL Server or application implemented) might help - depends upon nature of data.

share|improve this answer
add comment

At initial reading it sounds like you are experiencing some network latency issues. Have you looked at some of the Network Perfmon counters? Those may give you some indication of what is going on with the network.

Quote from What Perfmon counters should I monitor and what each of them mean?

NETWORK IO

To measure network I/O, you can use the following counters:

Network InterfaceBytes Total/sec

Threshold: Sustained values of more than 80 percent of network bandwidth.

Significance: This counter indicates the rate at which bytes are sent and received over each network adapter. This counter helps you know whether the traffic at your network adapter is saturated and if you need to add another network adapter. How quickly you can identify a problem depends on the type of network you have as well as whether you share bandwidth with other applications.

Network InterfaceBytes Received/sec

This counter indicates the rate at which bytes are received over each network adapter. You can calculate the rate of incoming data as a part of total bandwidth. This will help you know that you need to optimize on the incoming data from the client or that you need to add another network adapter to handle the incoming traffic.

Network InterfaceBytes Sent/sec

This counter indicates the rate at which bytes are sent over each network adapter. You can calculate the rate of incoming data as a part of total bandwidth. This will help you know that you need to optimize on the data being sent to the client or you need to add another network adapter to handle the outbound traffic.

ServerBytes Total/sec

This value should not be more than 50 percent of network capacity.

This counter indicates the number of bytes sent and received over the network. Higher values indicate network bandwidth as the bottleneck. If the sum of Bytes Total/sec for all servers is roughly equal to the maximum transfer rates of your network, you may need to segment the network.

Processor% Interrupt Time

This counter indicates the percentage of time the processor spends receiving and servicing hardware interrupts. This value is an indirect indicator of the activity of devices that generate interrupts, such as network adapters.

Network Interface(*)Output Queue Length

This counter checks to see how many threads are waiting on the network adapter. If there are a lot of threads waiting on the network adapter, then the system is most likely saturating the network I/O most likely due to network latency or network bandwidth.

Output Queue Length is the length of the output packet queue (in packets). If this is longer than two, there are delays and the bottleneck should be found and eliminated, if possible. Since the requests are queued by the Network Driver Interface Specification (NDIS) in this implementation, this will always be 0.

share|improve this answer
    
After monitoring these stats in Perfmon, i noticed a few things. The total bytes/sec never goes up over 700K/s on any of the network cards. Even if I'm running a query that requests megabytes of data, this number stays at around 500K/sec. Our bandwidth is 100 MBPS, and we are not even getting to 1% usage of it. I am thinking there should be a limit configured somewhere that is forcing down the size of the packets, or limiting the transfer rate. The hardware interrupts / sec are at 700-2000. Output queue is empty. Network card usage peaks at about 4% at the highest. –  Alex May 1 '12 at 16:00
2  
There could be a mismatch between the network card speed and the switch port. Have you engaged your network team to look at it from the switch side? –  jgardner04 May 1 '12 at 16:04
add comment

This issue is now resolved.

It was a network problem, and the SQL box was using a 100 MB/s NIC card, instead of a 10 GB/s NIC card. A network configuration change to use the correct network card has fixed the problem. Now we are getting similar performance for all queries from the prod SQL box and from other boxes on the network.

Thanks everyone for your help. - Alex

share|improve this answer
add comment

Some preliminary questions: 1) The server has a SQL client on Prod. server machine set up, right? So if you make the same query from the client located on the same machine it will be completed in 2 seconds? Did you try to do this? Is it really 2 seconds? 2) You mentioned that configuration of your production environment has been changed (or production server moved to other network/total server rebuild done), right? What was the query consumption time in old production environment?

From any other box on the same network ...the same query takes 1 minute, 8 seconds. 3) You are saying that the query returns and is consumed from the client, located on any machine in the given network (expept your specific machine) in about 70 seconds? I understood correctly? 3.1 Incidentally what is the timing for consumption of this query, acceptable by the business? 4) However, you are specifiying that for a specific client machine that you are using the query output consumption time is: Client Execution Time 15:30:48 15 minutes? (and this time is clearly not acceptible)? Correct? 5) so the problem is limited to a single client machine? Or to ANY client/mid-tier etc machine (in a new environment)? 6) what is the delay shown by ping? from client computer to the server? 7) You (or network admin) did run tracert both ways (from client to server, from server to client)? How many hops? What is the combined time? 8) Is the old production network alive? Can you compare using Ping and Traceroute - what was the time and hops between client and server there?

Out of curiousity: this is an example of the query? or exact wording of the query? The query really does NOT contain WHERE clause? Agree with me that this is very unusual.. The table has a clustered index or is a Heap? The table contains how many rows all in all? The table is heavily fragmented? Out of curiosity: have why SELECT TOP NNN? Why not SET ROWCOUNT NNN - then SELECT * ? This query is issued how many times by the client per day? 1? 100? 1MLN? Underlying data is static or is dynamic and is changed much? How much (0.01 percent per day? 1 percent per day? 10 percent per day?) The query output is processed programmatically? (not by a user?) Why is it not cached/not stored on mid-tier? thanks, Alexei

share|improve this answer
    
Thanks a lot for the info. My responses below. 1. Correct. Client tools also installed on prod, and the same query i mentioned takes 2 seconds to return all of the 30,000 records (totalling 4 MB size). By the way, the query I used is just an example. It is not a real business query. It's just a means to get 4 MB of data from a table. We currently have a performance problem reading several megabytes of data from any table with any query currently. –  Alex May 1 '12 at 14:17
    
2. The consumption time was close, if not the same as that of the same query ran locally from the PROD box. (I.E. 2 seconds) 3. That's right 1 min 8 seconds is the execution time. This time varies among different client machines. From our development machine (located much further away than the stage machine), I ran this query 8 times in a row, and the time ranged from 11 seconds to 22 seconds. (average 18 sec. ) –  Alex May 1 '12 at 14:17
    
from our dev box tracert Prod_IP_Address 1 53 ms 52 ms 53 ms SQL2008 From the stage machine, the time is consistently over 1 minute. tracert Prod_IP_Address tracert: 1 1 ms <1 ms <1 ms SQL2008 From production web server: execution time is 53 seconds. tracert: 1 1 ms <1 ms <1 ms SQL2008 –  Alex May 1 '12 at 14:19
    
4. The top column "Client Execution Time" is just the local time of the machine (IE: 15:30:00) 5. The problem occurs on any machine hitting the production DB server, including on our production web server. 6. The ping delay is <1 MS from the stage box to the prod SQL box. 7. Please see above. 8. Unfortunately the old network no longer exists. –  Alex May 1 '12 at 14:19
    
It is really interesting that even though DEV pings 53 MS, it only takes 11-22 seconds to run the query. Whilst, stage pings 1 MS, it takes over 1 minute to return the data. Dev is also much further away geographically. And stage is right there next to the prod box, and yet is taking much longer. –  Alex May 1 '12 at 14:34
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.