TDS and character encoding

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

(raymond_b_jimenez@yahoo.com) writes:
>> 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 as I've noted in another thread, SqlClient does not do this when
you use it plainly. Run a plain ExecuteReader or DataAdapter.Fill and
you will not see it. There was a link posted that lead to an article
pointing out the problem with the CommandBuilder.

So that is one more thing to look into. Exactly what in your usage
of ADO .Net (and you still have not told us which data provider you
are using) triggers the usage of SET NO_BROWSETABLE ON.

>> 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?

If you thinks, submit this suggest on
http://connect.microsoft.com/SqlServer/Feedback.
Personally, I don't think it is worth the pain, also it would also
require changes in the client APIs. And all it would affect is query
batches sent to SQL Server and metadata sent back. If the query batches
sent to SQL Server is killing your network, maybe you should look into
using stored procedures.


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

(raymond_b_jimenez@yahoo.com) writes:
>> 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 as I've noted in another thread, SqlClient does not do this when
you use it plainly. Run a plain ExecuteReader or DataAdapter.Fill and
you will not see it. There was a link posted that lead to an article
pointing out the problem with the CommandBuilder.

So that is one more thing to look into. Exactly what in your usage
of ADO .Net (and you still have not told us which data provider you
are using) triggers the usage of SET NO_BROWSETABLE ON.

>> 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?

If you thinks, submit this suggest on
http://connect.microsoft.com/SqlServer/Feedback.
Personally, I don't think it is worth the pain, also it would also
require changes in the client APIs. And all it would affect is query
batches sent to SQL Server and metadata sent back. If the query batches
sent to SQL Server is killing your network, maybe you should look into
using stored procedures.


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

> Don't know a lot about TPC-E benchmarks. Are they measured over a
> network?

Database benchmarks are typically done with a dedicated database server and
remote client(s). You can download the results disclosure reports from
http://www.tpc.org/tpce/tpce_perf_results.asp to get details of the actual
configurations used. Looking at the specs of the network gear, it doesn't
look to me like the benchmark sponsors were too concerned about network
performance.

I agree with the others in this thread that the application and database
design are by far the biggest contributing factors to overall performance.
A little common sense, like filtering data on the server rather than the
client, goes a long way towards improving scalability and performance.


--
Hope this helps.

Dan Guzman
SQL Server MVP

<raymond_b_jimenez@yahoo.com> wrote in message
news:1188644332.019526.110140@y42g2000hsy.googlegr oups.com...
> 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
>

> Don't know a lot about TPC-E benchmarks. Are they measured over a
> network?

Database benchmarks are typically done with a dedicated database server and
remote client(s). You can download the results disclosure reports from
http://www.tpc.org/tpce/tpce_perf_results.asp to get details of the actual
configurations used. Looking at the specs of the network gear, it doesn't
look to me like the benchmark sponsors were too concerned about network
performance.

I agree with the others in this thread that the application and database
design are by far the biggest contributing factors to overall performance.
A little common sense, like filtering data on the server rather than the
client, goes a long way towards improving scalability and performance.


--
Hope this helps.

Dan Guzman
SQL Server MVP

<raymond_b_jimenez@yahoo.com> wrote in message
news:1188644332.019526.110140@y42g2000hsy.googlegr oups.com...
> 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
>

Erland Sommarskog (esquel@sommarskog.se) writes:
> (raymond_b_jimenez@yahoo.com) writes:
>>> 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?
>
> If you thinks, submit this suggest on
> http://connect.microsoft.com/SqlServer/Feedback.
> Personally, I don't think it is worth the pain, also it would also
> require changes in the client APIs. And all it would affect is query
> batches sent to SQL Server and metadata sent back. If the query batches
> sent to SQL Server is killing your network, maybe you should look into
> using stored procedures.

Thinking of it, rather than having to select the character encoding, it's
better if the option was for compression of the network traffic in general.

But I find it difficult to believe that this would be a good option
for the traffic between a web server and an SQL Server that are on
the same LAN. It could possibly be an option if you are on a slow connection
over VPN. In general, I have a feeling that the network considerations
for SQL Server are for LAN connections, because that is surely the most
common scenario.


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

Erland Sommarskog (esquel@sommarskog.se) writes:
> (raymond_b_jimenez@yahoo.com) writes:
>>> 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?
>
> If you thinks, submit this suggest on
> http://connect.microsoft.com/SqlServer/Feedback.
> Personally, I don't think it is worth the pain, also it would also
> require changes in the client APIs. And all it would affect is query
> batches sent to SQL Server and metadata sent back. If the query batches
> sent to SQL Server is killing your network, maybe you should look into
> using stored procedures.

Thinking of it, rather than having to select the character encoding, it's
better if the option was for compression of the network traffic in general.

But I find it difficult to believe that this would be a good option
for the traffic between a web server and an SQL Server that are on
the same LAN. It could possibly be an option if you are on a slow connection
over VPN. In general, I have a feeling that the network considerations
for SQL Server are for LAN connections, because that is surely the most
common scenario.


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

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

Pardon me for interjecting here Erland, I am not disagreeing with you but
agreeing, but this subject seems hard to tackle.
I have thought that network packet traffic contributes a lots, you ideally
want "package" as many results as you can into a packet (I mean this is
Nagle's algorithm) but I see precious little written on this subject. I see
nothing in Microsoft Whitepapers or anything else.

For example if I make a straight SELECT off a table for a Forward, ReadOnly
Cursor iusing ADO, what is the ideal Cache Size for maximum throughput?
It seems to me that it ought be Network Packet Size / Size of record. I am
sure there are some overhead bytes.
It ought to be determinable rather than just empirical guess work.
But how?

Thanks

Stephen Howe

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

Pardon me for interjecting here Erland, I am not disagreeing with you but
agreeing, but this subject seems hard to tackle.
I have thought that network packet traffic contributes a lots, you ideally
want "package" as many results as you can into a packet (I mean this is
Nagle's algorithm) but I see precious little written on this subject. I see
nothing in Microsoft Whitepapers or anything else.

For example if I make a straight SELECT off a table for a Forward, ReadOnly
Cursor iusing ADO, what is the ideal Cache Size for maximum throughput?
It seems to me that it ought be Network Packet Size / Size of record. I am
sure there are some overhead bytes.
It ought to be determinable rather than just empirical guess work.
But how?

Thanks

Stephen Howe