This manual is intended for database administrators, database designers, developers and system administrators.
You may want to use your own database for testing changes
and queries. Take a snapshot of the database in question and set
it up on a test machine.
This manual would normally be used to fine tune, troubleshoot or improve the performance on Adaptive Server. The Performance and Tuning Guide is divided into three books:
Volume 1 - Basics
Volume 2 - Optimizing and Abstract Plans
Volume 3 - Tools for Monitoring and Analyzing Performance
The following information is covered:
Volume 1- Basics
Chapter 1, “Overview,” describes the major components to be analyzed when addressing performance.
Chapter 2, “Networks and Performance,” provides a brief description of relational databases and good database design.
Chapter 3, “Using Engines and CPUs,”describes how client processes are scheduled on engines in Adaptive Server.
Chapter 4, “Distributing Engine Resources” describes how to assign execution precedence to specific applications.
Chapter 5, “Controlling Physical Data Placement” describes the uses of segments and partitions for controlling the physical placement of data on storage devices.
Chapter 6, “Database Design” provides a brief description of relational databases and good database design.
Chapter 7, “Data Storage” describes Adaptive Server page types, how data is stored on pages, and how queries on heap tables are executed.
Chapter 8, “Indexing for Performance” provides guidelines and examples for choosing indexes.
Chapter 9, “How Indexes Work” provides information on how indexes are used to resolve queries.
Chapter 10, “Locking in Adaptive Server” describes the types of locks that Adaptive Server uses and what types of locks are acquired during query processing.
Chapter 11, “Using Locking Commands” describes the commands that set locking schemes for tables and control isolation levels and other locking behavior during query processing.
Chapter 12, “Reporting on Locks” describes the system procedures that report on locks and lock contention.
Chapter 13, “Locking Configuration and Tuning” describes the impact of locking on performance and describes the tools to analyze locking problems and configure locking.
Chapter 14, “Setting Space Management Properties” describes how space management properties can be set for tables to improve performance and reduce the frequency of maintenance operations on tables and indexes.
Chapter 15, “Memory Use and Performance” describes how Adaptive Server uses memory for the procedure and data caches.
Chapter 16, “Determining Sizes of Tables and Indexes,” describes different methods for determining the current size of database objects and for estimating their future size.
Chapter 17, “Maintenance Activities and Performance” describes the impact of maintenance activities on performance, and how some activities, such as re-creating indexes, can improve performance.
Chapter 18, “tempdb Performance Issues” stresses the importance of the temporary database , tempdb, and provides suggestions for improving its performance.
Volume 2 - Optimizing and Abstract Plans
Chapter 19, “Adaptive Server Optimizer” explains the process of query optimization, how statistics are applied to search arguments and joins for queries.
Chapter 20, “Advanced Optimizing Tools” describes advanced tools for tuning query performance.
Chapter 21, “Query Tuning Tools” presents an overview of query tuning tools and describes how these tools can interact.
Chapter 22, “Access Methods and Query Costing for Single Tables” describes how Adaptive Server accesses tables in queries that only involve one table and how the costs are estimated for various access methods.
Chapter 23, “Accessing Methods and Costing for Joins and Subqueries” describes how Adaptive Server accesses tables during joins and subqueries, and how the costs are determined.
Chapter 24, “Parallel Query Processing” intoduces the concepts and resources required for parallel query processing.
Chapter 25, “Parallel Query Optimization” provides an indepth look at the optimization of parallel queries.
Chapter 26, “Parallel Sorting” describes the use of parallel sorting for queries and creating indexes.
Chapter 27, “Tuning Asynchronous Prefetch” describes how asynchronous prefetch improves performance for queries that perform large disk I/O.
Chapter 28, “Cursors and Performance” describes performance issues with cursors.
Chapter 29, “Introduction to Abstract Plans” provides an overview of abstract plans and how they can be used to solve query optimization problems.
Chapter 30, “Abstract Query Plan Guide” provides an introduction to writing abstract plans for specific types of queries and to using abstract plans to detect changes in query optimization due to configuration or system changes.
Chapter 31, “Creating and Using Abstract Plans” describes the commands that can be used to save and use abstract plans.
Chapter 32, “Managing Abstract Plans with System Procedures” describes the system procedures that manage abstract plans and abstract plan groups.
Chapter 33, “Abstract Plan Language Reference” describes the abstract plan language.
Volume 3 - Tools for Monitoring and Analyzing Performance
Chapter 34, “Using Statistics to Improve Performance” describes how to use the update statistics command to create and update statistics.
Chapter 35, “Using the set statistics Commands” explains the commands that provide information about execution.
Chapter 36, “Using set showplan” provides examples of showplan messages.
Chapter 37, “Statistics Tables and Displaying Statistics with optdiag” describes the tables that store statistics and the output of the optdiag command that displays the statistics used by the query optimizer.
Chapter 38, “Tuning with dbcc traceon” explains how to use the dbcc traceon commands to analyze query optimization problems.
Chapter 39, “Monitoring Performance with sp_sysmon” describes how to use a system procedure that monitors Adaptive Server performance.
The full index for all three volumes is in the back of Volume 3- Tools for Monitoring and Analyzing Performance.
The following documents comprise the Sybase Adaptive Server Enterprise documentation:
The release bulletin for your platform – contains last-minute information that was too late to be included in the books.
A more recent version of the release bulletin may be available on the World Wide Web. To check for critical product or document information that was added after the release of the product CD, use the Sybase Technical Library.
The Installation Guide for your platform – describes installation, upgrade, and configuration procedures for all Adaptive Server and related Sybase products.
Configuring Adaptive Server Enterprise for your platform – provides instructions for performing specific configuration tasks for Adaptive Server.
What’s New in Adaptive Server Enterprise? – describes the new features in Adaptive Server version 12.5, the system changes added to support those features, and the changes that may affect your existing applications.
Transact-SQL User’s Guide – documents Transact-SQL, Sybase’s enhanced version of the relational database language. This manual serves as a textbook for beginning users of the database management system. This manual also contains descriptions of the pubs2 and pubs3 sample databases.
System Administration Guide – provides in-depth information about administering servers and databases. This manual includes instructions and guidelines for managing physical resources, security, user and system databases, and specifying character conversion, international language, and sort order settings.
Reference Manual – contains detailed information about all Transact-SQL commands, functions, procedures, and datatypes. This manual also contains a list of the Transact-SQL reserved words and definitions of system tables.
Performance and Tuning Guide – explains how to tune Adaptive Server for maximum performance. This manual includes information about database design issues that affect performance, query optimization, how to tune Adaptive Server for very large databases, disk and cache issues, and the effects of locking and cursors on performance.
The Utility Guide – documents the Adaptive Server utility programs, such as isql and bcp, which are executed at the operating system level.
The Quick Reference Guide – provides a comprehensive listing of the names and syntax for commands, functions, system procedures, extended system procedures, datatypes, and utilities in a pocket-sized book. Available only in print version.
The System Tables Diagram – illustrates system tables and their entity relationships in a poster format. Available only in print version.
Error Messages and Troubleshooting Guide – explains how to resolve frequently occurring error messages and describes solutions to system problems frequently encountered by users.
Component Integration Services User’s Guide – explains how to use the Adaptive Server Component Integration Services feature to connect remote Sybase and non-Sybase databases.
Java in Adaptive Server Enterprise – describes how to install and use Java classes as datatypes, functions, and stored procedures in the Adaptive Server database.
Using Sybase Failover in a High Availability System – provides instructions for using Sybase’s Failover to configure an Adaptive Server as a companion server in a high availability system.
Using Adaptive Server Distributed Transaction Management Features – explains how to configure, use, and troubleshoot Adaptive Server DTM features in distributed transaction processing environments.
EJB Server User’s Guide – explains how to use EJB Server to deploy and execute Enterprise JavaBeans in Adaptive Server.
XA Interface Integration Guide for CICS, Encina, and TUXEDO – provides instructions for using Sybase’s DTM XA interface with X/Open XA transaction managers.
Glossary – defines technical terms used in the Adaptive Server documentation.
Sybase jConnect for JDBC Programmer’s Reference – describes the jConnect for JDBC product and explains how to use it to access data stored in relational database management systems.
Full-Text Search Specialty Data Store User’s Guide – describes how to use the Full-Text Search feature with Verity to search Adaptive Server Enterprise data.
Historical Server User’s Guide –describes how to use Historical Server to obtain performance information for SQL Server and Adaptive Server.
Monitor Server User’s Guide – describes how to use Monitor Server to obtain performance statistics from SQL Server and Adaptive Server.
Monitor Client Library Programmer’s Guide – describes how to write Monitor Client Library applications that access Adaptive Server performance data.
Use the Sybase Technical Library CD and the Technical Library Product Manuals Web site to learn more about your product:
Technical Library CD contains product manuals and is included with your software. The DynaText browser (downloadable from Product Manuals) allows you to access technical information about your product in an easy-to-use format.
Refer to the Technical Library Installation Guide in your documentation package for instructions on installing and starting the Technical Library.
Technical Library Product Manuals Web site is an HTML version of the Technical Library CD that you can access using a standard Web browser. In addition to product manuals, you will find links to the Technical Documents Web site (formerly known as Tech Info Library), the Solved Cases page, and Sybase/Powersoft newsgroups.
To access the Technical Library Product Manuals Web site, go to Product Manuals.
Technical documentation at the Sybase Web site is updated frequently.
For the latest information on product certifications
Point your Web browser to Technical Documents.
Select Products from the navigation bar on the left.
Select a product name from the product list.
Select the Certification Report filter, specify a time frame, and click Go.
Click a Certification Report title to display the report.
For the latest information on EBFs and Updates
Point your Web browser to Technical Documents.
Select EBFs/Updates. Enter user name and password information, if prompted (for existing Web accounts) or create a new account (a free service).
Specify a time frame and click Go.
Select a product.
Click an EBF/Update title to display the report.
To create a personalized view of the Sybase Web
site (including support pages)
Set up a MySybase profile. MySybase is a free service that allows you to create a personalized view of Sybase Web pages.
Point your Web browser to Technical Documents
Click MySybase and create a MySybase profile.
This section describes conventions used in this manual.
SQL is a free-form language. There are no rules about the number of words you can put on a line or where you must break a line. However, for readability, all examples and syntax statements in this manual are formatted so that each clause of a statement begins on a new line. Clauses that have more than one part extend to additional lines, which are indented.
The font and syntax conventions used in this manual are shown in Table 1.0:
Element |
Example |
|---|---|
Command names, command option names, utility names, utility flags, and other keywords are bold. |
select sp_configure |
Database names, datatypes, file names and path names are in italics. |
master database |
Variables, or words that stand for values that you fill in, are in italics. |
select column_name from table_name where search_conditions |
Parentheses are to be typed as part of the command. |
compute row_aggregate ( column_name ) |
Curly braces indicate that you must choose at least one of the enclosed options. Do not type the braces. |
{cash, check, credit}
|
Brackets mean choosing one or more of the enclosed options is optional. Do not type the brackets. |
[anchovies] |
The vertical bar means you may select only one of the options shown. |
{die_on_your_feet | live_on_your_knees | live_on_your_feet}
|
The comma means you may choose as many of the options shown as you like, separating your choices with commas to be typed as part of the command. |
[extra_cheese, avocados, sour_cream]
|
An ellipsis (...) means that you can repeat the last unit as many times as you like. |
buy thing = price [cash | check | credit] [, thing = price [cash | check | credit] ]... You must buy at least one thing and give its price. You may choose a method of payment: one of the items enclosed in square brackets. You may also choose to buy additional things: as many of them as you like. For each thing you buy, give its name, its price, and (optionally) a method of payment. |
Syntax statements (displaying the syntax and all options for a command) appear as follows:
sp_dropdevice [ device_name]
or, for a command with more options:
select column_name from table_name where search_conditions
In syntax statements, keywords (commands) are in normal font and identifiers are in lowercase: normal font for keywords, italics for user-supplied words.
Examples of output from the computer appear as follows:
0736 New Age Books Boston MA0877 Binnet & Hardley Washington DC1389 Algodata Infosystems Berkeley CA
In this manual, most of the examples are in lowercase. However, you can disregard case when typing Transact-SQL keywords. For example, SELECT, Select, and select are the same. Note that Adaptive Server’s sensitivity to the case of database objects, such as table names, depends on the sort order installed on Adaptive Server. You can change case sensitivity for single-byte character sets by reconfiguring the Adaptive Server sort order.
See in the System Administration Guide for more information.
Adaptive Server syntax statements use the following types of expressions:
Usage |
Definition |
|---|---|
expression |
Can include constants, literals, functions, column identifiers, variables, or parameters |
logical expression |
An expression that returns TRUE, FALSE, or UNKNOWN |
constant expression |
An expression that always returns the same value, such as “5+3” or “ABCDE” |
float_expr |
Any floating-point expression or expression that implicitly converts to a floating value |
integer_expr |
Any integer expression, or an expression that implicitly converts to an integer value |
numeric_expr |
Any numeric expression that returns a single value |
char_expr |
Any expression that returns a single character-type value |
binary_expression |
An expression that returns a single binary or varbinary value |
Many of the examples in this manual are based on a database called pubtune. The database schema is the same as the pubs2 database, but the tables used in the examples have more rows: titles has 5000, authors has 5000, and titleauthor has 6250. Different indexes are generated to show different features for many examples, and these indexes are described in the text.The pubtune database is not provided with Adaptive Server. Since most of the examples show the results of commands such as set showplan and set statistics io, running the queries in this manual on pubs2 tables will not produce the same I/O results, and in many cases, will not produce the same query plans as those shown here.
Each Sybase installation that has purchased a support contract has one or more designated people who are authorized to contact Sybase Technical Support. If you cannot resolve a problem using the manuals or online help, please have the designated person contact Sybase Technical Support or the Sybase subsidiary in your area.
