Databases Reference
In-Depth Information
The I/O Affinity property binds I/O operation to some particular subset of processors. By default, SQL
Server I/O operation is allowed on all available processors. In high volume OLTP environments,
grouping I/O tasks on dedicated resources could improve performance, since it can maximize data usage
within local memory to those processors and decrease unnecessary system bus traffic.
Like all other advanced server settings, use this with caution. Ideally, you would conduct a before and
after test to collect performance data to confirm that this could really help your operation. You should
also be careful not to overload any single processor with tons of tasks. Refer to Figure 7-5 for details.
Memory
SQL Server is a very memory-intensive application. Generally speaking, the more physical memory you
can afford, the better it will perform. As discussed earlier, you generally should go with 64-bit servers, as
they provide much more virtual address space than a 32-bit system does. Therefore, it provides a bigger
headroom to scale up. For 32-bit systems, if you have enough memory, you can use the
/PAE
switch in the
boot.ini
file under c:
and enable AWE using
sp_configure
to allocate more memory for SQL Server
consumption. The following code demonstrates how to enable AWE in SQL Server:
\
sp_configure 'show advanced', 1
reconfigure
sp_configure 'awe enabled', 1
reconfigure
Specifically, if you have a 64-bit system, it is very important that you grant the Lock pages in memory
right to SQL Server's startup account. That is done via the group policy editor snap-in called gpedit.msc
(see Figure 7-6).
Please refer to Chapter 6 for more information on locking pages in memory.
In addition, there are a couple of other memory parameters to tinker with. For example, you can set the
minimum size and maximum size of memory SQL Server can use. You can give SQL Server a fixed size
memory to use, by setting the minimum and maximum memory size to the same value. If you so, keep
in mind that SQL Server memory grows until it reaches the minimum memory setting and then it does
not release it. You can do all these with script by using
sp_configure
. However, Figure 7-7 shows you
how to change it in the graphical user interface.
Once again, if SQL Server is not sharing resources with other SQL Server instances or applications, it is
best to leave these memory settings at their default value.However,ifyouaresharingresourceswith
other applications on the server, you may set the memory size for SQL Server so it has enough memory
to work with and yet not over-allocate to the point that the other applications are starved for memory.
Network I/O
I/O can be further classified as network I/O and disk I/O. Disk I/O and its comparison to network I/O
is discussed in Chapter 6. Please refer to it if you need additional information in that area. We will focus
on network I/O in this chapter.
Search WWH ::
Custom Search