Alphabetical list of configuration parameters (used by sp_configure) that control, affect, use, or report ASE memory / ASE cache configuration.

• additional network memory - specifies the maximum size of additional memory that can be used for network packets that are larger than the default packet size.
• allow resource limits - specifies the use of resource limits.
• audit queue size - specifies the size of the audit records / audit queue.
• default network packet size - specifies the default packet size for all ASE Server users.
• event buffers per engine - specifies the number of events per ASE Server engine that can be monitored simultaneously by ASE Monitor.
• max number network listeners - specifies the maximum number of network listeners allowed by an ASE Server at one time.
• max online engines - specifies the maximum number of ASE Server engines that can be online at any one time in an SMP environment.
• max SQL text monitored - specifies the total number of bytes an ASE Server allocates for each user task to store SQL text.
• memory per worker processes - specifies the amount of memory (in bytes) used by worker processes. Each worker process requires memory for messaging during query processing. This memory is allocated from a shared memory pool.
• number of alarms - specifies the number of alarm structures allocated by an ASE Server.
• number of aux scan descriptors - specifies the number of auxiliary scan descriptors available in a pool shared by all users on an ASE server.
• number of devices - specifies the number of database devices an ASE Server can use.
• number of large i/o buffers - specifies the number of allocation unit-sized buffers reserved for performing large I/O for certain ASE Server utilities.
• number of mailboxes - specifies the number of mailbox structures allocated by an ASE Server.
• number of messages - specifies the number of message structures allocated by an ASE Server.
• number of open databases - specifies the maximum number of databases that can be open simultaneously on an ASE Server.
• number of open indexes - specifies the maximum number of indexes that can be used simultaneously on ASE Server.
• number of open objects - specifies the maximum number of objects that can be open simultaneously on an ASE Server.
• number of open partitions - specifies the number of partitions that an ASE Server can access at one time.
• number of remote connections - specifies the number of logical connections that can be open to and from an ASE Server at one time.
• number of remote logins - controls the number of active user connections from ASE Server to remote servers.
• number of remote sites - determines the maximum number of remote sites that can access a ASE server simultaneously.
• number or worker processes - specify the maximum number of worker processes that ASE servers can use for all simultaneously running parallel queries.
• permission cache entries - determines the number of cache protectors per task.
• procedure cache size - specifies the size of your procedure cache in 2K pages, regardless of the server's logical page size.
• remote server pre-read packets - etermines the number of packets that will be "pre-read" by a site handler during connections with remote servers.
• stack guard size - specifies the size (in bytes) of the stack guard area. The stack guard area is an overflow stack of configurable size at the end of each stack.
• stack size - specifies the size (in bytes) of the execution stacks used by each user process on an ASE Server.
• total logical memory - reports the actual memory requirement for ASE. Excluding any memory required for the executable.
• user log cache size - specifies the size (in bytes) for each user's log cache.

To report/display the ASE cache configuration use sp_helpcache

To configure the ASE cache use sp_cacheconfig

Each connection requires ~350KB of memory. So the maximum number is limited by the physical memory whereas the ASE server allows to create up to 32.767 connnections.

To verify how much connections are used. Execute the stored procedure:

sp_monitorconfig "number of user connection"

Examlpe output of sp_monitorconfig "number of user connection"

Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt
------------------------- ----------- ----------- ------- ----------- -----------
number of user connection         996           4   0.40          125           0
 

The amount of memory required per user connection varies by platform. It also depends on other configuration variables like:

• default network packet size
• stack size
• stack guard size
• user log cache size

Changing any of these parameters changes the amount of space used by each user connection

Version ASE 12.5, ASE 15.0.3 and higher.

This query displays license information inside the Sybase IQ:

SELECT * FROM sa_eng_properties() WHERE PropDescription LIKE ('Name of %')

This coming Thursday and Friday, Embarcadero and ISUG are hosting a free webcast on Sybase ASE Query Optimization. Two well-known Sybase experts, Peter Dobler and Kevin Sherlock, will be taking your questions live. This looks like a good one as they are going to go into areas that aren’t covered in the manuals. Here’s the link for more information and registration: http://www.embarcadero.com/webinars/sybase-ase-query-optimization

SELECT
    a.SPID
  , convert(char(15), a.Login) AS Login
  , convert(char(15),a.Application) AS Application
  , convert(char(20),a.Command) AS Command
  , convert(char(20),a.DBName) AS Dbname
  , b.CpuTime
  , b.WaitTime
  , b.PhysicalReads
  , b.LogicalReads
  , b.PagesModified
      FROM
          master..monProcess a
        , master..monProcessStatement b
            WHERE a.SPID = b.SPID
            AND a.KPID = b.KPID

