Sybase configuration Parameters

sp_configure ' max online engines ', 4
Go
--Configure the number of boot CPUs
sp_configure ' number of engines at startup ', 4
Go
--Configure Maximum memory count
sp_configure ' max memory ', 2097151
Go
--Allocating maximum stored procedure cache
sp_configure ' procedure cache ', 102400
Go
--Configure Caching
Sp_cacheconfig ' default data cache ', ' 700M '
Go
--Default cache allocation page size
Sp_poolconfig ' default data cache ', ' 200M ', ' 16K '
Go
--Network packet size
sp_configure ' max network packet size ', 1024
Go

--Maximum number of connections
sp_configure ' number of user connections ', 500
Go
--Maximum Open object
sp_configure ' number of open object ', 9000
Go
--Maximum Index
sp_configure ' number of open index ', 10000
Go
--Maximum number of locks
sp_configure ' number of locks ', 100000
Go

--Increase network memory
--sp_configure ' Additional network memory ', 1024
Go

--Lock memory
sp_configure ' lock shared memory ', 512
Go

--Optimizing tempdb
Select dbid, Name,segmap
From Sysusages, sysdevices
where Sysdevices.low <= sysusages.size +vstart
and Sysdevices.high >=sysusages.size+vstart-1
and dbid =2
and (status=2 or status=3)
Go
Use tempdb
Go
Sp_dropsegment ' Default ', Tempdb,master
Go
Sp_dropsegment ' logsegment ', tempdb,master
Go

Select dbid, Name,segmap
From Sysusages, sysdevices
where Sysdevices.low <= sysusages.size +vstart
and Sysdevices.high >=sysusages.size+vstart-1
and dbid =2
and (status=2 or status=3)
Go

Sp_cacheconfig tempdb_cache, ' 100M '
Go
Sp_poolconfig tempdb_cache, ' 50M ', ' 16K '
Go
Sp_bindcache ' Tempdb_cache ', tempdb
Go
Sp_helpcache Tempdb_cache

Select Name,id from Syscharsets

DBCC TRACEON (3604)
DBCC MEMUSAGE

1 Optimization of database installation
1.1 Application database using bare devices
If you are installing a database server under a UNIX (UnixWare) operating system, install the device (device) of the Sybase application database into a bare device. That is, when creating an application database device (such as: ivsp,db160,nap2000, etc.) with a bare device, point the file name to the corresponding file in the/dev/dsk/subdirectory. System database devices (such as master, etc.) still point to the file system.
1.2 Patch Program
To complete the Sybase database installation, please note that the original Sybase software will be with the latest patches, must be put on the patch, otherwise there will be some inexplicable problems. For NT version of the patch is a zip file, extracted to the c:/sybase subdirectory can be.
1.3 Installing SYBASE online Help
After installing SYBASE, there is a file under the installation directory:. /scripts/ins_syn_sql, execute the script on the server:
Sybase for UNIX version:./isql-usa-p-I.. /scripts/ins_syn_sql
Sybase for Winnt version: Isql-usa-p-i/sybase/scripts/ins_syn_sql
Once executed, you can connect SQL SERVER on any SYBASE client and get help online for any command:
Sp_syntax "keyword"
For example, Sp_syntax "alter" lists all commands that contain the "alter" character
2 Optimization of database configuration
2.1 Optimizing master Database
First enlarge the master equipment space, the initial default is 30M, increase to 150M. Then increase the master database space, the default data segments and log segments are 5M size, recommended to the data segment 100M, log segment 50M.
ALTER DATABASE Master on master=95
2.2 Optimizing the tempdb database
The tempdb device is created first, assigned to the tempdb database, the default tempdb database data segment and log segment are 2M sizes, and created on a master device, it is recommended to change to a data segment of 200M, log segment 50M, to be created on the tempdb device.
ALTER database tempdb on TEMPDB=200

SQL Sevrer All users share the tempdb database for both worksheets and temporary tables, and the main bottleneck in tempdb is disk i/0. The solution is to put tempdb on a faster device. In a UNIX environment, put tempdb in a file system without the original device. Because tempdb automatically creates a 2M database on a master device when it creates data, the best way to reduce conflicts is to remove tempdb from the master device. In order to achieve the above objective, the following approaches can be achieved:

