| Groups | Blog | Home |
sql server (alternate) : tcp traffic
The application setup is: Access frontend + ODBC (TCP/IP) + MSSQL7 Few days ago i was informed that the app generates high network traffic. Thus i started to look for some savings. I found out (sniffer) that even if the client is idle every 30sek. a TCP packet comes from client to server, then answer goes back. The packet seems to be empty (TCP data is 1 byte: 0x00), the answer is empty as well. Seems like kind of watchdog. And it does not seem to be related to ODBC refresh and any other option possible for setup in Access client (I set those settings to different that 30sek. values, and 30 sek. traffic still exists). Does anybody know what those packets are for, what module is responsible for them and how to change interval? My best regards for anybody reading this post,
Use the profiler for SQL server.
it show the traffic. if any of the queries can be changed to stored procedures that will reduce the traffic by many folds. [quoted text, click to view] "Mirek" <mirgr@interia.pl> wrote in message news:b8bf8315.0309120727.14ff2b02@posting.google.com... > Hi, > > The application setup is: Access frontend + ODBC (TCP/IP) + MSSQL7 > > Few days ago i was informed that the app generates high network > traffic. Thus > i started to look for some savings. > > I found out (sniffer) that even if the client is idle every 30sek. a > TCP packet comes from client to server, then answer goes back. The > packet seems to be empty (TCP data is 1 byte: 0x00), the answer is > empty as well. Seems like kind of watchdog. And it does not seem to be > related to ODBC refresh and any other option possible for setup in > Access client (I set those settings to different that 30sek. values, > and 30 sek. traffic still exists). > > Does anybody know what those packets are for, what module is > responsible for them and how to change interval? > > My best regards for anybody reading this post, > Mirek
Hi
I am not sure what the packet is for, but you don't say how many clients are using this? I suspect that the problem is more to do with Access downloading data to work on it locally which it is notorious for doing. John [quoted text, click to view] "Mirek" <mirgr@interia.pl> wrote in message news:b8bf8315.0309120727.14ff2b02@posting.google.com... > Hi, > > The application setup is: Access frontend + ODBC (TCP/IP) + MSSQL7 > > Few days ago i was informed that the app generates high network > traffic. Thus > i started to look for some savings. > > I found out (sniffer) that even if the client is idle every 30sek. a > TCP packet comes from client to server, then answer goes back. The > packet seems to be empty (TCP data is 1 byte: 0x00), the answer is > empty as well. Seems like kind of watchdog. And it does not seem to be > related to ODBC refresh and any other option possible for setup in > Access client (I set those settings to different that 30sek. values, > and 30 sek. traffic still exists). > > Does anybody know what those packets are for, what module is > responsible for them and how to change interval? > > My best regards for anybody reading this post, > Mirek
John,
Only 4 clients are using this. I made a measurement of the traffic it was about 350 packets / per hour / client. For 30 sec interval thare are 120 empty packets / hour /client (60min / 30sec), which is 30% of whole traffic. It does not seem to be related to Access client, but rather to network protocol used for connecting to MSSQL: the same effect appears while using Enerprise Manager. I checked with Profiler, and it does not show those 'packets' (I run it in parallel with sniffer). As far as I understand Profiler features it cannot show network traffic, it just shows queries arrived&executed at SQL processes. For the while the problem becomes just interesting for me, so once again: anybody knows what whose packets are from, what module is generating it, how to adjust the interval? Regards, Mirek [quoted text, click to view] "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:<3f63436a$0$10770$afc38c87@news.easynet.co.uk>...
> Hi > > I am not sure what the packet is for, but you don't say how many clients are > using this? I suspect that the problem is more to do with Access downloading > data to work on it locally which it is notorious for doing. > > John > > > "Mirek" <mirgr@interia.pl> wrote in message > news:b8bf8315.0309120727.14ff2b02@posting.google.com... > > Hi, > > > > The application setup is: Access frontend + ODBC (TCP/IP) + MSSQL7 > > > > Few days ago i was informed that the app generates high network > > traffic. Thus > > i started to look for some savings. > > > > I found out (sniffer) that even if the client is idle every 30sek. a > > TCP packet comes from client to server, then answer goes back. The > > packet seems to be empty (TCP data is 1 byte: 0x00), the answer is > > empty as well. Seems like kind of watchdog. And it does not seem to be > > related to ODBC refresh and any other option possible for setup in > > Access client (I set those settings to different that 30sek. values, > > and 30 sek. traffic still exists). > > > > Does anybody know what those packets are for, what module is > > responsible for them and how to change interval? > > > > My best regards for anybody reading this post,
[quoted text, click to view]
Mirek wrote: > > It does not seem to be related to Access client, but rather to network > protocol used for connecting to MSSQL: the same effect appears while > using Enerprise Manager. > Mirek, We've experienced similar problems with both Access clients and Enterprise Manager connecting to our SQL installations. The first evidence we noticed were the thousands of Logon/Logoff events in the host's Security logs. After a little digging, we discovered that this was being caused by the clients' "server polling" functionality. Basically, when the client is open, it'll login to SQL every n seconds to make sure it's still alive, and if not will alert the user, etc.. This can actually be disabled in Enterprise manger by selecting "Options" from the "Tools" menu and clearing the checkbox next to "Poll server to find out state of server and related services." As for Access, we havn't found a way to disable this behavior, and unfortunatly, the Access clients are provided by a thrid party solution and can't be rewritten in-house. So, if you find a way to squash this in Access, I'd love to hear about it! Good luck, - Dan --- Dan Alig daligatwhartondotupenndotedu
[quoted text, click to view]
Mirek wrote: > > It does not seem to be related to Access client, but rather to network > protocol used for connecting to MSSQL: the same effect appears while > using Enerprise Manager. > Mirek, We've experienced similar problems with both Access clients and Enterprise Manager connecting to our SQL installations. The first evidence we noticed were the thousands of Logon/Logoff events in the host's Security logs. After a little digging, we discovered that this was being caused by the clients' "server polling" functionality. Basically, when the client is open, it'll login to SQL every n seconds to make sure it's still alive, and if not will alert the user, etc.. This can actually be disabled in Enterprise manger by selecting "Options" from the "Tools" menu and clearing the checkbox next to "Poll server to find out state of server and related services." As for Access, we haven't found a way to disable this behavior, and unfortunately, the Access clients are provided by a third party solution and can't be rewritten in-house. So, if you find a way to squash this in Access, I'd love to hear about it! Good luck, - Dan
Sorry I was so cryptic in my first post.
if you have the SQL profile set up it will show all the connection as well as the processes. What I expected you to see is the keepalive from the ODBC connection. the only way around this is to open SQLsever OLEDB connections and use Execute, IMHO. Look in the profiler for increment in the connection ID's or connect, disconnect messages. in the sniffer. Do you have pooling enabled? http://support.microsoft.com/default.aspx?scid=kb;en-us;328476 RESOLUTION To ensure pooling, you must create additional data source/session pairs, or explicitly create ActiveX Data Objects (ADO) connections. By explicitly creating the ADO connection or OLE DB Session, you can be guaranteed that the connection will be pooled by OLE DB, which means that the next request for an ADO connection or OLE DB Session can reuse an existing session and SQL Server connection. http://support.microsoft.com/default.aspx?scid=kb;en-us;271128 [quoted text, click to view] "Mirek" <mirgr@interia.pl> wrote in message news:b8bf8315.0309150026.24dbee65@posting.google.com... > John, > > Only 4 clients are using this. I made a measurement of the traffic > it was about 350 packets / per hour / client. > > For 30 sec interval thare are 120 empty packets / hour /client (60min > / 30sec), > which is 30% of whole traffic. > > It does not seem to be related to Access client, but rather to network > protocol used for connecting to MSSQL: the same effect appears while > using Enerprise Manager. > > I checked with Profiler, and it does not show those 'packets' (I run > it in parallel with sniffer). As far as I understand Profiler features > it cannot show network traffic, it just shows queries arrived&executed > at SQL processes. > > For the while the problem becomes just interesting for me, so once > again: > anybody knows what whose packets are from, what module is generating > it, how to adjust the interval? > > Regards, > Mirek > > > "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:<3f63436a$0$10770$afc38c87@news.easynet.co.uk>... > > Hi > > > > I am not sure what the packet is for, but you don't say how many clients are > > using this? I suspect that the problem is more to do with Access downloading > > data to work on it locally which it is notorious for doing. > > > > John > > > > > > "Mirek" <mirgr@interia.pl> wrote in message > > news:b8bf8315.0309120727.14ff2b02@posting.google.com... > > > Hi, > > > > > > The application setup is: Access frontend + ODBC (TCP/IP) + MSSQL7 > > > > > > Few days ago i was informed that the app generates high network > > > traffic. Thus > > > i started to look for some savings. > > > > > > I found out (sniffer) that even if the client is idle every 30sek. a > > > TCP packet comes from client to server, then answer goes back. The > > > packet seems to be empty (TCP data is 1 byte: 0x00), the answer is > > > empty as well. Seems like kind of watchdog. And it does not seem to be > > > related to ODBC refresh and any other option possible for setup in > > > Access client (I set those settings to different that 30sek. values, > > > and 30 sek. traffic still exists). > > > > > > Does anybody know what those packets are for, what module is > > > responsible for them and how to change interval? > > > > > > My best regards for anybody reading this post, > > > Mirek
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |