Network Components and SQL Server Performance

3 4

The network is divided into two layers: the software layer, which houses the network protocols, and the hardware layer. For the purposes of this book, the hardware layer includes the software drivers necessary to run the hardware. These network layers are independent of each other and can each have more than one component. For example, it is possible to run both TCP/IP and IPX/SPX over the same network card, as well as to run multiple network cards with the same protocol. This structure is illustrated in Figure 11-8.

Figure 11-8. The network layers.

Each network layer has its own characteristics and performance considerations. As mentioned, there are several reasons for choosing a particular protocol or network hardware component. Usually, this choice is made based on your business rules and how each system is connected to other systems in your network. This book will not try to convince you to run a particular protocol or to use particular network hardware. In this section, we'll examine the software-related and hardware-related factors that might affect SQL Server functionality and performance.

The Software Layer—Network Protocols

As mentioned, the network protocols include named pipes, TCP/IP, NWLink IPX/SPX, AppleTalk, and Banyan VINES. Essentially, all the network protocols function in about the same manner as far as SQL Server is concerned. If a network isn't functioning or performing as you expect, the problem most likely resides in the hardware layer.

Connectivity problems, on the other hand, usually occur at either the net-library layer or the network protocol layer. If you are having problems connecting a SQL Server client to a SQL Server 2000 server, try connecting in some other manner—for example, by using Windows Explorer. If you can connect via Windows Explorer but not via SQL Server, your problem probably relates to SQL Server. Be sure that you are trying to connect using the appropriate network protocol. If multiple protocols are configured, it is sometimes hard to figure out which one you are using. If you can connect to the server via PING, Internet Explorer, or some other external source, the problem is most likely in your selection of net-libraries.

Regardless of which network protocol you use, a number of performance issues can arise at the hardware level. By configuring your system to stay within the limits of your network, you should experience few problems.

The Hardware Layer

You'll need to understand the hardware layer to determine where network performance problems are occurring. The physical hardware layer and the protocol layers are independent, which means that you can run various network protocols on any number of network hardware devices. The networking hardware you choose helps determine the performance of the network. The amount of traffic that the network can handle depends on both the type of the network and the speed of the network.

Network Bandwidth

Network bandwidth measures the amount of data that a network can transmit in a specified amount of time. Network bandwidth is sometimes stated in the name of the network hardware itself, as in 10BaseT and 100BaseT, which indicate a 10-megabit per second (Mbps) bandwidth and a 100-Mbps bandwidth, respectively.

This measurement of network throughput can sometimes be deceiving, however. For most network hardware, the rate at which a particular network adapter can transmit data decreases as the size of the transmission decreases because each network transmission takes a certain amount of overhead. For example, the amount of overhead necessary to transmit 64 kilobytes (KB) of data is approximately the same amount of overhead that is necessary to transmit 2 KB of data. RDBMS's, including SQL Server, typically deal with transmissions of small amounts of data. Thus, the amount of data that your server can handle might be smaller than the bandwidth of the network hardware.

Ethernet

Perhaps the most popular network hardware is Ethernet, although many other options are available. Ethernet speeds have increased over the last few years and will continue to increase. Xerox, DEC, and Intel developed Ethernet in 1976. In the early days, when coaxial cable, or coax, was used, Ethernet had a bandwidth of approximately 3 Mbps. This bandwidth increased with the introduction of 10BaseT technology, which boosted the network bandwidth to 10 Mbps, and then 100BaseT technology increased it again, to 100 Mbps. Soon, Gigabit Ethernet will be available, which offers 1-Gbps bandwidth. This comparison is shown in the following table.

Network	Bandwidth
Coax Ethernet	3 Mbps
10BaseT	10 Mbps
100BaseT	100 Mbps
Gigabit Ethernet	1000 Mbps

Although the bandwidth of Ethernet networks is skyrocketing, Ethernet suffers from a serious problem: multiple Ethernet adapters might try to transmit data at the same time. If two or more adapters transmit at precisely the same time, a collision will occur. Each of the adapters involved in the collision must wait and then attempt to transmit the data again. Although the amount of time lost is small, these delays can add up. The more collisions that occur, the longer you will wait for retransmission.

The chance of a collision increases as network traffic increases. If the amount of traffic approaches the capacity of the network, the chance of a collision is quite high, as Figure 11-9 illustrates. Performance degrades when collisions occur. It is important to monitor network traffic and to watch for collisions. For example, you can follow a rule of thumb not to exceed 75 percent of the network's bandwidth. Of course, your network will have brief periods of heavy usage during which traffic exceeds this value, but you don't want it to exceed this limit for long periods of time.

Figure 11-9. The relationship between the chance of collision and network utilization.

Token Ring

A token ring network gives each member of the ring a chance to communicate with other members by passing a token. The token allows only one system at a time to transmit data on the network. With this type of architecture, you can utilize almost the entire bandwidth of the network without causing undue delays in communication.

Token ring, like Ethernet, encompasses a number of technologies, each offering a different bandwidth, as shown in the following table. But because token ring is a series of point-to-point connections and no collisions can occur, almost the entire bandwidth can be used. As is Ethernet technology, token ring technology is constantly improving.

Network	Bandwidth
IEEE 802.3	token ring 1, 4, or 16 Mbps
IEEE 802.5	100 Mbps
Gigabit	token ring 1000 Mbps
Many other network hardware options are available, including ATM and fiber optics.