Posted by Erland Sommarskog on 08/31/07 21:17

(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]