Over the past few years, there have been a number of titles released on the Sybase enterprise database management system. Administrator's Guide to Sybase ASE 12.5, released late last year, was widely adopted by consultants, DBAs, and administrators of Sybase ASE.

Sybase ASE 12.5 High Availability, released in early 2002, is especially appropriate for those whose businesses demand 24x7 availability of data and applications. The third title in this series, Sybase ASE 12.5 Performance and Tuning, is the one we'll take a closer look at this month. It promises to be just as valuable as the other books, especially to DBAs whose primary task is database maintenance and troubleshooting. The lead author, Jeffrey Garbus, is considered throughout the Sybase community to be one of the most experienced database administrators, consultants, and authors for the ASE enterprise data management software. So let's take a look at the book.

The Chapters
Chapter 1: Introduction
In this chapter we learn that tuning is a balancing act and, as such, we need to prioritize our needs, then we can begin the tuning process. The authors point out the things we need to consider such as:

• Evaluate baseline time for your CPU/drive to determine the expected performance.
• Decide whether the response time is appropriate for the best path the optimizer could take. Are the results expected?
• Examine the problem query. Is it too complex? Does the query resolve the user's actual needs?
• Are appropriate indexes selected for the query?
• Is the optimizer selecting the correct path?
• Break down the query; do individual components also take too long?
• Acquire as much information as possible regarding the circumstances of the query.
• Remember that you can't tune for everything; a physical design will, by nature, be optimized for specific types of queries.
• Spotlight obvious problems.
• Consider tuning options that are transparent to users: indexes, segments, etc.
• Estimate the requirements to resolve problems prior to final rollout. Find (or tool) what will simulate user activity, and act on acquired information.

Chapter 2: Physical Database Design Issues
It's true that from a purely practical standpoint, you'll find that 95% of query tuning for a database application is index selection. To do this correctly, the ones who tune the queries or procedures have to understand how the physical storage structures work (including how space is managed) and how the server chooses indexes. This chapter describes the different index types and how the data is stored physically in the server, and also describes what happens to indexes when the data changes (by inserting or deleting rows), which is necessary to understand as data modification statements can have a variety of adverse impacts on performance with regard to indexes.

Chapter 3: I/O - The Complete Works
As time passes, your database will grow and, with more data available to query, the database administrator will have to understand and tune the I/O system in order to provide adequate response times for retrieving and writing data. This chapter will help you solve upcoming issues. It's not only aimed at database administrators that have systems storing terabytes of information, but at those who have systems with a few hundred megabytes as well, as these can also be problematic. Its goal is to provide you with enough information so you can investigate and understand the systems and understand where the I/O bottlenecks are and how to solve them. The reader will understand that for professional tuning purposes it's important to be aware of the operating system and hardware limits. This will enable you to make educated guesses when it comes to configuring and tuning the ASE servers and the associated hardware. For me, as a more software-oriented consultant, it was very interesting to read about the available hardware and how to measure if changing something at the hardware level really helped to make the system faster.

Chapter 4: Indexes
The default mechanism for searching through data in the Adaptive Server is a table scan. This means that the table is treated as a list and searched from top to bottom. This is a perfectly reasonable method for finding information when the table is very small, but as the table grows, it becomes increasingly inefficient because scans are, by nature, an indirect way of accessing data rows. We learn that while indexes and their different index types are instrumental in improving the performance of queries, they can also slow the performance of inserts, deletes, and updates; we also learn why it's essential to use the different statistical methods that are available.

Chapter 5: The Optimizer Statistics
The previous chapter was a preperation for this chapter, where the authors dig into the details of the optimizer statistics. A tip provided: when upgrading to 11.9.2 or above from an earlier version, you'll need to take the statistics into consideration. It's a good idea to run update statistics after any upgrade. If a column is the leading column of an index when upgrading from a pre-11.9.2 version, the old distribution page is read and its values are used to establish the new statistics. This essentially copies the old statistics values into the new values. This copy is not as accurate as statistics obtained by reading the data in ASE 11.9.2 or above. I was impressed at how many administrators I met who didn't know that and haven't done it so far.