Database Reference
In-Depth Information
Not Running Unnecessary Software
Corporate policy frequently requires virus checking software be installed on the server. You can also have other
products running on the server. When possible, no unnecessary software should be running on the same server as
SQL Server. Exterior applications that have nothing to do with maintaining the Windows Server or SQL Server are best
placed on a different machine.
Network Bottleneck Analysis
In SQL Server OLTP production environments, you will find few performance issues that are because of problems with
the network. Most of the network issues you face in an OLTP environment are in fact hardware or driver limitations or
issues with switches or routers. Most of these issues can be best diagnosed with the Network Monitor tool. However,
Performance Monitor also provides objects that collect data on network activity, as shown in Table
4-2
.
Table 4-2.
Performance Monitor Counters to Analyze Network Pressure
Object(Instance[,InstanceN])
Counter
Description
Value
Network Interface(Network card)
Bytes Total/sec
Rate at which bytes are
transferred on the NIC
Average value < 50% of NIC
capacity, but compare with
baseline
Network Segment
% Net Utilization
Percentage of network
bandwidth in use on a
network segment
Average value < 80% of
network bandwidth, but
compare with baseline
Bytes Total/Sec
You can use the Bytes Total/sec counter to determine how the network interface card (NIC) or network adapter
is performing. The Bytes Total/sec counter should report high values to indicate a large number of successful
transmissions. Compare this value with that reported by the Network Interface\Current Bandwidth performance
counter, which reflects each adapter's bandwidth.
To allow headroom for spikes in traffic, you should usually average no more than 50 percent of capacity. If
this number is close to the capacity of the connection and if processor and memory use are moderate, then the
connection may well be a problem.
% Net Utilization
The % Net Utilization counter represents the percentage of network bandwidth in use on a network segment. The
threshold for this counter depends on the type of network. For Ethernet networks, for example, 30 percent is the
recommended threshold when SQL Server is on a shared network hub. For SQL Server on a dedicated full-duplex
network, even though near 100 percent usage of the network is acceptable, it is advantageous to keep the network
utilization below an acceptable threshold to keep room for the spikes in the load.
■
you must install the network monitor Driver to collect performance data using the network segment
object counters.
Note
