Home

	Expand Search

Performance and Tuning Guide Volumes 1 - 3 (Online Only)

Chapter 2 Networks and Performance

Chapter 2

Networks and Performance

This chapter discusses the role that the network plays in performance of applications using Adaptive Server.

Introduction

Usually, the System Administrator is the first to recognize a problem on the network or in performance, including such things as:

• Process response times vary significantly for no apparent reason.

• Queries that return a large number of rows take longer than expected.

• Operating system processing slows down during normal Adaptive Server processing periods.

• Adaptive Server processing slows down during certain operating system processing periods.

• A particular client process seems to slow all other processes.

Potential performance problems

Some of the underlying problems that can be caused by networks are:

• Adaptive Server uses network services poorly.

• The physical limits of the network have been reached.

• Processes are retrieving unnecessary data values, increasing network traffic unnecessarily.

• Processes are opening and closing connections too often, increasing network load.

• Processes are frequently submitting the same SQL transaction, causing excessive and redundant network traffic.

• Adaptive Server does not have enough network memory.

• Adaptive Server's network packet sizes are not big enough to handle the type of processing needed by certain clients.

Basic questions on network performance

When looking at problems that you think might be network-related, ask yourself these questions:

• Which processes usually retrieve a large amount of data?

• Are a large number of network errors occurring?

• What is the overall performance of the network?

• What is the mix of transactions being performed using SQL and stored procedures?

• Are a large number of processes using the two-phase commit protocol?

• Are replication services being performed on the network?

• How much of the network is being used by the operating system?

Techniques summary

Once you have gathered the data, you can take advantage of several techniques that should improve network performance. These techniques include:

• Using small packets for most database activity

• Using larger packet sizes for tasks that perform large data transfers

• Using stored procedures to reduce overall traffic

• Filtering data to avoid large transfers

• Isolating heavy network users from ordinary users

• Using client control mechanisms for special cases

Using sp_sysmon while changing network configuration

Use sp_sysmon while making network configuration changes to observe the effects on performance. Use Adaptive Server Monitor to pinpoint network contention on a particular database object.

For more information about using sp_sysmon, see the Performance and Tuning Guide: Tools for Monitoring and Analyzing Performance book.

How Adaptive Server uses the network

All client/server communication occurs over a network via packets. Packets contain a header and routing information, as well as the data they carry.

Adaptive Server was one of the first database systems to be built on a network-based client/server architecture. Clients initiate a connection to the server. The connection sends client requests and server responses. Applications can have as many connections open concurrently as they need to perform the required task.

The protocol used between the client and server is known as the Tabular Data Stream(TM) (TDS), which forms the basis of communication for many Sybase products.

Changing network packet sizes

By default, all connections to Adaptive Server use a default packet size of 512 bytes. This works well for clients sending short queries and receiving small result sets. However, some applications may benefit from an increased packet size.

Typically, OLTP sends and receives large numbers of packets that contain very little data. A typical insert statement or update statement may be only 100 or 200 bytes. A data retrieval, even one that joins several tables, may bring back only one or two rows of data, and still not completely fill a packet. Applications using stored procedures and cursors also typically send and receive small packets.

Decision support applications often include large batches of Transact-SQL and return larger result sets.

In both OLTP and DSS environments, there may be special needs such as batch data loads or text processing that can benefit from larger packets.

The System Administration Guide describes how to change these configuration parameters:

• The default network packet size, if most of your applications are performing large reads and writes

• The max network packet size and additional network memory, which provides additional memory space for large packet connections

Only a System Administrator can change these configuration parameters.

Large versus default packet sizes for user connections

Adaptive Server reserves enough space for all configured user connections to log in at the default packet size. Large network packets cannot use that space. Connections that use the default network packet size always have three buffers reserved for the connection.

Connections that request large packet sizes acquire the space for their network I/O buffers from the additional network memory region. If there is not enough space in this region to allocate three buffers at the large packet size, connections use the default packet size instead.

Number of packets is important

Generally, the number of packets being transferred is more important than the size of the packets. "Network" performance also includes the time needed by the CPU and operating system to process a network packet. This per-packet overhead affects performance the most. Larger packets reduce the overall overhead costs and achieve higher physical throughput, provided that you have enough data to be sent.

The following big transfer sources may benefit from large packet sizes:

• Bulk copy

• readtext and writetext commands

• select statements with large result sets

There is always a point at which increasing the packet size will not improve performance, and may in fact decrease performance, because the packets are not always full. Although there are analytical methods for predicting that point, it is more common to vary the size experimentally and plot the results. If you conduct such experiments over a period of time and conditions, you can determine a packet size that works well for a lot of processes. However, since the packet size can be customized for every connection, specific experiments for specific processes can be beneficial.

The results can be significantly different between applications. Bulk copy might work best at one packet size, while large image data retrievals might perform better at a different packet size.

