 |
|
SQL Server Tips by Robin Schumacher
|
Other Parameters that
Deserve a Look
There are only a few more configuration parameters that may need
tweaking from time to time; the network packet size parameter; the
fill factor configuration parameter; and the priority boost
parameter.
The network packet size parameter controls the size of the fixed
chunks of data that are sent back and forth between clients and SQL
Server. If there is an application that routinely bulk loads lots of
data into a database or transfers large volumes of text or image
data, this parameter can be increased from its default of four KB.
Another performance indicator is increased wait count and wait time
activity in the NETWORKIO wait event of SQL Server. Increasing the
parameter to eight KB or more can result in fewer network reads and
writes.
The fill factor configuration parameter globally controls the
fillfactor amount used for index creation operations that do not
specify the fillfactor option. An upcoming section will deal more
thoroughly with fillfactor, but for now, if the DBA knows that there
are SQL Server-driven applications that are very INSERT and UPDATE
intensive, this option can be changed to something other than the
default. This normally involves changing fillfactor to a figure in
the neighborhood of 50-70%.
Finally, if the Windows server is a dedicated SQL Server machine,
the priority boost parameter can be set to a value of one. This
indicates that SQL Server should run at a higher priority than other
processes on the Windows machine. Microsoft only recommends doing
this on multi-CPU boxes that are dedicated to running SQL Server.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
