About this book


This manual is intended for database administrators, database designers, developers and system administrators.

NoteYou 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.

How to use this book

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:

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.

Related documents

The following documents comprise the Sybase Adaptive Server Enterprise documentation:

Other sources of information

Use the Sybase Technical Library CD and the Technical Library Product Manuals Web site to learn more about your product:

Sybase certifications on the Web

Technical documentation at the Sybase Web site is updated frequently.

StepsFor the latest information on product certifications

  1. Point your Web browser to Technical Documents.

  2. Select Products from the navigation bar on the left.

  3. Select a product name from the product list.

  4. Select the Certification Report filter, specify a time frame, and click Go.

  5. Click a Certification Report title to display the report.

StepsFor the latest information on EBFs and Updates

  1. Point your Web browser to Technical Documents.

  2. Select EBFs/Updates. Enter user name and password information, if prompted (for existing Web accounts) or create a new account (a free service).

  3. Specify a time frame and click Go.

  4. Select a product.

  5. Click an EBF/Update title to display the report.

StepsTo 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.

  1. Point your Web browser to Technical Documents

  2. Click MySybase and create a MySybase profile.


This section describes conventions used in this manual.

Formatting SQL statements

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.

Font and syntax conventions

The font and syntax conventions used in this manual are shown in Table 1.0:

Table 1: Font and syntax conventions in this manual



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.








Parentheses are to be typed as part of the command.


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.


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.

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.


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:

Table 2: Types of expressions used in syntax statements




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”


Any floating-point expression or expression that implicitly converts to a floating value


Any integer expression, or an expression that implicitly converts to an integer value


Any numeric expression that returns a single value


Any expression that returns a single character-type value


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.

If you need help

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.