| Register | FAQ | Calendar | Search | Today's Posts | Mark Forums Read |
|
#11
| |||
| |||
| (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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
#12
| |||
| |||
| (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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
#13
| |||
| |||
| 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/pro...ads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
#14
| |||
| |||
| 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/pro...ads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
#15
| |||
| |||
| > 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 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. 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 |
|
#16
| |||
| |||
| > 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 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. 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 |
|
#17
| |||
| |||
| 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 |
|
#18
| |||
| |||
| 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 |
|
#19
| |||
| |||
| 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 |
|
#20
| |||
| |||
| 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 |
![]() |
| Thread Tools | |
| Display Modes | |
In an effort to better serve ads to our visitors, cookies are used on objectmix.com. For more information, check out our Privacy Policy.