SQL Server Performance Forum – Threads Archive

local value for network packet size

I know it is not recommended to change the default seting for
network packet size from 4KB to a large value, but if I have
queries need to carry large amount of data between application
and sql server, can I set a local value for network packet size
and reset it to default after having done with such exec? In ADO, there is a CacheSize parameter to manipulate, which
affects only locally. Is this the same as: EXEC sp_configure ‘network packet size’, 8192
RECONFIGURE WITH OVERRIDE
GO
? I meant, will this exec sp_configure change only locally or globally?

---

Running sp on server, change globally. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

if your app server and sql server are connected on gigabit ethernet, and all components support large packets (both adapters and any switched in between) you might think about enabling large packets in addition to this setting, otherwise, it may not help

---

Does your application database deals with image/text datatypes if so make sure you’ve tested this option and apply to production. General guidance is do not change the packet size unless you are certain that it will improve performance. For most applications, the default packet size is best. And its not advisable to change this option on fly which would affect the performance too. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Satya, your last comment is the one I was looking for:
I know in general I should keep it as default, but
in some cases, I need to set this to larger value.
Since this change is a global change and I am not
sure the consequence on other queries, I might as
well just leave it unchanged.
Thanks!

---

In anycase ensure testing is in place before accelerating to Produciton. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>