tcp traffic

[Mirek]

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

[BJ Freeman]

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.
"Mirek" <mi***@interia. pl> wrote in message
news:b8******** *************** ***@posting.goo gle.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 Bell]

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" <mi***@interia. pl> wrote in message
news:b8******** *************** ***@posting.goo gle.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

[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&execute d
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" <jb************ @hotmail.com> wrote in message news:<3f******* *************** *@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" <mi***@interia. pl> wrote in message
news:b8******** *************** ***@posting.goo gle.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

[Dan Alig]

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

[Dan Alig]

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

[BJ Freeman]

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...b;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...b;en-us;271128

"Mirek" <mi***@interia. pl> wrote in message
news:b8******** *************** ***@posting.goo gle.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&execute d
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" <jb************ @hotmail.com> wrote in message

news:<3f******* *************** *@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" <mi***@interia. pl> wrote in message
news:b8******** *************** ***@posting.goo gle.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