Tuning Adaptive Server Enterprise for performance can involve several processes in analyzing the “Why?” of slow performance, contention, optimizing and usage.
This manual covers the basics for understanding and investigating performance questions in Adaptive Server. It guides you in how to look for the places that may be impeding performance.
The remaining manuals for the Performance and Tuning Series are:
Performance and Tuning: Locking
Adaptive Server locks the tables, data pages, or data rows currently used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data within and across transactions. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time.
Carefully considered indexes, built on top of a good database design, are the foundation of a high-performance Adaptive Server installation. However, adding indexes without proper analysis can reduce the overall performance of your system. Insert, update, and delete operations can take longer when a large number of indexes need to be updated.
Performance and Tuning: Optimizer and Abstract Plans
The Optimizer in the Adaptive Server takes a query and finds the best way to execute it. The optimization is done based on the statistics for a database or table. The optimized plan stays in effect until the statistics are updated or the query changes. You can update the statistics on the entire table or by sampling on a percentage of the data.
Adaptive Server can generate an abstract plan for a query, and save the text and its associated abstract plan in the sysqueryplanssystem table. Abstract plans provide an alternative to options that must be specified in the batch or query in order to influence optimizer decisions. Using abstract plans, you can influence the optimization of a SQL statement without having to modify the statement syntax.
Performance and Tuning: Monitoring and Analyzing
Adaptive Server employs reports for monitoring the server. This manual explains how statistics are obtained and used for monitoring and optimizing. The stored procedure sp_sysmon produces a large report that shows the performance in Adaptive Server.
You can also use the Sybase Monitor in Sybase Central for realtime information on the status of the server.
Each of the manuals has been set up to cover specific information that may be used by the system administrator and the database administrator.
