Transaction Processing | Microsoft SQL Server 2000 Administrators Companion

3 4

In this section, we'll look at how to analyze the CPU, memory, and disk usage trends of a database server in order to select the proper system for a given application. A database server performs only database functions; in terms of its workload, the server performs only transactions. When a SELECT or UPDATE statement is executed, the database server interprets the statement as a series of read and write operations. In fact, any transaction can be broken down into database reads and writes. At this atomic level, a database server processes I/Os. We should select a system that can handle both the type and volume of transactions and the I/Os those transactions will generate. The two main transaction types are Online Transaction Processing (OLTP) and Decision Support System (DSS).

OLTP Transactions

An OLTP transaction is a workload unit that is usually expected to run in a short period of time because it deals with the database in real time or in online mode. In other words, these transactions will update the database constantly, based on the most current information available, so that the next user can rely on that information being the most current. For example, in an order entry system, all the information pertaining to inventory is kept in tables spread across a disk system, and the database is on line. Any user has access to the database information. Database tables such as Item_Table or Stock_Level_Table contain the most current information about the types and quantity of the items that are sold. That way, when an order for a certain quantity of a specific item is received, you can access the database tables to determine whether the item is available and the quantity of the item in stock, to prevent overselling of an item.

A typical sizing scenario for a transaction processing system such as this involves conducting an interview to gather specific information. During the interview, you might talk to the database designer, the application designer, and the management staff representative. They could provide input and feedback on the expected number of transactions to be processed and the time of day during which the transactions will be expected to be processed (for example, 25,000 transactions should be processed within the 8-hour workday), the number of concurrent users, and the peak operation period (or peak utilization period)—the period in a processing day in which the system is most stressed. The interview is probably the most important part of the sizing process.

NOTE
When you are designing an OLTP system, choose hardware with enough transaction processing capacity to accommodate the peak utilization period. That way you are automatically accommodating the worst-case scenario.

REAL WORLD Automated Teller Machines
Let's look at the example of an automated teller machine (ATM) system. Say you've been hired by a national bank to design an ATM system for their Chicago branch. In the interview, you might discover that the peak utilization period for a network of ATMs is between the hours of 11:00 AM and 2:00 PM—coincidentally, the time range when most people go to lunch. With this information, you can choose a transaction processing system with enough capacity to accommodate this peak utilization period.

DSS Transactions

The second type of transaction system is DSS. DSS transactions involve large returns of information and take much longer to process than OLTP transactions. A DSS transaction can take hours, or even days, to process. An example of a DSS system is an inventory archive system, in which little writing to the database occurs except when an update is taking place. These systems usually provide information to management staff so that they can make important decisions—concerning, for example, business growth or levels of stock on hand. As another example, the U.S. Air Force uses a DSS system to inform high-level personnel about the current status, location, and weaponry of its jet fighters, bombers, and personnel.

As mentioned, a DSS transaction is usually not completed in the same time frame as an OLTP transaction—DSS transactions take much longer to process because of the amount of data they gather. Whereas an OLTP transaction will gather the data required by a unique key (such as a customer number), it starts and ends the query with only the information pertaining to that key. In DSS, the query does not start with a unique key; instead, it starts at the beginning of the database table and continues through all the data to the end of the table. A DSS transaction will also include any table joins, linking to other tables to get further information.

NOTE
When you are designing a DSS system, choose large data block sizes so that more records will fit per I/O transfer, causing less I/O activity.

In this type of system, the performance analyst will expect to see the utilization of CPUs and other system resources at nearly 100 percent, so the concern is not what utilization the system is running at, but how long the system will take to process the query. A rule of thumb for designing a DSS system is to throw as much hardware at it as is reasonable. In other words, don't just have enough disks on hand to handle the space needed for the database, but plan to lay out the database across multiple volumes in order to disperse the I/O activity. Memory is not really a consideration here because there will not be much cache activity. (DSS transactions involve full table scans, which means they start at the top of the table and work their way down.)

REAL WORLD Quarterly Sales
Suppose you are compiling quarterly sales figures for a corporate report. You need to gather information pertaining to the sales of items during that quarter within all regions that the sales organization covers. This search involves first linking to the beginning of the region table to get to the first customer table. After the first customer name is retrieved, a link to the customer order table is established to determine what items were ordered during this time period. The search continues with the second customer name, then the third, and so on. After all the data for the customers for that region is scanned, the next customer table (by region) is retrieved and the process continues. This processing usually takes many hours to complete.