Posted by TheSQLGuru on 08/31/07 21:45

In addition to agreement with others statements about refactoring the
application to reduce network traffic, I proffer the following: lets assume
that you are an Amazon.com wannabee and you really DO have 25Mbps
steady-state bandwidth needs between IIS and SQL Server. That gives you
have almost THREE orders of magnitude before running out of currently
available network capacity (10GB ethernet). Even cheapo 1000MB ethernet
gives a huge amount of headroom to grow into.

BTW, I have been a SQL Server database consultant for 10+ years. I have
NEVER YET been to a client that had a fully optimized database application,
and most of them were HORRID from a performance standpoint. :-))

--
TheSQLGuru
President
Indicium Resources, Inc.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns999DEDA151319Yazorman@127.0.0.1...
> (raymond_b_jimenez@yahoo.com) writes:
>> Well William, that is clearly not the case where you have a REAL
>> database with REAL traffic. When I mean REAL, I mean a 25Mbps stream
>> between the IIS servers and SQL Server... Getting away from about
>> 10Mbps of unneeded traffic does not seem like polishing to me...
>> I can guarantee you that this is having serious impact on performance,
>> and when you're digging really into it (things like TCP/IP slow-
>> starts...), you really get to know why it's huge impact for the
>> client, the DB server and performance.
>
> Rather than blaming TDS, maybe you should look into trimming the
> application. TDS is not going to change.
>
> First step is to analyse what is making up those 25 Mbps. Is it SQL
> commands? Or is data? SQL batches are, as I discussed in my previous post,
> Unicode by necessity. Data is another matter.
>
> As I said, I have not eavesdropped on TDS, but I would execpt varchar
> data to be sent as bytes. That is, the value 'character' would be eleven
> bytes on the wire. On the other hand, the value N'character' would be
> 20 bytes. And of course, metadata goes as Unicode.
>
> Now, what can you do to reduce the amount the network traffic? If you
> feel that you don't need Unicode, use varchar for you character data
> and not nvarchar. (But keep in mind that the day when you need to support,
> say, Japanese may be closer in time than you think.) But most of all,
> trim your result sets from unneeded columns. Make sure that there are
> not a lot of "SELECT *" in your queries, and that you don't retrieve
> rows you don't need.
>
> Furthermore, network traffic is not only about bytes, but also about
> roundtrips. Don't get the details of the order, and then make one
> call for each product on the order, but get all data at once.
>
> And, yes, while you would have seen a gross cut if TDS was UTF-8 on
> the wire and not UTF-16, a Chinese user would have seen an increase
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]