Sybase Database Performance Tuning

Some small aspects of database Performance tuning:
1. 1 Performance Index
Database performance is generally measured in two metrics: response time and throughput. The faster the response, the greater the throughput and the better the database performance. Response time and throughput cannot be improved together in some cases. 1. 2 Tuning Level
Tuning the Sybase database performance can be done in four ways:
Operating system level: network performance, operating system parameters, hardware performance, etc. to improve.
(ii) SQL Server level: Adjust access methods, improve memory management and lock management.
Third, the database design level: The use of descending normal form design, reasonable design index, distributed storage data and so on.
IV Application level: Use efficient SQL statements, arrange transactions reasonably, apply cursors, and handle locks.
This article does not discuss the first, third and fourth aspects, and the second mentioned concepts only apply to Sybase databases.

1. 3 Tuning Tools
When analyzing the performance of Sybase databases, you use some of the performance tuning tools provided by the database system itself, including several system stored procedures:
Name feature Brief introduction
Sp_sysmon Enterprise-Class System performance reporting tool
Sp_lock View the Lock
sp_who View the activity of a thread
Query processing mode for sp_procqmode stored procedures
sp_configure configure SQL Server system-level parameters
Sp_estspace estimate the space and time needed to create a table
sp_spaceused estimate the total number of rows in the table and the space occupied by the table and index
sp_monitor monitoring CPU, I/O statistics activity

When you use some of the tools such as isql, you can also set several options in a query session to display the results of various statistical analyses when the SQL statement is executed:
Meaning of Directive on
After the set NOEXEC On/off parse the SQL statement, you also perform the
Set STATISTICS IO on/off Statistics SQL perform required I/O
Set STATISTICS TIME ON/OFF Statistics SQL statement execution time consuming
Set SHOWPLAN On/off display query plan

1. 4 Sp_sysmon Use
Enterprise performance reporting tools, System stored procedure Sp_sysmon use method:
Under ISQL, first enter Sp_sysmon ' Begin_sample ' to start a report sample after a period of time, then enter the Sp_sys Mon ' End_sample ' ends the last report sampling
or immediately follows a parameter Sp_sysmon ' End_sample ', and ' Dcache ' ends the last report sample, but only shows the data buffering (the database Cache Management) section. The optional parameters of the
replaceable Dcache are shown in the following table:
The full name of the parameter parameter, the content range interpretation
Dcache data cache Management, data buffering
Kernel Kernel utilization, about the engine, Network and I/O situations
Wpm Worker Process Management
Parallel Parallel Query Management
taskmgmt Task Management
APPMGM T application Management
ESP esp Management
Housekeeper housekeeper Task activity
monaccess Monitor Access to Executing SQL
xactsum Transaction profile
xactmgmt Transaction Management
INDEXMGMT index Management, index management
mdcache Metadata Cache Management
Locks lock Management, lock management
Pcache Procedure cache Management
Memory Memory M Anagement
Recovery Recovery Management
Diskio diski/omanagement, disk I/O management
NetIO networki/omanagement

1. 5
The performance benchmark report of database system can be obtained by Sp_sysmon, but it can be used as reference and control basis if it is to be produced in a relatively stable state.

1. 6 Understanding Storage Methods
It is only clear that the underlying details of the data stored in the database, such as the physical structure of data pages, index pages, the size of each row, the width of different types of columns, and so on, can be a deep understanding of various tuning measures. Please refer to the book for more complex and meticulous information on this issue.
Generally, in order to facilitate the database to be directly updated (direct Updates) for the operation of changing data, follow these guidelines:
1 Avoid using columns that allow null values and variable-length columns unless necessary.
2 if the varchar and varbinary columns are filled more fully, do not hesitate to convert to char and binary columns. For the page fillfactor parameter that you specify when you build the table, you weigh the value to determine the size of the number. General: Small value, suitable for a number of randomly inserted tables, the table's data is often deleted, and often increased; large value, suitable for most of the data is added to the end of the table, such as ticket system ticket stub and refund stub table.

2 SQL Server-level tuning
2. 1 Managing Shared Memory
The most important aspect of database performance optimization is the optimal management of memory. The shared memory consumed by the database is divided into data buffering (the cache), stored procedure buffering (Procedure cache), and so on. Using sp_configure ' cache ' under isql, you can see the percentage of the stored procedure buffering (procedure cache percent), the entire data buffer size (the total number, cache size) and other parameters.

2. 1. 1 Stored procedure buffering (Procedure cache)
Stored procedure buffering maintains a query plan for the following objects:
Procedures: Stored Procedures
Triggers: Triggers
Views: view
Rules: Rule
Defaults: Default
Cursors: Cursors
Stored procedures cannot be reentrant, meaning that each concurrent user invocation produces a copy in memory.
Procedure, triggers, and views are optimized by the query optimizer to create a query plan when they are loaded into the Procedure cache. If the stored procedure is in the buffer, it is not required to be recompiled when invoked. If the procedure cache is too small, the stored procedure is often flushed out by other stored procedures that are transferred into memory, and when invoked again, the stored procedure is transferred into memory and then recompiled, and the user request has to wait. In the most serious case, if the procedure cache is insufficient, the stored procedure cannot even run. So in the case of sufficient memory, procedure cache percent parameters as large as possible.

2. 1. 2 Data cache
Data buffering is used to cache data pages and index pages, which is the remaining memory space that is removed from stored procedure buffering and other buffers occupied by the system. It is the most effective way to increase the data buffering by adding physical memory to the server. Of course, if you can't add memory, you can only increase the data buffering by reducing the proportion of stored procedure buffers. by sp_configure the "extenti/obuffers", 20 (Adjustable) command, some pages in the data cache are reserved for use when creating indexes, which can significantly improve the performance of creating indexes. However, be aware of each opening up a buffer to occupy 16K bytes of system memory.

2. 1. 3 name Buffers
The following commands are used:
1> Sp_helpcache
2> Go
To view the named buffer in a ticket database, the results are as follows:
Cache Name Config size Run size overhead

------------------------ ------------- ---------- ----------
Ds30_tran_log 20.00 MB 20.00 MB 2.05 MB
Systemtable 20.00 MB 20.00 MB 2.05 MB
Default data cache 0.00 MB 4462.86 MB 464.97 MB
Left_base_center 16.00 MB 16.00 MB 1.57 MB
Price_cache 8.00 mb 8.00 MB 0.85 mb
You can see that there are 4 named buffers, respectively binding the ticket System Application log table, some important and commonly used system tables, the rest of the ticket table, ticket price Series table, the other 1 is the default data buffering. This configuration is not the most reasonable, you should further systemtable this name buffer subdivided into many, each a separate system table.