Very Computer

when should packet size (ado.net, sql server) be changed?
we have a vb.net application that updates/inserts/deltes a sqlserver 200
database; but we notice that at times; it sems to be pausing betwen
transactions. this behavior is intermittent.

can increasing packet size help? what lese could be the cause of this
behavior?

thanks
stephane

Hi Stephane,

It's my pleasure to assist you with your issue.

From your description, I understand that you would like to know whether increasing packet
size will help improve the transactions performance.

However, I need to set your expectation that such random problems are complex to
troubleshoot in newsgroups. You may contact Microsoft Product Support Services via the
following links in case the issue is urgent.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Based on my research, packet size is a Provider-Specific Connection Parameters that
indicates a network packet size in bytes. The packet size property value must be from 512
through 32767 and the default SQLOLEDB network packet size is 4096.

Increasing the packet size may not improve the performance because it can aggravate the
burden of network communication. For decreasing the network traffic, I think stored procedure
with re-used Execution Plan can be a better choice. Moreover, the Hardware and Operating
System Limitations and the SQL Server Configuration Settings can also be a consideration.

I recommend you look through the following articles for recommendations and apply some
suggestions that meet the needs on your side.

Optimizing Microsoft SQL Server Performance
http://support.microsoft.com/?id=110352

ADO Performance Best Practices - Get the best performance from your ADO application
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dnsqlmag01/html/BestPrac.asp

319942 HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942

Thank you for using MSDN Newsgroup! If there is anything more I can assist you with, please
feel free to let me know.

Regards,
Billy Yao
Microsoft Online Partner Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Hi Stephane,

How is this issue going on your side currently?

I understand the behavior was intermittent and it's hard to trace the
problem on your side. I am just writing to see whether the problem is under
control on your side after following the suggestions and workarounds
provided to you.

It seems difficult to address the symptom as it was random, and reproducing
the problem also seems impossible. I'm unsure of what you mean by "pausing
between transactions". Could you specify more or provide more detailed
information? What led you to think it was a pausing problem between
transactions? Can you describe what are those transactions? As many details
as possible will make things clear and help us move closer to finding the
cause.

If you have any updates or feedback regarding your experience on this
specific issue, please reply directly to the thread. Your satisfaction is
our #1 priority! Please feel free to let us know if there is anything more
we can assist you with.

Best regards,

Billy Yao
Microsoft Online Partner Support

----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.