Here are nine "secret" tips that you can use to ensure your SQL Server environment is performing in the most efficient manner possible.
1. If your SQL Server has a lot of memory available and there are many queries
running concurrently on the server, you can increase the 'min memory per query' option to improve the
performance of queries that use hashing or sorting operations.
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.
2. 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.
3. 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 a 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.
4. 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 rows on the index
page, SQL Server will create a new index page and transfer some rows from
the previous page to the new one. This operation is called page splitting.
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 is very rarely changed),
you can set the 'fill factor' option to 100. When the table's data is
modified often, you can decrease the 'fill factor' option to 70 percent, for
example.
5. 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 cases,
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.
6. You can set the 'priority boost' SQL Server options to 1.
You can set this option to 1, if you want 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 a server to SQL Server.
In other cases, do not change this option.
7. Set the 'max worker threads' options to the maximum number of 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 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, which can improve SQL Server
performance because thread pooling will not be used.
8. 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' option is
used to allow SQL Server to work when other applications pretend to use all
available memory and the 'max server memory' option is used to allow
other applications to work when SQL Server tries to use all available
resources.
9. 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 (meaning SQL Server can take more memory if needed, or it 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.
»
See All Articles by Columnist Alexander Chigrik
» 
Comment and Contribute