1: Start SQL SERVER in Single-user state
Start Single User method: Dataserver-dmaster.dat-m
2: Login with SA
3: Create a dumb database in the file system.
4: Delete references to the existing tempdb database in the sysusages and Sysdatabase tables
5: Get the database ID of the dumb database and modify the references to tempdb by the sysusages and Ysdatabase tables accordingly
6: Restart the database
As an example of creating a 200M tempdb database in Newdevice, the following procedure is performed:
Create Database Newtemp on newdevice=200
Go/* Create a new database * *
BEGIN Tran
Go/* Start a transaction to prevent the entire SQL Server from running when the operation is wrong.
Delete sysusages where dbid = 2
Delete sysdatabases where dbid = 2
Go/* Deletes a reference to tempdb from the system table and executes it only in single user state * *
Select dbid from sysdatabases where name = ' Newtemp '

Go/* Get newtemp Database ID, assumed to be 10*/

Update sysusages Set dbid = 2 where dbid=10

Update sysdatabases set name= ' tempdb ', dbid=2 where name= ' newtemp '

Go/* Modify the reference to the database, and change the reference to Newtemp to the reference to tempdb * *

Select Name,dbid, from sysdatabases where name = ' tempdb '

SELECT * from sysusages where dbid = 2

Go/* Test modification is correct, correct submit, otherwise available rollback rollback * * *

Commit Tran

Go/* Modified successfully, reboot the system * *

This method is only valid for tempdb and cannot be used by other databases. Because tempdb is reinitialized each time when SQL Server starts.
2.3 Optimizing System Parameters
The following parameters are standard recommended values and can be modified according to actual conditions.
SQL script annotation to optimize system parameters
sp_configure ' total memory ', 100000 optimizing the memory of the database, should be based on different machine configuration settings, recommended to half the physical memory size. In Db_block, that is, each unit is 2k, the example above is 200M, and the default is 24M.
sp_configure "lock scheme", 1, "DataRows" system defaults to table-level locks, optimized for row locks
sp_configure "number of locks", 10000 enlarges maximum lock process, defaults to 5000.
(If you set an existing table (Tabel) as a lock for a row lock, execute:
ALTER TABLE TABLE_NAME LOCK DataRows)
sp_configure ' procedure cache percent ', 30 default value: 20 Recommended value: Procedure use a higher frequency when using a larger value, not more than 30
sp_configure "number of user connections", 100 maximum user connections, default to 25, 70k memory per connection
sp_configure ' number of devices ', 20 change maximum device file data to 15
sp_configure ' number of open databases ', 20 max Open database, you need to increase this parameter for multiple databases on a single database service, default is 15
sp_configure ' max online engines CPU ', 2 default value: 1 Recommended value: The number of CPUs using the actual machine
sp_configure ' total data cache size ', 60000 default value: 0 Recommended value: 30% using SQL Server memory
If Sybase startup is not normal after the above parameter changes, you can check Sybase's error log and modify the corresponding options in sybase.cfg to a smaller value.
Enclosure: Allocation of SYBASE SQL SERVER memory
1. SQL SERVER Executable code 3-4m
2. Static memory used by SQL SERVER 2.2-3.25m
3. Memory consumed by user configurable parameters, as shown in the following example (11.9.2 version):
The default values Occupy memory
Subscriber connections (user connections) 25 per 70k
Number of open databases (open database) 12 per 60k
Number of open objects (open objects) 500 per 1k
Number of indexes open (open Indexs) 500 per 1k
Lock number (locks) 5000 per approx 0.1k
Number of database devices (data device) 10 per 0.5k
4. The remaining portion is allocated to
Process caching (determined by procedure cache percent, default value is 20%)
Data caching (default is minus 1, 2, 3 items 80%)
2.4 Optimizing Database System Properties
Select the database properties in Sybase Center to select the following items in the Options option in the property.
Allow Select Into/bulk Copy
Truncate Log on Checkpoint
Checkpoint on recovery
Abort transction on full log
Free MySpace Accounting
Allow Nulls by default
Auto identity column in Non-unquie index
Method Two: Execute the following SQL script in Sqlplus
Such as:
sp_dboption mydb, "Abort tran on Log Full", True (sets the process to terminate when the log space of the database is filled, using the SA user)
sp_dboption mydb, "Select Into/bulkcopy", True
sp_dboption mydb, "Trunc Log on chkpt", True
sp_dboption mydb, "No chkpt on recovery", true
sp_dboption mydb, "No free Spaces ACCTG", true
sp_dboption mydb, "Allow Nulls by default", True
sp_dboption mydb, "Auto identity dbo use only", true
2.5 Creating threshold stored procedures
You can modify the following scripts or create multiple threshold stored procedures based on different applications, and specify the appropriate stored procedures when you configure thresholds.
CREATE PROCEDURE Sp_thresholdaction
@dbname varchar (), @segmentname varchar, @free_space int, @status int
As DUMP transaction @dbname with NO_LOG
Print "LOG DUMP: '%1! ' for '%2! ' dumped", @segmentname, @dbname
Go
2.6 Configuring multiple Thresholds
Method One:
Open Sybase and double-click the segment segments->; of the corresponding database (db) Logsegment, in the Thresholds page, you can set the threshold for automatically clearing the log. One of the last Chance is the system default opportunity threshold, which is the last chance to automatically clear the log when the system log free space is less than the value. When set, the threshold size can be set to about 20% of the total log space size.
Add more threshold values.
Method Two:
1, use the following instructions to detect the database log capacity (with the page)
Select sum (size) from master. sysusages where dbid=db_id ("database_name" and (SEGMAP&AMP;4) =4
2, using Sp_addthreshold to add a new threshold, the size of the log capacity of 50%, such as the above statement shows a value of 2048
Sp_addthreshold Database_name,logsegment,1024,proc_dump_display
Note: It is possible to cross the current threshold because of a large transaction, so you must add more than one threshold
Using the command SELECT @ @thresh_hysteresis to view the latency values of the database, such as the result is 64 pages, the next threshold is set to the "most recent threshold-(2*64)", then add two smaller thresholds to each of the thresholds you set.
3 optimization of the index
On the basis of good database design, it is necessary to use indexes efficiently and maintain indexes frequently, and the related contents of indexes are described below.
3.1 Creating an Index
Indexes are grouped into three categories: Clustered index (clustered indexes), nonclustered index (nonclustered indexes), overlay index (covering indexes)
Because indexing accelerates query speed, it slows down the speed of data updates. You can create a nonclustered index on another segment by building a table on a single segment, which improves operational performance on separate physical devices.
Create [unique][clustered|nonclustered] index index_name on table_name (column_name ...)
3.2 Reconstruction Index
As data rows are inserted, deleted, and data pages are split, some index pages may contain only a few pages of data, while in addition to performing bulk I/O, rebuilding nonclustered indexes can reduce fragmentation and maintain the efficiency of large I/O. Rebuilding an index is actually a re-organizing B-tree space. You need to rebuild the index in the following situations:
(1), data and usage patterns vary greatly.
(2), the order of the ordering has changed.
(3), to do a large number of inserts or has been completed.
(4) More disk reads than expected for queries that use large chunks of I/O.
(5), due to a large number of data modification, so that the data pages and index pages are not fully used, resulting in the use of space beyond the estimate.
(6) There was a problem with DBCC checking out indexes.
When a clustered index is rebuilt, all nonclustered indexes for this table are rebuilt.
3.3 Update of index statistics
When you create an index on a table that contains data, SQL Server creates a distributed data page that holds two statistics about the index: the distribution table and the density table. The optimizer uses this page to determine whether the index is useful for a particular query. But this statistic is not dynamically recalculated. This means that when the data for a table changes, the statistics are likely to be outdated, which affects the optimizer's pursuit of the most promising goals. Therefore, you should run the UPDATE STATISTICS command in the following situations:
(1), the insertion and deletion of data rows modify the distribution of the data.
(2) Adding data rows to a table that deletes data with TRUNCATE table.
(3), modify the value of the indexed column.
4 Query optimization
4.1 Not IN clause
I do not know if you like to use ' not in ' such an operation, if it is, then try to use (not) EXISTS substitution.
Example:
Statement 1
Select Dname, deptno from dept WHERE Deptno not in (SELECT deptno from EMP);
Statement 2
Select Dname, Deptno from dept where not EXISTS (select Deptno from emp where dept.deptno = Emp.deptno);
Obviously, 2 is a lot better than the 1 execution performance, because the EMP is full table scan in 1, which is a waste of time. And there is no use of the EMP index in 1 because there is no WHERE clause. The statements in 2 for the EMP are range scan.
4.2 Mass Query
In the mass query, as little as possible with format conversion.
If you use
WHERE a.order_no = B.order_no
And not
WHERE To_number (substr (A.order_no, InStr (B.order_no, '. ')-1) = To_number (substr (A.order_no, InStr (B.order_no, '. ')-1)
3 Query mass data can be used optimizer hints, such as/*+ordered * *
Such as
SELECT/*+ Full (EMP) * * E.ename from EMP E WHERE e.job = ' clerk ';
Instead of
SELECT e.ename from EMP E WHERE e.job | | ' = ' clerk ';