If testing shows that some specific applications can achieve better performance with larger packet sizes, but that most applications send and receive small packets, clients need to request the larger packet size.

Evaluation tools with Adaptive Server

The sp_monitor system procedure reports on packet activity. This report shows only the packet-related output:

...
packets received packets sent packet err
---------------- ------------ ----------
10866(10580)     19991(19748) 0(0)
...

You can also use these global variables:

• @@pack_sent - Number of packets sent by Adaptive Server

• @@pack_received - Number of packets received

• @@packet_errors - Number of errors

These SQL statements show how the counters can be used:

select "before" = @@pack_sent

select * from titles

select "after" = @@pack_sent

Both sp_monitor and the global variables report all packet activity for all users since the last restart of Adaptive Server.

See the Performance and Tuning Guide: Tools for Performance Statistics book for more information about sp_monitor and these global variables.

Evaluation tools outside of Adaptive Server

Operating system commands also provide information about packet transfers. See the documentation for your operating system for more information about these commands.

Server-based techniques for reducing network traffic

Using stored procedures, views, and triggers can reduce network traffic. These Transact-SQL tools can store large chunks of code on the server so that only short commands need to be sent across the network. If your applications send large batches of Transact-SQL commands to Adaptive Server, converting them to use stored procedures can reduce network traffic.

• Stored procedures

  Applications that send large batches of Transact-SQL can place less load on the network if the SQL is converted to stored procedures. Views can also help reduce the amount of network traffic.

  You may be able to reduce network overhead by turning off "doneinproc" packets.

  For more information, see "Reducing packet overhead"

• Ask for only the information you need

  Applications should request only the rows and columns they need, filtering as much data as possible at the server to reduce the number of packets that need to be sent. In many cases, this can also reduce the disk I/O load.

• Large transfers

  Large transfers simultaneously decrease overall throughput and increase the average response time. If possible, large transfers should be done during off-hours. If large transfers are common, consider acquiring network hardware that is suitable for such transfers. Table 2-1 shows the characteristics of some network types.

  Network options

  Type	Characteristics
  Token ring	Token ring hardware responds better than Ethernet hardware during periods of heavy use.
  Fiber optic	Fiber-optic hardware provides very high bandwidth, but is usually too expensive to use throughout an entire network.
  Separate network	A separate network can be used to handle network traffic between the highest volume workstations and Adaptive Server.

• Network overload

  Overloaded networks are becoming increasingly common as more and more computers, printers, and peripherals are network equipped. Network managers rarely detect problems before database users start complaining to their System Administrator

  Be prepared to provide local network managers with your predicted or actual network requirements when they are considering the adding resources. You should also keep an eye on the network and try to anticipate problems that result from newly added equipment or application requirements.

Impact of other server activities

You should be aware of the impact of other server activity and maintenance on network activity, especially:

• Two-phase commit protocol

• Replication processing

• Backup processing

These activities, especially replication processing and the two-phase commit protocol, involve network communication. Systems that make extensive use of these activities may see network-related problems. Accordingly, these activities should be done only as necessary. Try to restrict backup activity to times when other network activity is low.

Single user versus multiple users

You must take the presence of other users into consideration before trying to solve a database problem, especially if those users are using the same network.

Since most networks can transfer only one packet at a time, many users may be delayed while a large transfer is in progress. Such a delay may cause locks to be held longer, which causes even more delays.

When response time is "abnormally" high, and normal tests indicate no problem, it could be due to other users on the same network. In such cases, ask the user when the process was being run, if the operating system was generally sluggish, if other users were doing large transfers, and so on.

In general, consider multiuser impacts, such as the delay caused by a long transaction, before digging more deeply into the database system to solve an abnormal response time problem.

Improving network performance

Isolate heavy network users

Isolate heavy network users from ordinary network users by placing them on a separate network, as shown in Figure 2-1.

In the "Before" diagram, clients accessing two different Adaptive Servers use one network card. Clients accessing Servers A and B have to compete over the network and past the network card.

In the "After" diagram, clients accessing Server A use one network card and clients accessing Server B use another.

Set tcp no delay on TCP networks

By default, the configuration parameter tcp no delay is set to "off," meaning that the network performs packet batching. It briefly delays sending partial packets over the network.

While this improves network performance in terminal-emulation environments, it can slow performance for Adaptive Server applications that send and receive small batches. To disable packet batching, a System Administrator can set the tcp no delay configuration parameter to 1.

Configure multiple network listeners

Use two (or more) ports listening for a single Adaptive Server. Front-end software may be directed to any configured network ports by setting the DSQUERY environment variable.

Using multiple network ports spreads out the network load and eliminates or reduces network bottlenecks, thus increasing Adaptive Server throughput.

See the Adaptive Server configuration guide for your platform for information on configuring multiple network listeners.