Is this a network speed Issue?

Question

Is this a network speed Issue?

bryanasaurus

SSC Veteran

Points: 221
More actions
June 5, 2012 at 3:42 am

#259111

I'm trying to understand why queries to my database are so slow!
I have a system connected to a dedicated SAN and SQLIO shows good throughput results (~300MB/s sequential reads). There doesn't seem to be disk, CPU or memory pressure on the server (I've looked at wait stats and OS performance counters).
If I use Management Studio to do a [font="Courier New"]SELECT *[/font] from a large table, the query takes ~1m40s to complete (I'm selecting on the clustered index to minimise other influences).
If select the same data into another table (ie, I do a [font="Courier New"]SELECT INTO[/font]) rather than request the data back to Management Studio, the query takes about 30s to complete.
The strange thing is, I get the same results whether I run the command using SSMS on the server or on a client workstation. I've tried both Shared Memory and TCP/IP connections on the server, but still the results are the same!
Network usage during the queries is extremely low but, if I do a file transfer to the server, I see good network throughput so the network itself seems ok.
Can anyone help me understand what's happening here?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

doing a SELECT from a large table's going to take time, so yeah, that's a factor. the size of the data, whether there are varchar(max) or image type columns affects the amount of data, obviously.

network speed, and also the perceived time for the presentation software*(in this case SSMS) to load the data into a grid for visibility.

SQL might be done with gathering and sending the data in the first 30 seconds, but the rest of the time would be the sending the data over the network, and the presentation software might be chewing up the rest of the time loading it into a data table on the client,allocating memory and dumping stuff to swap space, binding the datatable to the grid,and actually drawing the grid itself with the data in in it.

the actual execution plan would tell you the SQL server portion on it , and you could deduce the other time as being client side related.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SSMS is badly written. Seriously, it's written in a way that causes Async network IO waits as SQL waits to send the results and that's regardless of whether it's on the server or a client.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bryanasaurus SSC Veteran Points: 221 More actions · Answer 3

I've attached some wait statistics taken during the [font="Courier New"]SELECT *[/font] and [font="Courier New"]SELECT INTO[/font] queries.

With the [font="Courier New"]SELECT *[/font] command, there are network waits which aren't present with the [font="Courier New"]SELECT INTO[/font] query.

But both commands were run using SSMS on the server!! If SQL can execute the command in ~30secs, why does it take ~1m40secs for SSMS on the same box to display the results? Should I expect to see Network waits for a query executed by a client on the server itself?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

See my post directly above.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bryanasaurus SSC Veteran Points: 221 More actions · Answer 5

An additional part to this problem!....

If this is a network problem, why does my SQL query use so little of the network bandwidth compared to file-transfers (see 2nd attachment)?

A file transfer uses around 50% network bandwidth - My SQL query never uses above 1% of network bandwidth! I know disk I/O is OK and I know network I/O is ok with file transfers....Is something within SQL or SSMS throttling the use of network I/O?

bryanasaurus SSC Veteran Points: 221 More actions · Answer 6

Hi GilaMonster - Thanks for the comments...I get the same type of results with SSIS and with SQLCmd. Would you expect SSMS connecting via shared memory to produce network I/O waits?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Yes, for exactly the reason I wrote.

Async Network IO waits are actually not network waits most of the time. They're waits for the client app to acknowledge the resultset, and SSMS is badly written and causes lots of those waits. Nothing whatsoever to do with network.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bryanasaurus SSC Veteran Points: 221 More actions · Answer 8

Hi GilaMonster,

You got me thinking and I now agree - you're absolutely right!

I set SSMS to 'Discard Results after execution' so that no results are rendered in the display... The query now completes in about 7 seconds! Client statistics still show the same amount of data being returned... Only difference is they're not displayed in SSMS

Thanks for your help!

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 9

GilaMonster (6/5/2012)
Yes, for exactly the reason I wrote.
Async Network IO waits are actually not network waits most of the time. They're waits for the client app to acknowledge the resultset, and SSMS is badly written and causes lots of those waits. Nothing whatsoever to do with network.

Very interesting information, Gail. +1

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com