|
|
| |
SQL Server Settings Optimization Tips
*****
- You can increase the 'min memory per query' option to improve the
performance of queries that use hashing or sorting operations, if your
SQL Server has a lot of memory available and there are many queries
running concurrently on the server.
The SQL Server will automatically allocate, at a minimum, the amount of
memory set in this configuration setting. The default 'min memory per query'
option is equal to 1024 Kb.
*****
- You can increase the 'max async IO' option if your SQL Server works
on a high performance server with high-speed intelligent disk subsystem
(such as hardware-based RAID with more than 10 disks).
This option specifies the maximum number of outstanding asynchronous
disk I/O requests that the entire server can issue against a file.
By the way, the 'max async IO' SQL Server option is no longer supported
in SQL Server 2000.
*****
- You can change the 'network packet size' option to the appropriate value.
This option can improve performance on networks whose base topology supports
larger packets than TCP/IP's default of 4096 bytes. For example, if client
sends or receives large amounts of data, a larger packet size can improve
performance, because it results in fewer network reads and writes.
The default value for the 'network packet size' option is 4096 bytes.
Microsoft does not recommend changing this option, because for most
applications, the default packet size of 4096 bytes is best.
*****
- You can change the 'fill factor' option to the appropriate value.
The 'fill factor' option specifies how full SQL Server will make each
index page. When there is no free space to insert new row on the index
page, SQL Server will create new index page and transfer some rows from
the previous page to the new one. This operation is called page splits.
You can reduce the number of page splits by setting the appropriate
fill factor option to reserve free space on each index page.
The fill factor is a value from 1 through 100 that specifies the
percentage of the index page to be left empty.
The default value for fill factor is 0. It is treated similarly to a
fill factor value of 100, the difference in that SQL Server leaves
some space within the upper level of the index tree for FILLFACTOR = 0.
The fill factor percentage is used only at the time the index is created.
If the table contains read-only data (or data that very rarely changed),
you can set the 'fill factor' option to 100. When the table's data modified
very often, you can decrease the 'fill factor' option to 70 percent, for
example.
*****
- You can increase the 'recovery interval' value.
The 'recovery interval' option specifies the maximum number of minutes
per database that SQL Server needs to complete its recovery procedures.
The default value of this option is 0. It means that SQL Server will
automatically configure this option.
SQL Server issues a checkpoint using the 'recovery interval' option.
Microsoft does not recommend changing this option in general case,
but sometimes you can improve performance by changing this option.
You can monitor disk-write activity on the data files, and if you see
periodic spikes that send disk utilization to 100 percent, you can
increase the recovery interval. In this case, Microsoft suggests
setting the 'recovery interval' option to 5 and continuing monitoring.
*****
- You can set the 'priority boost' SQL Server options to 1.
You can set this option to 1, if you want from SQL Server to work with
a higher priority than other processes on the same computer. The default
value is 0. Setting 'priority boost' to 1 can degrade the performance of
other applications running on the same computer with SQL Server.
So, you should set the 'priority boost' SQL Server options to 1 only if
you have dedicated server to SQL Server.
In other case, do not change this option.
*****
- Set the 'max worker threads' options to the maximum number of the
user connections to your SQL Server box.
The default setting for the 'max worker threads' option is 255. If the
number of user connections will be less than the 'max worker threads'
value, a separate operating system thread will be created for each client
connection, but if the number of user connections will exceed this value
the thread pooling will be used.
For example, if the maximum number of the user connections to your SQL Server
box is equal to 50, you can set the 'max worker threads' options to 50, this
frees up resources for SQL Server to use elsewhere. If the maximum number
of the user connections to your SQL Server box is equal to 500, you can set
the 'max worker threads' options to 500, this can improve SQL Server
performance because thread pooling will not be used.
*****
- You can specify the 'min server memory' and 'max server memory' options.
These options can be used to specify the fixed amount of memory to allocate
to SQL Server. In this case, you should set the 'min server memory' and
'max server memory' to the same value (equal to the maximum amount of
physical memory that SQL Server will use), and set the 'set working set size'
SQL Server option to 1. This can improve performance because SQL Server
will not dynamically allocate memory.
You can also change these options when SQL Server works on the same computer
with other applications. In this case, the 'min server memory' options is
used to allow SQL Server works when other applications pretend to use all
available memory, and the 'max server memory' options is used to allow
other applications work when SQL Server tried to use all available resources.
*****
- You can specify the 'set working set size' SQL Server option to
reserve the amount of physical memory space for SQL Server.
Unlike SQL Server 6.5, SQL Server 7.0/2000 can automatically allocate
memory (can take more memory if SQL Server need it, and can give memory
back to operation system). This is one of the main advantages in
comparison with previous versions, but dynamic memory allocation
takes some time. If you know the maximum amount of physical memory
that SQL Server will use, you can specify this amount by setting
'min server memory' and 'max server memory' to the same value (equal
to the maximum amount of physical memory that SQL Server will use)
and set the 'set working set size' option to 1.
*****
|
|
|
