Network Monitoring

3 4

As we've seen, the type and speed of the network hardware you choose can affect the overall performance of your database system. If you try to transmit more data than your network can handle at one time, data transmissions will queue up and will be delayed. This delay will, in turn, slow down the entire system.

You can get an idea of the maximum performance of the network based on the hardware that is installed. You also have an idea of where performance problems can occur. Using this information, you can often solve problems by simply adding network cards. The first step in finding network problems is to periodically monitor the network. You can use the data you collect to determine whether you are experiencing a network problem and to then develop a solution to any problems that you might experience.

Monitoring Performance

Monitoring the network is not always as easy as you might think. It is usually necessary to purchase additional network-monitoring hardware or software to be able to effectively monitor the network. A couple of factors determine whether such purchases are necessary, as discussed in the next two paragraphs.

First, all the database servers and clients on your physical network might not be using the same protocols. For example, a system that is running TCP/IP on Ethernet will be able to detect (at the operating system level) only traffic that is TCP/IP in nature. IPX/SPX packets will be filtered out at the device driver level. Typically, custom device drivers and network layers are required by network-monitoring software.

Second, the network card itself filters out data that is not intended for your particular machine; therefore, not all of the network data is transmitted up to the driver or operating system. To view all network activity, you must apply a custom device driver and network layer. Thus, a typical workstation is not usually able to monitor all the traffic on the physical network without your making some modifications.

Once you have installed network-monitoring hardware or software or both, you can get a good idea of the amount of traffic your network is handling. This traffic might be directed at your systems, but sometimes traffic occurs as a result of routing or configuration problems. (The topic of debugging network hardware problems is beyond the scope of this book.) After you have your network-monitoring system in place, look at the following items:

• Utilization How much data is being transmitted across the network? How does this amount compare with the bandwidth of the network hardware?
• Packet sizes How big are the network transmissions? Are they large, efficient transmissions or small ones?
• Collisions (if applicable) Are you experiencing a large number of collisions? If so, why?
• Errors Do you have many damaged transmissions that need to be retransmitted? This can be an indication of faulty network adapters or cabling.

Determining Whether You Have a Problem

Once you have collected the performance data, you will need to determine whether you have a problem. This is not always easy. Network performance problems do not usually manifest themselves in failures; rather, the effect is degraded performance. To determine whether you are experiencing a problem, you will have to compare the data extracted from monitoring with the configuration information for your network.

It is usually a good idea not to exceed 75 percent of the network's bandwidth. If the majority of network transmissions are small, you might want to reduce this percentage; handling many small transmissions requires more overhead than handling a few large transmissions. In an Ethernet network, this reduction will in turn reduce the number of collisions. Response time to network requests will decrease, resulting in a faster network.

Some problems are a little more obvious to detect than bandwidth issues. Check for a high rate of collisions and errors. If you are close to the 75 percent threshold and collisions are high, you might be nearing the capacity of your network. If network traffic is relatively light and collisions are high, you might be experiencing hardware errors.

Also, check for transmission errors. Transmission errors generally indicate faulty hardware. The hardware in question can be anything from a network card to cables, routers, bridges, and so on. Once a problem is identified, it's time to call in a network expert.

Finding Solutions to Network Problems

You can solve bandwidth problems in a number of ways, depending on the specific problem. You might be able to solve the problem by purchasing more or different hardware, segmenting the network, or even redesigning the application.

One way to reduce your percentage of network utilization is to increase the network's bandwidth. Moving from 10BaseT to 100BaseT increases the bandwidth tenfold. This solution is simple and easy, but it can be expensive. Let's look at alternatives.

If you are seeing too much traffic on the network, it might be time to divide the network into subnets, based on departments or workgroups. By subnetting, you can create a network for each office or department instead of having the entire company on the same network. This process will reduce the number of systems on a single network and thus reduce the traffic. Sometimes, the network will grow slowly over a long period of time, and you might not notice additional traffic until a problem occurs. The use of subnets might be the best solution to alleviate network congestion.

Another solution is to look at the network usage from a functional standpoint. Is the network being used for good reasons? Are the applications returning too much data? It is always a good idea to look at the SQL Server client applications to be sure that they are not requesting more rows than users need. Using queries that return the minimum number of rows is an easy way to reduce network traffic if you have many users.

As you can see, there can be a variety of problems and thus a variety of solutions. Don't be afraid to look at all the possibilities. Logic errors in applications can sometimes manifest themselves as network bandwidth problems. Scheduling problems can also arise—for example, it's not a good idea to back up your network during the busiest time of day.