TDS and character encoding - Framework and Interface Programming

(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 ****yse 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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 ****yse 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/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

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 ****yse 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/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

> Rather than blaming TDS, maybe you should look into trimming the
> application. TDS is not going to change.
BTDT. Especially with Profiler, which is a great tool.

> First step is to ****yse 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.
Queries and replies. Replies are in the 3-4 packets range. Lots of
stored procedures of course.

> 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.
Don't accept anything for granted. Seeing is a completely different
experience.

> 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.
BTDT

> 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.
You're absolutely correct! That's why I got another thread going on
about "SET NO_BROWSETABLE ON". This one seems particularly annoying,
as it seems it is getting inserted automatically by ADO.Net. Accounts
for about 20% of the data traffic, and one additional round trip do
the DB server for each query.

> 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.
Wouldn't it be great to have an option?

rj

> Rather than blaming TDS, maybe you should look into trimming the
> application. TDS is not going to change.
BTDT. Especially with Profiler, which is a great tool.

> First step is to ****yse 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.
Queries and replies. Replies are in the 3-4 packets range. Lots of
stored procedures of course.

> 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.
Don't accept anything for granted. Seeing is a completely different
experience.

> 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.
BTDT

> 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.
You're absolutely correct! That's why I got another thread going on
about "SET NO_BROWSETABLE ON". This one seems particularly annoying,
as it seems it is getting inserted automatically by ADO.Net. Accounts
for about 20% of the data traffic, and one additional round trip do
the DB server for each query.

> 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.
Wouldn't it be great to have an option?

rj

On 31 Ago, 22:45, "TheSQLGuru" <kgbo...@earthlink.net> wrote:
> 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.
While all that is true, you have several other factors pounding you
with these amounts of traffic. Name two: TCP/IP slow-start and
interrupts on the receiving servers. Last one can be solved with
better network cards, but mines are already offloading work on the
hardware.

rj

On 31 Ago, 22:45, "TheSQLGuru" <kgbo...@earthlink.net> wrote:
> 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.
While all that is true, you have several other factors pounding you
with these amounts of traffic. Name two: TCP/IP slow-start and
interrupts on the receiving servers. Last one can be solved with
better network cards, but mines are already offloading work on the
hardware.

rj

On 31 Ago, 18:54, "William \(Bill\) Vaughn"
<billvaRemoveT...@betav.com> wrote:
> Given that SQL Server has the highest TPC-E benchmarks in the industry,
> don't you think that the SQL Server team has made the TDS stream as
> efficient as possible? IMHO, it's not the line protocol or the lowest layers
> of the interface that should be the focus of performance tuning, but the
> applications, database designs and query methodologies that should dominate
> your attempts to improve throughput and scalibility. Reducing the traffic on
> the TDS channel will go a long way to improving performance if you have to
> move that much volume over the wire to make a difference.
Don't know a lot about TPC-E benchmarks. Are they measured over a
network?

rj

On 31 Ago, 18:54, "William \(Bill\) Vaughn"
<billvaRemoveT...@betav.com> wrote:
> Given that SQL Server has the highest TPC-E benchmarks in the industry,
> don't you think that the SQL Server team has made the TDS stream as
> efficient as possible? IMHO, it's not the line protocol or the lowest layers
> of the interface that should be the focus of performance tuning, but the
> applications, database designs and query methodologies that should dominate
> your attempts to improve throughput and scalibility. Reducing the traffic on
> the TDS channel will go a long way to improving performance if you have to
> move that much volume over the wire to make a difference.
Don't know a lot about TPC-E benchmarks. Are they measured over a
network?

rj