select w.WaitTime, w.Waits, i.Description
  from monSysWaits w, monWaitEventInfo i
  where w.WaitEventID = i.WaitEventID
  order by WaitTime
  desc

These can all be ran on ASE version 12.5.0.3 and above.

select ProcName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),DBName = isnull(db_name(DBID), "UNKNOWN"),ElapsedTime = datediff(ms, min(StartTime), max(EndTime))
  from master..monSysStatement
  group by SPID, DBID, ProcedureID, BatchID
  having ProcedureID != 0
  order by 3

Note: The output contains 1 row for every run, meaning we may see 1 sp have 3 rows. You may create a temp table to store these values and select that temp table to get the averages.

These can all be ran on ASE version 12.5.0.3 and above.

select Object = convert(char(15), object_name(ObjectID, DBID)), IndexID, OptSelectCount, UsedCount, Operations
  from master..monOpenObjectActivity
  where ObjectID > 99
  and IndexID > 0
  order by UsedCount

Note: If you want to check the system tables statistics, just put ObjectID < 100 instead of ObjectID >99.

These can all be ran on ASE version 12.5.0.3 and above.

select SPID, suser_name(ServerUserID), SequenceInBatch, SQLText
  from master..monSysSQLText
  where SPID=<spid>
  order by SequenceInBatch

NOTE: Why use this? We've all been used to executing dbcc sqltext
to get the SQL command. But you may have noticed that sometimes, if not
most of the time, we only get a part of the whole sql command. With the
above SQL text, you are sure to get the complete text.

These can all be ran on ASE version 12.5.0.3 and above.

Re-Sync after Primary DB load

When loading a dump into a primary database, the connections to the primary and the warm-standby databases need to be dropped and rebuild. In this case, also disable the REP-Agents on both DBs:

use <DB>
go
sp_config_rep_agent <DB>,"disable"
go

Do this step after dropping the replication connections. After loading the dump into the primary database, before setting up replication, don't forget to:

• disable the RepAgent again
• drop the maintenance alias user again and re-add it as a regular user.

When just resyncing a Warm-Standby database, follow these instructions:

Sync WS replication

• On primary replication server, drop physical connections:

drop connection to <rep.srv._ws>.<replicated.db>
go

• disalbe rep_agent on WS

use  <replicated.db>
go
sp_config_rep_agent <replicated.db>, "disable"
go

• drop <replicated.db>_maint user or alias in WS db

use  <replicated.db>
go
sp_dropalias <replicated.db>_maint
go

• drop <prim.db>_maint user or alias in prim. db

sp_dropalias <prim.db>_maint
go

• sp_adduser _maint to db

sp_adduser <prim.db>_maint
go

• check logical connection name on REP.srv.

admin logical_status
go

• rebuild WS connection to WS with rs_init
  • When asked if this DB is a replicated DB, answer YES
  • Setup a "physical connection for a logical connection"
  • Setup a standby database
  • "initialise using dump and load"? -> YES
  • "use dump marker"? -> YES

• drop <replicated.db>_maint user on prim

sp_dropuser <prim.db>_maint
go

• add alias (user <prim.db>_maint) to dbo

sp_addalias <prim.db>_maint, dbo
go

• dump and load db

dump database <prim.db> to "/dumpdir/<prim.db>
go

load database <replicated.db> from "/dumpdir/<prim.db>.bck"
go

• set db online

online database <replicated.db>
go

• resume connection to WS

resume connection to <rep.srv._ws>.<replicated.db>
go

If you have any problems resuming the connection and you find messages like these in the Rep-Errorlog:

"The DSI thread for database '<rep.srv._ws>.<replicated.db>' is being
shutdown. DSI received data server error #17232 which is mapped to
STOP_REPLICATION. See logged data server errors for more information.
"The error was caused by line #158 of stored procedure 'sp_dropuser'
on the data server mapped from input command #2 (or a command before
it) of the failed transaction."

or:

"The DSI thread for database '<rep.srv._ws>.<replicated.db>' is being
shutdown. DSI received data server error #17234 which is mapped to
STOP_REPLICATION. See logged data server errors for more information.
The error was caused by line #167 of stored procedure 'sp_addalias'
on the data server mapped from input command #2 (or a command before
it) of the failed transaction."

then you probably did something wrong while dropping and recreating the 'maintenance user'. Repeat the following command until you find a different error message. Doing this, you will skip the "create-" and "drop-user" commands, which you don't need on the replicated system.

resume connection to <rep.srv._ws>.<replicated.db> skip tran
go